The Chicago to Atlanta flight is a popular route for our airline. A one-way ticket for this flight currently costs $420. The Boeing 737 MAX 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 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 shown that on average only 99% of people who reserved a ticket actually show up for the flight (i.e., 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 ticket (i.e., $420) plus pay an additional $550 in compensation costs. 1) Determine the number of reservations required to break even on this flight. Provide your answer on a new worksheet titled Part B-1.

2) This question builds on your work from B-1. Competition is fierce for the Chicago to Atlanta route, and the airline realizes that it may need to reduce the current ticket price. They ask you to provide projections for number of reservations, number of denied passenger check ins (due to overbooking), penalties, sales, net income, and profit margin for this route at different ticket prices. The average number of reservations for this route is 184 when the ticket price is $420, use those figures as a base for your calculations. The airline conducted a price sensitivity analysis and determined that for every $20 increase in ticket price, demand will decrease by 1 ticket reservation. Similarly, ticket sales will increase by 1 reservation for every $20 decrease in ticket price. Using an appropriate data table, calculate the number of reservations, number of passengers denied check ins (due to overbooking), total penalties, total sales, net income, and profit margin (as a percentage) for ticket prices ranging from $100 to $500, in $20 increments. Present this in a worksheet titled Part B-2 (Hint: you can copy your model from Part B-1 and adjust the formulas if needed). Create a graph that visualizes the effects that changing prices will have on profitability. The graph should only include sales, net income, and profit margin at the different ticket prices. Include an analysis of these results in your memo to management, highlighting the ticket prices that would give the airline the maximum profitability. Support your analysis in your memo with a meaningful graph/visualization. Tip: o Dont round up/down values in the model or th