please answer this question in excel or if u could show the formulas and functio

Question

please answer this question in excel or if u could show the formulas and functions.

Revenue Management Assignments II. San Francisco Express Airlines, SaFE for short, flies from PHL to SFO. On a Thursday evening flight, the number of last-minute no-shows and cancellations is Poisson distributed with mean 7.5. SaFE has an unlimited number of low fare travelers who pay $300. The cost of bumping such a passenger is estimated to be $350 (due lost good will as well as the cost of routing their itinerary through other airlines). SaFE offers this low fare because it also comes with a cancellation/rebooking fee of $150 – if a customer doesn’t show up for the flight or cancels her reservation, she must pay $150 to use the ticket on another flight.

1) To maximize revenue from this flight, how many seats should the airline overbook?

2) Customers are more reliable on the Friday evening flight. On that flight, the average number of no shows and cancellations is Poisson with mean 4.5. Suppose SaFE overbooks that flight by 6 seats. What is the probability that at least 1 passenger will be bumped from this flight?

Explanation / Answer

Cost of underage (shortage), Cu = 350

Cost of overage (excess), Co = 300-150 = 150

Critical Ratio, CR = Cu / (Co + Cu) = 350 / (350 + 150) = 0.70

Poisson mean of no-shows = 7.5

We find the optimum value of x (overbooking) by using goal seek function and excel formula POISSON(x, 7.5,true) = 0.7

x = 8.9 ~ 9

1) Therefore, to maximise revenue, 9 seats should be overbooked.

2) Probability = 1 - POISSON(6,4.5,true) = 0.17

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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