0:00

There were two distributions that I did not show you

Â examples of in the cookies Monte Carlo simulation example.

Â One of them is the triangular distribution,

Â the other is the beta pert distribution that I'll be describing in the next screencast.

Â You're going to have to implement both of these in your Monte Carlo simulation project.

Â So, I want to explain what the triangular distribution is.

Â The triangular distribution is when you have

Â a lower bound L similar to some of the other distributions like the uniform distribution.

Â You have an upper bound U,

Â and then you've got a mode or middle.

Â So, mode is the most common,

Â and the distribution is just a triangle.

Â Using simple geometry you can obtain these equations that I show here.

Â There are two cases,

Â that is when you're on the left side of this triangle.

Â So, you're below the mode M,

Â and then in the second case is when you are above the mode.

Â In either case, what you're trying to do is you're trying

Â to generate based on this distribution,

Â you're trying to generate X,

Â that follows a triangular distribution.

Â So, using some math,

Â we can cope with these two equations here,

Â just like what we've been doing in the previous examples,

Â the area underneath this curve is one,

Â and what we want to do is we want to generate,

Â we want to generate a probability.

Â In the previous screencast I've called this R. So,

Â it's just a random number between zero and one,

Â and then we want to start from the left and sort of fill in that value,

Â that area until we can drop down and determine an output, the X value.

Â So, in order to generate a random number that follows a triangular distribution,

Â we're going do the same thing that we've been doing.

Â We're going to choose a random number that follows

Â a uniform distribution between zero and one,

Â and that's done using the RAND function in Excel or R and D in VBA.

Â Next, we're going to use if-then statements to convert R into an output.

Â We're going to use the equations that were presented in the previous slide.

Â These equations, so we're going to base analysis on these.

Â Now it's going to be a little bit more complicated than some of

Â the previous stuff because X here,

Â we need to find X.

Â We're going to be given a piece,

Â we're going to guess P which is equivalent to R. So,

Â we're going to determine, maybe point three,

Â and what we need to do, we're going to know M,

Â U and L, but what we need to do is solve back for X.

Â So, some of you may realize that this is a quadratic equation,

Â so we're going to actually use the quadratic equation to solve for

Â X given a P and all of these other factors.

Â For some of you, this might be more advanced than you want to see.

Â So, I'll be providing the code for this in

Â a separate function and file that I'll post on the course website.

Â But again, we're going to guess an R, R is equivalent to P,

Â and then we're going to back calculate out X

Â that corresponds to that. And we're going to do this.

Â So, each simulation, each time you're running a simulation,

Â just a point in your simulation,

Â P will be different.

Â So, for all of the Ps,

Â that's the random number,

Â we're going to generate different values of X.

Â So, that's how we can generate X values that correspond to the triangular distribution.

Â So, let me show you how to do this in VBA.

Â Here is the function in VBA.

Â Again, I'm going to provide this.

Â I'm calling this function triangular inverse,

Â given a P, that's the probability,

Â and I've got some information here given a probability P,

Â that's also what I've been calling R is between zero and one.

Â So, it's a uniformly distributed variable between zero and one.

Â So, given the probability P or R,

Â and if you know the lower,

Â upper and most common inputs,

Â then this function calculates a corresponding X value.

Â So, this generates an X value,

Â so a value between your lower and upper limits based upon

Â the triangular function using M as the most common input.

Â So, you're going to use this to output X values.

Â There is some complicated math here,

Â again it's a piecewise function because you have

Â the left of the mode and right of the mode.

Â So, you've got this piecewise function,

Â and again if you're not that mathematically inclined,

Â I wouldn't worry too much just use this directly in your Monte Carlo simulation project.

Â So, let's go through an example where L is two,

Â U is nine and M is seven,

Â so it's sort of like a offset triangle.

Â And we're going to then use VBA.

Â I'm going to show you how we can use this function that I've created,

Â the inverse triangular, to just randomly spit out X values that follow this distribution.

Â So, in general, a lot of our X values are going to be close to seven,

Â similar to the average of a normal distribution.

Â But, we should be getting the likelihood of getting something close to two is very small,

Â and then the likelihood of getting something closer to nine is also small,

Â but most of our values should lie around seven.

Â So, let me show you how we can do this in Excel using that function that I'm providing.

Â So, I'm going to type in an L, an M,

Â a U and in this case I'm just going to use two, seven and nine.

Â I want to go ahead and just insert,

Â I'm going make names from these.

Â So, I can use those,

Â I can use them L,

Â M and U in the formulas,

Â the functions, and then I'm going to create.

Â These are going to be random numbers that follow

Â that triangular distribution and if I start typing it in,

Â you see here I have my function.

Â You're going to be using this just in VBA

Â and I'll show you how to do that here in a minute,

Â but functions you can also use in Excel as I'm doing now,

Â and what we need to do is we need to generate a random number.

Â Just as we've been doing in Excel that's R, A and D,

Â and then we have our lower value,

Â our middle value and the upper value.

Â And when I press enter,

Â it's giving us a random value.

Â And now I can copy that down,

Â to give us a bunch of those that follow that distribution.

Â So, let's go ahead and do this in VBA.

Â So, I've got my function there,

Â and I'm just going to use sub test,

Â and I can just do message box and you won't be using message box,

Â but you'll have this somehow in your user form,

Â and I'm just going to do,

Â I can use my triangular inverse function,

Â I'm going to generate a random number and my low is two,

Â my middle or mode is seven and then the upper is nine.

Â So, you're going do something similar to this.

Â If I run this using F5,

Â we generate a number 6.97 and you can keep going,

Â you can do this many, many times,

Â and that's how you can generate random numbers that

Â follow a triangular distribution. Thanks for watching.

Â