Whenever needed, enter your answers with 2 decimal places. Even though exact answers change every time the formulas on the spreadsheet are re-evaluated, it is OK. Just enter the number that you get, they are graded with an appropriate level of tolerance.
South Central Airlines (SCA) operates a commuter flight between LAX and Denver. The airplane holds 50 passengers, and currently SCA books only up to 50 reservations. Past data shows that SCA always sells all 50 reservations but that, on average, two passengers do not show up. As a result, with 50 reservations, the flight is often being flown with empty seats. To capture additional profit, SCA is considering an overbooking strategy in which they would accept 52 reservations even though the airplane holds only 50 passengers. SCA believes that it will be able to always book all 52 reservations.
SCA receives a marginal profit of $100 for each passenger who books a reservation (regardless of whether or not they show up). The airline will also incur a cost for any passenger denied seating on the flight which is on average $250. This cost includes the added expenses of rescheduling the passenger as well as a negotiated compensation amount to be paid to the customer for their inconvenience. Develop a spreadsheet model for this overbooking system that calculates the total profit for any given number of passengers that show up.
Hint: In developing the spreadsheet, separately calculate the total revenue and the total cost of overbooking. You can use the IF function to facilitate calculation of total cost of overbooking.
1. If 48 passengers show up, what would be the total profit for SCA?
2. If 52 passengers show up, what would be the total profit for SCA ?