Explore
For Enterprise
Join for Free
Log In

Artes e Humanas
Negócios
Ciência da Computação
Ciência de Dados
Tecnologia da informação
da saúdesaúde
Matemática e Lógica
Desenvolvimento Pessoal
Ciência e Engenharia Física
Ciências Sociais
Línguas
Notas
Certificados

Explore todo o Coursera

Navegar
Pesquisar
For Enterprise
Entrar
Inscreva-se gratuitamente

2. Defining Output Variables and Analyzing the Results

Loading...

Business Analytics for Decision Making

Universidade do Colorado em Boulder

4.5 (231 classificações) | 13K alunos inscritos

Curso 3 de 5 no Advanced Business Analytics Programa de cursos integrados

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.

Visualizar o programa do curso

Habilidades que você aprenderá

Data Analysis, Simulation, Business Analytics, Mathematical Optimization

Avaliações

4.5 (231 classificações)

5 stars
148 ratings
4 stars
63 ratings
3 stars
12 ratings
2 stars
5 ratings
1 star
3 ratings

MB

May 24, 2019

Great learning opportunity Through Mr. Laguna its an outstanding way of bringing the classes for our business processes right at the desktop for our e

JZ

Jun 13, 2018

Very interesting, covered a lot of information very thoroughly for a 4-week course. Well-explained.

Na lição

Dealing with Uncertainty and Analyzing Risk

1. Adding Uncertainty to a Spreadsheet Model9:05

2. Defining Output Variables and Analyzing the Results7:52

3. Using Historical Data to Model Uncertainty7:43

Ministrado por

Manuel Laguna
Professor

Transcrição

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.

Baixar na App Store

Baixar no Google Play

Coursera
Sobre
Liderança
Carreiras
Lista de cursos
Certificados
Certificados MasterTrack™
Notas
For Enterprise
For Government

Comunidade
Learners
Partners
Developers
Beta Testers
Translators

Conectar
Blog
Facebook
LinkedIn
Twitter
Instagram
YouTube
Tech Blog

Mais
Termos
Privacidade
Ajuda
Acessibilidade
Imprensa
Contato
Diretório
Afiliados