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?
The probability distribution for the number of passengers showing up when 52 reservations are accepted is estimated as follows:

3. What type of random variable is the number of passengers that show up?
A. Discrete random variable
B. Continuous random variable

4. What is the likelihood that overbooking would happen?
Create a simulation model to capture the impact of uncertainty in number of customers showing up on total profit and total cost of overbooking. Run 10,000 trials of this simulation.

5. What is the average profit with this overbooking strategy?

6. What is the standard deviation of profit with this overbooking strategy?

7. What is the likelihood that SCA would be able to earn a profit of $5100 or more?

SCA knows that the cost of overbooking per passenger is going to be uncertain as it might be able to negotiate different amounts with different passengers. It is estimated that when there is any customer overbooked, the TOTAL cost of overbooking is normally distributed with a mean equal to "350*number of overbooked customers", and a standard deviation of "100*square root of number of overbooked customers". Implement this information in your simulation model. (Hint: When generating numbers for total cost of overbooking, don't forget the IF function for checking if the airline is overbooked, and if that was the case, generate the random number!)

8. What type of random variable is the total cost of overbooking?
A. Discrete random variable
B. Continuous random variable

9. What would be the average profit when uncertainty in overbooking cost is incorporated?

10. What is the average profit whenever overbooking does happen?

11. The likelihood for number of passengers showing up (as given in the table earlier) are obtained based on some managerial estimates. To better reflect on this uncertainty, data on all past flights from LAX to Denver has been studied and it is found that 4.6% of all passengers that reserved a ticket did not show up on the flight reserved. Which of the following best describes a probability distribution for the number of passengers that would show up in the considered overbooking strategy (i.e. reserving 52 seats on a flight with 50 seats)?
A. Binomial distribution with trial probability of 95.4% and number of trials of 52
B. Binomial distribution with trial probability of 4.6% and number of trials of 52
C. Binomial distribution with trial probability of 4.6% and number of trials of 50
D. Binomial distribution with trial probability of 95.4% and number of trials of 50
E. Normal distribution with mean of 95.4 and standard deviation of 52
F. Normal distribution with mean of 49.6 and standard deviation of 0.046
SCA also thinks that marginal profit per passenger to be uncertain as well, as some passengers purchase early at a discounted price but business passengers buy later or with more flexibility leading to higher margins. The data file provided shows margins obtained for 200 passengers flying on this flight in the past.

12. What type of random variable is the profit margin per passenger?
A. Discrete random variable
B. Continuous random variable

13. Draw a histogram of the data. Which of the following distributions could potentially be used to best describe this data?
A. Uniform distribution
B. Normal distribution
C. Binomial distribution
D. Exponential distribution