Download the posted Excel spreadsheet and include your name within the name of the file when you save it. Use the spreadsheet to create a simulation based on the following information. 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 your best estimated probability of the average profit being greater or lower than the given avg. profits for 30 reservations 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.

?flight simulation Hw w18 Q- Search Sheet Insert Page Layout Formulas Dta Review View Out Copy ? AutoSum Times New R 14 . A-Av ! HJ , wrap Text Merge & Center- $. ,% ? +0400 Conditional Format Cel Insert Delete Format Sort & Clear Formatting as Table Styles Per Pass Ass URN 28 29 30 Mean prof 32 #of Total Trial # Demand On Profit Overbooked OB CostNet Profit Sheet1 + 100%