The last distribution there we're going to talk about is the most complicated. It's known as the Beta-PERT distribution. So, to introduce the Beta-PERT distribution, let's take a look back at the triangular distribution. The triangular distribution is quite it's not very smooth and it's kind of a jagged, it just got the sharp corner here. What we do with the Beta-PERT distribution is sort of smooth this out. So, what the Beta-PERT tends to do is still has a lower value, still an upper value, and still a mode or most common, but it tends to sort of smooth this out into a nice smooth distribution. So, that's sort of the main difference between triangular and the Beta-PERT. So, the Beta-PERT might look something like this on the left, and it's based on this distribution known as the beta distribution which I will introduce in a moment. It's not that imperative that you know all the different aspects about these distributions but, I just wanted to introduce them just like all other distribution functions the area underneath the curve is one, so we can use this as a probability density function. The beta distribution by itself has shape parameters similar to how the gaussian distribution has two parameters that has average and standard deviation. The beta distribution has shaped parameters alpha and beta, and I'll sort of show you how this works here in a minute in excel. The Beta-PERT distribution always has a low and a high value, so, a and c, and when we do the PERT distribution part of this, then we add in a b here which is the mode or similar to M in the triangular distribution. So, we always have a minimum, a maximum, and then a mode and that sort of shapes the beta distribution into something that we want. So, let me first explain what the beta distribution is and I'm going to kind of simulate this in excel, and then I'll come back to this and show you how we make the PERT modification to this. This is a simulation of the beta distribution in excel. So I've just chosen alpha and beta. Remember, we have to have alpha and beta are parameters that go into the beta distribution so I have alpha and beta. I've also got my lower bound which is two, and my upper bound C which is four. So, these two things a and c are required, they're the upper and lower limits of the beta distribution. So, I've just plotted here from from X going from zero to four. We're going to that's the lower bound and the upper bound that's our low to high. And we are going to then what I've done here is I've just plugged in a formula for the beta distribution, and this reference is the x value that's found on the x-axis is the first argument, and we have alpha, beta and we're using false for the cumulative option. Again, this might be beyond the scope of what a lot you want to know but I just wanted to kind of show you what this what's involved with this and then we've got our lower bound c four which is little a and our upper bound. So, that's how I created the data here. The important thing that I want to show you is again the beta distribution depends upon alpha and beta, when they're equal it's a symmetric distribution so it's very similar to the Gaussian distribution. But then when I change these values maybe two and five it makes, it gives it sort of this rounded triangular distribution appearance, so it's asymmetric, you have more on the left side than the right side. So, we can change these parameters to sort of effect what's going on with the distribution. The Beta-PERT distribution is a modification of the beta distribution and what we do is we re-parameterize the beta distribution, and we use these two equations here. Again, remember that the triangular distribution has a and c, the low and high, but the triangular distribution also has a mode which I call M, it's also equivalent to b here. So, it's the most common it's sort of where the peak is of the Beta-PERT distribution. So, we've re-parameterized the beta distribution. We calculate alpha and beta their functions of a, b, and c. So, we put in these equations here to calculate the alpha and beta and then those values of alpha beta just go into the beta distribution. So, again A is the pessimistic value, C is the optimistic value, and B is the mode, the most common value. In the Beta-PERT distribution is simply a rounded triangular distribution. If you're interested PERT, forget when this was developed but I think it was something to do with the US military. Program evaluation and review technique useful for project completion, time analysis, so a lot of new business and finance people will probably be interested in using the Beta-PERT distribution. To use the Beta-PERT distribution in VBA, just like we've done with the previous distributions we generate a random number between zero and one using Rand or Rnd in VBA. We calculate alpha and beta using those formulas on the previous slide, in terms of little a, little b, and little c. Then we use Excel's inverse beta function to convert R to a value between a and b. So, A in Excel we would use BETA.INV, the first argument is our random number that we generated between zero and one and then we use alpha and beta. Alpha and beta were calculated using the formulas on the previous slide. I'll show you here in a minute, and then we have the lower bound and the upper bound. You might be wondering where b is taken into account, but remember b has been taken into account in the calculations for alpha and beta. And in VBA we could just refer to the WorksheetFunction.Beta_Inv or alpha beta a and c. So, let's go ahead and do that in VBA. Before I show you this in VBA, I want to just kind of show you what this looks like in Excel, just graphically visually see how it's offset. We have a, b, and c. Remember, b is the most common, a low bound, c is the high bound, b is kind of a mode in the middle. And if I put the mode exactly between the two, it's symmetric. So one, two, three it's symmetric, but then you can kind of shape this by changing what would b is, so we can change that to make it an asymmetric PERT distribution. So, it's essentially the triangular distribution but nice and rounded and smooth. Let me show you how to do this in VBA syntax. I'm just going to like I've been doing we just going to provide an example here message box. Before we do that we need to define some things. So I'm dimming my variables a, b, and c are all double's, alpha and beta. For this example I'm just going to choose a to b one, b to be equal to 1.5, and c to equal three similar to the example I just showed on Excel. In your project, you're going to have the user define these values, right? So that's going to be defined in the user form. So, we have a, b, and c. We also need to calculate alpha and beta so I'm just going to plug in some formulas for these alpha and beta equations here on the left. So, I got my alpha and beta, the next step then recall is to well, we first need to determine a random number, we're going to do that. But the next step then once we have alpha and beta computed is to use the beta inverse function, so we're going to reference the worksheet function BETA.INV. I'm just going to message box this just so we can generate some sample data. Message box and we do a worksheetfunction.Beta_Inv and we need to calculate a random number so I'm going to use VBA as random number generator Rnd, that's a number between zero and one. Anything between zero and one is equally likely, that will give us sort of a probability, and then the second argument is alpha so I'm just going to refer to our alpha that we calculated then beta, and then we have our low bound a little a and our upper bound little c. So that's how then we can create, we can generate a random variable that follows the Beta-PERT distribution between one and three with the mode around 1.5. So, if I press F5 we generated a number here. So, it follows our distribution. Run this again, we calculate another one and so on. So you would do this for each of your simulations and your Monte Carlo simulation you're going to generate a new value of a random number that follows this distribution. Hopefully, you enjoyed this screencast and good luck with your project.
