In this course you will learn how to create models for decision making. We will start with cluster analysis, a technique for data reduction that is very useful in market segmentation. You will then learn the basics of Monte Carlo simulation that will help you model the uncertainty that is prevalent in many business decisions. A key element of decision making is to identify the best course of action. Since businesses problems often have too many alternative solutions, you will learn how optimization can help you identify the best option. What is really exciting about this course is that you won’t need to know a computer language or advanced statistics to learn about these predictive and prescriptive analytic models. The Analytic Solver Platform and basic knowledge of Excel is all you’ll need. Learners participating in assignments will be able to get free access to the Analytic Solver Platform.
2. Defining Output Variables and Analyzing the Results
Loading...
Do curso por University of Colorado Boulder
Business Analytics for Decision Making
183 classificações
University of Colorado Boulder
183 classificações
Curso 3 de 5 em Specialization Advanced Business Analytics
Na lição
Dealing with Uncertainty and Analyzing Risk
Conheça os instrutores
Manuel LagunaProfessor
Leeds School of Business
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.
Explore nosso catálogo
Registre-se gratuitamente e obtenha recomendações, atualizações e ofertas personalizadas.
O Coursera proporciona acesso universal à melhor educação do mundo fazendo parcerias com as melhores universidades e organizações para oferecer cursos on-line.
© 2019 Coursera Inc. Todos os direitos reservados.
