11-11 South Central Airlines (SCA) operates a commuter flight between Atlanta and Charlotte. The regional jet holds 47 passengers and currently SCA only books up to 47 reservations. Past data shows that SCA always sells all 47 reservations, but on average, two passengers do not show up. As a result, with 47 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 49 reservations even though the airplane holds only 47 passengers. SCA believes that it will be able to always book all 49 reservations. The probability distribution for the number of passengers showing up when 49 reservations are accepted is estimated as follows: Passengers Showing Up Probabilit y 46 0.05 47 0.2 48 0.4 49 0.2 50 0.15 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 $130 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 (47*$100=$4,700)? 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 overbook strategy This sheet simulates the average net profit when using the overbooking strategy Inputs Simulation Passengers Showing Up Probability Cumulative Probability 46 47 48 49 50 0.05 0.2 0.4 0.2 0.15 0.05 0.25 0.65 0.85 1 Marginal profit Capacity Cost Reservations 100 47 130 ... for overbooking (loss of goodwill). 49 Notes and Audit Trail 100 simulations are run. The simulation number (run) is shown in the run column. Random numbers are generated using Excel's function =RAND(). The generated random numbers are then copied and pasted as values to stop the random numbers from changing if the spreadsheet refreshes. The simulated number of passengers showing up is obtained by equating the rrandom number to the cumulative probability of passengers showing up. This is achieved with the aid of a nested IF statement. If passengers showing up exceed airplane capacity, then the number of overbooked customers = Passengers showing up - airplane capacity. Otherwise, 0. Marginal profit = number of reservations * marginal profit per reservation. Total loss of goodwill = Number of overbooked customers - loss of goodwill per customer. Net profit = Marginal profit - total loss of goodwill. Simulation Results Run Random Passengers Overbooked Number Showing Up customers 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 0.779 0.454 0.207 0.295 0.101 0.918 0.714 0.155 0.736 0.066 0.949 0.954 0.632 0.382 0.500 0.651 0.628 0.912 0.974 0.251 0.653 0.858 0.330 0.800 0.921 0.321 0.569 0.598 0.120 0.943 0.082 0.452 0.444 0.018 0.412 0.573 0.014 0.540 0.755 0.544 0.093 0.497 0.867 0.890 0.523 0.453 0.947 0.600 0.708 0.073 0.325 0.259 0.695 0.684 0.430 0.331 0.253 0.309 0.841 0.982 0.129 0.936 0.636 0.047 0.680 0.007 0.146 0.170 0.780 0.062 0.683 0.771 0.626 0.212 49 48 47 48 47 50 49 47 49 47 50 50 48 48 48 49 48 50 50 48 49 50 48 49 50 48 48 48 47 50 47 48 48 46 48 48 46 48 49 48 47 48 50 50 48 48 50 48 49 47 48 48 49 49 48 48 48 48 49 50 47 50 48 46 49 46 47 47 49 47 49 49 48 47 Marginal Loss of Net Profit with profit Goodwill Overbooking 2 1 0 1 0 3 2 0 2 0 3 3 1 1 1 2 1 3 3 1 2 3 1 2 3 1 1 1 0 3 0 1 1 0 1 1 0 1 2 1 0 1 3 3 1 1 3 1 2 0 1 1 2 2 1 1 1 1 2 3 0 3 1 0 2 0 0 0 2 0 2 2 1 0 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 260 130 0 130 0 390 260 0 260 0 390 390 130 130 130 260 130 390 390 130 260 390 130 260 390 130 130 130 0 390 0 130 130 0 130 130 0 130 260 130 0 130 390 390 130 130 390 130 260 0 130 130 260 260 130 130 130 130 260 390 0 390 130 0 260 0 0 0 260 0 260 260 130 0 4,640 4,770 4,900 4,770 4,900 4,510 4,640 4,900 4,640 4,900 4,510 4,510 4,770 4,770 4,770 4,640 4,770 4,510 4,510 4,770 4,640 4,510 4,770 4,640 4,510 4,770 4,770 4,770 4,900 4,510 4,900 4,770 4,770 4,900 4,770 4,770 4,900 4,770 4,640 4,770 4,900 4,770 4,510 4,510 4,770 4,770 4,510 4,770 4,640 4,900 4,770 4,770 4,640 4,640 4,770 4,770 4,770 4,770 4,640 4,510 4,900 4,510 4,770 4,900 4,640 4,900 4,900 4,900 4,640 4,900 4,640 4,640 4,770 4,900 a) average net profit b) Pr(Net profit < 4700) 4,715 41% ... (count of instance how your simulation model could be used to evaluate other over For each overbooking level, we need to estimate the probability di Next, we simulate the net profit in the same way as done in this sp Finally, we compute statistics such as the average net profit. A reasonable decision is to consider choosing the overbooking leve net profit. Other factors may nevertheless need to be considered. For exampl ... Regulation may limit the extent of overbooking; ... The extent of overbooking may be positively correlated with the ... Strategy followed by competitors; and ... The long term effect on the company's reputation. 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 0.344 0.590 0.967 0.362 0.966 0.942 0.906 0.444 0.543 0.551 0.705 0.891 0.156 0.749 0.605 0.722 0.678 0.590 0.118 0.718 0.332 0.297 0.476 0.904 0.971 0.668 48 48 50 48 50 50 50 48 48 48 49 50 47 49 48 49 49 48 47 49 48 48 48 50 50 49 1 1 3 1 3 3 3 1 1 1 2 3 0 2 1 2 2 1 0 2 1 1 1 3 3 2 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 4900 130 130 390 130 390 390 390 130 130 130 260 390 0 260 130 260 260 130 0 260 130 130 130 390 390 260 4,770 4,770 4,510 4,770 4,510 4,510 4,510 4,770 4,770 4,770 4,640 4,510 4,900 4,640 4,770 4,640 4,640 4,770 4,900 4,640 4,770 4,770 4,770 4,510 4,510 4,640 es where net profit is less than 4700)/ 100 rbooking levels istribution of passengers showing up. preadsheet. el that results in the highest average le: e loss of goodwill per customer; and