The Chicago to Atlanta flight is a popular route for our airline. A one-way ticket for this flight currently costs $420.

[answerhappygod]

The Chicago to Atlanta flight is a popular route for ourairline. A one-way ticket for this flight currently costs $420. TheBoeing 737 MAX that flies this route holds 180 passengers. Thefixed cost of flying this route (i.e., flying an empty plane) is$17,000, and the airline incurs a variable cost of $100 (food,beverages) for every passenger flying on the plane. Quite often,the airline will oversell this flight route, as past data has shownthat on average only 99% of people who reserved a ticket actuallyshow up for the flight (i.e., this is known as ticket utilization).People who have reserved (and therefore paid for) a ticket for theflight are not provided a refund or credit if they do not show upfor the flight. However, in situations where the flight isoverbooked, the airline must reimburse passengers who show up forthe flight but cannot board due to overbooking; for their ticket(i.e., $420) plus pay an additional $550 in compensation costs. 1)Determine the number of reservations required to break even on thisflight. Provide your answer on a new worksheet titled “PartB-1”.
2) This question builds on your work from B-1. Competition isfierce for the Chicago to Atlanta route, and the airline realizesthat it may need to reduce the current ticket price. They ask youto provide projections for number of reservations, number of deniedpassenger check ins (due to overbooking), penalties, sales, netincome, and profit margin for this route at different ticketprices. The average number of reservations for this route is 184when the ticket price is $420, use those figures as a base for yourcalculations. The airline conducted a price sensitivity analysisand determined that for every $20 increase in ticket price, demandwill decrease by 1 ticket reservation. Similarly, ticket sales willincrease by 1 reservation for every $20 decrease in ticket price.Using an appropriate data table, calculate the number ofreservations, number of passengers denied check ins (due tooverbooking), total penalties, total sales, net income, and profitmargin (as a percentage) for ticket prices ranging from $100 to$500, in $20 increments. Present this in a worksheet titled “PartB-2” (Hint: you can copy your model from “Part B-1” and adjust theformulas if needed). Create a graph that visualizes the effectsthat changing prices will have on profitability. The graph shouldonly include sales, net income, and profit margin at the differentticket prices. Include an analysis of these results in your memo tomanagement, highlighting the ticket prices that would give theairline the maximum profitability. Support your analysis in yourmemo with a meaningful graph/visualization. Tip: o Don’t roundup/down values in the model or th