Whenever needed, enter your answers with 2 decimal places. Even though exact ans

Question

Whenever needed, enter your answers with 2 decimal places. Even though exact answers change every time the formulas on the spreadsheet are re-evaluated, it is OK. Just enter the number that you get, they are graded with an appropriate level of tolerance.

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.

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!)

Q1: What would be the average profit when uncertainty in overbooking cost is incorporated?

Q2: What is the average profit whenever overbooking does happen?

profit margin 107.77 99.68 109.84 100.29 83.1 109.57 87.13 99.5 105.33 87.97 89.38 97.53 105.03 92.01 82.86 88.6 90.29 109.34 83.81 101.38 87.17 90.01 104.38 85.88 87.69 95.74 96.27 99.17 92.39 89.23 95.76 107.26 90.93 100.97 94.9 99.63 89.44 93.26 103.25 102.82 92.22 85.43 109.02 81.26 106.11 87.64 107.87 93.41 105.92 108.78 106.86 108.3 92.5 96.49 94.49 98.04 90.09 92.75 86.95 89.79 92.39 106.18 109.24 83.45 109.55 102.95 100.81 95.41 91.73 97.61 84.38 108.84 107.08 96.92 107.52 99.44 102.66 83.53 99.82 94.97 95.67 89.56 103.78 100 96.62 102.58 107.66 106.49 95.29 88.69 91.35 84.81 108.25 86.12 87.27 80.68 99.33 88.08 81.1 95.95 96.85 107.13 90.47 103.37 108.07 93.55 103.73 98.18 95.53 89.82 85.64 85.19 83.57 104.99 95.29 96.06 97.68 99.17 99.33 100.08 98.77 99.43 82.75 81.7 97.93 100.3 106.57 101.95 80.72 90.6 84.71 87.41 92.71 99.32 82.04 94.71 101.63 84.69 91.56 94.36 85.09 107.7 80.43 84.31 90.62 92.81 89.16 104.78 103.14 102.47 99.76 83.53 85.59 101.38 90.39 83.66 89.78 88.18 89.77 96.88 107.97 86.29 85.45 83.53 89.19 97.21 90.65 87.91 100.32 88.84 105.63 82.39 104.29 93.75 99.34 91.82 105.91 104.54 83.63 104.78 100.14 99.95 93.23 86.31 107.78 86.88 83.28 85.02 98 98.71 91.64 107.94 102.33 108.11 95.35 101 103.92 91.8 90.74 82.74

Explanation / Answer

Please find the simulations in the dropbox link.

https://www.dropbox.com/s/fkedb8o2i9j9ksw/Book2.xlsx?dl=0

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.