A. Please complete the problems using Excel, answer the questions asked in Word, then submit both files. B. You do not need to automate your table. Just run six simulations and input your values manually. C. 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 shown on the picture attached (click here for PDFU). 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 prepurchases 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 prepurchase between these two cities so that they maximize their daily profit. Whenever an Alliance Air customer flies on a prepurchased seat, Alliance Air obtains $150 in profit. If not all of the prepurchased seats are taken, then Alliance Air makes a profit of $80 by selling the seats at a discount to passengers outside of their customer base. However, if Alliance Air has more customers seeking a seat than they have prepurchased, Alliance Air is forced to book that passenger on a seat purchased that day. In this case, Alliance Air has a profit of $170 due to the high cost of same-day flights. Based on their data, Alliance Air knows that number of customers seeking a night on any day follows a Poisson distribution with a mean of 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 pre purchased 37 seats on the plane. What is the average daily profit in this scenario? 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 prepurchases 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 prepurchase between these two cities so that they maximize their daily profit. Whenever an Alliance Air customer flies on a prepurchased seat, Alliance Air obtains $150 in profit. If not all of the prepurchased seats are taken, then Alliance Air makes a profit of $80 by selling the seats at a discount to passengers outside of their customer base. However, if Alliance Air has more customers seeking a seat than they have prepurchased, Alliance Air is forced to book that passenger on a seat purchased that day. In this case, Alliance Air has a profit of $170 due to the high cost of same-day flights. Based on their data, Alliance Air knows that number of customers seeking a flight on any day follows a Poisson distribution with a mean of 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 pre-purchased 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 nil 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 ok). What are the minimum and maximum daily profits if they were to purchase this quantity
