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!

The April 2020 Webinar on Monte Carlo Simulation

Last week, I held another free webinar on probabilistic estimation. This time, it was on Monte Carlo simulation.

A lot of project managers and businesspeople have heard about Monte Carlo simulation, but not nearly as many people know exactly what it is (and what it isn’t), or how to use it to inform decision-making.

I created this spreadsheet and used it during this month’s webinar. In it, I explain how we can calculate the probability of rolling a “7” using a pair of dice just by simulating the problem.

Now, we know that the probability of getting a “7” is 16.7% (6 possible ways of rolling a “7” divided by 36 total possible outcomes from rolling a pair of dice). But what if we didn’t know how to solve the problem using a math formula? What if creating a math formula was too complicated? What if it were impossible? That’s when Monte Carlo simulation comes into play.

By simulating complex problems thousands of times, we can learn the probabilities of all kinds of possible outcomes. Then, we can use that information to help make decisions about what to do today.

I included some instructions inside the spreadsheet, and I also have an included worksheet that shows how to simulate a single project task, too.

Questions? Feel free to contact me to learn more about Monte Carlo simulation, and check out my future free, monthly webinars. I’ll be doing this particular webinar again probably later in 2020.

SPERT and the Excel Histogram chart

Since releasing Version 4 of the Statistical PERT® Normal Edition this past January, I’ve had a few people contact me saying that they couldn’t use the new Monte Carlo simulation tab that I’ve included in that release.

After doing some investigation, I now think I know what the problem is. The Monte Carlo simulation worksheet in V4 uses Excel’s “Histogram chart” to visually depict the 10,000 trials of the simulation. However, Microsoft only introduced the histogram chart in Excel 2016. Excel 2010 and Excel 2013 don’t have this chart, so users of these older versions of Excel see an error message where the histogram is supposed to be.

If you’re using either Excel 2010 or Excel 2013, you can manually create the histogram chart yourself by Googling “histogram chart Excel 2013” and you’ll see results that explain a manual process for creating a histogram chart. Here are some of the result links I found:

Of course, you can always save yourself the trouble of manually creating a histogram chart by upgrading to the latest release of Excel.

BTW, Microsoft’s lifecycle support for Excel 2010 ends October 2020.

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.

SPERT Normal Edition Version 4 Released!

Today I released a new version of Statistical PERT® Normal Edition! Version 4 of SPERT Normal Edition adds these new/enhanced features:

  • New Monte Carlo simulation tab for simulating 10,000 trials of a single, random variable
  • New Agile Burnup chart for creating an agile release forecast
  • More subjective judgment options (10) to choose from when determining how likely the most likely outcome really is
  • Revised ratio scale modifiers for use with the SPERT Standard Deviation formula
  • Enhanced agile forecast tab
  • Added a way to use your own, explicit standard deviation, which is especially useful if you have historical data
  • Minor formatting enhancements
  • A one-click way to ask for help using my free support service

Version 4 is currently available on the Statistical PERT download page, or you can download Version 4 from GitHub, too.

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.

Version 3 Released!

On March 10, 2019, I officially released the public version of Statistical PERT Normal Edition. This is a terrific enhancement upgrade to Statistical PERT because Version 3 adds new and enhanced data visualization features that help you see the uncertainty you’re modeling.

New in Version 3:

  • Row-level Sparklines that show the shape of the implied bell-curve, based upon your 3-point estimate and subjective judgment about the most likely outcome
  • New, tri-colored combo charts which show the bell-curve for the aggregate of all the uncertainties you’ve modeled (in the 1-point entry, 3-point entry, and Mixed entry worksheets)
  • Three, side-by-side scenario comparisons on the Agile Forecast tab, to make it easier to do sensitivity analysis when choosing different input variables

