Useful quantitative models help you to make informed decisions both in situations in which the factors affecting your decision are clear, as well as in situations in which some important factors are not clear at all. In this course, you can learn how to create quantitative models to reflect complex realities, and how to include in your model elements of risk and uncertainty. You’ll also learn the methods for creating predictive models for identifying optimal choices; and how those choices change in response to changes in the model’s assumptions. You’ll also learn the basics of the measurement and management of risk. By the end of this course, you’ll be able to build your own models with your own data, so that you can begin making data-informed decisions. You’ll also be prepared for the next course in the Specialization.
4.2 Connecting Random Inputs and Random Outputs in a Simulation
Loading...
Del curso dictado por University of Pennsylvania
Modeling Risk and Realities
1140 calificaciones
University of Pennsylvania
1140 calificaciones
Curso 3 de 5 en SpecializationBusiness and Financial Modeling
De la lección
Week 4: Balancing Risk and Reward Using Simulation
This module is designed to help you use simulations to enabling compare different alternatives when continuous distributions are used to describe uncertainty. Through an in-depth examination of the simulation toolkit, you'll learn how to make decisions in high uncertainty settings where random inputs are described by continuous probability distributions. You'll also learn how to run a simulation model, analyze simulation output, and compare alternative decisions to decide on the most optimal solution. By the end of this module, you'll be able to make decisions and manage risk using simulation, and more broadly, to make successful business decisions in an increasing complex and rapidly evolving business world.
Conoce a los instructores
Sergei SavinAssociate Professor of Operations, Information and Decisions
The Wharton School
Senthil VeeraraghavanAssociate Professor of Operations, Information and Decisions
The Wharton School
Welcome to session two of the fourth week of modeling risk and reality scores.
In this session, we will go over converting an algebraic formulation
of a simulation model into a spreadsheet form.
In particular, we will look into the details of setting up and
running a simulation in Excel.
We're starting session two of week four of our course.
In the first session, we have talked about the differences between two approaches to
modeling uncertainty, scenario based distributions and
continuous distributions.
But I've also introduced an example of a company that needs to decide
how to design a new apartment building in the face of uncertain demand, and
discussed the notions of random inputs and random outputs in a simulation.
In this session, we will continue to work with this example and
see how we can set up and run a simulation in Excel.
Okay, let's start by recalling the example we focus on.
We're describing a decision facing a company that plans to construct a new
apartment building.
The company will have two types of apartments in this building,
regular and luxury.
The decision is effectively how many floors to allocate to regular apartments,
and how many to luxury apartments since floors cannot mix the apartments,
and each regular floor will have exactly eight apartments and each luxury floor,
exactly four.
Effectively, there will be two sales phases for new apartments.
We can call initial phase and salvage phase.
During the initial phase, the company plans to charge prices that will generate
a profit of $500,000 for each regular apartment sold, and
of $900,000 for each luxury apartment sold.
In the salvage phase, all of the apartments unsold in the initial phase,
if any, will be disposed at lower profit values, $100,000 for
a regular apartment and $150,000 for a luxury apartment.
The key uncertainty factors in this settings are the numbers of buyers for
each apartment type the company will get in the initial sales phase.
The company projects that the number of regular buyers can be modeled
using a normally distributed random variable with mean of 90 and
a standard deviation of 25.
Also, the number of luxury buyers will be modeled as a normally distributed random
variable with mean of ten and a standard deviation of three.
Normal distributions are convenient, in particular, they only use two parameters,
mean and standard deviation, but they can produce fractional values and
negative values.
We need to make sure that we account for
this when we generate integer positive demand values using those distributions.
As we discussed in the last sessions, we will be using simulation to answer
question regarding the distribution of the profit that the company will earn.
This is an algebraic formula that connects random inputs that the company
cannot fully control, demand for regular apartments, D-R, and demand for luxury
apartments, D-L, to a random output that the company is interested in, the profit.
Simulation will proceed to generate instances of random inputs and
to calculate the corresponding values for the random output.
So this is how we're going to generate random demand values.
Let's look at the demand for regular apartments, D R.
First.
We're going to generate a random value VR
from the normal distribution with mean 90 and a standard deviation of 25.
But we'll see later in this session how to do it in Excel.
Now, a normal distribution can in theory produce instances that fall between
negative infinity and positive infinity and that can take fractional values.
So we can get a value of the R that is fractional, like 93.7.
We can even get a value that's negative like -3.4.
We need to have a plan of how to use the values coming from the normal distribution
to model the non-negative integer demand values like 90, 78, or 110.
Here's what we're going to do.
We'll calculate the demand value we're going to use in our simulation
using the E formula.
Let's see how it works.
The E formula has the following structure.
If condition, choice 1, choice 2.
If condition is true, the value that the formula uses is equal to choice 1.
If condition is false, the formula is equal to choice 2.
For example, the formula for use has VR less than zero as a condition.
So if the value that the Excel generates for us from the normal distribution is
negative, DR will be set to zero.
Otherwise, DR will be set to equal to the integer part of VR.
The integer part of VR is the largest integer that does not exceed VR.
So we generate the random value from the normal distribution, VR.
We convert this value to a DR we can use as a realization of non negative
integer demand, and we plug it in into the formula for the profit.
We can do the same for the random value of the demand for luxury apartments.
In particular we first generate a random value from the normal distribution with
mean ten and standard deviation of three.
Convert it using a formula to the value that we can use as demand realization,
and plug it into the profit formula.
So this is how we're going to use simulation to generate the values of
the random inputs, the demand values and then, we're going to use this formula
to calculate the corresponding value of profit.
The sequence of steps which is described is usually called a simulation run.
The simulation run generates a pair of demand values, one for regular apartments
and one for luxury apartments, and the corresponding value of the profit.
We can repeat the simulation runs as many times as we like, and
get a sample distribution of profit.
We call it sample distribution because it is based on a finite number of
samples from demand values.
In order to obtain the true distribution of profit, we will theoretically need to
take infinite number of realizations of demand values DR and DL to make sure that
the entire distribution of DR and DL participates in the simulation.
But generating infinite number of realizations will take infinite time, so
we'll always have to settle for a finite sample.
The larger is the number of simulation runs,
the closer is the sample of the demand values to the continuous distribution.
And the closer is the sample distribution of profit to its true distribution.
So in practice, running long simulations, that is,
simulations with large number of simulation runs, is essential for
getting reliable estimates for reward and risk measures.
We'll return to the subject in session three.
Once we have a sample of profit values, we can use it to estimate the expected
profit, the standard deviation of profit, the probability of the profit to fall
below a threshold, and any other reward and risk measures.
Excel can help us run this simulation as well as analyze its results.
We're turning now to a spreadsheet star growth underscore zero to see how we're
set up and run the simulation of profit values for the star growth company.
Okay.
This is the cell filed star growth underscore zero that we will use for
running a simulation for the profit of the star growth company.
We have separated this file into three areas.
The area in the top left corner, A4 through C15,
contains the problem parameters.
He will have the profit values obtained by selling a regular or
a luxury apartment during the initial sales phase and
the salvage phase the cells B5 through C6.
The profit threshold of $45 million the company considers to be acceptable.
Cell B8.
The numbers of apartments that describe
the decision that Stargrove wants to evaluate.
Cells B11 and C11.
And the parameters of the normal distribution that describe demand for
regular and luxury apartments.
Cells B14 through C15.
The second area in the file is where the simulation is set up and run.
Cells A17 Through I28.
Let's have a closer look at this area.
We're going to run a simulation with ten simulation runs.
Which means that we will generate ten pairs of values for
the demand for regular and luxury apartments.
As we discussed earlier, the Excel will generate ten pairs of values VR and
VL from the normal distribution with mean of 90 and
a standard deviation of 25 for VR, and the normal distribution with mean 10 and
the standard deviation of three for VL.
Those values will be stored in cells B19,
Through.
C28, colored in green to make sure that the random inputs
into the simulation stand out visually.
For example.
So for B19 and C19 we'll store the values for
the R and the L in the first simulation run.
Currently we have put the expected values for the R and the L in all of those cells.
90 for the R values and
ten for VL values.
And we will later replace them by random instances,
taking from the respective normal distributions.
Now, cells D19 through E28,
calculate the corresponding values of DR and DL.
The values in those cells use E function to create non-negative and
integer random demand values.
For example, cell D19 converts
the value of VR from the cell B19.
Here's the formula.
We will use DR and DL values from cells D19 through E28.
To calculate the profit values for each simulation run.
We calculate the profit for each simulation run in three steps.
In the first step, we use the regular demand
values DR from the cells D19 through D28.
To calculate the profit contribution from the sales of the regular apartments.
Those profit contributions are calculated in cells F19
through F28, for
example cell F19
calculates the profit from sales of regular apartments if the demand for
regular apartments is given in cell D19.
The formula in the Cell F19.
It's profit contributions from initial and
salvage sale phases, following the corresponding formula in the slides.
In the second step, we use the luxury demand values DL from the cells E19
through E28, to calculate
the profit contribution from the sales of luxury apartments.
Those profit contributions are calculated in the cells G19 through G28.
Using the formulas similar to the ones
in the cells F19 through F28.
Finally in the third step the profit contributions from sales of regular and
luxury apartments are added in cells H19 through H28.
Cells H19 through H28 contain simulation output values that is
values of the profit generated in ten simulation runs we're considering.
We color them in blue to make them visually distinguishable.
Cells i-19 through i-28
contain the indicators of whether the profit value obtained
in a particular simulation run falls below the threshold of $45 million dollars.
For example, the formula in the cell i-19,
if H19 less than B8,
then it's one, otherwise it's zero, puts the value of one into the cell
if the profit value in the cell H19 is below the threshold of 45 million.
If the profit value in the cell H19 is at or above the threshold
of 45 million the formula in the cell i19 evaluates to zero.
But we'll use the values in the cells i19 through i28
to estimate the probability of the profit being below the threshold.
Finally, the last area of this file,
comprised of cells F4 through G5,
Contains the estimates for the reward and risk measures we're interested in.
Cell G4 contains the average
of the simulated profit values from the cells H19 through H28.
This is our estimate of the expected profit value.
Cell G5, Contains the average
of the simulated indicator values From the cells I19 through I28.
Because it is the average of zeros and ones the number of the cell
G5 as we will see registers the frequency with which
ones are placed in the cells I19 through I28.
This will become clear once we're on the actual simulation.
Now it is time to run our simulation.
Next, we'll ask Excel to replace the numbers currently in cells b19 through
b28, by random values, VR.
Generated from the normal distribution with mean 90 and
a standard deviation of 25.
We'll also ask Excel to replace the numbers currently in cells C19
through C28 by random values, VL, generated from the normal distribution
with mean ten and a standard deviation of three.
That we do to accomplish this task is to use Excel's capability
to generate random variables from several popular distributions.
For this, we'll need to use Excel's data analysis tool pack.
We'll go to the data tab and click on data analysis button.
In the appearing menu we select random number generation option and click OK.
In the appearing dialog, we select one as the number of variables and
ten as the number of random numbers.
This instructs excel to generate ten instances of a single, random variable.
Now we need to specify the type of the distribution we want, and its parameters.
We select normal, and put ninety as its mean.
And 25 as its standard deviation.
Next, in order to start a simulation, we need to put in a value of random seed.
Let's put some combination of numbers, say 123.
Into the random seed box.
The role of random seed is as follows.
Excel does not generate truly random numbers,
it generates numbers usually labeled as pseudo random.
But those numbers roughly speaking are random enough
to help us generate statistically valid predictions.
Random seed instructs Excel to generate ten
pseudo random numbers from a distribution we specified in a particular way.
We do not really need to know much about how the pseudo random variables
are generated by Excel to run an interpret simulation.
Keep in mind however that if you are running Excel 2013 on Windows, and
you set up your model in the same way I do.
And use the same number of simulation runs in the same seed.
You will generate exactly the same demand values as I do.
So if you want to compare your simulation results to mine,
you should be setting up your simulation model in the same way as I do.
Use the same number of simulation numbers, like ten, and
the same seed, like one to three.
Note that if you are using Excel on Mac or using some other spreadsheet program
like Google Sheets, you will get different simulated values
even if you are getting the same number of simulation runs and the same seed as I do.
This is normal as the algorithm used for
generating pseudorandom numbers maybe different on different systems.
In practice, it does not met at much
with pseudo select as long as you run a fair little long simulation.
In other words, as long as the value in the number of random numbers box is high.
So for a simulation that has only ten simulation runs,
the result of the simulation you will get in terms of risk and
reward estimates can change quite a bit if you choose a different seed value.
But if you run a simulation with say 10,000 simulation runs,
it will not really matter in the end what seed you select.
Finally, in the output options,
select the cell B19 as the output range.
This way, excel will put ten random values of generates into the cells
b19, through b28.
When you click OK, Excel
will generate ten random numbers into the cells B19 through
B28 using the distribution and the parameters we specified.
The numbers in cells B19 through B28 are all instances of a normal
random variable with mean of 90 and the standard deviation of 25.
Now let's generate random values for the demand for luxury apartments.
Again, we're going to the data analysis tool pack.
We click on random number generation.
We specify one in ten instances.
Choose normal distribution with mean ten and standard deviation three.
Choose a random seed of say one, two, three, four.
And select C19 as the output range.
After we're on the simulation, we'll get ten random instances
of normal random variable with mean ten as striation of three,
placed into the cells C19 through C28.
The excel changed the color of our random input cells.
Let's make them green again.
[SOUND] So we have run
our simulation with ten
simulation runs.
For each run, we have generated random realizations of the demand values for
regular and luxury apartments and calculated the correspondent and
profit values.
We see that out of ten scenarios we simulated, only in two scenarios, scenario
one and scenario six the simulated profit value was below that threshold.
Observe that the formula in cell G5 basically
counts the number of times one was observed in the cells I19 through I28,
and then it divides this number by ten.
This is what the average of zeros and ones in the cells I19 through I28 calculates.
We will save the results of this simulation in the file Stargrove and
analyze it in the next session.
Now we have designed and run our first simulation model.
In the next session, we will look at the interpretation of simulation results.
Explora nuestro catálogo
Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.
Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.
© 2018 Coursera Inc. Todos los derechos reservados.
