How Statistical PERT gives programmers confident estimates
- select the contributor at the end of the page -
Anyone who codes understands how difficult it can be coming up with accurate estimates for certain projects. Pinpointing an exact (or at least close-to-exact) time frame can be tricky, to say the least. Thankfully, there are methods you can use to help you confidently estimate how long it will likely take to do certain things, such as coding and testing a new function for a client’s Web application.
Three-point estimates
To better align a client’s or supervisors expectations, we could start by offering a three-point estimation, which identifies the minimum, most likely, and maximum outcomes for coding the desired Web function. In this case, you might end up saying something along the lines of, “I think it will take 40 hours to code that. If I’m lucky, the new Web controls I’m using will work without any trouble, and I’ll get the whole thing done in as little as 24 hours. If I run into problems, though, and I have to engage the Web controls vendor, it could take up to 80 hours to complete.”
With a three-point estimate, you offer a sense of the relative riskiness of coding a new Web function; the problem is that you’re not exactly displaying confidence in getting the work completed in just 40 hours.
Program evaluation review technique (PERT)
With a single risk-adjusted estimate, you might use the Program Evaluation Review Technique (PERT). The PERT formula looks like this: (Minimum + [4 x Most Likely] + Maximum) / 6. Using the previous example, a PERT estimate would look like this: (24 + [4 * 40] + 80) / 6 = 44 hours (four hours longer than what we’re thinking the task will likely take to complete).
The PERT formula creates the arithmetic mean for a bell-shaped, statistical curve. (Bell-shaped uncertainties have minimum and maximum outcomes that are feasible but highly improbable, whereas the most likely outcome has a much higher probability of occurring.) On its own, PERT isn’t very useful since every PERT estimate has only about a 50 percent probability of occurrence (the exact probability depends on the exact shape of the implied, bell-shaped curve). And really, who wants to be right only half of the time?
Statistical PERT
Thankfully, there’s a way that you can better align expectations and offer more realistic probabilities with a new technique called Statistical PERT. Statistical PERT brings the ease of PERT into the 21st century by using built-in, statistical functions inside Microsoft Excel. To use Statistical PERT, you need to know two Excel functions: NORM.DIST (normal distribution) and NORM.INV (normal inverse). The NORM.DIST function finds the cumulative probability of any estimate (called “X”). Conversely, NORM.INV finds the value of X for any specified, cumulative probability.
To use Statistical PERT, start with these two steps:
- Make a three-point estimate (minimum, most likely, maximum), as mentioned above.
- Use the PERT formula to obtain the arithmetic mean.
NORM.DIST and NORM.INV
It’s important to note that both the NORM.DIST and NORM.INV functions require a standard deviation. To calculate this, use the SPERT standard deviation formula along with the SPERT-7 Rule. The SPERT-7 Rule equates your subjective opinion about how likely the most likely outcome really is to a ratio scale multiplier:
- 7% Nearly certain
- 14% High confidence
- 21% Medium-high confidence
- 28% Medium-low confidence
- 35% Low confidence
- 42% Guesstimate
Suppose you had “Medium-high” confidence in your most likely point-estimate of 40 hours. The corresponding ratio scale multiplier is 21 percent. Now you can use the SPERT standard deviation formula: [Maximum – Minimum] * Ratio Scale Multiplier. In my example, the SPERT standard deviation is [80 – 24] * 21 percent = 11.76 hours. Now I’ve completed steps three and four of the Statistical PERT process, and am ready to tackle step five:
3. Choose a subjective opinion about how likely the most likely outcome really is.
4. Use the SPERT-7 Rule and SPERT standard deviation formula to obtain a standard deviation.
5. Use Excel’s NORM.DIST and NORM.INV to obtain probabilistic estimates.
To create an estimate with 80 percent confidence, I use NORM.INV which requires three arguments: probability, mean, standard deviation. In my example, I enter this into Excel: NORM.INV(0.80, 44, 11.76). The result is 54 hours. Want 90 percent confidence? Try this: NORM.INV (0.90, 44, 11.76). The result is 59 hours.
What if we start with an estimate, then find its cumulative probability? Suppose you ask about the chance of getting the task done in just 35 hours? We’d use the NORM.DIST function to solve for that.
NORM.DIST requires four arguments: X, mean, standard deviation, TRUE/FALSE (we always say, TRUE to obtain a cumulative probability). In my example, the Excel function would look like this: NORM.DIST(35, 44, 11.76, TRUE) = 22 percent. There’s only a 22 percent chance that I will get the Web function coded in 35 hours or less. Expectations aligned!
Aside from making statistical probabilities accessible to the masses, Statistical PERT lets estimators use their subjective opinion to influence the probability of any estimate, so the probabilities better align with the estimator’s sense of ease or unease in the most likely outcome. Using the SPERT-7 Rule, changes in subjective opinion also change the ratio scale multiplier used in the SPERT standard deviation formula, and that affects the probabilistic estimates generated from using NORM.DIST and NORM.INV.
Try it! If you have Microsoft Excel 2010 or 2013, you have all you need to start making probabilistic estimates using Statistical PERT.
