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: 1. 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. 2. 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? 3. 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? Alliance Air Service \begin{tabular}{|l|l|} \hline Average Demand & \\ \hline Pre-Purchased Flight Seats & \\ \hline \end{tabular} Seats Sold for Full Profit \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline Pre-Purchased Flights & AVG Profit & MIN Profit & MAX Profit \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} \begin{tabular}{|l|l|} \hline Discounted Flights Sold & \\ \hline Same Day Flights to Purchase & \\ \hline \end{tabular} Daily Profit 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: 1. 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. 2. 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? 3. 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? Alliance Air Service \begin{tabular}{|l|l|} \hline Average Demand & \\ \hline Pre-Purchased Flight Seats & \\ \hline \end{tabular} Seats Sold for Full Profit \begin{tabular}{|l|l|l|l|} \hline & & & \\ \hline & & & \\ \hline Pre-Purchased Flights & AVG Profit & MIN Profit & MAX Profit \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline & & & \\ \hline \end{tabular} \begin{tabular}{|l|l|} \hline Discounted Flights Sold & \\ \hline Same Day Flights to Purchase & \\ \hline \end{tabular} Daily Profit