0:00

In this video we're going to show how to turn at the deterministic model

Â into one that includes uncertainty.

Â We're going to do this with a specific example.

Â Overbooking is a common practice in the airline industry.

Â On every flight there is a fraction of passengers that do not show up.

Â Anticipating this, airlines overbook flights.

Â That is they sell more tickets than the capacity of the aircraft,

Â hoping that the right number of customers will show up and the plane will fly full.

Â Overbooking increases the probability of a full flight,

Â maximizing revenue for the airline.

Â But if overbooking causes passengers to be bumped,

Â there is a cost to the airline in both travel vouchers and good will.

Â This of course reduces revenue.

Â The secret to overbooking is finding that magical number

Â of extra reservations to avoid both empty seats and bumped flyers.

Â If every passenger that would miss a flight for any reason at all will have

Â to buy another ticket then empty seats would not represent a loss of revenue.

Â However, this is not how things work.

Â Business travelers often pay higher fares for refundable tickets.

Â These travelers need flexibility of changing plans at the last minute and

Â the airline is happy to provide this service for a price.

Â But even travelers flying on non-refundable tickets could miss a flight

Â and be transferred to a later flight for a fee or no fee at all.

Â Without overbooking the seats of the no shows would be empty.

Â The loss of revenue of an empty seat typically exceeds

Â the actual cost of compensating a bumped passenger.

Â But angering people is not good for business, and this is why

Â the overbooking models include cost of not only what the passenger receives,

Â such as flight voucher, but also an estimate of loss of goodwill.

Â The problem of setting an overbooking limit on a flight is complex.

Â Airline analysts use models that consider historical data, the mix of refundable and

Â non-refundable tickets, and the different airfare classes and levels.

Â They even consider location since a flight leaving Las Vegas

Â might have more no shows than one leaving Detroit.

Â We're going to use a simplified version of this problem to show how simulation is

Â used to deal with the uncertainty of passenger behavior and

Â delays in flight schedules.

Â We're going to assume that we have been charged with the task of establishing

Â an overbooking limit for a particular flight of a discount airline.

Â The airline sells non-refundable tickets only, but passengers that miss

Â the flight can transfer to another flight for a fee of $60.

Â The flight uses an Airbus 319 with 134 seats.

Â Historically, the average demand for

Â this flight has been 150 passengers and the show up rate has been 92%.

Â The price for the ticket is $314, and we have considered

Â that each passenger that is bumped cost the airline $400.

Â With this data, we will build a model to calculate net revenue for this flight.

Â 3:11

Before modeling this problem on a spreadsheet, it is useful to understand

Â the logic of how the overbooking limit interacts with the no-show rate.

Â This graphical representation of the model shows that

Â everything starts in the yellow box with the decision to overbook.

Â The overbooking limit is added to the capacity of the aircraft

Â to calculate the booking capacity.

Â The number of passengers actually booked is the minimum between

Â the booking capacity and the actually demand.

Â This simply means that we cannot book more passengers than the demand and

Â we're not allowed to book more passengers than the booking capacity.

Â For various reasons some booked passengers do not show up, but

Â most of them do and are ready to board.

Â If the number of passengers that are ready to board exceeds

Â the capacity of the aircraft, then we have to bump some passengers to a later flight.

Â Now you can the connection between an overbooking limit and

Â the number of bumped passengers.

Â Know that this connection is not direct and

Â other factors such as actual demand and no-show rates play an important role.

Â Now, let's create the spreadsheet model for this.

Â Locate and open the Excel file titled, Flight Overbooking Simulation.

Â The workbook has two spreadsheets.

Â Let's start with the Overbooking Model.

Â The top part of the spreadsheet contains the data, which includes the average

Â demand, the percentage of ticketed passengers that actually show up for

Â the flight, the capacity of the aircraft, the ticket price,

Â the transfer fee that no-shows must pay to take another flight and

Â the cost to the airline for every bumped passenger.

Â We use these values in the model that estimates the number of bumped passengers

Â and the net revenue.

Â The model has one input, two assumptions and two outputs.

Â The input in the light gold cell is the overbooking limit.

Â The assumptions are in the green cells.

Â One is the actual demand and

Â the other is the number of passengers that show up and are ready to board.

Â The outputs,

Â in the pale orange cells, are the number of bumped passengers and the net revenue.

Â When the overbooking limit is set at 0 and the demand is assumed to be 154

Â passengers, the model predicts a net revenue of $42,732.

Â Naturally, there are no bumped passengers.

Â Therefore, the overbooking cost is 0.

Â We can see that the model also predicts that there will be 11 no shows.

Â This means that in order to increase the net revenue,

Â we could increase the overbooking limit to at least 11 passengers.

Â If we do that, the estimated net revenue increases to $46,250.

Â Note that the number of no shows is now 12.

Â So we can increase the overbooking limit to 12 to match the number of no shows.

Â The revenue is now $46,564.

Â Considering the assumptions that the actual demand is 154 passengers and

Â that the show up rate is 92%, an overbooking of 12 maximizes net revenue.

Â The model provides good insights.

Â First of all, overbooking has the potential for increasing revenue.

Â However, the model does not take advantage of additional historical data

Â that is available for this particular flight.

Â We're only using average values for historical records.

Â Let's assume that using historical data we conclude that the demand for

Â this flight follows a Poisson distribution with an average of 150 passengers.

Â Let's also assume that through a statistical analysis

Â of the number of booked passengers who show up,

Â we discover that it follows a binomial distribution with a success rate of 0.92.

Â According to this distribution,

Â there is an 85% chance that more than 134 passengers will show up at the gate.

Â With this additional information we can create a simulation model

Â that is based on the deterministic model that we just built.

Â This is what we call adding uncertainty to a spreadsheet model.

Â Let's take a look at the model with uncertainty.

Â Click on the spreadsheet named Overbooking Simulation.

Â The green cells contain functions from the Analytic Solver Platform,

Â which we will refer to as ASP.

Â If you don't have the ASP loaded on Excel, watch the video title

Â Introduction to the Analytic Solver Platform first and then comeback here.

Â The actual demand is now a random number that follows a Poisson distribution.

Â The number of passengers ready to board is also a random number that

Â follows a binomial distribution.

Â All Analytic Solver Platform functions start with the letters PSI.

Â If we click on the play button, we can see that the net revenue changes.

Â We can also press on the F9 key to recalculate the spreadsheet and

Â we will see that certain cells change values and the net revenue changes.

Â Each time we do this the software runs the number of trials as specified in

Â the tools section of the ribbon.

Â I have mine set to 10,000 trials.

Â Note that because this is assimilation with an overbooking limit of 12,

Â the number of bumped passengers is not always 0.

Â In this video we have used a flight overbooking example

Â to show how to turn a deterministic model into a simulation model.

Â We have learned how to add uncertainty using the simulation

Â functions in the Analytic Solver Platform.

Â In the next video we will learn how to collect simulation data and

Â how to use this output to answer relevant questions and gain useful insights.

Â