Monthly Archives: August 2015

Monte Carlo – better than Statistical PERT?

When I took the PMP exam in 2005, the book I used to help me prepare, Rita Mulcahy’s “PMP Exam Prep” which is still a popular PMP prep book, even though Ms. Mulcahy passed away a few years ago (her company keeps the book updated for the current iteration of the PMP exam).  In her book, Ms. Mulcahy said that “PMI suggests that Monte Carlo simulation will create a project duration that is closer to reality than CPM or PERT.” (page 105, 4th edition).

She’s right — but only if someone is skilled in creating a Monte Carlo simulation model, and only if that same person has access to good Monte Carlo simulation software.

I created SPERT because if you’re a project manager on a shoestring budget, and you don’t have access to Monte Carlo simulation, and you don’t want to figure out which probability distribution is the very best fit for every task on your project, you could still benefit from using a technique based in statistics and probabilities, and a technique that is “accurate enough” for most purposes.

SPERT is better than PERT because it provides an infinite number of estimates, each of which offer a corresponding probability that the estimate will be (or will not be) exceeded.  SPERT provides for the estimator’s subjective opinion, too, about the most likely outcome in a way that neither PERT nor Monte Carlo simulation can readily do.

But SPERT is just one technique, and as such, it has drawbacks just like other estimation approaches have drawbacks, too.  As a project manager, you want to have a lot of tools and techniques in your toolbox that you can pull out and use at any time.  SPERT is as easy-to-use, easy-to-understand technique that can help you gain insight into the estimation process, impacts, and it helps you to communicate in concrete terms what you very abstractly feel.

Monte Carlo simulation, when done correctly, requires thoughtful consideration of far more than a 3-point estimate and a subjective opinion like SPERT requires.  The pay-off for the added work of building a Monte Carlo simulation is that it will evaluate all the paths through the network diagram (not just the critical path) and the resulting normal curve created by the Central Limit Theorem can help you see what-if scenarios from every angle.   But not every estimation problem requires that level of rigor.  Sometimes, you need to put together an estimate very quickly, and be able to convey a sense of confidence in the estimate you provide a project sponsor or stakeholder.  Sometimes, using SPERT is an excellent choice; sometimes, a Monte Carlo simulation is.  Sometimes other estimation approaches are needed and are useful.

There are many more choices than PERT, SPERT and Monte Carlo simulation to estimate a project.   But if you have access to Microsoft Excel, you already have the necessary software to create SPERT estimates.  All you need now is the skill to do so.

Skewing and Statistical PERT

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?

Why 21% = Monte Carlo simulation-like results

Using the SPERT-7 Rule, you can create a Statistical PERT estimation for a bell-shaped uncertainty and achieve surprisingly good results by choosing “Medium-high confidence” for your subjective opinion about how likely the most likely outcome really is.  “Medium-high confidence” corresponds to a 21% Ratio Scale Multiplier for the Statistical PERT standard deviation formula, which is:  (Maximum – Minimum) * Ratio Scale Multiplier.

Let’s choose three estimation examples.  One will be for a symmetrical estimation, one will be for an estimate skewed to the left, and the other will be skewed to the right.

Here are the 3-points we’ll use:

  1. 5000, 8000, 11,000 (symmetrical)
  2. 1000, 8000, 11,000 (left-skewed)
  3. 5000, 8000, 25,000 (right-skewed)

Using the Statistical PERT template, the estimates with 85% confidence are, respectively:

  1. 9,306
  2. 9,510
  3. 14,686

Using a Monte Carlo simulation of the same 3-point estimates and specifying the PERT distribution (in Palisade’s @Risk program, that’s the RiskPert function), the resulting estimates at the 85th percentile are, respectively:

  1. 9,260, a decrease from the SPERT estimate of 0.50%
  2. 9,310, a decrease from the SPERT estimate of 2.15%
  3. 14,027, a decrease from the SPERT estimate of 4.70%

Using a symmetrical 3-point estimate led to very close results between SPERT and Monte Carlo simulation, which is rather (pleasantly) surprising because the 21% Ratio Scale Multiplier used by SPERT was just a number of convenience (it’s evenly divisible by seven).  Left-side skewing broadened the difference, but only by about 2%.  Right-side skewing created the biggest difference.  Why was that?

We’ll look into that in the next post.

Why 42% = Guesstimate

In yesterday’s blog post, I explained why the SPERT-7 Rule equates near certainty in the most likely outcome of a bell-shaped uncertainty with a 7% Ratio Scale Multiplier.  In today’s post, I’ll explain why under a condition of great uncertainty surrounding the most likely outcome, the Ratio Scale Multiplier is 42% in the SPERT-7 Rule.

Some people question how you can have a 3-point estimate that has a lot of uncertainty surrounding the most likely outcome, though, so let’s deal with that.

If you have expert knowledge, historical data, benchmarks, research and the like, you’ll probably have pretty good confidence in what the most likely outcome is, and how likely that outcome is relative to any other outcome.  But what if you don’t have expert knowledge, historical data, benchmarks or research?  What if all you have is a vague guess?

Even with a vague guess, you can still guess what the most likely outcome will be, and what the minimum and maximum point-estimates are, too.  You’ll probably be wrong; the actual outcome will probably not be what you expected.  But that doesn’t mean you can’t estimate under a condition of great uncertainty about the most likely outcome!

The SPERT-7 Rule says that a Guesstimate occurs when you distribute 100 hypothetical trials over a 3-point estimate using a 33-34-33 split, where 34 trials are equal to the most likely outcome, and both the minimum and maximum point-estimate have 33 trials.

