An airline operates a commuter plane that holds 30 passengers. For every seat that has a passenger on the flight, the airline makes a $230 profit. The airline takes 30 reservations for the flight but has an average of a couple passengers not showing up. Their average profit per flight has been about $5,975.

Create a simulation to evaluate an overbooking strategy in which the airline would accept 32 reservations even though the airplane holds only 30 passengers. The probability distribution for the number of passengers showing up when 32 reservations are accepted is as follows:

Showing UP                           Probability

      28                                               .11

      29                                             .13

      30                                              .42

      31                                               .24

      32                                               .10

The airline will incur a cost of $375 for any passengers denied seating on the flight. Once you complete the first row (Trial 1) of your simulation, copy and paste the rows below so you complete 100 trials.

In cell F11, calculate the average net profit from the 100 trials. In cell I11, recommend either 30 reservations or 32 reservations include the difference in the average profits.

Make sure all your Excel entries in row 16 are referring to cell locations and using “$” where needed.

Column C represents the number of passengers showing up. Column D represents the number of passengers allowed on the flight (there are only 30 seats). Column F represents the number of passengers that were denied a seat. Column G represents the overbooking cost.