Please note that these are predicted responses to the scenario published for the January 2011 examination. At this stage I do not know which questions might be asked or if the scenario is indeed similar to the practice one.
Activity 1 : Understanding the problem (13 marks - these must relate directly to the model and problem being solved)• there are 4 return routes
• 2 short haul routes 7 times per week
• 2 long haul routes 2 times per week
• the long haul to Canberra stops to refuel in Dubai and passengers can embark/disembark
• 366 is the maximum passenger capacity for both short and long haul routes
• short haul have 6 staff
• long haul have 8 staff
• 20 kg of luggage on all flights per passenger
• planes have a lifespan of 300,000 flying hours
• planes have a full service check every 5000 flying hours
• each plane has identical fuel capacity, baggage space and fuel consumption
• the planes of the short haul and long haul flights are rotated to extend the life of each plane
Decisions that need to be made (3 marks)
• the cost of the tickets
• Using the survey data to find out at what cost per mile passengers start to drop out
• the maximum profit which can be made
• the number of passengers on each flight
Assumptions (3 marks)
• that the model uses kms as its unit of measurement consistently
• the cost of the tickets which makes up total revenue is in actual fact the price of the ticket
• that the staff maximum flying hours is limited to the hours given
• the planes flying hours are limited to the hours given
• that the aircraft were purchased freehold and no loans need to be repaid
• 366 passengers are flying in one class; i.e. no business or first class seats
Activity 2 : Sources of Information (15 marks - must relate to the data) or Method of Collecting DataWhere is the data sourced from:
A marketing survey to identify how much people are prepared to pay for the flights and then calculated as a cost/km.
Distances are derived from known distance data; previous flight records
Factors affecting Accuracy
• Is the logic of the model correct?
• Are the data formats appropriate?
• Is the syntax of the formulae correct?
• Are the cell references correct?
• Is the data reliable and consistent?
• How reliable was the survey technique. Identify potential flaws
• Typo – user data entry error
• More than on data source to choose from? State the highest/longest/most repeating/most consistent data (unless they look like anomalies!) Why are you choosing this source? So that all costs are catered for and to minimise an unanticipated shortfall in profit.
What could invalidate the model and the profits estimated? / what has not been taken into consideration?
• Strikes and bad weather could keep passengers and crew stranded and therefore compensation would need to be paid minimising profit.
• An airline accident would require large payouts and could ground the aircraft until the investigation is over
• Fuel prices could increase dramatically as a result of political instabilities/wars
• More breakdowns than anticipated could affect the profit
• Distances could increase as a result of unforeseen weather such as a volcanic ash cloud
Activity 3 : (29 marks) Complete the model and find the maximum profitChoose the most appropriate data (the longest distances/highest fuel price/highest estimated cost/km/the most frequently occurring same sets of data, consistency e.g. km not miles)
Import Passenger data into the Passenger worksheet
If the data is in an incorrect format; type in, use fill handle and format correctly.
Import the distance data into the Distances worksheet
In Fuel, enter the fuel cost > multiply the fuel cost with the fuel required (using $)
Staff worksheet Staff cost per km=annual salary / km per year
Cost Revenue Sheet
Flight Profit = Total Revenue-Total Cost
Weekly Profit = If (D8 = “Short Haul”, Flight Profit *7, Flight Profit*2)
To understand how the number of tickets sold for a specific route was found
=VLOOKUP(E8/INDEX (Distances!$A$6:$G$12,MATCH(B8,Distances!$A$6:$A$12,0),MATCH(C8,Distances!$A$6:$G$6,0)),Passengers!$A$8:$C$108,IF(D8="Short Haul",2,3))
lookup the cost of the tickets (e.g. try 655) divided by London Madrid distance (in this case 1235)
The result is 0.53
so lookup 0.53
from the passenger table (A8:c108)
return the value from the 2nd column if it’s a short haul (the no of tickets sold is 317 because Lon to Madrid is short haul)
or return the value from the 3rd column if it’s a long haul
To find the distance for a specific route (London to Madrid in this case)
=(INDEX (Distances!$A$6:$G$12,MATCH(B8,Distances!$A$6:$A$12,0),MATCH(C8,Distances!$A$6:$G$6,0)))
To estimate the cost of the tickets for a specific route and maximise profit
Try £100, £500, £700, £800, £1000 - see where the profit hits its peak. Between £500 and £1000? Try Between £800 and £1000? How about £900? And then narrow it down further.
The question will possibly have targets - > £2m gets you 2 marks, > £3m gets you 3, > £5m gets you 4.
You can use goal seek to find the cost of the tickets and number of seats sold in order to reach the target they ask for.
e.g. Set Cell (Select the profit cell that needs to hit a target)
To Value (the target value)
By changing Cell (select the cost of tickets cell)
In the passenger sheet you could work out the cost of the tickets by multiplying the cost/km by a distance e.g. London Madrid = 1235 then finding the maximum value. However, this is faulty because all the cost variables are not included in this calculation and it is the balance between all the variables that gives us the desired result. We are not seeking the maximum cost/km.
Using the maximum seats figures in the passengers worksheet the profit works out to £3.5 million if the fuel is £1, but much more in the costrevenue sheet so you cannot rely on a formula that looks at the highest cost only as not all factors have been taken into consideration such as the weight of the passengers which directly affects fuel consumption.
There is a trade-off between the number of passengers flying on the flight and the price of the ticket. Finding the maximum profit for the airline requires a balance between the number of seats sold and the maximum profit that can be made, given that there are many other factors involved including the number of staff on the aircraft and the weight of the baggage and the passengers. Therefore, the plane does not have to have maximum seat occupancy in order to make the most profit.
Activity 4 : Report (25 marks) / LetterHaving considered both the results of your model and other factors, you will need to use your knowledge to make a decision. As a knowledge worker your task is not only to make recommendations based on the information you have selected or derived, but to justify your decisions to others.
You could be asked to write a report to someone - usually the person who has hired you and usually to justify your decision. You could be asked to make graphical data . You could also be asked to write a letter to the airline or a travel agent. You could be asked to produce a pie chart showing seat occupancy for certain flights.
To show that you understand the difference between max seat occupancy and max profit you should compare the prices to fill up the plane at the highest possible price, or the highest price to draw the biggest profit (which we can expect will not fill the plane).
Is it best to go for maximum profit as the tickets not sold could be offered at reduced price to staff/corporate partners etc in order to further increase profits? But how will this affect the fuel consumption and therefore profitability?
As a minimum your report should include:
• A sensible title and subheadings
• Introduction
o An introduction explaining what the report is about
• Decisions
o The decisions you came to and the profit which can be achieved
• Justification
o An explanation as to why you made these decisions
• Other Considerations
o Any other factors which may need to be considered
• Conclusion
o A concluding statement
A letter should have
A letter date
Appropriate salutation and close Dear Passengers Yours faithfully Sami
Senders address
Reason for letter Re: The cost of flights
Structured in paragraphs
Correct information given
Activity 5 - Evaluation (6-7 Marks) of the modelThe ease of use!!! REMEMBER NOTHING IS EASY!! Speak critically e.g. difficulty with testing model with numbers at the beginning…not knowing where to start…random numbers then moved to a more logical system . Finding values in between the ones given in the passenger sheet.
How did it help you find a solution?
What else you could do to achieve this? Set out the table so that different values could be tested for each route.
"If you did this again, how would you improve this" with data and source.
The report could be automatically generated by mail merging the data into a prepared report.
You would check the distance sources against an aviator’s equivalent to Google Maps. You would check the survey data or carry out the survey yourself under strict controls.
Standard ways of working (2 Marks)
Authenticating work (All pages have activity number, name, candidate number, centre number)
Appropriate structure (pages in correct order and folder assembled correctly)
Kinds of what-if analysis - There are three kinds of what-if analysis tools in Excel: scenarios, data tables, and goal seek. Scenarios and data tables take sets of input values and project forward to determine possible results. Goal seek differs from scenarios and data tables in that it takes a result and projects backwards to determine possible input values that produce that result.
On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
Click Add.
In the Scenario name box, type a name for the scenario.
In the Changing cells box, enter the references for the cells that you want to specify in your scenario.
For example, if you want to see how changing the values of cells B1 and B2 will affect the outcome of a formula based on those cells, enter B1,B2.
Click OK.
In the Scenario Values dialog box, type the values that you want to use in the changing cells for this scenario.
To create the scenario, click OK.
If you want to create additional scenarios, repeat steps 2 through 8.
After you finish creating scenarios, click OK, and then click Close in the Scenario Manager dialog box.
When you display a scenario, you switch to the set of values that are saved as part of that scenario.
The scenario values are displayed in the cells that change from scenario to scenario, in addition to the results cells.
On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
Click the name of the scenario that you want to display.
Click Show.
On the Data tab, in the Data Tools group, click What-If Analysis, and then click Scenario Manager.
Click Summary.
Click Scenario summary or Scenario PivotTable report.
In the Result cells box, enter the references for the cells that refer to cells whose values are changed by the scenarios.
Separate multiple references with commas.
.jpg)
