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.

Â