New SPERT-Beta Release for September 2016

Here is another new release of Statistical PERT – Beta Edition version 0.2 Build 5.  This is a development build, meaning this is still being developed before its first production release.  I expect the initial production release to be in early 2017.

In Build 5, I’ve added several more subjective terms to describe the most likely outcome.  Now, there are 10 subjective terms from which to choose:

  • Near certainty
  • Extremely high confidence
  • Very high confidence
  • High confidence
  • Medium-high confidence
  • Medium confidence
  • Medium-low confidence
  • Low confidence
  • Very Low confidence
  • Guesstimate

I’ve better aligned the results of this Beta Edition with the Normal Edition of Statistical PERT (which uses the normal distribution), meaning, that Medium or Medium-High confidences in the most likely outcome will be approximately the same for symmetrical bell-curves between the Normal Edition and Beta Edition of Statistical PERT.

Also in Build 5, I’ve added the ability to obtain probabilistic estimates from a triangular (or nearly triangular) distribution.

Instead of continuing to offer download files from this blog, all download will be done from the main download area of the Statistical PERT website.  This will allow me to track how many times different versions of Statistical PERT are downloaded.

Feel free to contact me with any questions.  As I get through the development cycle for this new edition, I’ll begin writing more about how this edition utilizes the beta distribution.

Palisade @Risk7 RiskPERT comparison

On the first worksheet of the SPERT-Beta download (for version 0.2, Build 2), I’ve added a worksheet that does a comparison of nine different, right-skewed uncertainties.  The skewing ranges from mild to severe.  What I wanted to learn is how this version of SPERT-Beta compares with a Monte Carlo simulation of Palisade’s @Risk7 using the RiskPERT function in that add-in program.

I compared both SPERT-Beta and the SPERT-Normal edition (which uses the normal distribution), which is the original edition of Statistical PERT, already released and available for download and use.

I used two different subjective opinions within the SPERT worksheets.  For SPERT-Beta, I chose High Confidence and Medium-High Confidence in the most likely outcome.  For SPERT-Normal, I chose Medium and Medium-High Confidence in the most likely outcome.  These subjective choices approximate what a Monte Carlo simulation would give using the RiskPERT function in @Risk7.

You wouldn’t expect SPERT-Normal to give very similar results to RiskPERT since SPERT-Normal uses a symmetrical curve to model a asymmetrical uncertainties.  And yet, the results, even for severely skewed uncertainties, were not too far from the RiskPERT results.

I don’t recommend using SPERT-Normal for estimating uncertainties that are more than moderately skewed (to the left or right).  Using SPERT-Beta will give more accurate results in those cases because SPERT-Beta can build an implicit, skewed curve, whereas SPERT-Normal can’t.

New SPERT-Beta release for July 2016!

It’s been a long time since I played around with the next edition of Statistical PERT:  SPERT-Beta, which will use the beta distribution functions inside Microsoft Excel.

I’ve retooled the SPERT-Beta template so it’s simpler with fewer choices.  More importantly, I’ve changed the philosophy I used to create the initial SPERT-Beta template.  This new pre-release template of SPERT-Beta uses ratio scales that more accurately create a skewed probability curve, placing the most likely outcome — the mode — at or very near the top of the curve.  In my earlier version of SPERT-Beta, the mode was often nowhere near the top of the curve for many of the most likely confidence choices.  Now, choosing any of the most likely confidence choices will still place the mode in its proper place:  at the top of the mountain.

With this new version, choosing “Medium Confidence” in the most likely outcome will yield probabilistic results that closely mirror what you would get by running a Monte Carlo simulation using Palisade’s @Risk7 (specifically, the RiskPERT function).

I ran a comparison between SPERT-Beta and @Risk7’s RiskPERT, and the results were very often very close, even for skewed uncertainties.

As always, feel free to download and use, but be aware that this is a development version and may still contain serious flaws anywhere in the spreadsheet.

SPERT-Beta Confidence Interval v. Monte Carlo Simulation

Today I created ten, 3-point estimates with various skews and Most Likely Confidence levels in a SPERT-Beta Excel workbook.  The values I chose might be something like what a project manager might choose when estimating ten tasks on a project.  Tasks were often skewed to the right, meaning that there was a greater likelihood that an outcome would be greater than the most likely outcome than less.  I included one triangular distribution where the minimum point-estimate was the same as the most likely estimate (50, 50, 100).

Now, according to the Central Limit Theorem, you obtain a bell-shaped distribution for the sum of underlying distributions, irrespective of what kind of an underlying distribution you choose.  The CLT also stipulates that the variables should be independent, too, and they should all have the same kind of distribution.  Clearly, my ten tasks didn’t neatly fit into the stipulations for relying on the CLT to create confidence intervals for the entire ten estimates.

