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.

Update Coming to Pluralsight Course in 2019

I recently published a new course on Pluralsight, a leading provider of online course content for IT and creative professionals.  It’s called Scrum Essentials Exam Prep, and it’s a course to help someone be well-prepared to take — and pass — the Professional Scrum Master Level One exam from Scrum.org.

Pluralsight and I are now in the early stages of negotiating a new agreement to create an updated course to replace the original one I did for them back in 2015, Easily Estimate Projects Using Statistics and Excel.  The new course will use Statistical PERT’s forthcoming Version 3, which I hope to have available in January, 2019.  The new course will show users how, exactly, to use and modify the freely-licensed Statistical PERT example workbooks and templates.

A lot has changed with Statistical PERT since 2015 when my original course with Pluralsight was published.  I’m very eager to show everyone all the cool estimation problems that Statistical PERT can solve!

If you’re interested to learn more about this new Pluralsight course, stay tuned to this blog, as I’ll begin writing about the course development here.

How Probable Are Your “Most Likely” Estimates?

Are you using “most likely” estimates in your project schedule?  If so, how likely are your “most likely” estimates?

This week, ProjectManagement.com published an article I wrote that shares the story of a PMO director who was frustrated with the late delivery of his projects.  Although his teams created “most likely” estimates that they asserted could be accomplished with 90% certainty, the reality was that most of the time, their “most likely” outcomes were too optimistic.

Learn how one PMO director used Statistical PERT Beta Edition to model schedule uncertainty, and see the difference between a “most likely” outcome and a “highly probable” outcome.

https://www.projectmanagement.com/articles/370549/Getting-Beta-with-Statistical-PERT

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.