Use Excel:

South Central Airlines (SCA) operates a regional flight between Atlanta and Charlotte. The regional jet holds 100 passengers, and currently SCA books only up to 100 reservations. Past data shows that SCA sells all 100 reservations but usually not all the passengers show up to the flight. As a result, with 100 reservations, the flight is often being flown by empty seats. To capture additional profit, SCA is considering an overbooking strategy in which they would accept 105 reservations even though the airplane holds 100 passengers. SCA believes that it will be able to always book all 105 reservations. The probability distribution for the number of passengers showing up is uniform from 90 to 105 passengers. The average profit SCA receives per reservation for each flight is around $100 but fluctuates based on additional charges such as extra luggage or pre-purchased in-flight food/beverage sales and can be represented with a normal distribution with mean $100 and standard deviation of $4. The airline will also incur a cost for any passenger denied seating on the flight. This cost, which covers the added expenses of rescheduling the passenger as well as loss of goodwill, is estimated to be $200 per passenger. Develop a spreadsheet simulation model for this overbooking system.

a. What is the average net profit for each flight with the overbooking strategy?

b. What is the probability that the net profit with the overbooking strategy will be less than the net profit without overbooking?