Category Archives: New Release

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
  • New hover comments for each column’s heading (some worksheets)

Statistical PERT – Beta Edition won’t have its Version 1 release until later in 1Q-2017, but the January release of Version 0.6 will be vastly improved over all preceding development versions, and will look very similar to SPERT-Normal Version 2.  And I’ll make SPERT-Beta available on the main site instead of only being available on the Beta Blog.

So, look forward to a great new year with great new versions of Statistical PERT!

New Version, New Edition of Statistical PERT

Statistical PERT – Normal Edition, New Version 2.0

I’m excited to announce that early in 2017, I will be releasing a new 2.0 version of the original Statistical PERT Excel workbooks and templates that I began creating in 2015.  Version 2.0 will introduce one-point, probabilistic estimation by letting estimators choose a heuristic to calculate both the minimum and maximum point-estimates in a three-point estimate.

A heuristic is well-defined on Wikipedia as, “any approach to problem solving, learning, or discovery that employs a practical method not guaranteed to be optimal or perfect, but sufficient for the immediate goals. Where finding an optimal solution is impossible or impractical, heuristic methods can be used to speed up the process of finding a satisfactory solution. Heuristics can be mental shortcuts that ease the cognitive load of making a decision.”

What I’ve learned in the two years that I and others have used Statistical PERT is that sometimes people just don’t want to go through the mental work of figuring out a 3-point estimate for an uncertain outcome.  Sometimes, it’s easier to start with a single estimate value and have the rest of the bell-shape curve implicitly created for you.  And although that may not be as precise and optimal as specifying all three points of a 3-point estimate, using a heuristic to create two of the three points in a 3-point estimate may be “good enough” for project planning purposes.

In the 2017 release of Statistical PERT, estimators may choose to employ a simple heuristic that takes a single point-estimate — the most likely outcome — and reduces it by a specified percentage to create the minimum point-estimate, and increases the most likely outcome by a specified percentage to create the maximum point-estimate.  SPERT estimators can choose separate heuristics for the minimum and maximum calculations.

For example, if I specify a value of 100 for the most likely outcome (the unit of measure doesn’t matter), I can say that the minimum point-estimate is 25% less than the most likely outcome, and the derived minimum would be 75.  Then I can say that the maximum point-estimate is 50% greater than the most likely outcome, and the derived maximum would be 150.  The resulting three-point estimate used to model the uncertainty would be (75, 100, 150).  I only had to set the minimum and maximum heuristics once for the entire worksheet, and all my subsequent one-point estimates will use heuristics to automatically create the minimum and maximum point-estimates.  Obviously, the math formulas involved are exceedingly simple, so the innovation here is mostly just about ease-of-use in creating three-point estimates, rather than technical innovation.

Estimators will be able to override heuristically-derived minimum and maximum point-estimates by just entering an actual, numeric value in place of the formula used to create minimum and maximum point-estimates.  All SPERT download files will still offer a separate worksheet to enter 3-point estimates without using heuristics, too.  Heuristics, then, are in addition to what is available in a Statistical PERT worksheet, not a replacement of anything currently available.

All existing Statistical PERT Excel downloads will be re-labeled as “Statistical PERT – Normal Edition” to reflect that all the currently-available downloads use Excel’s built-in normal distribution functions, NORM.DIST and NORM.INV.

Which brings me to the other exciting change for 2017….

Statistical PERT – Beta Edition, New Version 1.0

It’s been one year since I began a new quest to create a simple way to create probabilistic estimates using the more-flexible beta distribution, which handles skewed uncertainties more accurately than the normal distribution does.  I wanted to use Excel’s two beta distributions, BETA.DIST and BETA.INV in a spreadsheet that was just as easy to use as the original SPERT Excel spreadsheets, which hid the complexity of creating a standard deviation that the normal distribution functions require.

Excel’s beta distribution functions use two shape parameters, alpha and beta, which determine the precise shape of the beta curve.  Specifying alpha and beta shape parameters is not at all intuitive, however, so the goal was to create a technique that figures out a good approximation of alpha and beta based solely upon the estimator’s three-point estimate and the estimator’s subjective opinion about how likely the most likely outcome really is (just like the original Statistical PERT spreadsheets using the normal distribution!).

The details behind Statistical PERT – Beta Edition are too much to include in this blog post, but you can see a development build of the forthcoming version 1.0 that will be released in early 2017, along with the version 2.0 of Statistical PERT – Normal Edition.

Together, both editions of Statistical PERT will let anyone easily make estimates using Excel’s built-in, statistical functions.  And all Statistical PERT versions and editions will continue to be freely licensed under the GNU GPL, so you can download, use, modify and share any SPERT file like you’ve always been able to do.

Download a development-only build of Statistical PERT – Beta Edition here.

