0:00

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.

Â