Welcome to Highline BI348 class video number 66.
Hey , if you want to download this file,
BI348 Chapter 11 video 66 and follow along,
click on the link below the video.
Hey, this is the third video in chapter 11
all about Monte Carlo simulation.
Now in video number one, we had an introduction
of Monte Carlo simulation.
Last video, we talked about randomizing
different variables.
And here's our comprehensive problem.
Here's the description.
I'm just going to scroll down here and show you
the bits I've already entered into the spreadsheet.
Of course, rule number one for simulation
is we follow our good spreadsheet model guidelines.
We have formula inputs here, our model down here.
Here's our company, Bay Air Services.
That's a compressor company down in Richmond, California.
They make compressors, and they're
interested in investigating through simulation
to learn about the probability of a loss for a new product.
Now really, they want to see if the new product is profitable.
If the probability is too high for the simulated loss,
then we'll have to consider making
a decision about whether we want to try and manufacture
this new compressor.
Now we have some set static variables
and some random variables.
Our set static variables, these are the variables
we're going to assume that won't change through our simulation.
Selling price per unit, that will be P, $199.
We're going to try to compete directly with those blue Emglow
compressors that you see like at Home Depot.
Hey, our total administrative in advertising
cost, TAC, that will be $850,000.
Our random variables are uncertain variables.
We're going to have direct labor cost per unit, that will be
DLC, it's a discrete variable.
Our distribution that we base our randomizing on
is a historical relative frequency distribution.
There it is.
Direct labor cost per unit, from past data
we've estimated those are the discrete amounts and here
are the probabilities.
We actually saw this exact distribution
in an example last video.
Our second variable, material cost per unit,
MC, it's a continuous variable using the uniform distribution.
There is our min and there is our max value.
Our third random variable is demand,
that's D, continuous variable based on the normal probability
distribution.
Our mean is 17,500, and the standard deviation is 5,000.
There's our math formula, demand times,
and we take the price per unit, minus direct labor
cost per unit minus material cost per unit.
And then once we get that total from our multiplication,
we'll subtract the total fixed costs,
or our total administrative and advertising costs.
Now, one thing we can do in this type of analysis,
we don't have to run a simulation.
Notice for each one of our uncertain variables,
we have best, base, and worst case scenario.
Now notice for a cost the best scenario is the lowest cost.
The worst is the highest.
Same for this material cost per unit, the lowest is the best.
The highest is the worst.
For our demand, the best is going
to be the highest number of units.
The base, and then of course the worst
will be the lowest number of units.
Notice we have to assume demand can't go below 0.
So actually, we could come down here in our model area,
and all I did was create a formula for the best, the base,
and the worst.
Now, we can clearly see that, well, there is potentially
a loss of 850,000, and of course that's when demand is 0,
so it's all of our fixed costs up here.
The base case is about a profit of 322,000.
And wow, that would be awesome, the best case $1.9 million
of profit.
Now the problem with this kind of analysis,
yes we see these three values, but we really
don't know what the probability of each of these is.
$850,000 is a lot to lose, but if the probability
is very small, then perhaps we want to, as a decision,
take on this project.
So that's where simulation comes in.
Now we're going to start with our direct
to labor costs, this variable right here,
and here's our probability distribution.
Just as we did last video, when we
get to our randomizing formulas, we have to have a lookup table.
And we have to create the first column
with the cumulative probabilities.
So we're always starting off with 0,
that's the lowest probability.
And for approximate match lookup,
that's what you need as your first value.
And then the subsequent values-- if we're doing cumulative,
I'm going to say, hey, give me the sum of the very first one,
colon close parentheses, and then
I'm going to lock this variable right here, F4.
That's an expandable range, Control Enter,
And I'm going to copy it down.
You can go to any particular cell and see sure enough,
it is expanding, so we get our cumulative total.
Now, we can use the rand function,
which randomly generates a probability between 0 and 1.
Use that as our lookup value, look it
up here using approximate match, and retrieve a direct labor
cost randomly.
Now last video, we talked about the details setting lookup
and the other various randomizing formulas
we're going to use so this should be a slight review.
All right, so I added some formatting there.
Now, we want to come down here-- I'm going to scroll down--
we can see all of our details for our variables up here.
We have our lookup table.
Here is our model area.
Now, we're going to start off with a simulation of on,
but we actually want to do it 10,000 times.
So I'm going to point to the fill handle,
and when I see my cross hair, I'm
going to right click drag down, and then back.
Let go, and a secret pop up menu pops up, and I say Series.
I'm going to make sure and say this series must fill down
the column.
The step value is one, and I want
to end at 10,000 so I type 10,000 in.
And as soon as I click OK, 1 to 10,000 Control down arrow,
control up arrow.
Now we need to create one, two, three randomizing variables,
and then build our final profit formula.
That's our profit on the right there.
So you ready?
Scroll down a little bit here.
Equals, and we're use a lookup function
to do approximate match lookup.
And the lookup value is going to be rand.
That generates a number between 0 and 1.
We talked all about that in great detail in our last video.
The array first column, second column, copy it down.
And lookup is different than view lookup.
It doesn't need to know the column number for that array
because it will always take the value from the last concludes.
Close parentheses, tab, so there is my randomizing variable
for direct labor cost.
If I hit F9, you can see it's randomly
selecting, according to these probabilities, our direct labor
cost.
Material cost per unit, we're going
to assume that this is a uniform distribution,
so we simply have to use rand between.
Now there's a bottom and a top, the min and the max.
And if I go to material, this is the min right here.
And I'm going to come down here.
Right now, that would be 75, and I don't want that.
I need pennies too.
So as we saw the last video, we multiply this times 100.
So really for rand, the bottom is 7,500 now.
Comma, the top 105, and I need to multiply times 100.
So really it's 1,050.
Close parentheses, not quite what I want,
because I want to randomize with pennies.
So then I divide by 100.
And there's a randomizing formula
based on a uniform distribution for material cost per unit.
If I hit the F9, to have two randomizing variables.
Now, we go on to demand.
And this is a normal distribution
with a mean and standard deviation.
So I'm going to start with the norm.dist is
for game in a probability.
Inverse is for getting an actual x-value.
Our random variable is demand, that's the x.
Probability, rand which will give us between 0 and 1.
That's the cumulative probability.
Norm inverse needs a cumulative probability there to get an x.
Again last video, we saw nice pictures and everything
to describe how this works.
The mean, there it is.
Comma, the standard deviation, there it is.
Close parentheses.
Now, this is not quite what I want, but let's hit Enter.
If I hit F9, you can see it's definitely
going to generate based on the normal distribution
and standard deviation of 5,000 and mean of 17,500,
the right values.
However, rand is going to give us 15 digits there.
And we don't want that, so we want to round this.
F2, R-O-U-N-D to round this, comma,
and the number of digits 0 to round to the integer,
Control Enter.
Now, if we copied this down 10,000 rows,
we will get negative values, and demand
can't have a negative value in our situation here.
So F2, we're going to have to treat this as one value.
The other value will be 0.
And I will say please always give me a max of number one
will be 0, comma or number two.
So max has a choice.
When this comes out negative, max will take 0.
When this comes out positive.
Max will select that once.
So there you go.
Enter, now if I hit F9 to randomize,
I have my three random variables.
Now we can build our final profit formula.
Equals the demand times open parentheses--
and I need my price, there it is right there.
Minus direct labor cost per unit,
minus material cost per unit.
Close parentheses, minus-- and there's our fixed cost,
administrative and advertising.
And that's our formula.
Now if I hit the F9 key, this is different than all
of the examples we've seen in the previous two videos.
Now we have one, two, three different variables
going into our final formula that are being randomized.
F9, F9, now we want to tell this simulation
to repeat 10,000 times.
And if we were to put all of the correct cell references locked
and all that kind of stuff and copy this down,
it would take too long to calculate.
So as we learned two videos ago, there's
a great way to use the data table feature.
Now the data table requires that there
are some formulas in the first row,
and then you highlight however many substitute variables
you want to substitute into your formula.
And tell data table, and it will calculate all them for you.
But the trick is, we're going to tell the data table this
is the formula input cell, and this cell
doesn't lead to any one of these formulas.
So it's a way of tricking the data table to simply copy
down these formulas 10,000 times and randomized them each time.
And it is a dramatic time saver in terms of formula calculation
time.
So you ready?
I'm going to highlight the first row, Control Shift, down
arrow all the way down to 10,000 simulations.
Control Backspace to jump back to the active cell.
Now I go up to Data, Data Tools, What
If Analysis, Data Table or the keyboard,
Alt DT for a data table.
There is no row input.
Column input, normally this is the column, right?
So these items are in a column, and we
want those substituted in.
But if you click on a cell that doesn't lead back
to any one of the actual formulas,
then all it does is copy these down blindly
without doing any substitution.
So when I click OK, there we go.
That is simply amazing.
Now I'm going to add some formatting.
10,000 rows of simulated values, that's
amazing because now we can take this profit, do some analysis,
and see whether we should take on this project.
What will our decision be?
Right off the bat, let's calculate average.
And I'm going to average all the profit numbers-- not the one
up there, but all the 10,000 below.
Control shift, down arrow, Control Backspace,
and I'm going to click on number one Control C to copy that.
So the average, wow 324,000, that's not so bad.
Even if I hit F9 a few times, right?
Oh yeah, so it's pretty consistent.
The count equals count Control V-- that's 10,000,
we already know that-- equals max.
Here's the max possible profit.
Probably an extreme value like that
is going to be unlikely, but $2 million bucks, that's
looking pretty good.
The min, 850,000, that's expected at demand equals 0.
The standard deviation, we've got
to know something about the variation
and I'm using the sample one.
Control V and Enter, so standard deviation and the mean
are almost exactly equal to each other.
Now, before we even knew our frequency distribution which
is always the ultimate goal here with this simulation,
I'm going to go ahead and calculate
the probability of a loss.
And I'm going to say criteria range
Control V, comma, in parentheses,
and I'm going to say loss.
Last video, we did less than or equal to 0.
I'm just going to say less than 0,
so 0 is not included in this.
Close parentheses, and I'm going to divide it by my count.
So the probability of a loss, wow almost 20%.
So if I hit F9, you could see the probability staying
in pretty close, 18, 19, 20%.
But now we want to do our frequency distribution.
Here's our range of possible values
we could get if we take on this project given
all of the probability distributions
for each one of our variables.
There's the range.
We want to calculate the frequency.
As we saw last video and back in Chapter 2,
you set up frequency function with the upper values
for the bins.
These are the bins that the frequency function
automatically calculates.
I'm going to highlight one more cell than I have upper values.
In the active cell at the top, I enter
the array function frequency data
array, that's all the values.
Control V, comma, bins, those are the upper limits
for each category.
Close parentheses, and this is an array function
so I hold Control Shift and Enter
to enter it simultaneously.
I immediately look up to the formula bar.
I see my curly bracket, so I know
Excel understood it was an array calculation.
There it is.
I need to also say equals the particular frequency divided
by left arrow, Control Shift, down arrow, F4 to lock it,
Control Enter, double click and send it down.
And there are all of our probabilities.
So the probability of between 500,000 and 250,000
is about 4.4%, between 750,000 and 500,000 less than 1%.
The one we have to worry about is this one
right here which is from $250,000 to 0.
So we do have some probabilities we
have to mull over and consider what
our decision is going to be.
But we can see we have some pretty big probabilities
without even doing a function to add them all up.
I could highlight those and see down here in the status bar
it says about 80% probability of having a profit above 0.
F9 will give us randomized values.
Look at that.
It looks pretty consistent.
We could also build a chart and look at the distribution,
but right now these probabilities
are helpful for decision making.
So in this video, we talked about a simulation model
where we had one, two, three randomized
or uncertain variables.
We built our model.
We did our simulation.
And we did our analysis to help with decision making.
All right, next video we will see
an example of a random binomial variable
used in simulation to decide whether a marketing
plan is reasonable or not.
All right, we'll see you next video.