Statistical PERT uses a normal curve to create probabilities. The Excel functions NORM.DIST and NORM.INV assume that every normal curve is symmetrical — even when it’s not. How do I know that? Take NORM.DIST for example. If I want the cumulative probability for the left-side area under the normal curve where the x is 49, the mean is 50, and the standard deviation is 10, the result is 0.460172163. So the x value of 49 is 1 less than the mean of 50, and the area under 49 and to its left covers 46% of the area under the curve. If I run the same calculation in another cell, but this time I find an x value of 51 (1 more than the mean of 50), and I find the area under 51 and to its right (eg, the cell function is: 1 – NORM.DIST(51,50,10,TRUE)), the result is still 0.460172163. So Excel’s normal curve functions think every curve is symmetrical even though, in real life, that’s nearly never the case.
This is what will cause differences between a Monte Carlo simulation of a 3-point estimate (using a PERT distribution) and SPERT’s probabilities. When we enter a mean into NORM.INV or NORM.DIST, Excel thinks that value is also the mode and median, too, because we’re dealing with a normal curve. With a normal curve, the mode, median and mean are all the same, they’re all the tippity-top of a bell-shaped curve.
But with SPERT, the mean is calculated using the PERT formula, and the mean isn’t likely to be the same as the mode or median because the implied curve created by a 3-point estimate is probably skewed, either to the left or to the right. But Excel doesn’t know this when the NORM.INV and NORM.DIST functions are employed in a formula. In Monte Carlo simulation using a PERT distribution, the simulator does recognize the skewing of the probability curve, and so the resulting probabilities obtained from many trials are properly adjusted in a way that SPERT can’t do. So Monte Carlo simulation produces superior results because it can respond to skewness in a probability curve.
Does that make Monte Carlo simulation better than SPERT?