0:00

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.

Â 2:51

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.

Â 4:53

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.

Â 6:13

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.

Â