In: Finance
Question 5 (Total of 24 marks)
Hit-and-Miss Airlines are considering providing a new daily service between two cities. The aircraft has a maximum capacity of 200 passengers and each flight incurs a fixed cost of $27,000 regardless of the number of passangers. In addition, a cost is also incurred of $75 per passanger to cover such things as catering, booking, baggage handling.
The company is thinking of charging $225 per ticket. How many passengers will the airline need on each flight to break even? Find the break-even point algebraically and illustrate it using an EXCEL graph. Attach the printout or copy your EXCEL graph into your assignment submission. Based on your analysis, will Hit-and-Miss realize a profit or a loss if 160 seats are sold for a particular flight? Explain briefly.
EXCEL Instructions: Create a column called Number of Passengers and in that column enter values from 0 to 200 in increments of 20. Then create two more columns, one for Total Cost and another for Total Revenue. Enter appropriate formulae in EXCEL to obtain the total cost and total revenue corresponding to each value in the Number of Passengers column. Highlight the resulting three sets of numbers and go to the Chart Wizard to obtain the graph. Make sure that your graph has been labelled appropriately (i.e. title, axis labels, legend). Please refer to Topic 3 in the EXCEL Booklet for further instructions on entering formulae and graphing in EXCEL.
Break even point is the point where revenues = total costs
Let no. of pasengers be "x". Total cost = fixed costs+variable costs
= 27000+75x.
Revenue = 225x
Thus at breakeven, 225x = 27000+75x
or 150x = 27000
or x = 180.
Thus in order to break even the airlines will need 180 passengers.
The amount of 160 passengers is less than the break even requirement of 180 and so there will be a loss. This can be proved mathematically as well. Revenue = 160*225 = 36,000. Costs = 27000+(75*160) = 39,000. Thus loss = 36,000 - 39,000 = $3,000 loss.
Excel table:
No. of passengers | Total cost | Total revenue |
0 | 27,000.00 | 0.00 |
20 | 28,500.00 | 4,500.00 |
40 | 30,000.00 | 9,000.00 |
60 | 31,500.00 | 13,500.00 |
80 | 33,000.00 | 18,000.00 |
100 | 34,500.00 | 22,500.00 |
120 | 36,000.00 | 27,000.00 |
140 | 37,500.00 | 31,500.00 |
160 | 39,000.00 | 36,000.00 |
180 | 40,500.00 | 40,500.00 |
200 | 42,000.00 | 45,000.00 |
Excel graph: