Welcome to HIghline BI
348 class Video Number 64. If you want to download
this workbook BI 348 Chapter 11 Video 64, click
on the link below the video. Hey, this chapter is Chapter 11. We're going to talk about the
amazing Monte Carlo simulation. Now, we actually have six
videos, and in this video, we're just going to have a basic
introduction of Monte Carlo simulation, and
we're actually going to compare modeling that we've
done earlier in the class and see how it's different
than a Monte Carlo simulation. Then in our next
video, we'll talk about creating random variables,
which is the essence of a Monte Carlo simulation, and then we
have four awesome examples. Now, before we go into our
next sheet, look at this. I left the sheet 1. I'm going to double click
this and call this Topics. All right. Now, we want to go
over to the sheet and talk about what's
not a simulation. So we're going to go to
the sheet, Not Simulation. Now, we've done this earlier
in the class, Chapter 7 and other chapters,
we've built a model. We have our formula
input area also called parameters, assumptions. We have decision variables,
but here's a new term, set or static variables. So far in this class, all we've
had are set static variables, and what does that mean? We have price, demand,
total fixed cost, and variable cost per unit. Each one of these
is a single number. So when we come down here
and we build our model, notice early on in the class,
we always built our math model to express the
logic of the formula and then we build our formula. So I'm going to
build the formula with what are called set
or static variables equal sign the demand times
open parentheses-- and notice each time I
click on a cell-- there's the price-- it's just
C3 pointing up there. It's a single cell looking
at a single number. Those are set static variables. I'm going to say price minus
variable cost per unit close parentheses minus
total fixed costs. Now, notice four cell
references, four formula inputs. When I Control-Enter, it
calculates the profit. If I change one of these
formal inputs-- again, this is set or static variable,
notice I'm changing it, so it's not really set or
static because I'm allowed to change a formula input. That's the whole reason
that Excel was invented. But watch this. I'm still only putting one
number into the formula. So when I change the
price to 20 and hit Enter, instantly the formula updates. Now, the two important things
about a set or static variable is, the first thing is,
there's a single formula input and there's a single
output from our formula. Now, let's envision a
different situation. All of these numbers
are our best estimates, but what if when we got to
estimating product, variable, cost? We absolutely have no
confidence that it's going to be exactly 15. But perhaps we're confident that
the range of prices could go from $13.50 all the way to $16. That would be a random variable
or an uncertain variable. And that's the essence
of what a simulation is. We're going to have some
variable that's not certain and we're going to need to
throw many different possible potential values for that
variable into our formula, and we will get many
different outputs. That's a simulation. Let's go over to
this sheet simulation and look at our first example
of an uncertain random variable. Now, here's our same
formula, inputs, parameters, assumptions, decision variables. Set static variables price,
demand, and total fixed cost, we'll assume are
set, are static, but the uncertain variable
will be product, variable, cost per unit. Now, luckily the accountants
were paying attention. They built this relative
frequency distribution from purchase
information this year and we're going to
assume that the purchase information from this year
will be relevant for predicting next year. But here it is prices $13.50
up to $14 happen 20.5% of the time. $14 up to
$14.50, 18.5% of the time, all the way up to $15.50
up to $16, 20.75%. So any time we have an
uncertain random variable and we need to use
it in our simulation, we have to note something
about the distribution of that variable. Now, we went ahead
and plotted this and sure enough, it looks
like a uniform distribution. The next step is then
to use that knowledge of the distribution
and create an Excel formula that will randomly
generate that variable. Now before we do that, I want
to go over to our PowerPoint slides and just remind ourselves
from our pre-req class of some of the distributions we saw. Discrete probability
distribution. This is two videos ahead,
we'll have direct labor cost, and it won't be a continuous, it
will be either 39 or 40 or 41. Notice we plotted this
from our past data and we can see each one
has a different probability of occurring. We also saw other discrete
probability distributions such as the binomial
probability distribution where we had a certain number
of successes and n number of trials. Another distribution,
this is the continuous one we're going to use
in this video here, and we also saw things
like the normal probability distribution, and a
couple of videos ahead, we'll have a demand
variable that will follow this distribution. So knowledge of the actual
probability distribution is required if you're
going to do simulation. Now, let's go back
over to Excel. All right. Here is the min. Here is the max
value for our range. We know our distribution
is continuous approximated by the uniform
distribution, so watch this. In Excel, there are a bunch of
great functions for randomly creating variables. Now, the RAND function and
the RANDBETWEEN function follow the uniform distribution. In this video, we'll
look at the RANDBETWEEN. Next video we'll see
RANDBETWEEN, RAND, BINOMINV, NORMINV, VLOOKUP, and a
bunch of other functions for creating random variables. All right. In this cell right here, I
need to randomly create numbers between 13.50 all
the way up to 16. Hey, the RANDBETWEEN
function is amazing for this. All you have to do is give
it the bottom and the top and it will generate
random numbers following the uniform distribution. All right. So you already? We're going to start off
with just a simple example. Hey, I'm going to click on the
MIN comma and the MAX, closed parenthesis. Now, when I enter
this, it's not going to exactly work
correctly, but we'll see that it does randomly
generate numbers. Hit Control-Enter. Now, you have to hit the
F9 key, F9, F9, F9, F9. Each time I hit the F9 key,
it's randomly creating a number based on the min, the max,
and the uniform distribution. Now, that's not
exactly what we want, so I'm going to hit F2
to put it in Edit mode. I really want $13.50,
$13.51, $13.52, all the way up to
$15.99 and $16. So I'm simply going to
multiply each number-- min and max-- times 100. If I Control-Enter
and hit F9, you can see I get $15.99,
$15.27, $14.75. Not what I want. I need to slide the decimal
two times, so F2 divide by 100. And there is our formula
to randomly generate numbers based on the
uniform distribution and our past accounting
data of min and max. There it is. F9 F9, F9. That is beautiful. Now, we have to create
our simulation, which means we have to
throw that number, that formula into our model and
then simulate it many times. That means we can have
many output values. Now before I do that, I'm
actually going to hide, so I'm going to
hide all of these. I clicked on Row Header
held Shift, clicked on 7, right click and Hide. All right. So you're ready? This is going to
be pretty amazing. Equals demand times,
in parentheses, the price-- those
two are static-- minus our variable cost--
random uncertain variable close parentheses-- minus
another static input. By the way, in
this video we have just one uncertain variable
and a bunch of static. We could actually
make all of these have a range of values
based on a distribution and we'll do that
in a later video. But here Control-Enter. Now, if I hit F9, F9, whoa. So you can see that
that's a good profit, but once in a while,
we're going to get a loss. So let's run the simulation. The simulation will
actually repeat this and we're going to
repeat this 10,000 times. And the beauty of that is we'll
have a full range of values, and we can create a relative
frequency distribution to calculate the
probability of each outcome. And it will tell
us as a decision maker what's the probability
of getting a loss. Now, if we were to put that
whole formula in the cell and copy it down 10,000
times, it would work, but it would calculate
really slowly. So there's a great
trick in Excel. You set up your one formula
with formula inputs including a random variable
and we're going to use the Data Table feature,
which will, in essence, copy this formula down
10,000 times, and it will calculate much
faster than if we actually created all the cells
with regular formulas. Now, watch this. I need to have my
number of simulations go from 1 down to
10,000, so we're going to learn a great trick. I'm going to point to the
fill handle in the corner and when I see my cross hair
or Angry Rabbit, right click. Drag down then drag back--
I'm right clicking, remember? And when I let go, a
secret menu pops up, and I want to point series. Now, in series, I want
to say, fill the series down the column. The step value is going to be
1, and the stop value is 10,000. When I click OK,
instantly, it went from 1 control down arrow
to 10,000 control up arrow. Now, let me Control-Z. I always
do it this way, right click, pull back series. But in case you forget that, you
can go to Home over to Editing, Fill, and there it is. Series will bring up the same
exact dialog box, Columns. By the way, I forget
this all the time. I say, 1, 10,000. If I click OK, it's going to
shoot the numbers in the row, so be sure and take Columns. I'm sure if you start doing
this for the first time, you'll make that same mistake
and it destroys everything, so be careful. Click OK. All right. You ready? The way a data
table works-- and we learn this back in
Chapter 7-- is you have at least one
formula at the top. We put our variable that we want
to substitute into the formula off to the side,
and the data table will make the substitution
except for this number has nothing to do with our formula. The beauty of this is
you see over here it says Data Table Column
Input Empty Cell? That's the trick. To get the Data
Table feature to copy this formula that has nothing
to do with these column inputs down. All it will do is it will just
get it to randomize many times. So are you ready? I'm going to highlight formula
Control-Shift down arrow. All of these numbers here
are theoretically our column inputs-- Data, Data Tools. What if Analysis, data
table, or the keyboard ALT-D, T for data table. Nothing for row input. Column input? You can click on any empty
cell, but I would be careful. I would be sure and label
it so that we're not going to mess with this. We know exactly that cell is
being used in our data table. When I click OK, that
is simply amazing. Now, I'm actually going
to Control-Shift down arrow and add some formatting. And there it is, the data table. And you can see up here there
is that array formula that's entered in. There's no row reference,
so it says nothing comma and then there's the column
reference right there, an the empty cell. In essence, what the Data Table
feature in this table array function did, is it tried
to substitute these in based on this cell
here but because it has nothing to do with a
formula, it just copies it down and it randomizes. You can try this on your own. You can copy a randomized
formula down 10,000 times and then see. Not always, but
most of the time, it will really bog
down your calculations. Every time I hit F9,
it calculates quickly. So the Data Table
feature really does us a great benefit when we're
doing simulations in that it speeds up the calculations. Now, all of these numbers here
we now use for our analysis. We need to calculate
the mean, which will be one estimate
representing all the simulated values. Standard deviation, what's the
min possible loss we can have? What's the biggest gain? What's the probability of
actual no gain or a loss? So let's come over
here and Equals Count, we're going to count numbers. Click on the top cell
Control-Shift down arrow, Control-Backspace. And I'm actually going
to click on this value 1 to highlight that whole
range and Control-C to copy. Enter. All right. So we have 10,000
equals Average. Here's our mean. Control-V, so 1,274. We could use that value
to make our decision. Hey, now that we've run
our simulation, on average, we're going to have
a gain of 1,274. But that's not the
complete picture yet. We also need to
look at variation of the sample,
Control-V, and Enter. So it looks like quite a big
variation in this data set. We also need to see what the
Min, what's the worst possible lost we could have. Control-V from our simulation. That looks like minus 1,400. The biggest possible
profit, Control-V, 3,975. We can also ask the
question, what's the probability of no gain,
that means 0 or a loss? Count IFs, Control-V
comma, m the criteria, and I'm going to hard code
this in in double quotes. Less than or equal to
0 close parentheses. Now, that will
count all of them. Divide by the total
count will give us-- and I've already formatted
this, so it will give us the percentage, 26.07. Now, if I hit the F9 key,
notice these are changing and so are these. So there is some variation in
each simulation that you run. Now, this is good analysis and
in particular, this one right here, the profitability
of no gain or loss, that could help
the decision maker decide whether they want
to run this product or not. But the full picture is going to
be our frequency distribution. So we want to scroll
down here, and we want to build a
frequency distribution, and we're going to use the
frequency function that we learned back in Chapter 2. I need to figure out the
upper limits for each one of my categories to count. I'm looking at the
min and the max. So I think I'm going to
start at minus 1,500, and it looks like I need
to go up past 4,000. So watch this. I'm going to use my same trick. I'm going to point
to the fill handle and when I see my cross
hair, right click, drag down, drag back, Series. I'm going to say,
fill down Columns. The step value, I'm going
to make it 500, Tab, and we're going
to stop at 4,000. Click OK. So instantly, I have
my upper limits. Now, for the frequency function,
this is an array function. It will use each one of these
as the upper limit for counting. So this category
right here, the count that's going to be in this cell,
will count everything less than or equal to minus 500,
but bigger than 1,000. All right. So with array functions, since
the frequency function will deliver one more value,
the number of upper limits, we highlight that number of
cells and then the active cell equals frequency. It wants the data
array, Control-V. That's that range for our
simulated values, comma, the bins. I want all of these. Those are the upper
limits, close parentheses. Now, this is an array function. You have to enter it with
the special keystroke, Control-Shift Enter, and
it will simultaneously enter and deliver all
of the frequencies to all of the cells. Ready? Control-Shift and Enter. There is the values. We told Excel that this is an
array function by Control-Shift Enter, but don't forget, when
you're doing array functions, look up in the formula bar. Do you see your curly brackets? That's Excel telling
you, I understood it's an array formula, so
it looks like it's working. There's our frequencies. I want to go ahead and calculate
the relative frequency, our estimated probabilities. I'm going to take the
particular count divided by-- and I should just
do it down here. Watch this. That's going to be the
total at the bottom, and I'm going to lock it
with F4, Control-Enter. Copy it down. Come down here, ALT
equals to add up all of the frequency counts from
our simulated values, 10,000. We get all of our
relative frequencies. Now, I went ahead and added
the explicit categories to remind you how the
frequency function works. If we give it the
first upper limit, counts everything less
than or equal to that, all of the categories in
between the min and the max upper limits have an
x in between where the upper limit
is always included but the lower limit is not. We come all the way
down to the last one. And the reason frequency
adds one more count than there are upper limits is
because it counts everything above that last limit. Those are the
explicit categories. Now, I'm going to add
some number formatting to show our relative frequencies
or estimated probabilities as a percentage
with two decimals. Because here is the real
power of simulation. I can clearly see the
probabilities associated with different outcomes. So for example, the probability
of getting 0 or less profit, we add all those up. That's the calculation
we did up here. We can also add all these
up to get the probability of getting the profit. We can look at a
specific category between 1,000 and 2,000. The probability is about 20%. If we hit the F9 key,
you see these are randomizing, so these update. Now, this is a
uniform distribution and we only have one
variable, so these should all be pretty uniform. In later examples, we'll have
multiple different variables in our simulation. This is the advantage
of simulation. We're able to see various
outcomes and the probabilities associated with each. Now, let's go over to
PowerPoint and summarize. I'm on slide 13. What is a Monte
Carlo simulation? When your spreadsheet model
has formula inputs that are uncertain rather than
provide just a single formula input like we've done so
far in this class, Chapter 7 and others, we have to
supply a range of inputs and thus, the model will deliver
a range of output values. We then look at the relative
frequency distribution created from the range of
output values to learn about the estimated
probabilities for model outputs. In this way, we can
provide decision makers with estimated probabilities
for uncertain variables so they can assess the risk
of undesirable outcomes and the likelihood of
desirable outcomes. Now, let's go to slide 16 and
review the steps for our Monte Carlo simulation. Step 1, create a spreadsheet
model using good spreadsheet model guidelines. Number 2, determine the
set or static variables and the uncertain
or random variables. Step 3, from past data
or other information, we have to estimate
the probability distribution for our
uncertain or random variables. Step 4, build randomized
formulas in Excel. In the next few videos,
we'll see various ways to create random variables. In this video, we
saw the RANDBETWEEN. Step 5, create a simulation
using that awesome Excel Data Table feature and
the column input pointing to an empty cell. That one speeds up calculation. 6, we analyze simulation data
with mean, standard deviation, min-max, relative frequency, and
other techniques for analyzing. And finally, the main
advantage, decision makers can see the full range
of possible values and the likelihood
or probability of each potential outcome. Now, in our next video,
we'll look at various ways to randomize variables
in Excel, looking at various distributions,
and then the four videos after that, we'll have
four awesome examples. All right. We'll see you next video.
