Monthly Archives: December 2016

Statistical PERT Comparison with @Risk

From the very beginning of the creation of Statistical PERT, I have used Palisade’s excellent risk analysis Excel add-in program, @Risk, to compare Statistical PERT’s probabilistic results with those obtained by @Risk using, mostly, the RiskPERT function that comes with @Risk.  RiskPERT is a special form of the beta distribution that models the kind of probability curve traditionally associated with PERT.  I very often gauge Statistical PERT’s accuracy against the results from a Monte Carlo simulation using @Risk.

As I ready the release of Statistical PERT – Normal Edition Version 2 and Statistical PERT – Beta Edition Version 0.6, I wanted to compare the results of these new versions with what I would get using the RiskPERT function.  To make this comparison, I have to set Statistical PERT to use “Medium Confidence” in the Most LIkely outcome to obtain the closet match to a RiskPERT distribution curve.

In performing a sample of six different three-point estimates (only one of which implied a symmetrical bell-curve, one which implied a left-skewed curve, and four which implied right-skewed curves), I was very pleased with the results.

The Normal Edition, of course, extends the implied probability curve to infinity both to the left and to the right; hence, the comparison results with RiskPERT tends to be very different at the extreme ends (1%, 5%, 95% and 99%).  But the Normal Edition performed admirably even though it fits asymmetrical bell-curves to a symmetrical normal curve.

The Beta Edition, as expected, did very well tracking to RiskPERT even at the extreme ends, and it tended to track more closely to RiskPERT overall than what the Normal Edition could do.  This is, in fact, the key reason I wanted to develop a Beta Edition of Statistical PERT was to improve the implied fitting of a probability curve to a 3-point estimate that implied a skewed uncertainty.

Overall, the difference between an estimate from @Risk and a SPERT estimate was usually around 2% or less, and very rarely was it more than 3.5%.  SPERT-Beta’s average difference with RiskPERT was 1.6% across all the 3-point estimates I tested and never greater than 2.2% except when dealing with a perfectly symmetrical bell curve.  SPERT-Beta’s kurtosis for a normal curve is slightly lower than the kurtosis used by RiskPERT, which explains why SPERT-Normal did a better job tracking to RiskPERT for a normal bell-curve than what SPERT-Beta did.

Download an Excel spreadsheet showing a full comparison of @Risk’s RiskPERT function, Statistical PERT – Normal Edition, and Statistical PERT – Beta Edition.  And stay tuned:  these new releases of Statistical PERT happen on January 2, 2017!


What’s New in Version 2

Like Santa in the North Pole, I’ve been working hard in my workshop, preparing the new release of Statistical PERT which I’ll be tentatively releasing on Monday, January 2, 2017.

Initially, Version 2 of Statistical PERT (Normal Edition) was just going to offer users an easy way to estimate using just a single point-estimate and heuristics to generate the minimum and maximum point-estimates, which are necessary to create an implied, bell-shaped probability curve.  But as I worked to prepare for Statistical PERT – Beta Edition, which uses Excel’s two beta distribution functions (BETA.DIST and BETA.INV), I found myself doing a lot more to the original Statistical PERT spreadsheet (which is now dubbed Statistical PERT – Normal Edition, since it uses Excel’s two normal distribution functions, NORM.DIST and NORM.INV).

The biggest change, philosophically, is that when I release Version 2 of SPERT-Normal, there will only be one example workbook and one template, rather than the six example workbooks and six templates that are available under the current 1.4 version.  Instead of creating special “flavors” of Statistical PERT for task duration, expenses, revenue, agile sprints, event attendance and portfolios, there will only be one “flavor.”  This will make it easier for me to release new versions of Statistical PERT without the added effort of updating multiple example workbooks and templates.  But the good news is that Version 2 will make it easy to estimate any bell-shaped uncertainty by letting estimators choose whether they want to view the left-side or right-side area of the implied probability curve, and whether they want to see currency formatting or not in all the different cells.

So for task duration, for instance, most estimators will want a high-probability estimate with 70% confidence or higher.  Version 2 of SPERT-Normal lets you obtain cumulative probability estimates from the left-side area of the implied distribution curve.  But if you wanted to estimate revenue, and you wanted a conservative revenue estimate, you might want to choose an estimate that has a very high probability of being exceeded.  In that case, you want would want to see the cumulative probability from the right-side area.  In Version 2, you can toggle between the left-side and right-side area, so you can either see the probability of a SPERT estimate being equal to or greater than an uncertainty (the left-side area), or you can choose to see the likelihood of a SPERT estimate being exceeded by the uncertainty (the right-side area).

Aside from that change, here’s what else is new in Version 2 of Statistical PERT – Normal Edition:

  • A separate worksheet for entering 1-point estimates that will use heuristics to auto-create a 3-point estimate
  • A new toggle that lets you choose whether to use currency formatting or not
  • New conditional formatting for the sum of all uncertainties
  • Removal of hidden columns that aren’t needed
  • Improved visual layout and formatting throughout
  • New hover comments for each column’s heading (some worksheets)

