# 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.

# 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.

# 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