Question

Please outline each step used along the way to solve the problem using excel only with cell numbers and formulas used. Thank you.

Whenever an Alliance Air customer flies on a prepurchased seat, Alliance Air obtains $100 in profits. However, if Alliance Air has more customers seeking a seat then they have prepurchased, Alliance Air is forced to book that passenger on a seat purchased that day. In such a situation, Alliance Air has a profit of -$170 due to the high cost of same-day flights. If not all of their prepurchased seats are taken, then Alliance Air makes a profit of -$20 by selling the seats at a discount to passengers outside of their customer base. Based on their data, Alliance Air knows that number of customers seeking a flight on any day follows a Poisson distribution with mean 40.

Using this information, complete the following tasks/questions:

1. Develop an Excel spreadsheet model which can calculate the daily profits for Alliance Air, given any particular demand for that day and assuming Alliance Air pre-purchased 37 seats on the plane.
2. Using the given demand distribution, set-up your spreadsheet as a simulation model using daily profits as the output. Run the simulation assuming Alliance Air prepurchases 37 seats on the plane. What is the average daily profit in this scenario?
3. How many seats would you recommend Alliance Air to prepurchase if they wanted to maximize their average daily profit? To answer this, modify the number of Pre-Purchased Daily Seats from 30 to 55 (in the increments of 5) and fill in the table provided with statistics about the expected daily profits (Note that the average might keep changing slightly as you record those values, that’s okay). What are the minimum and maximum daily profits if they were to purchase this quantity? What percentage of the time is Alliance Air’s daily profit above 2,250?

Alliance Air Service
Yellow Cell is Input Cell
Prepurchased Seat Sale	$       100				Avg Profit	Min Profit	Max Profit	% above 2250
Same Day Flight	$     (170)
Discounted Flight Sale	$       (20)
Average Demand	40
Pre-Purchased Flights	37
Same Day Flights to purchase
Discounted Flights Sold
Daily Profit

Answer 1

Excel formulation for the simulation setup (row 11 to 78 are hidden)

Simulation (10,000 runs) results with input variable = Prepurchase Qty. ($C$2) and output variable = Total profit ($E$8). use the Data Table option of Excel to simulate.

Following are the results for different prepurchase quantities:

Qty.	30	37	40	45	50	51	52
Minimum profit	-3290	-890	-80	1380	1040	1140	640
Maximum profit	3000	3700	4000	4500	5000	5100	5200
Average profit	1418.276	2891.821	3282.545	3607.599	3646.586	3631.028	3631.618

It seems that 50 is a reasonable prepurchase quantity as the average (expected) profit becomes maximum there.

-------------

For Prepurchase Qty. = 50

The formula =COUNTIF(K3:K10002,">"&2250) = 9,707

So, the percentage of the time the daily profit is above 2,250 = 9,707 / 10,000 = 97.07%