Another type of distribution that you're going to have to implement in your Monte Carlo simulations is the discrete distribution. So in my example with the cookies I have butter, eggs, and chocolate chips as discrete variables. A lot of times you'll have parameters in the model that you're trying to analyze that can only take on discrete values. So in this example butter may be you can get it from three different sources 25% of the time you can find a cheap butter for $0.60 a stick. Most of the time you can't find the cheap butter and you have kind of like an average butter that is $0.75 per stick. And then sometimes when both of those are sold out or unavailable, you have to pay $1 per stick. So you've kind of go these probabilities of discreet variables. So it's like the one or the other or the other. So you can have multiple things, eggs, another example here maybe. 20% of the time you can find cheap eggs but most of the time, eggs are $0.25 per egg. And if those are sold out 30% of the time you have to pay over twice per egg. And so it's really important to be able to accommodate discrete variables into your Monte Carlo simulations. A lot of business folks will want to make note of this,because obviously sometimes you have to make decisions that is one choice or the other. Maybe if your looking at investing in a major project, there's a 30% chance that lands going to cost. Maybe you're looking at one particular option for purchasing land and another option for purchasing land. And there's a 30% chance you'll be able to get one option. There's a 70% chance you'll be able to get the other option. So these discrete functions are quite useful. I've just got an example here of a discrete distribution. There's a 20% chance that the value for x will take on 1. There's a 45% chance it'll take on a value of 2. And then there's a 35% chance it'll take on a value of 3. The total area underneath these curves or adding up all the bars should equal 1. All options add up to 100% probability. So in discrete distribution you have different probabilities for each of the possible outcomes. Now to generate a random number that follows a discrete distribution it's just like what we've been talking about in the previous distribution. You always choose a random number between 0 and 1 equally likely, either using the rand function in Excel, or RND in VBA. And then what you do is you always start from the left, and we start filling in the area that we get from R. So let's say R is equal to 0.1. Then we would start filling in the area. I might get half way up. And in that case our discrete variable is going to be 1. But then maybe I choose and R=0.7. What we do there is we start filling up. So I'm going to take up all of this. Then I have 0.5 remaining so I'm going to fill all of this. And after I fill both of those I'm going to have 0.05 remaining so I'll get somewhere in here. So wherever we end up after we filled in all of our R, so that the area that we've created in our random number is what we're going to get. So in this case if R=0.7 we're going to get X=3. If R=0.35 then you should that X=2. So we chose a random number then we use if then statements to convert R into an output so in this example. If R is less than .2, then x is going to be equal to one. Elself, this is sort of pseudo code here, Elself R less than .65, so it it's less than the sum of the first two bars, then x is going to be equal to two. If x is not one, then x is going to be 2 as long as R is less than the total sum of the first two bars. Otherwise, that means x is greater than 0.65, greater than or equal to 0.65. Otherwise, x is going to be equal to 3. So for the discrete distribution, which is going to kind of write some code to do this. We wouldn't ordinarily do this in Excel, so I'm just going to go straight to VBA. I'm going to have a test here. So I'm going to say Dim R as a double. R is going to be equal to our random number. So we can define that using R and D. If R is less than 0.2, then X is equal to 1. And I need to Dim X here as It's going to be an integer in this case because x can only be 1, 2, or 3. But in your stuff, in your project you're going to want to probably Dim that as the double, all right? Elseif, so if R is not less than .2, we can say Elseif R < the sum of the first two bars, we have .2 plus .45. Then x = 2 Else, otherwise, if none of those is satisfied, then x = 3 and we End if. So I can go ahead and press F5, and let me F8 through this just so we can see what's going on. So I choose a random number and it chose 0.014 and that is less than 0.2 so X is equal to 1. And I don't do anything with this so let me just do MsgBox x at the end. All right so there's our random variable one, two or three. So it chose one in that case. Let's do this again. We choose a value of 0.76. So 0.76 Is not less than 0.2, it's not less than 0.65. So we say x equals 3, all right? And then we message box that. So the only options are 1, 2 and 3. The different values we put in here will give us sort of the relative probabilities of getting 1, 2, and a 3. So if the relative probability of getting a 1 is very small then this number is very small. If the probability of getting 2 is really big then this will be big, but it also depends upon this. Because again this, the 0.65 is the sum of the first two and so on. So that's how you can generate a number in VBA that follows a discrete distribution.
