Make Confident Estimates in 5 Steps

Do you sometimes have difficulty making project-related estimates?  When you do make an estimate about about an uncertain, future outcome, how confident are you in your estimate?

How confident do you want to be?

Making estimates about uncertain, future outcomes is something every project manager does regularly. Sometimes, owing to our experience and knowledge, making those estimates is easy. But other times, creating an estimate is difficult to do. Even after we make an estimate, we may not feel confident about that estimate.

Here’s a new estimation technique to use when you’re faced with estimating an uncertain outcome: Statistical PERT. Statistical PERT is an easy, five-step technique that uses Microsoft Excel to create probabilistic estimates for bell-shaped uncertainties. (A bell-shaped uncertainty is one where there is an improbable minimum value, and equally improbable maximum value, and a most likely outcome that lies somewhere towards the middle between those two extremes).

You already know PERT: the Program Evaluation Review Technique. PERT estimation using the PERT formula has been around for decades, even before the birth of personal computers. The PERT formula is:

Optimistic+4(Most Likely)+Pessimistic)/6

What does the PERT formula calculate? It returns the arithmetic average for a bell-shaped curve that is implied by an estimator’s three-point estimate. With PERT, one-half of the expected outcomes for an uncertainty should exceed a PERT estimate, meaning a PERT estimate is no greater than 50% reliable. Do you want to estimate project tasks and be wrong half the time? I don’t!

Some people manipulate the PERT formula to place greater weight on the pessimistic outcome, but it’s an arbitrary manipulation of the PERT formula. Isn’t there a better, easier, more flexible way to obtain probabilistic estimates?

Yes, there is! Using two, built-in statistical functions that come standard with Microsoft Excel, Statistical PERT lets anyone create probabilistic estimates using a simple, five-step process. Even better, Statistical PERT lets estimators use their own, subjective opinion about the most likely outcome to influence those probabilistic estimates.
Let’s examine the five steps of Statistical PERT.

Step 1: Make a three-point estimate. This is no different than making a PERT three-point estimate. Choose minimum and maximum values that represent implausible, extreme values for the uncertainty you are estimating. Then, choose a most likely outcome you think best represents what will actually happen in the future. The most likely outcome should be somewhere towards the middle of the range.

Step 2: Use the PERT formula to estimate the mean. The statistical mean is the arithmetic average of all possible outcomes for the uncertainty you’re estimating. The PERT formula estimates the mean.

Step 3: Render a subjective opinion about how likely the most likely outcome really is. With Statistical PERT, you can choose between six different opinions:

1. Nearly certain
2. High confidence
3. Medium-high confidence
4. Medium-low confidence
5. Low confidence
6. Guesstimate

Step 4: Create a SPERT standard deviation using the SPERT-7 Rule. Each of the subjective opinions in Step 3 corresponds to a ratio scale multiplier that is used in Step 4’s SPERT standard deviation formula. The correspondence looks like this:

• 7% (nearly certain)
• 14% (high confidence)
• 21% (medium-high confidence)
• 28% (medium-low confidence)
• 35% (low confidence)
• 42% (guesstimate)

The SPERT standard deviation formula is easy:

SPERT Standard Deviation=(Maximum-Minimum) × Ratio Scale Multiplier

Step 5: Use NORM.DIST and/or NORM.INV to create probabilistic estimates in Microsoft Excel. With the PERT-estimated mean and the SPERT-estimated standard deviation, you now use two statistical functions for the normal probability distribution in Microsoft Excel 2010 or later. The NORM.DIST function (normal distribution) finds the cumulative probability of any value between the minimum point-estimate and the maximum point-estimate. The NORM.INV function (normal inverse) finds the precise point between the minimum and maximum point-estimates that matches any probability you choose between 0% and 100%.

Here’s a Statistical PERT example. Suppose I want to create a planning estimate for a project task that I think will most likely take 60 hours to complete, but it could take as little as 40 hours or as many as 100 hours.

Step 1: Make a three-point estimate. My three-point estimate is {40, 60, 100}. As long as the minimum and maximum point-estimates are improbable and possible, and the most likely point-estimate is somewhere near the middle of the range, I can use Statistical PERT.

Step 2: Use the PERT formula to find the mean.

PERT Estimated Mean= (40+4(60)+100)/6= 380/6=63.333 hours

Step 3: Render a subjective opinion about how likely the most likely outcome really is. I’ll say that I have “Medium-high” confidence in the most likely outcome of 60 hours.

Step 4: Create a SPERT standard deviation using the SPERT-7 Rule. The SPERT-7 Rule equates “Medium-high confidence” to a ratio scale multiplier of 21%. Now I can find the SPERT standard deviation:

SPERT Standard Deviation=(100-40)×21%=12.6 hours

Step 5: Use NORM.DIST and/or NORM.INV to create probabilistic estimates in Microsoft Excel. We’ll use NORM.INV to find a SPERT estimate that has 80% confidence. Put another way, I want an estimate where there is only a 20% risk that the actual outcome will exceed my SPERT planning estimate.

NORM.INV requires three arguments: probability, mean, and standard deviation. The preceding steps obtained all that information, so, in Excel, I just plug-in the values into the NORM.INV function:

NORM.INV(0.80,63.333,12.6)=73.9 hours

That’s it! My SPERT estimate with 80% confidence is 74 hours. So, there is an 80% probability that the actual outcome for my project task will be equal to or less than 74 hours; there is a 20% risk that the actual outcome will exceed 74 hours.

Using Statistical PERT is easy to do.  Try using Statistical PERT the next time you’re faced with estimating a difficult, bell-shaped uncertainty on your next project! Statistical PERT makes it easy to confidently estimate any bell-shaped uncertainty.