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.
