Problem 11-11 South Central Airlines (SCA) operates a commuter flight between At
ID: 2746300 • Letter: P
Question
Problem 11-11
South Central Airlines (SCA) operates a commuter flight between Atlanta and Charlotte. The regional jet holds 50 passengers and currently SCA only books up to 50 reservations. Past data shows that SCA always sells all 50 reservations, but 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. The probability distribution for the number of passengers showing up when 52 reservations are accepted is estimated as follows:
SCA receives a marginal profit of $100 for each passenger who books a reservation (regardless whether they show up or not). The airline will also incur a cost for any passenger denied seating on the flight. This cost covers added expenses of rescheduling the passenger as well as loss of goodwill, estimated to be $150 per passenger. Develop a spreadsheet simulation model for this overbooking system. Simulate the number of passengers showing up for a flight (Do 100 simulations). Assume the airline collects revenue from every ticket booked, even if the passenger doesn't show up.
Problem 11-11
South Central Airlines (SCA) operates a commuter flight between Atlanta and Charlotte. The regional jet holds 50 passengers and currently SCA only books up to 50 reservations. Past data shows that SCA always sells all 50 reservations, but 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. The probability distribution for the number of passengers showing up when 52 reservations are accepted is estimated as follows:
PassengersShowing Up
Probability 48 0.05 49 0.25 50 0.5 51 0.15 52 0.05
SCA receives a marginal profit of $100 for each passenger who books a reservation (regardless whether they show up or not). The airline will also incur a cost for any passenger denied seating on the flight. This cost covers added expenses of rescheduling the passenger as well as loss of goodwill, estimated to be $150 per passenger. Develop a spreadsheet simulation model for this overbooking system. Simulate the number of passengers showing up for a flight (Do 100 simulations). Assume the airline collects revenue from every ticket booked, even if the passenger doesn't show up.
(a) What is the average net profit for each flight with the overbooking strategy? If required, round your answer to the nearest dollar. $ (b) What is the probability that the net profit with the overbooking strategy will be less than the net profit without overbooking (50*$100 = $5,000)? If required, round your answer to two decimal places. (c) Explain how your simulation model could be used to evaluate other overbooking levels such as 51, 53, and 54 and for recommending a best overbooking strategy.Explanation / Answer
The spreadsheet model for South Central is:
A
B
C
1
2
South Central Airlines
3
4
Inputs
50
5
Airplane Capacity
$100
6
Profit per flying passenger
$150
7
Cost per bumped passenger
8
9
Decision
10
No. of Reservations Accepted
Decision (or fixed at 52)
11
12
Calculations
13
Passengers showing up
Custom distribution (cells A24:B28 below)
14
Passengers flown
=min(B13,B5)
15
Profit for flying passengers
=B6*B14
16
Cost of overbooking
=B7*MAX(0,B13-B5)
17
18
Performance Measures
19
Net Profit
=B15-B16
Forecast
20
21
22
Custom Distribution
23
Passengers showing up
Probability
24
48
0.05
25
49
0.25
26
50
0.5
27
51
0.15
28
52
0.05
a. The Frequency View shows only five different values of Net Profit because there are only five different numbers of passengers which will show up for the flight. Each number of passengers showing up has its own distinct value for Net Profit.
b. The strategy of accepting 52 reservations for the flight is better than their current strategy of only accepting 50 reservations.
If they accept 50 reservations, they have, on average, 48 people showing up for the flight. This results in an average profit per flight of $100 x 48 = $4,800.
If they accept 52 reservations, the Statistics table from Crystal Ball tells us that the average net profit is $5,033. Since this is higher than the $4,800 they are currently getting, the new strategy is better.
c. NO! If a new simulation was run with a larger number of trials, the average net profit and the standard deviation of the net profit would be about the same as the $5,033 that we got from the current simulation. (The key to understanding why this is true is to realize that both the average and the standard deviation that Crystal Ball calculates are estimates of the population mean and population standard deviation of all possible Net Profit values. Similarly, the average and standard deviation from a new, larger number of trials, simulation would also be estimates of these same population parameters, and hence, except for random variation, have the same value.)
A
B
C
1
2
South Central Airlines
3
4
Inputs
50
5
Airplane Capacity
$100
6
Profit per flying passenger
$150
7
Cost per bumped passenger
8
9
Decision
10
No. of Reservations Accepted
Decision (or fixed at 52)
11
12
Calculations
13
Passengers showing up
Custom distribution (cells A24:B28 below)
14
Passengers flown
=min(B13,B5)
15
Profit for flying passengers
=B6*B14
16
Cost of overbooking
=B7*MAX(0,B13-B5)
17
18
Performance Measures
19
Net Profit
=B15-B16
Forecast
20
21
22
Custom Distribution
23
Passengers showing up
Probability
24
48
0.05
25
49
0.25
26
50
0.5
27
51
0.15
28
52
0.05