Alliance Air Download Alliance Air is a service in which customers pay a flat$-$rate to Alliance Air and are guaranteed a seat on an airplane between any two locations. To offer this service, Alliance Air pre$-$purchases daily seats between every pair of major cities. A route, which has been growing in popularity, is between Phoenix and Dallas. Alliance Air is seeking your advice on how many seats to pre$-$purchase between these two cities so that they maximize their daily profit.

Whenever an Alliance Air customer flies on a pre$-$purchased seat, Alliance Air obtains $$150$ in profit. However, if not all of the pre$-$purchased seats are taken, then Alliance Air has a profit of $-$$$80$ by selling the seats at a discount to passengers outside of their customer base. In addition, if Alliance Air has more customers seeking a seat than they have pre$-$purchased, Alliance Air is forced to book that passenger on a seat purchased that day. In this case, Alliance Air has a profit of $-$$$70$ due to the high cost of same$-$day flights.

Alliance Air understands that its demand is uncertain. Therefore, they have been keeping track of their historical daily demand.

Using this information, complete the following tasks$/$questions:

Develop an Excel spreadsheet model which can calculate the daily profits for Alliance Air, assuming any particular demand for that day and that Alliance Air pre$-$purchased $37$ seats on the plane. Hint: Your model should include an understock and an overstock formulas.

Fit distribution to Historical Demand data to find a statistical distribution that fits these data best. Then apply this distribution to your demand, and set$-$up your spreadsheet as a simulation model using daily profits as an output. Run a simulation assuming Alliance Air pre$-$purchased $37$ seats on a plane. What is the average daily profit in this scenario?

How many seats would you recommend Alliance Air to pre$-$purchase if they wanted to maximize their average daily profit? To answer this question, modify the number of Pre$-$Purchased Daily Seats from $25$ to $55($in the increments of $5)$ and fill in the table provided with statistics about the expected daily profits $($Note that the average, min and max might keep changing slightly as you record those values, that$$s ok$).$ What are the minimum and maximum daily profits if they were to purchase this quantity?