Version 1.4 Released!

Today, I realized Version 1.4 of Statistical PERT – Normal Edition for all SPERT example workbooks and templates (task duration, expense, revenue, agile, project portfolios, events).

Here’s what’s new in this release:

  • I removed the “Unskewed” probabilities which used the mode instead of the mean.  While using the mode would, under some conditions, yield a more accurate probability than using the mean, I found that most people were just confused by the difference between using the mode and using the mean.  In most cases, using the PERT mean will give accurate results, so I decided to remove the confusion by removing the columns devoted to “Unskewed” probabilities.
  • There is a new pie chart and associated cells which lets estimators create confidence intervals for any selected confidence level.  You can specify a confidence level and the confidence interval is determined for you, or, optionally, you can give a confidence interval and the confidence level is calculated for you.
  • There is a new pie chart showing what uncertainty looks like with respect to how likely is the Most Likely outcome.  One of Statistical PERT’s unique attributes is that it allows estimators to use subjective judgment (private knowledge, gut instinct, emotions, intuition) to rationally adjust the SPERT estimates.  Many people don’t know how to choose a subjective judgment, though.  This new pie chart shows what each subjective choice looks like if the only three outcomes were the minimum, most likely and maximum point-estimates.  Interestingly, there was a PMI Global Congress presentation that talked about visually making risk-based choices.
  • I standardized the SPERT example workbooks and templates so future changes can be easily made across all the different SPERT example workbooks and templates.  So, instead of the task duration’s template having headings like Optimistic, Most Likely and Pessimistic (where the order would be reversed for the Revenue template), now all templates use Minimum, Most Likely and Maximum as the headings for the three-point estimate columns.
  • Each SPERT file now has a Welcome! tab that introduces Statistical PERT to the downloader, and it has a link to a Quick Start guide that can help to quickly learn how to use any SPERT download.
  • I made a number of other tweaks, too, of a minor nature.  SPERT, for example, is now a federally registered trademark.

If you haven’t already done so, I encourage you to download your favorite SPERT example workbook or template!

SPERT™ Mobile released + Version 1.3 of SPERT™ for Task Estimation

This weekend, I released a new mobile version of Statistical PERT:  SPERT™ Mobile.  This new special release makes Statistical PERT easily used on iPhones and Android smartphones that have Microsoft Excel Mobile installed.  If you have a smartphone but don’t have Excel Mobile installed, it’s a free install.  Download Excel Mobile either from iTunes or Google Play.

Also this weekend, I updated Statistical PERT for task duration to Version 1.3.  In this new release, I’ve added a Welcome worksheet that has some brief, introductory information, including a link to a new Quick Start guide for use with any Statistical PERT spreadsheet.  In Version 1.3, I’ve add a pie chart visual to show the confidence interval for a collection of tasks, plus the upperbound and lowerbounds of the task total.

What is Soothsaying?

If you want to try Soothsaying your project’s risk register, it might help you to learn a little bit about that means.

Below is the article that’s published on ProjectManagement.com on soothsaying your risk register (in case you’re not a PMI member and therefore can’t read the article on PMI’s sister website).

From ProjectManagement.com – August 2016 theme on Risk

If you could look at your project’s risk register, then peer into a crystal ball and foretell which risks will occur sometime during your project’s execution phase, would that be helpful? And if you could tell your project sponsor, with 90% confidence, how much risk contingency you need to protect your project—would you be interested? You can—by becoming a risk soothsayer.

This doesn’t have anything to do with divination. Let me show you how to soothsay your risk register using a special Microsoft Excel® template to do risk analysis.

Before I do that, though, let’s consider how risk analysis is typically done. Usually, risks are simply put into an Excel template like this one, and if they’re given at all, risk probabilities and impacts are deterministically determined. A risk has a “low” or “medium” probability of occurrence, but there’s no definition for what that means (so there can be no alignment about that definition). And risk impacts are “medium,” “high” or “very high”—but what does that mean? Too often, deterministic risk analysis neither brings about stakeholder alignment, nor does it improve project decision making.

Wouldn’t it be better to know that, under existing conditions of uncertainty, you expect up to four of the 15 risks in your risk register to fire (that is, occur), and so you’ll need 44 additional time-units of schedule contingency to protect your project with 90% confidence? But if the sponsor wants less contingency, you can protect your project with 76% confidence if they provide just 33 additional time-units of schedule contingency.

An Example Risk Register
Let’s look at where these numbers and probabilities come from. From there, you can begin soothsaying your own risk register. Let’s start with an example risk register that has 15 identified risks. Rather than just saying each risk has “medium”, “low” or “very low” probability of occurrence, let’s name the specific probability we want to associate to each subjective term. How about starting with Figure 1 to align all stakeholder’s definition of Risk Probability:


