I’m preparing a new Version 3 release of Statistical PERT® Normal Edition for an expected release this January, 2019. This new version of the SPERT Normal Edition will add new visual features to show the implied, bell-shaped, normal distribution curve for every row using Excel SparkLines. It will also create a new, bell-shaped, normal distribution curve for the summarized row on each worksheet, too, which sums all the row details.
You can download an early-release version of Version 3 by going to the Download page.
Check it out!
I just came back from the LeanAgileUS two-day conference in Fort Lauderdale, held earlier this week. It was a great conference! One of the key themes that I noticed is that agile teams need to respond to organizational requests such as these:
- When will the next release be ready?
- How many features do you expect to be included in the next release?
Several of the presenters advocated for the use of Monte Carlo simulation, such as that found by Actionable Agile, a vendor (and conference sponsor) offering visual tools for agile teams.
Statistical PERT has always been intended to simplify the task of estimating using Excel’s built-in statistical functions. I realized that I could do a much better job of adapting Statistical PERT to the needs of agile teams who either estimate in the absence of data (using just expert opinion), or who estimate having data (such as cycle times for completed user stories).
So this begins a new quest beginning in 2018! Create useful, forecasting tools that leverage Excel and that use Statistical PERT’s simple approach to stochastic estimation.
The first result is a simple spreadsheet that lets an agile team enter a few variables based upon what they know and what they think will be true about their future efforts. Then, the spreadsheet uses Statistical PERT to build an implied, bell-shaped curve for an agile release date. Based upon the uncertainty surrounding the next release, including the work efforts of the team, the SPERT spreadsheet creates a probabilistic release date.
Try it out! Go to the Download page on this Beta Blog website to download development-only versions of Statistical PERT for use with agile teams.
Today, on March 1, 2017, I officially released Version 1.0 of Statistical PERT® Beta Edition!. This new version includes a “Beginners” worksheet that walks users step-by-step through the probabilistic estimation process. The Beta Edition, which uses Excel’s built-in beta distribution functions, is able to model even severely skewed, bell-shaped risk uncertainties with the same ease as the original Normal Edition.
You can learn more by reading the press release by clicking here.
Download Statistical PERT® Beta Edition, Version 1.0 – Example Workbook
Today, both Statistical PERT® Normal Edition and Statistical PERT® Beta Edition received minor updates. Both editions have a new Mixed entry worksheet that gives estimators a very flexible way of creating 3-point estimates. You can learn more by reading about this change by clicking here.
The February release of SPERT-Beta is an official “Release Candidate 1” and I expect that next month, Version 1 will debut.
Download Statistical PERT® Beta Edition, Release Candidate 1 Build 1
Today marks the release of two editions of Statistical PERT®: the Normal Edition and the Beta Edition. The Normal Edition is the original Statistical PERT that uses Excel’s two normal distribution functions, NORM.DIST and NORM.INV. The Beta Edition uses Excel’s two beta distribution functions, BETA.DIST and BETA.INV.
The Normal Edition of Statistical PERT is a full version upgrade to Version 2. The Beta Edition is still a development build, so it is Version 0.6. The 1.0 release of the Beta Edition will occur later in 1Q 2017.
Both the Normal Edition and Beta Edition look very similar, and both follow the same easy approach to probabilistic estimation:
- Create a three-point estimate (minimum, most likely, maximum)
- Render a subjective judgment about how likely the most likely outcome really is
- Choose a probabilistic planning estimate or make a risk-based forecast
The new releases of both the Normal Edition and Beta Edition offer estimators the option to use one-point estimates and heuristics to calculate the minimum and maximum point-estimates. Estimators can still specify their own minimum and maximum point-estimates, too, if they choose.
This latest release of SPERT-Beta is a Version 1 release candidate, meaning that I don’t expect to add any new features to the current development build.
Try both editions of Statistical PERT!
Download Statistical PERT® Beta Edition, Version 0.6 Build 1
This month’s development build brings Statistical PERT – Beta Edition even closer to a stable, Version 1.0 release in the first quarter of 2017. Early next year, I’ll begin creating different “flavors” of SPERT-Beta like I have for the original Statistical PERT spreadsheets, where there is a SPERT-Beta for tasks, expenses, revenue, agile sprints, portfolios and events. I’ll also continue testing SPERT-Beta to ensure accuracy and reasonability of its results.
In this December 2016 release — the last one for 2016 — I’ve only done some window dressing to prepare for SPERT-Beta’s initial, production release in 2017. I’ve hidden a number of columns that won’t be of general interest to estimators using SPERT-Beta, I replaced the text in the Welcome! tab so it acknowledges SPERT-Normal, and I cleaned-up some of the worksheets to remove clutter and create a better visual presentation. I didn’t make any changes to any formulas or lookup values.
Download the December release of this development-only build
This month’s development build brings Statistical PERT – Beta Edition one step closer to a stable, Version 1.0 release in the first half of 2017.
In this November release, I’ve added the same pie chart visual as Statistical PERT – Normal Edition (version 1.4.1, the latest). The pie chart (only on the ‘SPERT Beta 0.4 Build 2’ tab) shows the confidence interval size, based upon the estimator’s lowerbound and upperbound thresholds. Also in this release is the same data validation indicator that checks to see if the correctly-ordered, three-point estimate was entered (green check if everything is okay, red X if something’s wrong). The actual validation formula is a little different than what’s used in Statistical PERT – Normal Edition; in Statistical PERT – Beta Edition, it’s acceptable to enter a minimum or maximum value that is identical to the most likely value. Finally, I’ve added the same ‘Welcome’ worksheet, which, when the spreadsheet is first opened, is the first worksheet displayed. The ‘Welcome’ worksheet will change a bit in the future to distinguish the unique differences between the Beta Edition and the Normal Edition of Statistical PERT.
Download the November release of this development-only build
The October release of the forthcoming SPERT Beta Edition development template just has mostly minor, cosmetic changes. I updated the copyright, made quite a few formatting changes, and a few other, minor changes.
One key change I made was to the alpha-beta shape parameters for a condition of High Confidence in the most likely outcome. The earlier version used a beta shape parameter of 7.5, but in Build 2 (of version 0.3), I’ve tamped down the beta value to equal 7. Reason being, the percentage gap between High Confidence and Medium High confidence was 6.5% (using a constant planning estimate of 150 for a 3-point estimate of 60-120-240), and I wanted the probability change to be closer to the 4%-5% that is common when a different subjective judgment is made about the most likely outcome. The current release is now Version 0.3, Build 2.
Download the October release of this development-only build.
The Beta Edition of Statistical PERT uses the beta probability functions inside of Microsoft Excel. Specifically, the Beta Edition of SPERT uses BETA.DIST (beta distribution) and BETA.INV (beta inverse).
The key advantage of using the beta distribution over the Normal Edition of Statistical PERT is that, using the beta distribution, you can more accurately model skewed bell-shaped uncertainties. With the Normal Edition of SPERT, you can still model bell-shaped uncertainties that are skewed, but only to a certain point before the resulting probabilistic estimates are not reliable. For example, when the range between the minimum point-estimate and the most likely point-estimate (the mode) is half or one-third or even one-fourth the range between the most likely point-estimate and the maximum point-estimate, the resulting probabilities are still pretty accurate within a few percentage points. (This assumes that the standard of truth is using the PERT beta distribution as found in Palisade’s @Risk RiskPERT function — which is a key assumption that may or may not be true for a given estimate).
With the Beta Edition of Statistical PERT, you can model many skewed, bell-shaped uncertainties even to the point where you are dealing with a triangular distribution on either the left-side or the right-side of the curve.
If you download the development build of the Beta Edition of SPERT, play around with the ‘SPERT Beta Curve’ worksheet. By changing the values for the Minimum, Most Likely and Maximum point-estimates (cells B4, C4 and D4) and also the Most Likely Confidence column (cell J4), you’ll see different representations of the bell-shaped curve on the chart that’s in that worksheet.
The Beta Edition of SPERT uses 150 combinations of the two key parameters required by the BETA.DIST function: alpha and beta. Each combination has been carefully calibrated so the resulting bell-shaped curve still retains the mode as found in the Most Likely point-estimate in cell C4. The mode may not be exactly, precisely the same in the bell-shaped curve, but it will be very close — close enough that it should be fine for most estimation scenarios.
Try using the Beta Edition of SPERT! Just be aware that this is still a development build.
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
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.