The normal distribution, or sometimes you hear this as the bell curve or the Gaussian distribution, is a distribution that describes a lot of real world phenomena. And a lot of you are probably familiar already with the basics of the normal distribution. But numbers near the average are more likely than numbers near the tails. So, somewhere, getting a number in this region, is far more likely than getting a number down here in the tails or way up here in the upper tail. So we're going to use the normal distribution for a lot of the stuff in our simulations. And, just like all distributions, the area underneath this curve is one. So, what we can do in order to generate a number, let's say average is seven and standard deviation is two. We could use those parameters in some Excel formulas and VBA formulas to give us, just by chance, to give us values that lie along this x-axis. And in general, they're going to be closer to seven than they would be down in that left tail or up in the right tail. 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. Thanks for watching, and hope this helped.
