Category Archives: Beta Edition

Don’t Be Afraid to Use the Normal Distribution

At work, I’m planning a big upgrade to my employer’s enterprise resource planning (ERP) system. It’s been a decade since the last upgrade, and our vendor support is running out next year.

It’s early in the project lifecycle. We just selected a partner to help us with the upgrade. We’re now going through the procurement phase (detailed SOW and contract).

Before we engage with this vendor to do discovery work, I’m doing project planning with my project team. We identified 11 major activities that this project will plan and execute. Our project sponsor wants to know, how long will this project take?

To answer that question, our project team has modeled the 11 activities using both SPERT Normal Edition, SPERT Beta Edition and we used Monte Carlo simulation (Palisade’s @Risk program).

We used this global heuristic for the 11 activities: the minimum duration is 25% less than the most likely duration, and the maximum duration is 50% greater than the most likely duration. If we wanted, we could alter the heuristic results for each activity. And because we’re using SPERT, we can apply our subjective judgment, too, to express how likely will the most likely outcome really occur.

This is going to be a year-long project.

What’s interesting to me is that, of course, the beta distribution is a better fit to the way we’ve modeled the duration uncertainty of each activity. And yet, the SPERT Normal Edition calculates nearly the same result as the SPERT Beta Edition (or a Monte Carlo simulation, too, for that matter).

SPERT Beta Edition:

  • 50% probable duration is 245 days
  • 80% probable duration is 276 days
  • 90% probable duration is 292 days
  • 95% probable duration is 304 days

SPERT Normal Edition:

  • 50% probable duration is 247 days
  • 80% probable duration is 278 days
  • 90% probable duration is 294 days
  • 95% probable duration is 307 days

The normal distribution can handle mild-to-moderately skewed duration uncertainties like these. You don’t necessarily have to use a best-fitting probability distribution to model your project’s uncertainties.

Sometimes, using the normal distribution is “good enough” to make a good decision. And the normal distribution is among the easiest to use in Excel (NORM.DIST, NORM.INV functions).

Version 3 of SPERT Beta Edition

Yesterday I began working on Version 3 of Statistical PERT (SPERT) Beta Edition. Since all of my spring and summer travel plans have all been scuttled, I thought I’d get started working on Version 3 sooner rather than later.

Since beginning the Beta Edition three years ago, this edition has always been in catch-up mode to the original, Normal Edition (which uses the normal distribution to model uncertainties). And that’s no different with Version 3 of SPERT Beta Edition, with one notable exception.

Like Version 4 of SPERT Normal Edition, I’m going to have a new worksheet that models uncertainties using Monte Carlo simulation (but with the beta distribution instead of the normal distribution). SPERT users can experiment with simulating an uncertainty they’ve modeled using 10,000 trials just by pressing F9.

Unlike Version 4 of SPERT Normal Edition, Version 3 of SPERT Beta Edition won’t have an agile burn-up chart. That chart is well-suited to using the normal distribution, so I don’t see creating a similar burn-up chart in the Beta Edition.

Version 3 of SPERT Beta Edition will get a new feature that’s not in Version 4 of SPERT Normal Edition (yet; this feature will be part of next year’s Version 5 of SPERT Normal Edition). And that is, SPERT Scheduler.

SPERT Scheduler is a feature added on to the existing Mixed Entry worksheet. The Scheduler will allow users to model a plan-driven project’s critical path activities using calendar work days. The specific Excel function is the WORKDAY function, which has been available since Excel 2007.

Using the SPERT Scheduler, a modeler can see how long a project duration will be using different choices for the probabilistic duration estimates for each activity.

The key limitation of this feature is it can only model a project’s critical path, and it can’t address merge bias, which is what a full Monte Carlo simulation would do. But for many people who are simply looking for high-level project estimates based upon sequential activities, this will be a great solution for them.

If you’re interested, you can try-out Version 3 by visiting my GitHub repository, and selecting the Version 3 branch to download the latest iteration of SPERT Beta Edition Version 3. Just be aware that I haven’t fully tested spreadsheets in this branch; they’re still under development!

Version 2.1 of SPERT® Beta Edition Released

Yesterday I released a minor update to Version 2 of the Statistical PERT® Beta Edition. This minor release improves some formatting of the Vlookup tab so it uses the same, yellow-shading + blue text format for cells eligible for end-user inputs.

I also added another feature that was included in Version 4 of Statistical PERT® Normal Edition: “Click for help” buttons. On all interactive worksheets, there is a prominent, orange “Click to call” button to make it easy for anyone who wants free, email support to get it. The button links to my site’s “Contact Me” page where they can fill-in their name, email account, and question/issue.

This summer I’ll be working on Version 3 of SPERT® Beta Edition. Version 3 will include the same feature set as what the SPERT® Normal Edition has with its Version 4 release. That is, it will include the ability to use a specified standard deviation, it’ll have a Monte Carlo simulation worksheet, and have up to three scenarios on the Agile Forecast tab.

If you want to be notified whenever I release new versions of Statistical PERT, I’ve got a new way to do that: add yourself to my new email newsletter. Once a month, I’ll send a short newsletter offering a tip or trick for how to use Statistical PERT, plus I’ll send out notifications whenever I release new versions of these spreadsheets.

Agile Forecasting with SPERT Beta Edition V2

Today I added a new Agile Forecast tab to the forthcoming Version 2 of Statistical PERT Beta Edition. This Agile Forecast tab is very similar to the Agile Forecast tab in the SPERT Normal Edition Version 3. I introduced the Agile Forecast tab last year in Version 2 of SPERT Normal Edition.

What makes the Agile Forecast tab different in the SPERT Beta Edition is that it, obviously, uses the beta distribution to model the uncertainty around what each Scrum sprint (or agile iteration) will accomplish. This opens up the possibility to model this uncertainty using shape parameters that very nearly create a uniform distribution (in SPERT Beta Edition, this is called a “Guesstimate” about how likely the most likely outcome really is).

I compared some results between SPERT Normal and SPERT Beta Editions, and found that both editions calculate the same release date, just in different ways. Notably, I used “Medium-Low Confidence” in SPERT Normal, but had to use “Very Low Confidence” in SPERT Beta to obtain a close match of the standard deviation. By default, SPERT Normal uses only 7 subjective phrases for how likely the most likely outcome is, but SPERT Beta uses 10. The two editions treat the subjective phrases differently, even though they share the same word phrases.

Try downloading the latest in-development SPERT Beta Edition Version 2 (currently, version 2.0b) from GitHub and check-out the Agile Forecasting tab!

Version 2 of SPERT Beta Edition

I’ve begun working on the next version of Statistical PERT Beta Edition. Version 2 will have the same data visualization features as what SPERT Normal Edition received earlier this year. Namely:

  • Line Sparklines to show the implied, bell-shaped curve for each row containing a SPERT estimate
  • New, tri-colored combo charts to show the bell-shaped curve for summarized rows (on the 1-point, 3-point, and Mixed entry tabs)
  • New, tri-colored combo chart on the Charts tab, which uses the beta distribution to show skewed distributions
  • New, tri-colored pie chart on the Charts tab, to show an easy distribution of areas below, above and between the boundary limits
  • And perhaps a few others ideas, too….

If you’d like to download and try-out Version 2, you can it on my new GitHub repository. Be sure to download from the “version_2” branch to see the Version 2 SPERT Beta Edition. And keep in mind that until I officially release Version 2, this should be regarded as an in-development version, not a final, stable version.

If you have any comments or suggestions on how to further improve Statistical PERT, comment below or contact me.

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 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!