And yet….sometimes it’s good enough to be close enough so you obtain useful results.  While I used a variety of distributions among my ten, 3-point estimates, they did trend to being a little skewed to the right (but not always).

When I compared the resulting 90% confidence interval using SPERT-Beta with a 90% confidence interval obtained through Monte Carlo simulation (using @Risk’s RiskBetaGeneral function), I found amazingly close results, even though I wasn’t following the CLT stipulations perfectly.

  • SPERT-Beta, the 90% confidence interval was 793 – 938
  • Monte Carlo simulation, the 90% confidence interval was 796 – 940

Shockingly close!

Have a look at the results (all results were copied from the Excel file I was working in to do the compare).  If you have access to Monte Carlo simulation software, try comparing your own SPERT-Beta confidence intervals with results from a simulation model.  Try breaking the rules for using the CLT by using different, underlying distributions (that is, skewed to the left, skewed to the right, triangular, and with different Most Likely Confidence levels for each 3-point estimate) and see what effect that has on SPERT-Beta confidence intervals compared to simulated results.

Comparison of SPERT-Beta with Monte Carlo Simulation using RiskBetaGeneral

SPERT-Beta Development Release D

This new build of the SPERT-Beta template adds quite a few new features.  I’ve added ratio scales for standard deviation and mean, so the template will calculate an estimate of standard deviation, variance, and mean for each 3-point estimate that’s entered.

Using that information, the template calculates the mean for the entire portfolio being estimated, and the standard deviation for the entire portfolio (by taking the square root of the sum of variances).

And using THAT information, I’ve added the ability to find a confidence interval for the portfolio, which calculates a minimum and maximum estimate values for the entire portfolio.

To test this, build, I created four estimates:

  1. 100, 400, 500 (Low confidence)
  2. 200, 500, 1000 (Very low confidence)
  3. 500, 500, 5000 (Medium-low confidence)
  4. 1000, 10000, 12000 (Very high confidence)

The result was a portfolio having a SPERT-Beta-estimated mean of 11,878 with a standard deviation of 2,178.  The SPERT-Beta 90% confidence interval was 8,294 – 15,461.

Comparing this to a simulation model, I used 10,000 trials and the same 3-point estimates and combination of the SPERT worksheet’s choice for the shape parameters, alpha and beta.  In the simulation, the standard deviation was extremely close:  2,180.  The 90% confidence interval was a little different:  7,991 – 15,222.  The minimum threshold value in the simulation differed by almost 4% from the SPERT-Beta minimum threshold (the SPERT-Beta worksheet overstated the minimum).  The maximum threshold value in the simulation differed by only 1.6% from the SPERT-Beta maximum value (again, the SPERT-Beta worksheet overstated the maximum).

In looking at the simulation results, I could see that the portfolio of four estimates was bell-shaped but skewed to the left, slightly, which explains why the SPERT-Beta confidence interval differed from the simulation model.  Had I used more than just four 3-point estimates, and had the portfolio exhibited a more normal appearance overall, the SPERT-Beta confidence interval for the portfolio would create results that are closer to the simulation model.

Download Development Release D and view standard deviations, variances, means, and find a confidence interval of your choice using lucky Build 13!

(Visit the Download page to download the latest version of Statistical PERT – Beta Edition).

SPERT-Beta Development Release C

This version of SPERT-Beta adds a new set of probability curves — only they’re not curves!  Now, if the most likely outcome is equal to, or very close to, either the minimum or maximum point-estimates, the skew analysis will determine that a triangular distribution is the best shaped distribution for the uncertainty (and a right triangle, at that).  This means that for a right-skewed uncertainty where the minimum point-estimate is also the most likely outcome, the implied shape of the probability distribution is a right triangle sloping downward to the right.  Conversely, if the most likely outcome is equal to, or very close to, the maximum point-estimate, the skew analysis determines that the shape is a left-skewed, right triangle sloping downward to the left.

However, because the estimator can make a subjective opinion about how likely the most likely outcome really is, the actual shape of the implied distribution could be something other than a triangle.  For near certainty, for example, the shape is flat along the x-axis and rises very sharply either towards the minimum or maximum point-estimates (depending on whether the most likely outcome is equal to or very near to the minimum or maximum point-estimate).  For conditions where there is something less than medium-low confidence, the shape is concave and at the point of a guesstimate, the shape is virtually uniform.

So, if you want a triangular distribution, specify medium-low confidence, which approximates a right triangle.  However, because of the way this template is constructed, it isn’t a perfectly-shaped right triangle — it’s close, but results at the range midpoint appear to differ by about 2%.  For a perfect triangle, the beta value for medium-low confidence would need to be equal to 2, not 1.9.  It’s set by default as 1.9 for medium-low confidence because medium-low confidence puts about 27% of the area under the curve to the small side of the curve at the 3-point range’s midpoint; a triangular distribution would put exactly 25% of the area under the curve on the small side of the range midpoint.  To keep the triangular skew consistent with the meaning of all other skew values (like “near certainty” or “high confidence”), I opted to accept that the SPERT-Beta template won’t actually create probabilities for a perfectly-shaped, right triangle.

