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?

- Create the arithmetic average (the mean) using the PERT formula; the result is 130.
- 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).
- 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!

M.Thank you. This was the single best explanation I’ve found on this & it fully illucidated my understanding of how to do PERT in excel. I had a number of bits, but this really brought it all together for me and made it achievable.