Figure 1: Sample definitions of risk probability

Obviously, we can add or remove subjective terms and equate them with different probabilities until the project team and sponsor agree on what they mean. This brings about stakeholder alignment.

Now let’s move from deterministic estimates about risk impact to more probabilistic ones. An easy way to do that is using the Program Evaluation and Review Technique (PERT), which requires a three-point estimate for project uncertainties (minimum, most likely, maximum). Usually, these judgments are made using expert opinion just like with single-value (deterministic) impact estimates, but now everyone gets a sense of how much impact variation exists for each risk. Figure 2 shows an example of a single entry in the risk register for schedule impact using a three-point estimate:


Figure 2: A PERT expected value for risk impact

Notice that the entry calculates the PERT expected value (10) of the risk impact using the PERT formula:

(Minimum + 4(Most Likely) + Maximum) / 6

Since there are 15 risks in my example risk register, I’ll make three-point estimates for all of them, and sum the PERT expected values for all 15 risks; the sum total is 166 time-units, and the average of impact is 11.1 time-units per risk. Figure 3 shows just the last three risks in the risk register before the PERT sum total and average:


Figure 3: The PERT sum and average for 15 risks

But it’s highly unlikely all 15 risks are going to fire on our project, so we don’t need a schedule reserve of 166. If we knew exactlyhow many risks were going to fire, and which ones they were, then we’d know exactly how much schedule contingency to have on our project.This is where Excel’s binomial functions come in—we’ll use Excel’s built-in statistical functions to soothsay the risk register.

The Binomial Functions in Excel
The binomial probability distribution answers the question, “Up to how many risks will fire on this project?” (I use the verb “fire” to mean that the risk occurs during the execution phase, and now we have to deal with its uncertain, adverse impacts). In Excel, there are two binomial functions: BINOM.DIST (binomial distribution) and BINOM.INV (binomial inverse). Let’s use BINOM.INV to soothsay the risk register.

BINOM.INV requires three arguments:

  • trials are the number of risks in the risk register
  • probability of success is the probability of occurrence for each trial (risk)
  • alpha is how confident we want to be in our soothsaying (usually 75% or more)

Example: Our sample risk register has 15 risks, so the trials argument is 15. We can use Excel’s AVERAGE function to get the average probability of occurrence across all 15 risks, where each risk has an individual occurrence probability of between 5% and 25%. The average risk occurrence is 13.7%, so the probability of success is 13.7%. (Don’t get confused: “Success” in this context means something bad happens to our project—a risk occurs, or fires, during the execution phase). Finally, let’s be 90% confident in the schedule reserve, so the alpha argument is 90%. In Excel, the BINOM.INV function looks like this:

BINOM.INV(15, 0.137, 0.90) = 4

With 90% confidence, we now soothsay that up to four project risks will fire during the project’s execution phase. It might be fewer than four, but I’m ready to accept a 10% chance that five or more risks will fire—an improbable occurrence that, to me (and hopefully to you), is tolerable.

So we need to protect our project against up to four risks that we soothsaid will fire during the project’s execution phase. The question now is: Which four? The four risks with the least impact, or the greatest? Or a mix of low-impact and high-impact risks?

Soothsaying the Schedule Reserve
Instead of soothsaying exactly which risks will fire, let’s start by finding the average expected value for all risks; that involves just adding up the PERT expected value for all 15 risks (166), then dividing by the number of risks in the register (15):

166 / 15 = 11 (rounded)

On average, the schedule impact for each risk in our risk register is 11 time-units.

We expect, with 90% confidence, that up to four risks will fire during the project execution phase, and each fired risk has an average schedule impact of 11 time-units. So let’s add 44 time-units to our project schedule to address the schedule risk found in our risk register:

4 fired risks * 11 time-units per risk = 44 time-units for the schedule reserve

Now that we’ve soothsaid schedule risk, we can use the exact same method to soothsay budget risk, too!

Monte Carlo Simulation Comparison
How well does risk soothsaying work? To find out, I used Palisade’s @Risk7® to create a Monte Carlo simulation model for the example risk register, and compared its results to our soothsaid schedule reserve of 44. I created the simulation model using the same 15 risks, their respective likelihood of occurrence and three-point estimates. Then I used @Risk’s RiskPERT function, which is a special beta probability distribution, to model the uncertain nature of each risk’s schedule impact. After running the simulation model for 50,000 trials, I compared its results with the schedule reserve that came from risk soothsaying.

Figure 4 shows what the Monte Carlo simulation results look like at the 90% confidence level:


Figure 4 : Monte Carlo simulation of the risk register

