Question 5 (Total of 18 marks) Air Atlantic is considering a new service between two cities. Its existing planes, each of which has a capacity of carrying 240 passengers, could be used for one flight a week with fixed costs of $30,000 and variable costs amounting to 50% of the ticket price.

(a) (10 marks) Air Atlantic is thinking of charging $200 per ticket. How many passengers will the airline need on each flight to break even? Find the break-even point algebraically and by using an EXCEL graph. Attach the printout or copy your EXCEL graph into your assignment submission. EXCEL Instructions: Create a column called Number of Passengers and in that column enter values from 0 to 400 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 Insert tab (or Chart menu) to obtain an appropriate diagram. 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.

(b) (4 marks) Based on your analysis from part (a), and without any additional calculations, will Air Atlantic realize a profit or a loss if 240 seats are sold for a flight? Does the break-even number of passengers from part (a) seem reasonable? Explain briefly.

(c) (4 marks) What price per seat should Air Atlantic charge to break-even on a full flight? Find the break-even ticket price algebraically.