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.

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.

1. Adding Uncertainty to a Spreadsheet Model

Business Analytics for Decision Making

与讲师见面