0:00

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.

Â 1:22

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.

Â 5:35

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.

Â