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 Arlines (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 alvays 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 book 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 lF function to facilitate calculation of total Cost of overbooking The probability distribution for the number of passengers showing up when 52 reservations are accepted is estimated as follows: Passengers Showing Up Probability 48 0.05 49 0.25 50 0.50 51 0.15 52 0.05 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

Explanation / Answer

Pasted some of the rows.

Average profit if cost of overbooking is not considered is 4962.67

Average profit if cost of overbooking is considered 4962.67-58.81 = 4903

Rand() Passengers show up Seat Capacity Excess booking Total Revenue Total cost of Overbooking Probability Cum. Probability On hand inventory 0.9708 52 50 2 5000 500 0.05 0 48 0.9974 52 50 2 5000 500 0.25 0.05 49 0.0815 49 50 -1 4900 0 0.5 0.3 50 0.0296 48 50 -2 4800 0 0.15 0.8 51 0.4100 50 50 0 5000 0 0.05 0.95 52 0.4655 50 50 0 5000 0 0.4040 50 50 0 5000 0 0.7235 50 50 0 5000 0 0.6298 50 50 0 5000 0 0.9035 51 50 1 5000 250 0.6063 50 50 0 5000 0 0.3245 50 50 0 5000 0 0.3158 50 50 0 5000 0 0.9428 51 50 1 5000 250 0.7568 50 50 0 5000 0 0.5181 50 50 0 5000 0 0.7402 50 50 0 5000 0 0.5221 50 50 0 5000 0 0.9261 51 50 1 5000 250 0.4731 50 50 0 5000 0 0.5166 50 50 0 5000 0 0.3074 50 50 0 5000 0 0.0749 49 50 -1 4900 0 0.0096 48 50 -2 4800 0 0.0580 49 50 -1 4900 0 0.4850 50 50 0 5000 0 0.7371 50 50 0 5000 0 0.1165 49 50 -1 4900 0 0.9447 51 50 1 5000 250 0.5673 50 50 0 5000 0 0.1754 49 50 -1 4900 0 0.5154 50 50 0 5000 0 0.6659 50 50 0 5000 0 0.4154 50 50 0 5000 0 0.0693 49 50 -1 4900 0 0.7434 50 50 0 5000 0 0.2868 49 50 -1 4900 0 0.2785 49 50 -1 4900 0 0.3854 50 50 0 5000 0 0.3258 50 50 0 5000 0 0.2931 49 50 -1 4900 0 0.0277 48 50 -2 4800 0 0.8624 51 50 1 5000 250 0.5707 50 50 0 5000 0 0.7235 50 50 0 5000 0 0.1865 49 50 -1 4900 0 0.4826 50 50 0 5000 0 0.1333 49 50 -1 4900 0 0.3164 50 50 0 5000 0 0.8883 51 50 1 5000 250 0.7584 50 50 0 5000 0 0.3552 50 50 0 5000 0 0.4563 50 50 0 5000 0 0.0516 49 50 -1 4900 0 0.8321 51 50 1 5000 250 0.0781 49 50 -1 4900 0 0.6462 50 50 0 5000 0 0.5190 50 50 0 5000 0 0.7534 50 50 0 5000 0 0.2381 49 50 -1 4900 0 50 -0.15 4962.67 58.81

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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