Tag Archives: new release

Statistical PERT® Beta Edition Released!

Yesterday, on March 1, 2017, I released the first production release — Version 1.0 — of Statistical PERT® Beta Edition.  It was a long journey to get to that day!  Click here to download it now, or read the press release.

About two and one-half years ago, I pondered over how to model a risk uncertainty based upon expert opinion rather than historical data.  Very often, project managers estimate projects using expert opinion, so the question in my mind was highly relevant to my job.  In fact, in the 15 years I’ve worked as a project manager, and the 15 years I’ve worked as a software developer, I can’t remember any time where I did not rely on expert opinion to estimate the work activities on a project schedule.

At the end of that weekend in October, 2014, I had created something I called SPERT, short for Statistical PERT.  My work was rooted in PERT (Program Evaluation and Review Technique, which every PMI-credentialed project manager knows).  I started with PERT’s simple formula to estimate the mean of an uncertainty.  But the trouble is using the mean is that it only has about a 50/50 chance of being correct.  Most project uncertainties are bell-shaped risks, and the mean of a bell-shaped uncertainty lies at or near the top of the bell-shaped curve, where half the area is to the left of the mean, and half the area is to the right of the mean.

I wrestled with how to come up with a standard deviation for an uncertainty where there was no historical data.  That was the key problem that Statistical PERT cracked.  Once I came up with a way to do that, then it was simply a matter of using Excel’s two normal distribution functions, NORM.DIST and NORM.INV, to come up with probabilistic estimates.

But most project uncertainties aren’t symmetrically shaped.  Most often, things take longer than expected rather than less time than expected.  Most project uncertainties, then, are skewed to the right, where the area under the bell-shaped curve is greater to the right of the mode (and the mean) than to the left.  Using the normal distribution gave “good enough” results, but was there way to approximate a skewed uncertainty using just the built-in, statistical functions of Excel?

The answer was (and is), Yes.  The beta distribution, which is the distribution originally used with PERT, is a wonderfully flexible distribution that can model left-skewed, right-skewed and symmetrical uncertainties.  It does it all!  The challenge with using the beta distribution (and the reason almost no project managers even know about it, much less use it) is that it uses two shape parameters, or arguments in Excel, called alpha and beta, and nobody except statisticians and data geeks know how to choose the right combination of alpha and beta to get a bell-shaped curve that matches the risk nature of an uncertainty.

That was the nut to crack with Statistical PERT Beta Edition.  How could I create an easy-to-use, Excel template that employed Excel’s two beta distribution functions, BETA.DIST and BETA.INV, to model risk uncertainty?  How could the template know what combination of alpha and beta to choose from using as little input from the estimator as possible?

So, 18 months ago I started walking down that road.  I spent a long time using an approach which, later, I jettisoned because it resulted in bell-shaped curves with modes that didn’t match the estimator’s three-point estimate input.  The second approach, which in future blog posts I’ll try to describe, was the one I settled on because it was simple for the estimator to use, could be matched to similar results using SPERT Normal Edition and a regular Monte Carlo simulation, and required nothing new from the estimator than what the original Statistical PERT approach required:

  1. Create a three-point estimate for the uncertainty (minimum, most likely, maximum)
  2. Render a subjective opinion about how likely was the most likely outcome to occur
  3. Select a probabilistic estimate or make a risk-based, range forecast

Statistical PERT Beta Edition is really easy to use.  It has the distinct advantage over its older brother, the Normal Edition, of being able to more accurately represent skewed uncertainties.  Its main disadvantage is that, unlike the Normal Edition, it cannot be easily changed, and you can’t employ the approach to a blank spreadsheet like you can with the Normal Edition.  But as long as you live within those constraints, downloading and then using a SPERT Beta Edition example workbook or template is wonderfully easy for the estimator, who is shielded from having to interact directly with Excel’s statistical functions.  The template does that for you.

I look forward to sharing both editions of Statistical PERT with project managers, business analysts, educators and students throughout 2017 and beyond.  It’s my firm conviction that probabilistic estimates create better alignment among people on the nature of an uncertainty, and it fosters better decision-making among those responsible to make choices about future uncertainties, such as how long a project might take, or how much it might cost.

I welcome your feedback!  Feel free to reach out to me with any questions about Statistical PERT.

Statistical PERT Version 2.1 Released

Today, I released a minor version update to Statistical PERT® Normal Edition.  Version 2.1 adds a new worksheet, SPERT Normal® (Mixed entry).  This new worksheet offers estimators a very flexible way to enter uncertainties into a SPERT worksheet.  Using this worksheet, estimators can very easily create three-point estimates with little effort using heuristics to generate the minimum and maximum extreme values for each uncertainty, and then estimators can selectively choose to change those auto-created values with row-level, exception-based values.