Statistical PERT – Beta Edition won’t have its Version 1 release until later in 1Q-2017, but the January release of Version 0.6 will be vastly improved over all preceding development versions, and will look very similar to SPERT-Normal Version 2.  And I’ll make SPERT-Beta available on the main site instead of only being available on the Beta Blog.

So, look forward to a great new year with great new versions of Statistical PERT!

New Version, New Edition of Statistical PERT

Statistical PERT – Normal Edition, New Version 2.0

I’m excited to announce that early in 2017, I will be releasing a new 2.0 version of the original Statistical PERT Excel workbooks and templates that I began creating in 2015.  Version 2.0 will introduce one-point, probabilistic estimation by letting estimators choose a heuristic to calculate both the minimum and maximum point-estimates in a three-point estimate.

A heuristic is well-defined on Wikipedia as, “any approach to problem solving, learning, or discovery that employs a practical method not guaranteed to be optimal or perfect, but sufficient for the immediate goals. Where finding an optimal solution is impossible or impractical, heuristic methods can be used to speed up the process of finding a satisfactory solution. Heuristics can be mental shortcuts that ease the cognitive load of making a decision.”

What I’ve learned in the two years that I and others have used Statistical PERT is that sometimes people just don’t want to go through the mental work of figuring out a 3-point estimate for an uncertain outcome.  Sometimes, it’s easier to start with a single estimate value and have the rest of the bell-shape curve implicitly created for you.  And although that may not be as precise and optimal as specifying all three points of a 3-point estimate, using a heuristic to create two of the three points in a 3-point estimate may be “good enough” for project planning purposes.

In the 2017 release of Statistical PERT, estimators may choose to employ a simple heuristic that takes a single point-estimate — the most likely outcome — and reduces it by a specified percentage to create the minimum point-estimate, and increases the most likely outcome by a specified percentage to create the maximum point-estimate.  SPERT estimators can choose separate heuristics for the minimum and maximum calculations.

For example, if I specify a value of 100 for the most likely outcome (the unit of measure doesn’t matter), I can say that the minimum point-estimate is 25% less than the most likely outcome, and the derived minimum would be 75.  Then I can say that the maximum point-estimate is 50% greater than the most likely outcome, and the derived maximum would be 150.  The resulting three-point estimate used to model the uncertainty would be (75, 100, 150).  I only had to set the minimum and maximum heuristics once for the entire worksheet, and all my subsequent one-point estimates will use heuristics to automatically create the minimum and maximum point-estimates.  Obviously, the math formulas involved are exceedingly simple, so the innovation here is mostly just about ease-of-use in creating three-point estimates, rather than technical innovation.

Estimators will be able to override heuristically-derived minimum and maximum point-estimates by just entering an actual, numeric value in place of the formula used to create minimum and maximum point-estimates.  All SPERT download files will still offer a separate worksheet to enter 3-point estimates without using heuristics, too.  Heuristics, then, are in addition to what is available in a Statistical PERT worksheet, not a replacement of anything currently available.

All existing Statistical PERT Excel downloads will be re-labeled as “Statistical PERT – Normal Edition” to reflect that all the currently-available downloads use Excel’s built-in normal distribution functions, NORM.DIST and NORM.INV.

Which brings me to the other exciting change for 2017….

Statistical PERT – Beta Edition, New Version 1.0

It’s been one year since I began a new quest to create a simple way to create probabilistic estimates using the more-flexible beta distribution, which handles skewed uncertainties more accurately than the normal distribution does.  I wanted to use Excel’s two beta distributions, BETA.DIST and BETA.INV in a spreadsheet that was just as easy to use as the original SPERT Excel spreadsheets, which hid the complexity of creating a standard deviation that the normal distribution functions require.

Excel’s beta distribution functions use two shape parameters, alpha and beta, which determine the precise shape of the beta curve.  Specifying alpha and beta shape parameters is not at all intuitive, however, so the goal was to create a technique that figures out a good approximation of alpha and beta based solely upon the estimator’s three-point estimate and the estimator’s subjective opinion about how likely the most likely outcome really is (just like the original Statistical PERT spreadsheets using the normal distribution!).

The details behind Statistical PERT – Beta Edition are too much to include in this blog post, but you can see a development build of the forthcoming version 1.0 that will be released in early 2017, along with the version 2.0 of Statistical PERT – Normal Edition.

Together, both editions of Statistical PERT will let anyone easily make estimates using Excel’s built-in, statistical functions.  And all Statistical PERT versions and editions will continue to be freely licensed under the GNU GPL, so you can download, use, modify and share any SPERT file like you’ve always been able to do.

Download a development-only build of Statistical PERT – Beta Edition here.