IMPORTANT:

A$.$ Please solve the decision making problem using Excel, answer the questions asked in Word, then submit both files.$$

B$.$ Just run seven simulations $($seven pre$-$purchased daily seat values$)$ and input your input and output values into the table manually.

C$.$ Like the in$-$class example, you need to directly apply your distribution to your uncertain input cell$$

D$.$ If for some reason your Excel worksheet would not recalculate your formulas automatically as you change the numbers for your demand $($unless you manually force it to do so$),$ you need to turn on the Automatic feature as described in this PDF

Actions

$.$

$$

Alliance Air Download Alliance Airis 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 from Phoenix to Dallas. Alliance Air is seeking your advice on how many seats to pre$-$purchase for this route so that they maximize their daily profit.

Whenever an Alliance Air customer buy 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 $($Lower Demand Case$).$ 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 $($Higher Demand Case$).$

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?$($Please set up your number of simulation trials as $1000)$

How many seats would you recommend Alliance Air to pre$-$purchase if they wanted to maximize their average daily profit? What are the minimum and maximum daily profits if they were to purchase this quantity?$$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 with the average, min and max daily profits.