In this worksheet, estimators have three ways to create the necessary 3-point estimate that every Statistical PERT calculation requires.  Firstly, two global heuristics are specified above the Minimum and Maximum column headings.  The Minimum heuristic will reduce the value entered in the Most Likely column by a percentage value.  Similarly, the Maximum heuristic will increase the value in the Most Likely column by a percentage value.  These two heuristics will affect all entered uncertainties in this particular worksheet.

Estimators can, at the row level, override any globally-derived Minimum and Maximum point-estimates in one of two ways,  One way to do that is specify a row-level heuristic by selecting a percentage value in either the Min % or Max % column.  These percentages only affect a single row.  They both either reduce or increase the value in the Most Likely column, just like the global heuristics do, but only at a single row level.  The other way estimators can override a calculated minimum and/or maximum point-estimate is to manually enter a value in the Min point and/or Max point column.  Manually entering a minimum and/or maximum point-estimate will always override any point-estimate value that is a calculated result.

Most Likely point-estimates must always be manually entered by an estimator; there is no built-in way to calculate a Most Likely point-estimate.  But once a Most Likely point-estimate is entered, an estimator can either use a global or row-level heuristic to create the minimum and/or maximum point-estimates for each uncertainty, or estimators can continue to manually enter specific values for those point-estimates.

While this new worksheet may look a little more complex because of the extra columns, using it is still very simple.  This simply makes it easier for an estimator to create exception-based point-estimates when dealing with a large number of uncertainties in the SPERT worksheet.

The example workbook includes examples of all three ways to create minimum and maximum point-estimates.  The first three rows (ID 1, 2 and 3) have minimum and maximum point-estimates that are created from the global heuristics.  For these three uncertainties, minimum point-estimates are 50% less than the most likely point-estimate, and maximum point-estimates are 100% greater than the most likely point-estimate.

ID 4, however, overrides the global heuristic with two row-level heuristics:  the minimum point-estimate is only 10% less than the most likely outcome, and the maximum point-estimate is only 50% greater.

ID 5 uses a row-level heuristic to reduce the most likely outcome by 25%, but the maximum point-estimate (160) is manually entered and overrides the global heuristic for creating maximum point-estimates.

ID 6 specifies a value of 40 for the minimum point-estimate, which overrides the global heuristic for creating minimum point-estimates, and the maximum point-estimate of 200 is manually entered, too.

ID 7 specifies 80 for the minimum point-estimate, but uses a row-level heuristic to create a maximum point-estimate that is 75% greater than ID 7’s most likely value.

So, the new Mixed entry worksheet offers estimators the greatest amount of flexibility to create 3-point estimates in the easiest way possible.  Download Version 2.1 now!

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.

Version 1.4 Released!

Today, I realized Version 1.4 of Statistical PERT – Normal Edition for all SPERT example workbooks and templates (task duration, expense, revenue, agile, project portfolios, events).

Here’s what’s new in this release:

  • I removed the “Unskewed” probabilities which used the mode instead of the mean.  While using the mode would, under some conditions, yield a more accurate probability than using the mean, I found that most people were just confused by the difference between using the mode and using the mean.  In most cases, using the PERT mean will give accurate results, so I decided to remove the confusion by removing the columns devoted to “Unskewed” probabilities.
  • There is a new pie chart and associated cells which lets estimators create confidence intervals for any selected confidence level.  You can specify a confidence level and the confidence interval is determined for you, or, optionally, you can give a confidence interval and the confidence level is calculated for you.
  • There is a new pie chart showing what uncertainty looks like with respect to how likely is the Most Likely outcome.  One of Statistical PERT’s unique attributes is that it allows estimators to use subjective judgment (private knowledge, gut instinct, emotions, intuition) to rationally adjust the SPERT estimates.  Many people don’t know how to choose a subjective judgment, though.  This new pie chart shows what each subjective choice looks like if the only three outcomes were the minimum, most likely and maximum point-estimates.  Interestingly, there was a PMI Global Congress presentation that talked about visually making risk-based choices.
  • I standardized the SPERT example workbooks and templates so future changes can be easily made across all the different SPERT example workbooks and templates.  So, instead of the task duration’s template having headings like Optimistic, Most Likely and Pessimistic (where the order would be reversed for the Revenue template), now all templates use Minimum, Most Likely and Maximum as the headings for the three-point estimate columns.
  • Each SPERT file now has a Welcome! tab that introduces Statistical PERT to the downloader, and it has a link to a Quick Start guide that can help to quickly learn how to use any SPERT download.
  • I made a number of other tweaks, too, of a minor nature.  SPERT, for example, is now a federally registered trademark.

If you haven’t already done so, I encourage you to download your favorite SPERT example workbook or template!