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.