COULD YOU PLEASE PROVIDE DETAILED STEP-BY-STEP SOLUTIONS TO THE BELOW QUESTION ALONG WITH ALL THE FORMULAS CALCULATED FOR THE NECESSARY CELLS Pacific Airlines flights from Seattle to San Francisco. The airplanes main cabin holds 120 passengers. PA sells both a discount 7-day advance fare and a full-price fare. The discount ticket sells for $150 and is non-refundable. The passengers have to purchase the discount tickets at least 7 days before the departure. The demand for the discount fare is between 50 and 150, but mostly likely to be 90. The full-price fare is $400. No advance purchase is required and passengers can get refund fully before the check-in time for the full-price tickets. The demand for the full-price fare is equally likely to be anywhere between 30 and 70 with essentially all of the demand occurring within one week of the flight. The average no-show rate is 5% for the non-refundable discount tickets and 15% for the refundable full-price tickets. We can assume full-price tickets can always be refunded since one can call to cancel before the check-in time. If more ticketed passengers show up than there are seats available, some passengers will have to be bumped to a later flight. The total cost (free flight, meals, hotel room, etc.) to the airline for bumping a passenger is $600. The fixed cost of operating the airplane is $10,000. There are two decisions to be made. 1) How many tickets should be made available at the discount fare? Too many and the airline risks losing out on potential full-fare passengers. Too few and the airline may have a less-than-full flight. 2) How many tickets should be issued in total? Too many and the airline risks needing to bump passengers. Too few and the airline risks having a less-than-full flight.

Questions:

a) Suppose that the airline makes available a maximum of 80 tickets for the discount fare and a maximum of 125 tickets in total. Create a spreadsheet model for this problem and replicate it 1000 times.

a1) What is the minimum, average, maximum profit that PA airlines can expect to have?

a2) What is the probability that PA airlines will earn a profit of more than $15,000? Establish a 95% confidence interval for the probability of PA earning a profit of more than $15,000.

a3) What are the average number of seats filled and the number of passengers bumped.

b) Suppose that PA does not overbook the flight, i.e. the maximum number of tickets issued is 120. What is the optimal maximum number of discount fare tickets to make available so as to maximize the airlines expected profit? Use PsiSimParam function (range the number from 65 to 74 in an inclement of 1). Using the optimal maximum number of discount fare tickets to make available you found above,

b1) What is the minimum, average, maximum profit that PA can expect to have?

b2) What is the probability that PA will earn a profit of more than $15,000? Establish a 95% confidence interval for the probability of PA earning a profit of more than $15,000. b3) What are the average number of seats filled and the number of passengers bumped.

c) Suppose that PA decides to overbook the flight. What are the maximum number of discount fare tickets and the maximum total number of tickets to make available so as to maximize the airlines expected profit? Use Simulation Optimization to find the answer. Using Standard Evolution Engine, replicate the model 1000 times. Using your result from the Simulation Optimization,

c1) What is the minimum, average, maximum profit that PA can expect to have?

c2) What is the probability that PA will earn a profit of more than $15,000? Establish a 95% confidence interval for the probability of PA earning a profit of more than $15,000.

c3) What are the average number of seats filled and the number of passengers bumped.