Goak seeking Microsoft Excel exercise

In a separate, excel sheet. Labeled Airline An airline has a popular flight from New York to Pittsburgh. A one-way ticket for this flight currently costs $320. The plane that flies this route holds 180 passengers. The fixed cost of flying this route (i.e., flying an empty plane) is 17,000 and the airline incurs a cost of $58 (for food, beverages) for every passenger flying on the plane. The airline will over sell this flight route, as past data has shown that on average 95% of people who reserved a ticket actually show up for the flight (this is known as ticket utilization). People who have reserved (and therefore paid for) a ticket for the flight are not provided a refund or credit if they do not show up for the flight. However, in situations where the flight is overbooked, the airline must reimburse passengers (who show up for the flight but cannot board due to overbooking) for their flight (i.e., $320) and pay an additional $200 in compensation costs 1 )Determine the number of reservations required to break even on this flight. Use the appropriate Excel method to accomplish this task as efficiently as possible. (Tip: Create a model that can be used for the remaining questions in this part). (5 Marks) 2) This question builds on your work in the previous question. Given the recent price wars in the airline business your company realizes that it may need to reduce the current price on the Chicago to Atlanta route. They have sked you to provide them with revenue and profit data for this route based on different ticket prices. They have indicated that the average number of reservations for this route is 200, and therefore suggest you use that figure in your calculations. Using an appropriate data table, calculate revenue and profit values for Chicago to Atlanta flights with ticket prices ranging from $250 to $350, in $10 increments. Present this information on a worksheet titled "Part C-2" with a data table showing your findings as well as a graph that can demonstrate the effects that changing prices will have on profitability. Include the results in the memo to management. (10 Marks) 3)This question also builds on your work in question C-1. Management realizes that they need to see profit figures when both the ticket prices and number of reservations vary. Therefore, they ask you to build a simple DSS that provides this information, varying ticket prices from $250 to $350 in $10 increments, and varying the number of ticket reservations from 60% of plane capacity to 120% of plane capacity in 10% increments. For this question, you can also assume an average number of reservations of 200. Show your work on a worksheet labelled "Part C-3 Include in the memo to management which combination of ticket price and number of reservations would give the airline the maximum profitability. (10 Marks)