Suppose we have a 3-point estimate of 60-120-180.  If we place those 100 trials in Excel, in cells A1 to A100, then use the STDEV.P function, we get a standard deviation equal to 48.74.  To calculate the ratio scale multiplier, divide the standard deviation (48.74) by the range (180-60); the result is 40.6%  If we model a skewed normal curve, where the 3-point estimate is 60-120-240, though, the standard deviation using STDEV.P is 74.48, and the Statistical PERT ratio scale is then 41.3%, which is a little closer to the SPERT-7 Rule’s 42% for a condition of great uncertainty over the most likely outcome.

Instead of using a non-evenly-divisible-by-7 value (and breaking the continuity of the rule), the SPERT-7 Rule uses 42%, which is close enough to be useful in creating Statistical PERT estimates.

Why 7% = Near Certainty

The SPERT-7 Rule says that to obtain a standard deviation for any bell-shaped uncertainty, find the range between maximum point-estimate and the minimum point-estimate, then multiply the range by 7%.  The product is a standard deviation to be used when the estimator is nearly certain that the most likely outcome will actually occur.

Why is that?  What’s so special about 7%?  Why not 5% or 8%?

In Statistical PERT, the ratio scale multiplier — for conditions where there is very high confidence in the most likely outcome — is found by distributing 100 hypothetical trials across only three possible outcomes (the minimum, most likely, and maximum estimates) by using a 1-98-1 split.

Think of a bell-shaped uncertainty with a three-point estimate.  In Excel, in cells A1 to A100, distribute 100 hypothetical trials of that uncertainty using only the values identified in your three points using a 1-98-1 split, where one cell contains the minimum point-estimate, one cell contains the maximum point-estimate, and the remaining 98 cells contain the most likely outcome.

Then, use STDEV.P to find the standard deviation for that data set (so, =STDEV.P(A1:A100).  To obtain the ratio scale multiplier, divide the standard deviation by the range between the maximum point-estimate and the minimum point-estimate.  The result will be somewhere pretty close to 7% (assuming your three-point estimate doesn’t show extraordinary skewing to the left or right of the mean).

Now, try changing to a different three-point estimate by changing the values in cells A1 to A100, but retain the 1-98-1 split showing very high confidence in the most likely outcome.  You’ll still get a ratio scale multiplier of about 7%.

If your three-points suggest a skewed normal curve, the ratio scale multiplier might be a little higher, like 8%, but for a normally-shaped normal curve, a 1-98-1 split of a three-point estimate will yield a SPERT ratio scale multiplier around 7%.  Hence, the start of the SPERT-7 rule.

Create SPERT standard deviations using the SPERT-7 rule

In PERT, a standard deviation can be easily created using the PERT standard deviation formula, which is:  (Pessimistic – Optimistic) / 6.  Statistical PERT, though, uses ratio scales to create a standard deviation; the ratio scales vary according to the estimator’s subjective opinion about how likely the most likely outcome really is.

When using one of the free SPERT Excel templates or example workbooks (available here), you can model those ratio scales using the tab entitled “Ratio Scale Modeler.”  That SPERT worksheet lets you distribute 100 hypothetical outcomes for a given uncertainty under a condition of very high confidence, very low confidence, and something in the middle.  The resulting ratio scale constant values can be copied/pasted into the file’s VLOOKUP (found in the VLOOKUP tab), making them usable throughout the entire SPERT file.

But what if you aren’t using a SPERT Excel template or SPERT example workbook?  What if you are suddenly faced with a situation where you want to create a SPERT estimate, and you need a SPERT standard deviation?

Use the “SPERT-7 Rule.”

The SPERT-7 Rule says that, under the following levels of confidence, use the corresponding ratio scale multiplier against the range between the minimum point-estimate (of your 3-point estimate) and the maximum point-estimate:

7% – Near certainty
14% – High confidence
21% – Medium-high confidence
28% – Medium-low confidence
35% – Low confidence
42% – Guesstimate

Each subjective opinion about the most likely outcome corresponds to a ratio scale multiplier, and each multiplier is evenly divisible by seven.

If you want to create an estimate that closely matches a Monte Carlo simulation using the PERT distribution, stick with 21% (Medium-high confidence).  The probability results from SPERT will be within 1% – 2% of a Monte Carlo simulation.

Example:  I have a 3-point duration estimate of 60-120-240.  I want to be 85% certain that my SPERT estimate will not be exceeded by the actual outcome, and the estimate should closely match a Monte Carlo simulation.  How can I create that?

  1. Create the arithmetic average (the mean) using the PERT formula; the result is 130.
  2. Create the SPERT standard deviation.  Take the range of the 3-point estimate and multiply is by 21%; the result is 37.8, which is ([240 – 60] * 0.21).
  3. Use Excel’s NORM.INV function using these arguments:  0.85, 130, 37.8; the result is 169.2.  I can be 85% confident that my SPERT estimate of 169 will not be exceeded by the actual outcome of the duration uncertainty I’m estimating.

How does the SPERT estimate of 169 compare to a Monte Carlo simulation of the same, 3-point estimate of 60-120-240?

Using Palisade’s @Risk Excel add-in, I ran a simulation using the =RiskPert function (which uses the PERT distribution, not the standard normal distribution), specifying the same 3-points:  60, 120, 240.  The @Risk simulator calculated, with 85% confidence, a value of 167.07.

There was just a 1.25% difference between the SPERT estimate of 169 and the Monte Carlo simulation of 167!



Welcome to the Statistical PERT blog!

In this blog, I’ll be sharing more tips and tricks about Statistical PERT, and answering questions about using it, configuring it, calibrating it, and more.

Feel free to post a comment asking any questions about Statistical PERT.  My aim is to make Statistical PERT easy to use, accurate, and helpful.