In our last video we built a simulation by adding uncertainty

to a flight overbooking model.

The model has two sources of uncertainty, the demand and the show-up rate.

Let's review the simulation model that we created.

So locate and open the Excel file, Flight Overbooking Simulation.

This workbook has two tabs.

Click on the Overbooking Simulation tab.

The top table has the problem data and the bottom table has a model.

The model starts with a policy decision of the overbooking limit.

This is the yellow cell, B13.

The two green cells, B14 and B17, are the ones with the certainty.

In B14, we use the PsiPoisson function from the analytic solver platform to

model the actual demand based on the average demand of 150 passengers.

In cell B17, we use PsiBinomial to model the show up process.

The binomial has two parameters, the number of passengers that were booked,

which is in cell B17, and the probability that they show up, which is in cell B6.

The model calculates the number of no shows and

then it determines how many passengers will actually board the plane,

versus those who are bumped to a later flight.

The last part of the model calculates revenue, cost and net revenue.

If we recalculate the spreadsheet by pressing F9,

we can see that the values in the green cells change.

And that causes changes in both the number of bumped passengers and

in the net revenue.

We have highlighted these two cells with light orange to indicate these

are outputs we're interested in tracking.

Every time that we recalculate the spreadsheet, 10,000 values for

the actual demand and the ready to board cells are generated.

Each set of values is what in simulation is called a trial.

As we mentioned in our previous video,

you can change the number of trials in the tools group of the ASP tab.

We need to tell the simulator that we would like to track the number

of bumped passengers and the net revenue.

This is done by selecting cells B20 and B24, and

clicking on Results in the simulation model group of the ASP tab.

Once we do that, we can check the cells to see what has changed.

By clicking on cell B20,

we can see that there is an additional function called PsiOutput.

This function does not modify the cell calculation.

It is there to tell the simulator that the cell is a simulation output and

it that it should collect data on it when the simulation is run.

The same function was added to cell B24.

To run the simulation, click on the Play button

in the Model tab of the Solver options and Model Specifications panel.

To analyze results, double-click on B24.

The simulator displays the empirical distribution of net revenue that

results from the simulation of 10,000 trials.

In addition to the empirical distribution,

the software reports the most common statistics.

For example, the simulation results show that

the new revenue is estimated to be $45,196

with a minimum value of $33,644 and

a maximum value of $47,778.

Estimating a range for the net revenue is important.

But what's interesting is being able to

estimate the probability of collecting some target net revenue.

For example, let's suppose that we were interested in knowing the probability that

the net revenue is $45,000 or more.

We can do this using the markers in the distribution chart.

The markers are red dotted vertical lines.

There is a lower and an upper marker.

In this case, we only need the lower marker, which we place on the value of 45.

So remove the upper marker and enter 45 in the text box for the lower marker.

We now see that the distribution estimates that there is 63.83%

probability that the net revenue will be $45,000 or more.

A similar analysis is possible on the number of bumped passengers.

Recall that the deterministic model estimates that, on average,

1 passenger will be bumped when setting the overbooking limit to 13 passengers.

If we double-click on cell B20, we access the distribution of bumped

passengers that results from the simulation.

The mean value of this distribution is 1, as predicted by the deterministic model.

However, now we have additional information.

For instance, if we set the lower marker to a value of 2,

we discover that according to our simulation results there is a 24.55%

chance that the number of bumped passengers is 2 or more.

We can also find that there is a low,

over 10% chance that the number of bumped passengers is 5 or more.

These probabilities represent the risk of the overbooking limit that we have chosen.

The simulation can now be used to find an overbooking limit

that meets some desirable level of risk.

For instance, suppose that the airline would like at least a 95% chance

that the number of bumped passengers is less than or equal to 2.

Which overbooking limit would accomplish this?

Let's use the interactive simulation feature to find this out.

In the Solve Actual group of the ASP tab, click on the Simulation bulb.

The bulb should turn yellow.

We then click on B20 to open the frequency distribution chart.

We set the lower marker to 2, and start decreasing the overbooking limit.

Every time we enter a new number, the spreadsheet is recalculated and

the simulation is run.

The frequency chart is updated at the end of the simulation, and

we can see the change in the probability value

that the number of bumped passengers is less than or equal to 2.

We keep decreasing the overbooking limit until we find out that a limit of

9 results in a probability slightly higher than 95%.

Now we have an overbooking limit for this flight that meets the airline policy.

In this video, we have added output variables to our simulation model by

using the PsiOutput function of the Analytic Solver Platform.

This function instructs the simulator to collect data on the cell where

the function is added.

The collected data generates an empirical distribution of the output variable.

This distribution helps us analyze risk,

which we define as a probability of some undesirable outcome.

In our overbooking example,

the undesirable outcome is either empty seats or too many bumped passengers.

The empirical distributions of net revenue and

bumped passengers can be used to asses the risk of various overbooking limits.

Finally, we show how to use the interactive simulation feature

in the context of searching for

an overbooking limit that could meet a particular airline policy.

This example has shown the power of simulation as a predictive analytics tool.

We will explore additional features in the next videos for this module.

2. Defining Output Variables and Analyzing the Results

Business Analytics for Decision Making

与讲师见面