An airline flies daily from San Francisco to Chicago. Most of the passengers are business travelers. There are 150 seats available in the single cabin plant. The average ticket price is $300 per seat. All fares are non-refundable, so no-shows forfeit the entire fare. The fixed cost to operate the flight is $30,000.

The airline’s revenue management analysts estimate that the demand for this flight is normally distributed with a mean of 195 reservation requests and a standard deviation of 30. The analysts also believe that only 80% of the travelers who make a reservation will show up for the flight. That is, on any flight there is on average 20% who do not show and forfeit their fare.

The airline’s revenue management analysts have decided to investigate the effect of raising the number of reservations to accept for this flight to 190 (in the past only 165 reservations were accepted, but this led to a lot of empty seats because of no shows).

When a customer is bumped from a flight, the airline places the bumped passenger on the next flight and compensates the bumped passenger for the inconvenience. The airline estimates that the total cost of bumping a passenger is $450.

The airline’s revenue management analysts want to investigate the effect that accepting 190 reservations will have on the following performance measures: the overall profit of each flight; the average number of filled seats; the probability that the flight will be full; and the average number of bumped passengers. To conduct its analysis, the revenue management analysts will develop a spreadsheet simulation model.

The spreadsheet simulation will model the demand for tickets as a normally distributed random variable with a mean of 195 and a standard deviation of 30. The number of ticketed passengers that show up for the flight is modeled as a binomial random variable with a probability of showing up that is 0.80. The binomial distribution is appropriate because the random event of interest is number of passengers with tickets who show up for the flight.

Complete the simulation spreadsheet provided. Recall that a normally distributed random variable can be generated using the NORM.INV function in Excel. The number of passengers who show up for the flight can be simulated using the BINOM.INV function in Excel. That function looks like =binom.inv (trials, probability of success, alpha). In this problem the trials are equal to the tickets sold. The probability of showing up is 0.80, and the alpha value is computed randomly using Excel’s rand () function.

Replicate the simulation 500 times keeping track of number of filled seats, number denied boarding (bumped) and profit. From the 500 replications compute the average number of bumped passengers, the average profit, and the relative frequency that the flight is full (i.e., has 150 or more filled seats).

Note..... This is excel model or Simulation Spreadsheet Provided

Seats Available (capacity)	150
Fixed Costs	$ 30,000.00
Average Seat Fare	$ 300.00
Cost of Bumping	$ 450.00
Ticket Demand Probability (normal distribution)
Mean	195
Standard Deviation	30
RESERVATIONS TO ACCEPT (DECISION MAKER INPUT)	190
Ticket Demand (Simulated)
Tickets Sold (calculated)
Probability Ticketed Passenger Shows Up	0.8
Number of Ticketed Passengers that Show Up (simulated)
Number of Filled Seats
Number Denied Boarding
Ticket Revenue
Fixed Costs
Bumping Costs
Profit