1:06

As always, what we do,

the first step is to choose a random number R between 0 and 1.

And that's a probability, probabilities always go between 0 and 1.

We're going to do that in Excel or VBA.

You guys are going to want to do that in VBA for your project.

And then you're going to use the norm.inverse function in Excel.

You can use the norm.inverse function.

There is no built in function in VBA, so if you wanted to do this in VBA,

which you're probably going to want to do,

you can use the WorksheetFunction.Norm_Inv.

So what we do is we start with a value R that ranges between 0 and

1, so let's just say R = 0.3.

And then we always start from the left and

we put in that area into here and we see, how far does that make it?

And it might make it to there.

So that's the number that we're going to output, so maybe that's 6.5.

So 6.5 then would be the number in that simulation

that we would base our calculations on.

Maybe next time we choose another random number between zero and one, and

that's 0.75.

Then, we put in that area all the way up to 0.75.

And then wherever that drops down onto the x-axis, that would be the second, for

the next simulation, that would be the value that we use.

So let's go ahead and let me show you how to do this in Excel and VBA.

The example I'm going to work through I'll do baking soda.

So baking soda is a normally distributed, the price of baking soda is normally

distributed with an average of $2.82 per pound and

standard deviation of $0.50 per pound.

So I'm going to type in my average which was 2.82, the standard deviation.

In other to work with normally distributed variables, you have to have average and

standard deviation.

They have to be known, so I have that and

then I'm just going to generate a random number that follows this distribution.

So it should be close to 282, but there's obviously variability in this, and that's

why we do the Monte Carlo simulations because we analyze the variability.

So to generate a number that follows this normal distribution,

I'm just going to use the =norm.inv function.

And the probability, we're going to choose a random number

using the random number generator in Excel, which is just rand.

And then my average is going to be cell C2.

I'm going to make that absolute by pressing F4 with the dollar signs.

And then the standard deviation is cell C3, making that absolute.

So just by chance, we've generated a value of 2.40.

And then I can drag this down and do a bunch of them.

So in each simulation, you're going to be using different values of

the cost of baking soda using this process, all right?

So I have to simulate it as a bunch.

And I were to plot this or take the average.

Actually, let's go ahead and do that just to show you that the average of our

simulated results should be close to the overall average.

It's not quite because of the nature of simulations and stuff,

but the average of my simulated variables here is very close to the actual average.

Let's go ahead and do this in VBA because again,

you're going to be wanting to do this in VBA.

So I'm just going to do MsgBox just so we can generate this and

I'll do WorksheetFunction.Norm_Inv.

I'm going to do (Rnd, that's how you can generate a random

number with equal likelihood between zero and one.

And I am going to put my average of 2.82 and the standard deviation of 0.5.

And now when I go ahead and run this using F5, we generate a number.

All right, we can keep doing this.

So you going to want to use the right part of here,

you going to want to use the work sheet function in VBA to generate

just random values that follow those normally distributed parameters.

And the variables that are normally distributed, you're going to do this with.