Try it out!  As complicated as I described this enhancement to the SPERT-Beta template, it’s extremely easy to use still.  Just enter a 3-point estimate, and set the most likely outcome to be equal to either the minimum point-estimate or the maximum point-estimate.  Once you do that, the skew analysis will say “Triangular” and the alpha shape parameter will be set accordingly.

(Visit the Download page to download the latest version of Statistical PERT – Beta Edition).

SPERT-Beta Development Release B

This is version 0.1 Build 11.  The change between Build 10 and 11 included a major change.  While testing, I learned that I wrongly set the order of the alpha and beta shape parameters between P4:Z15, so the implied shape of the curve was skewed on the opposite side of where it should have been.  The formula changes, then, correctly set the alpha and beta shape parameters so when a 3-point estimate implies a left-skewed curve, the shape parameters create an implied, left-skewed curve (and vice versa for a right-skewed curve).

There were a few minor changes, too:  I vertically centered all cells and added a minor edit to one column to avoid a #VALUE error if the wrong order of input values is used.

(Visit the Download page to download the latest version of Statistical PERT – Beta Edition).

SPERT-Beta Development Release A

If you’d like to get a sneak peek at the next edition of Statistical PERT which uses the beta distribution, you’ve come to the right spot!  This still-under-development release of the SPERT-Beta Excel workbook will only be available through this blog, not through the download page of the Statistical PERT website (which currently offers SPERT downloads using Excel functions for the normal distribution).

This SPERT-Beta workbook works just like all the other SPERT workbooks and templates.  Just add a 3-point estimate and make a subjective opinion about how likely the most likely outcome really is.  That’s it!  The SPERT-Beta formulas correlate your 3-point estimate to a matching set of beta distribution shape parameters which then can be used in Excel’s BETA.DIST and BETA.INV functions.

There’s a lot I could say about how this template is constructed, and that will come out in time.  Right now, if you download and use this template, feel free to share with me any issues, questions or problems you have.  Keep in mind that this is a work-in-progress, and visit here often to obtain the latest SPERT-Beta downloads.

(Visit the Download page to download the latest version of Statistical PERT – Beta Edition).

Statistical PERT – Beta!

This blog will provide information and download information about a new edition of Statistical PERT using the beta probability distribution!

I built the original Statistical PERT version in 2014 to easily leverage the built-in statistical functions in Microsoft Excel.  Among those statistical functions, the functions for the normal probability distribution (the so-called “bell-shaped curve”) — which are NORM.DIST and NORM.INV — are very easy to use, provided you know the standard deviation for a bell-shaped uncertainty.  Statistical PERT provides a rationale way for obtaining a standard deviation that corresponds to the bell-shaped uncertainty and the estimator’s subjective opinion about how likely the most likely outcome really is.

Using the normal distribution with Statistical PERT is very easy to do.  But the normal distribution isn’t appropriate for uncertainties which are more than just mildly skewed.

The beta distribution is the actual distribution upon which the original PERT was based.  And Microsoft Excel has built-in functions for the beta distribution, just like it has for the normal distribution (BETA.DIST and BETA.INV).  However, the beta distribution functions require several arguments, but the most difficult two arguments are two shape parameters, alpha and beta, that dictate the probabilistic shape of the resulting beta distribution.  Using these shape parameters, one can create a beta distribution that looks identical to a normal, bell-shaped distribution, or you can create right triangles, or shapes that show very strong skewing either to the left or to the right.  In short, the beta distribution is very flexible, but it’s harder to use.

My goal is to expand Statistical PERT to offer both an edition that’s easy to use, understand, and manipulate using the normal distribution, and another edition which is still just as easy to use and understand, but is more accurate and flexible than the version using the normal probability.  To do that, though, it will not be as easy to calibrate the SPERT templates which use the beta distribution.  It can still be calibrated and customized, but just not as easily.

Very soon, I’ll begin making downloads available on the new edition of Statistical PERT which uses the beta distribution.  These downloads won’t even be “beta” software, rather, they’ll be work-in-progress, “alpha” software which haven’t been tested rigorously and which will change and evolve a lot before I make them available for downloading on the Statistical PERT regular download page.

I’m really excited about expanding Statistical PERT in 2016 to a wide variety of project managers and anyone else who needs to easily make probabilistic estimates.  And I’m really excited about offering an edition of Statistical PERT which leverages the beta distribution inside Microsoft Excel!