Row-level Sparklines are useful because they visually show whether the bell-curve is flat (lots of uncertainty), narrow (not much uncertainty), or shaped familiarly (moderate uncertainty). They also indicate whether your 3-point estimate implies skewing either to the left or to the right. If the peak of the Sparkline bell-curve is centered, there is no skewing. If the peak is shifted to the left, then your uncertainty is skewed to the right, and vice versa. By showing the uncertainty in a Sparkline, you can immediately recognize what kind of uncertainty you’ve modeled, without looking at the actual input data you entered and deciphering mentally.

The tri-colored combo charts is a HUGE enhancement to Statistical PERT. You won’t realize how useful these are until you start to play with them. Their greatest value is they visually show the kind of uncertainty you’re estimating, which is very helpful when presenting or negotiating with stakeholders. Imagine showing your project sponsor (who is under-funding your project) a tri-colored combo chart that has more than 50% of the area under the curve in the color red. You say to your sponsor, “The area under the curve shows the likelihood your project will fail to meet its goal, given the project constraints we have to work with.” And then you explain to your sponsor how you arrived at this conclusion.

The tri-colored combo chart is a combination of a line chart and a bar chart. The colored area under the curve is actually 100 bars that touch each other. The line that touches the peak of each bar chart smooths the appearance of the normal curve. This whole combo chart is configurable — change the bar sizes, colors, much more.

To create the tri-colored bar chart, you’ll notice there are a lot of hidden columns to the right of the main worksheet display. Feel free to unhide these hidden columns and explore how the tri-colored bar chart’s data is created.

This summer, I’ll be working to enhance Statistical PERT Beta Edition to give it the same data visualization upgrade as what the Normal Edition now has. I hope to release Version 2 of the Beta Edition by the end of summer 2019.

I’m working with Pluralsight to release a brand-new course to replace my 2015 course that first introduced Statistical PERT to the world. Statistical PERT has changed a LOT over the last four years, so it’s a great time to replace my 2015 course, Easily Estimate Projects Using Statistics and Excel with my new course, Easily Estimate Projects and Products. This new course will show product owners working in an agile environment how to use Statistical PERT to forecast how many iterations (sprints) it will take to complete a given amount of work off the product backlog. Statistical PERT is a great, easy way for product owners to create feature forecasts.

Look for other, smaller enhancements to Statistical PERT Normal Edition later 2019 and 2020!

New Version 3 of Statistical PERT Normal Edition Coming January 2019!

I’ve been working on Version 3 of Statistical PERT Normal Edition for several weeks now.  The big change with Version 3 are the new visualization features that make it easier to see and understand the statistical probabilities associated with each estimate you make.

The visualization features include:

  • Sparklines, which show the implied, bell-shaped curve based upon your inputs
  • Tri-colored bar charts, which show the left-side, right-side and middle area of a bell-curve.

The Sparklines features will make it easy to immediate see whether an estimate has a lot of uncertainty or not, based upon the shape of the bell-curve.  While you could imply this by looking at the “Most Likely Confidence” selection, this visualization feature makes it easy to scan multiple rows to find those rows that have the most uncertainty.

Sparklines also show whether the 3-point estimate is perfectly symmetrical, or skewed either to the left or right.  This visualization of the 3-point estimate wasn’t possible in the current, Version 2 of SPERT.

The tri-color bar charts will be very helpful to see how much risk there is that a planning estimate may be exceeded.  This can be a powerful tool for project managers who must convey risk to project sponsor unwilling to provide sufficient resources to their project teams.

Aside from these major enhancements, I’m making minor tweaks and changes to further refine the SPERT spreadsheet.

Version 3 will be officially released very shortly after my new Pluralsight course on estimating is published — probably in late January, 2019.  Meanwhile, if you want to download a pre-release of Version 3, it’s available for downloading right now.  Just be aware that I haven’t yet done extensive testing with pre-release versions of Statistical PERT.

Statistical PERT Beta Edition (new Version 2) will also receive the same visualization updates as the SPERT Normal Edition is getting.  SPERT Beta Edition Version 2 will be released in the first half of 2019.