The graph shows that the most likely scenario (the mode) is that none of the 15 risks will fire during the project execution phase. But to have 90% confidence that there is enough buffer in the project schedule to handle risks that might fire, the model shows that we need a schedule reserve of 44 time-units—just exactly what we soothsaid! Only in 10% of the simulated trials would a reserve of 44 time-units fail to protect the project. (Note that both simulation modeling and risk soothsaying are equally dependent upon the quality of work done during risk assessment, and that results from risk simulation may vary from risk soothsaying).

Conclusion
Using Microsoft Excel’s built-in statistical functions, it’s easy to be a risk soothsayer. You can soothsay anytime you add, modify or remove risks from your risk register. Risk soothsaying allows project managers to thoughtfully protect the project schedule and budget against known risks, increasing the likelihood of overall project success.

Now try soothsaying using your project’s risk register! (Just be sure to tell your project team and sponsor that what you’re really doing is risk analysis…)

Statistical PERT for Fantasy Football!

This summer, I was in Chicago visiting family and talking with my nephew about fantasy football.  The whole Chicagoland family is really into fantasy football; I’m not.  I watch NFL games on TiVo, skipping commercials and past any game that looks like it’s non-competitive.

As I talked with my nephew about how he picks players from his team roster to put into the upcoming game, I realized there was an application for Statistical PERT even with fantasy football!

Essentially, my nephew explained that he has several players from which to choose to put into a game for one game position, like, who will be his team quarterback.  He has two or more to choose from on his team.  He faces the same choices with other player-positions, too.

The question is, which player on his fantasy team will perform best, given each player’s health, and the real-game opponents each player faces?

This uncertainty can be modeled using a regret table in statistics.  Using a regret table helps you make a reasonable, probabilistic choice that will give you the least regret among all the choices you have.

With this special adaption of Statistical PERT, I’ve added a regret table to the example workbook.  The regret table is conditionally formatted so it highlights which player will give you the best point performance at a variety of probabilistic points.  The player who will give you the least regret across many possible outcomes is the player you would put into the game.  That player will give you the least regret, statistically speaking.

So, you enter each player for a given position, model what you think each player is capable of for the upcoming weekend games, and let the SPERT worksheet show you which player will give you the least regret across many uncertain possibilities.

Notably, in testing this special Fantasy Football version, I didn’t find that using the subjective opinion about the mode changes anything; irrespective of how you subjectively feel about the most likely outcome, the spreadsheet will continue to choose the same player (or players, if there’s a tie) as the player/s that will give you the least regret.

We have a new tagline for anyone using this special version to help you pick your fantasy football players for next week’s game:  “Lose With Less Regret.”   🙂

Here’s the download!

Announcing Soothsayer™ for Project Risk Analysis

I’d like to announce a new, free, Excel template:  Soothersayer™ for Project Risk Analysis.  This Excel template uses the binomial distribution to estimate the risk impact of identified and assessed risks in the risk register.  This template can be used to analyze both schedule and budget impacts.  It’s intended to let project managers calculate the size of the project risk contingency needed to ensure project success.

Like all of the Statistical PERT templates, this template is freely licensed under the GNU GPL published by the Free Software Foundation.  Although this isn’t a “Statistical PERT” template, it falls into the same camp of Excel-based templates that are freely available and use the built-in statistical functions of Microsoft Excel 2010 / 2013 / 2016.

I’ll have a LOT more to say about Soothsayer in future blog posts.  For now, download it and try it out!  It’s simple and intuitive to use.

Statistical PERT for Fantasy Football!

I’m a football fan, but I am not a fantasy football fan; I don’t have enough time in the day to follow individual player stats and participate in a fantasy football league.

But I do have family members who are into fantasy football.  They’re about to start drafting players next month in preparation for the 2016 NFL football season.

As I talked with my nephew, I saw an application for Statistical PERT for fantasy football fans.  While one nephew-in-law goes crazy with player data and projections for how many passing yards each quarterback might throw in a game, another nephew works more simply.  For him, he just projects how many fantasy football points each player may score on week-to-week basis.

Player performance can be thought of as a bell-shaped uncertainty.  Sometimes they have stellar games, sometimes they play dismally, and often they just play their expected average (which, for these players, is well above average among their peers).

To help my nephews pick players to put into the game, I created an adaptation of Statistical PERT which can be used to select the player that will give them the least amount of regret among the whole range of possible player performance.

For this special edition of Statistical PERT — which won’t be available on the main website — you enter a 3-point estimate for each player option, a subjective opinion about the most likely performance for the next week’s match-up, and let Statistical PERT compare player performance across the whole bell curve, calculating the player who will give the fantasy team manager the least regret; this is the player that, on paper, has the best chance of scoring the most points for the fantasy football team.

Have a look by downloading the Statistical PERT worksheet for Fantasy Football!  Like all SPERT Excel templates, this is freely licensed.