The Statistical PERT downloads all offer the ability to see probabilities based upon the PERT estimate of the mean and also the mode, which in the templates are used as a surrogate of the mean. Why are there two sets of probabilities? Why not just one set using the PERT mean alone?
Let’s start crafting an answer by thinking of a perfectly shaped normal curve. In a perfectly shaped, perfectly symmetrical normal curve, the mean, median and mode are all exactly the same point on the curve: the very top of the curve. When using a perfectly shaped normal curve, the question of whether to use the mean or mode in Excel’s normal distribution functions, NORM.DIST and NORM.INV, is immaterial.
That immateriality starts to change, though, when dealing with skewed probability curves.
When I compare Statistical PERT to a PERT distribution (which is a special form of the beta distribution), I do so using a ratio scale multiplier of 20-21% and the PERT mean in the NORM.DIST and NORM.INV functions. That leads to SPERT probabilities that closely align with a Monte Carlo simulation using a PERT distribution. But the more skewed the curve, though, the less closely aligned the SPERT probabilities are, since SPERT relies on a normal distribution that assumes symmetry, not asymmetry.
I’m working on an analysis that compares SPERT with Monte Carlo simulation using 3-point estimates that imply a skewed curve. My analysis thus far shows that as long as the difference in ranges between the minimum and most likely value and the most likely and maximum value isn’t greater than a factor of four, SPERT can calculate probablities that are within 5% of a Monte Carlo simulation. Of course, the more symmetrical an implied curve is, the closer SPERT estimates are to a Monte Carlo simulation using the same 3-point estimate (and, for SPERT, a ratio scale multiplier of 20% or 21%).
Now lets consider for a moment a condition where the 3-point estimate implies an asymmetrical curve, but there is very strong confidence in the most likely outcome (that is, a very high kurtosis). The peak of the curve rises very high vertically and falls very dramatically from the peak and then extends rightward towards the maximum value. In words, suppose I say, “I am extremely confident the task will take 40 hours, but there is a very slight chance it could take as little as 30 hours and an equally slight chance it will take up to 80 hours, but I’m almost certain that the task will take exactly 40 hours.” This is the condition we’re dealing with.
A Monte Carlo simulation using the PERT distribution won’t capture this particular uncertainty very well. We’d have to either use a different form the beta distribution (on which the PERT distribution is based) or choose a completely different probability distribution that mirrors the scenario I just described where there is very high expectation in the most likely outcome, and the most likely to maximum outcome has a range that is four times greater than the minimum to most likely range.
The situation has gotten complex! But Statistical PERT is all about keeping things simple, yet still useful, so tomorrow let me address how this can be handled by using the mode (that is, the most likely outcome) as a surrogate for the mean in the normal distribution functions of Excel.