# SPERT-Beta Development Release ‘A’ available

Well, this past week instead of blogging, I spent a huge chunk of my time working on the next edition of Statistical PERT:  Statistical PERT – Beta, which will use MS Excel’s built-in beta distribution functions to easily create probabilistic estimates with greater accuracy and for a greater number of uncertainties, even those that are too skewed to use Statistical PERT built with the normal distribution functions.

I’m really excited about this next edition of Statistical PERT.  This next edition will be a companion to, not a replacement for, the original edition of Statistical PERT using the normal distribution.  Here’s how I characterize the two editions:

Statistical PERT – Normal

• Very easy to use
• Very easy to modify
• Very flexible
• Easy to understand and explain
• Can easily use the SPERT concepts without a pre-built SPERT template
• Works with any bell-shaped uncertainty
• Works with slightly-to-moderately skewed uncertainties
• Accuracy is between 95% – 99.9%

Statistical PERT – Beta

• Very easy to use
• Not as easy to modify
• Very flexible
• Not as easy to understand or explain
• Cannot easily use the SPERT concepts without a pre-built SPERT template
• Works with any bell-shaped uncertainty
• Works with even very severely skewed uncertainties
• Works with non-bell-shaped uncertainties
• Achieves very high accuracy (not sure exactly how accurate yet, but it is likely that accuracy will be > 99% for the uncertainties it is designed to estimate)

# “I’m not THAT uncertain!”

Just to complete yesterday’s blog post, you can not only modify the SPERT template to change what “Nearly Certain” means (when choosing a corresponding Ratio Scale Multiplier), you can also change what a “Guesstimate” means, too.

In the default SPERT templates and in the SPERT-7 Rule, you’ll see that a most likely estimate that is a “guesstimate” uses a Ratio Scale Multiplier of around 42%.  Where did that number come from?

Just like when modeling what “near certainty” means, we follow the same process of finding a standard deviation in Excel using the STDEV.P function.  Only this time, uncertainty when distributing 100 hypothetical trials of a bell-shaped uncertainty looks like this:  33-34-33 (minimum, most likely, maximum).

In Excel, enter 100 values in column A where you’re constrained by only three possible values for each trial (using any bell-shaped, 3-point estimate of your choosing).  In cells A1:A33, enter the minimum value.  In cells A34:A67, enter the most likely value.  In cells A68:A100, enter the maximum value.  Now, use STDEV.P(A1:100) to find the standard deviation for a very uncertain distribution of 100 hypothetical trials, where the distribution shows it’s actually not likely at all to get the most likely result (and yet, the most likely result is still the most likely result among three possible outcomes).

Now take that standard deviation and divide by 100.  You’ll get a number between 40-43% if your 3-point estimate isn’t indicative of a very skewed, bell-shaped uncertainty.

When you use 42% as the Ratio Scale Multiplier for a “Guesstimate” about the most likely point-estimate, you’ll find that the probabilistic estimates at the extremes will not stay within the minimum-to-maximum range.  So some estimates will be smaller than the minimum point-estimate, some estimates will be larger than the maximum point-estimate.  The normal curve as no practical boundary lines for how small or how large an outcome can be (don’t worry, such outcomes are essentially impossible from a practical standpoint).

But the SPERT template can change the Ratio Scale Multiplier associated with a “Guesstimate.”  Model a different, less uncertain distribution if you want:  use a 30-40-30 split, or 25-50-25, and say that that’s what a “Guesstimate” looks like to you.  Divide the revised standard deviation by 100 and plug-in the revised Ratio Scale Multiplier on the VLOOKUPS tab in the SPERT template, and you’ve altered how SPERT estimates will be calculated.

Just be sure that all the Ratio Scale Multipliers between “Near Certainty” and “Guesstimate” are within the range of a “Near Certainty” RSM and a “Guesstimate” RSM, and that the values are in ascending order beginning with the smallest RSM for “Near Certainty” and the largest RSM for “Guesstimate”!

# “But I’m more certain that THAT!”

In yesterday’s blog post, I explained that Statistical PERT’s definition of “near certainty” in a most likely outcome meant that the standard deviation for that still-uncertain uncertainty was 7% of the range between the maximum and minimum point-estimates.  (Confused already?  Read yesterday’s blog post firstly, then).

But someone might say, “My definition of a ‘nearly certain’ most likely outcome is more stringent than that.  I wouldn’t say out of 100 trials, 98 outcomes are equal to my most likely point-estimate means that my most likely outcome is nearly certain to occur.”

That’s fine; then change the meaning of what “Near Certainty” means in the SPERT template!

It’s easy to do.  Model your definition of “Near Certainty” by modifying the Ratio Scale Modeler worksheet in the SPERT template, or just create a blank spreadsheet to do the modeling.

Start the same way as before.  Enter a minimum point-estimate in cell A1, followed by a maximum point-estimate in cell A2.  Then, copy your most likely outcome into cells A3:An, where n = whatever number of hypothetical trials you want to model so you can show what ‘near certainty’ means to you.  Copy down cell A3 to cell A200, or cell A300 or A500 or A1000.  Whatever you want!

When you’re done copying cells in column A, follow the same process I described in the previous post.  Use STDEV.P to find the standard deviation for the distribution you’re modeling.  Then, divide that standard deviation by the number of hypothetical trials you used.  The result is a SPERT Ratio Scale Multiplier (RSM) to be used with a subjective opinion that says the most likely outcome is nearly certain to occur.

If you’re using a perfectly-shaped bell-curve, where only one trial is less than the most likely outcome and one trial is greater than the most likely outcome, you should find that the following:

• 1-198-1 creates an RSM of about 5%
• 1-298-1 creates an RSM of about 4%
• 1-498-1 creates an RSM of about 3%
• 1-998-1 creates an RSM of about 2%

Yes, the actual values of your three-point estimate will affect the RSM values, but you’ll find that the resulting RSM’s don’t change too much, in spite of the three-point estimate you’re modeling.  Knowing that, the RSM’s can be used for a number of other, different, three-point estimates within the SPERT estimation process.

If you model a skewed three-point estimate, you’ll find that the RSM’s will be bigger than what I’m showing above, and that makes sense, since there is greater uncertainty surrounding an asymmetrical bell-shaped curve than a symmetrical bell-shape.

You don’t have to be constrained by the default presumption that a ‘nearly certain’ most likely outcome means a 1-98-1 split of 100 hypothetical trials for an uncertainty.  You can define for yourself what ‘near certainty’ means to you….and to others.

# What’s your definition of a “Nearly Certain” outcome?

In Statistical PERT, a “nearly certain” outcome is one that has a standard deviation of about 7% of the range between the pessimistic and optimistic outcomes.  What’s so special about 7%?  Why not 5%?  Why not 1%?  Or 10%?

When I developed Statistical PERT last year, I arbitrarily considered a bell-shaped uncertainty that could have 100 possible outcomes (trials).  One hundred is just an easy number to work with, one that you can get your arms around and wrestle with.

If I told you to choose to distribute hypothetical outcomes for a bell-shaped uncertainty 100 times so I could get a sense of your uncertainty surrounding the mean and mode, you would rightfully balk.  Make up 100 hypothetical values for an uncertainty?  Just to distribute them between a minimum point-estimate and a maximum point-estimate?  Just so you can show me what kind of a bell the bell-shaped uncertainty is?

But what if instead I made the task easier by saying, “Show me how 100 hypothetical trials might play-out for this uncertainty, but with one caveat:  you can only choose three possible values for each trial:  minimum, most likely, and maximum.”  That would be considerably easier to do, as now you would just be left with figuring out how many times the minimum uncertainty would occur, how many times the most likely outcome would occur, and how many times the maximum outcome would occur.  You’re just distributing the 3-point estimate you already created.

And then, what if I said, “Show me what a ‘nearly certain’ likelihood looks like for the most likely outcome.”  You only have 100 hypothetical trials to work with, and you can only use one of three possible values to distribute across those 100 trials.

Very likely, you would distribute the 3-point estimate this way:  one time the minimum outcome would occur, one time the maximum outcome would occur, and 98 times the most likely outcome would occur.  To not equate at least 1 out of 100 trials with both the minimum and maximum outcomes would mean you’re not working with a bell-shaped uncertainty.

Now, open up Excel.  Enter a minimum value for a bell-shaped uncertainty in cell A1.  Put the maximum value in cell A2.  And for the next 98 cells in column A, put in a most likely value for the made-up, bell-shaped uncertainty of your choosing.

What’s the standard deviation for this distribution?  Use STDEV.P(A1:A100) to find out.  Depending on what you entered for cell values in column A, the standard deviation could be just about anything.

Finally, divide the standard deviation by the range between the minimum and maximum point-estimates.  What do you get?

A number pretty close to 7%.

If we start with the premise that a most likely outcome is “nearly certain” to occur, and that a nearly certain outcome can be modeled with a 1-98-1 split of 100 hypothetical trials, the resulting standard deviation divided by the range between the minimum and maximum point-estimates will be around 7%.

Don’t like the default SPERT definition of a “nearly certain” outcome?

Then change it.

Statistical PERT is a technique with a lot of flexibility built-in to it.  Find a standard deviation for 200 or 500 or 1000 trials, if you want.  And use more than just three possible values for each trial, too, if you wish.  The SPERT template is just a template, just a starting place.  You can adapt it to better fit your own needs.  Changing the meaning of a most likely outcome that is “nearly certain” to occur is well within the Statistical PERT framework.

# Statistical PERT – Beta Edition Winter, 2016

The tagline for Statistical PERT is “Estimation Made Easy.”  Dealing with probabilities isn’t easy, but using Microsoft Excel and the simple concepts behind Statistical PERT, anyone can make probabilistic estimates for bell-shaped uncertainties.

Unlike PERT, which is based on the beta probability distribution, Statistical PERT uses the normal distribution.  As long as the uncertainty has bell-shaped properties, this is absolutely not a problem.

Below is a graphical representation of a normal distribution; it has a standard deviation of 7, a mode of 50, and 95% of the area is between 36.3 and 63.7:

Below is a graphical representation of a beta distribution; it has a standard deviation of 7, a mode of 50, and 95% of the area is between 36.3 and 63.7:

Can you tell a difference?

No.  There is no easily discernible difference in shape of these two precisely drawn probability curves, although the bell-shaped curve created by the beta distribution has hard-stop anchors of 0 and 100, whereas the bell-shaped curve above it has infinitesimally small probabilities that are smaller than 0 and greater than 100.

Using Statistical PERT with bell-shaped uncertainties will yield very, very accurate results even though Statistical PERT was built with Microsoft Excel’s normal distribution functions, NORM.DIST and NORM.INV.  Even bell-shaped curves that are skewed but still bell-shaped can be used with Statistical PERT to create very accurate estimates.

But what if your uncertainty does not have bell-shaped properties to it?  What if it has a uniform shape, or a triangular shape, an extremely skewed bell shape, or some other shape besides a bell shape?

That is the premise I’m using for a very exciting, new addition to Statistical PERT!

I’m “in the lab” right now working to create a different edition of Statistical PERT using the beta distribution.  This opens up using Statistical PERT with a wider variety of uncertainties, and it allows for more-accurate estimation than using Statistical PERT beyond just the normal distribution.

Using the beta distribution is inherently difficult to do, though.  Microsoft Excel’s BETA.DIST function requires two arguments called alpha and beta, and working with them isn’t intuitive.

So my work between now and this winter is to create Statistical PERT – Beta Edition.  I want to create a simple, easy way to leverage the beta distribution without the estimator having to take a statistics class, and without having to worry about the shape of a beta distribution.  The resulting new Excel templates for SPERT Beta I’ll create will still be free to download, free to use, free to modify, and free to redistribute, without having to register on the site, without adware, without worrying about malicious macros embedded in the spreadsheet file.

# Which to use? Mean vs. Mode (Part 2)

To compare Statistical PERT’s use of the mode with a Monte Carlo simulation that  characterizes high confidence in the most likely outcome, we have to depart from using the PERT distribution — a special form of the beta distribution — and use a different beta probability curve to better model high confidence in the mode.

There would be no point to even considering the differences between using the mean and mode if the bell-shaped curve was symmetrical; in that event, the mode and mean would be the same value, and choosing between them would be a moot point.

Instead, we have to consider a skewed, bell-shaped curve with high expectation in the most likely outcome, but a curve which is asymmetrical.  Since in projects most outcomes that aren’t close to the most likely outcome exceed the mode, we’ll consider a bell-shaped curve that is skewed to the right.  The three-point estimate we’ll consider is 50-100-200.

When I ran a Monte Carlo simulation model using a PERT distribution, it led to a mean of 108, a mode of almost 101, and a standard deviation of 27.64.  That standard deviation is not showing the vertical rise that we want, where more of the area is closer to the mode (and mean).

Instead, let’s use a beta distribution where the alpha1 value is 4, and the alpha2 value is 7 (I’m using the RiskBetaGeneral function in Palisade’s @Risk Excel add-in).  The resulting simulation creates a mode of 100, a mean of nearly 105, and a standard deviation of 21.  We want a standard deviation that’s lower than the PERT distribution of 27.64 because there’s high confidence in the most likely outcome, so the standard deviation should be smaller.

Using the SPERT standard deviation formula and the SPERT-7 Rule, the formula is (Max – Min) * 14% for high confidence in the most likely outcome.  Using my three-point estimate of 50-100-200, the SPERT standard deviation is (200 – 50) * 14% = 21.  This matches the beta distribution using (4, 7) for the alpha values.  Here’s what the curve looks like with high certainty around the mode, but which still extends to the left to 50 and to the right towards an improbable 200.

Assuming that the Monte Carlo simulation best represents the nature of the uncertainty, how well does SPERT estimation align with a Monte Carlo simulation just using the simplistic SPERT formulas that rely on the normal distribution?

The first column above are different points on the beta curve.  The second column is the Monte Carlo result — how much area under the curve and to the left of the X point.  The third and and fifth columns show SPERT-calculated values using the mode and PERT mean, respectively.  Note that in SPERT estimation, the PERT mean is estimated using the PERT formula — not through simulation.  So, in the SPERT analysis above, the estimated PERT mean was 108, not the nearly 105 it was during the simulation.  The orange columns show the probability gap between the Monte Carlo simulation and the SPERT calculations.

Overall, SPERT calculations grew increasingly more accurate when dealing with probabilities of 90% and more.  At its worst, SPERT calculations using the mode were 6.5% off from the Monte Carlo simulation (when X was 110).  Using the estimated PERT mean, though, the error at that same point was 8.6%.  The error using the PERT mean was, at its worst, 9.5% off, whereas using the mode, the worst error was 6.5%.  It’s noteworthy to point out that SPERT estimation using the estimated PERT mean was more accurate than SPERT estimation using the mode at other points.  If I take weighted averages of the errors for both SPERT using the mode and SPERT using the mean, SPERT using the mode is slightly more favorable over a sample of values across the entire curve than estimation using SPERT and the estimated PERT mean.

The take-away from this is that, under a condition of high confidence in the most likely outcome on a skewed, bell-shaped uncertainty, SPERT estimation using the mode will result in estimation errors that are less overall than SPERT estimation using the mean, and SPERT using the mode’s errors won’t ever be as bad as SPERT estimation using the mean.

Using the normal distribution curve to model a skewed probability isn’t ideal, but it can be quick and still reasonably accurate — accurate within an estimator’s tolerance for error.  That won’t always be the case, though, and sometimes SPERT just isn’t a good technique to use to estimate uncertainties, even if they’re bell-shaped.

Recognizing this, I have an exciting announcement about Statistical PERT estimation on Monday, September 14!

# Which to use? Mean vs. Mode

Part 1

The Statistical PERT downloads all offer the ability to see probabilities based upon the PERT estimate of the mean and also the mode, which in the templates are used as a surrogate of the mean.  Why are there two sets of probabilities?  Why not just one set using the PERT mean alone?

Good question!

Let’s start crafting an answer by thinking of a perfectly shaped normal curve.  In a perfectly shaped, perfectly symmetrical normal curve, the mean, median and mode are all exactly the same point on the curve:  the very top of the curve.  When using a perfectly shaped normal curve, the question of whether to use the mean or mode in Excel’s normal distribution functions, NORM.DIST and NORM.INV, is immaterial.

That immateriality starts to change, though, when dealing with skewed probability curves.

When I compare Statistical PERT to a PERT distribution (which is a special form of the beta distribution), I do so using a ratio scale multiplier of 20-21% and the PERT mean in the NORM.DIST and NORM.INV functions.  That leads to SPERT probabilities that closely align with a Monte Carlo simulation using a PERT distribution.  But the more skewed the curve, though, the less closely aligned the SPERT probabilities are, since SPERT relies on a normal distribution that assumes symmetry, not asymmetry.

I’m working on an analysis that compares SPERT with Monte Carlo simulation using 3-point estimates that imply a skewed curve.  My analysis thus far shows that as long as the difference in ranges between the minimum and most likely value and the most likely and maximum value isn’t greater than a factor of four, SPERT can calculate probablities that are within 5% of a Monte Carlo simulation.  Of course, the more symmetrical an implied curve is, the closer SPERT estimates are to a Monte Carlo simulation using the same 3-point estimate (and, for SPERT, a ratio scale multiplier of 20% or 21%).

Now lets consider for a moment a condition where the 3-point estimate implies an asymmetrical curve, but there is very strong confidence in the most likely outcome (that is, a very high kurtosis).  The peak of the curve rises very high vertically and falls very dramatically from the peak and then extends rightward towards the maximum value.  In words, suppose I say, “I am extremely confident the task will take 40 hours, but there is a very slight chance it could take as little as 30 hours and an equally slight chance it will take up to 80 hours, but I’m almost certain that the task will take exactly 40 hours.”  This is the condition we’re dealing with.

A Monte Carlo simulation using the PERT distribution won’t capture this particular uncertainty very well.  We’d have to either use a different form the beta distribution (on which the PERT distribution is based) or choose a completely different probability distribution that mirrors the scenario I just described where there is very high expectation in the most likely outcome, and the most likely to maximum outcome has a range that is four times greater than the minimum to most likely range.

The situation has gotten complex!  But Statistical PERT is all about keeping things simple, yet still useful, so tomorrow let me address how this can be handled by using the mode (that is, the most likely outcome) as a surrogate for the mean in the normal distribution functions of Excel.

Next week after the Labor Day holiday, I’ll return to regular posts on Statistical PERT and estimation in general.  I just returned from a short vacation in Washington DC, welcoming incoming graduate students to George Washington University’s MSPM program.  Fun times!

In wanting to show someone how Statistical PERT works and how relatively easy the concepts are behind it, I used just a pen and paper — not Excel! — to create probabilistic estimates using statistical principles, rules, and assumptions.

Suppose there’s a room full of project managers, and all of them have their PMP credential.  And I asked you, “About what percent of the room are project managers who are 55 years old and older?”  You can’t see the room.  All you can do is make some assumptions and use whatever expert knowledge you have.  (You left your laptop back in the hotel room.)

What information do you know?  Not much.  You don’t even know how many people are in the room!

But you know that PMPs have real-life work experience and perhaps many, in not most, have 4-year college degrees.  If someone graduated from college in four years and immediately began work as a full-time project manager, they might be able to attain their PMP credential by, say, the age of 25, right?  A 25-year-old PMP is possible, but highly unlikely in a room of profesional, PMP-credentialed, project managers.

How about the upper-end?  How old might be the oldest PMP credential-holder?  Age 60 is certainly possible, and I know people who work even after their normal retirement age, so how about 70 years old?  There might be a PMP older than 70, but in a room of project managers, let’s just say that a reasonable estimate of the oldest PMP credential-holder is 70 years old.

What might be the most likely age of a PMP credential-holder?  Most probably, it’s not going to be someone in their 20s.  Or 60s.  Though many more PMPs are in their 30s than in their 20s, my experience is that a PMP is more likely to be in their 40s than in their 30s.  That leaves PMPs who are most likely in their 40s and 50s.  My personal gut feel is that the most likely age of an actively-engaged PMP is probably in their 40s, so I’ll say that the person is most likely 45 years old.

We’ve got a 3-point estimate:  25, 45, 70.  That’s about all we need to use SPERT estimation!

Let’s calculate the mean (arithmetic average) using the PERT formula:

[Min + 4(Most Likely) + Max] / 6.

So:  [25 + 4(45) + 70] / 6 = [25 + 180 + 70] / 6 = 275 / 6 = about 46 years old, nearly the mode of 45 years old (which was my most likely estimate).

Let’s calculate the standard deviation using the SPERT-7 Rule and the SPERT standard deviation formula which is:

[Maximum – Minimum] * Ratio Scale Multiplier

The SPERT-7 Rule uses ratio scale percentages corresponding to subjective opinions about the most likely outcome, where 7% = Nearly Certain, 14% = High Confidence, 21% = Medium-high Confidence, 28% = Medium-low Confidence, 35% = Low Confidence, and 42% = Guesstimate.  And we know that a Monte Carlo simulation using the PERT distribution (a special form of the beta distribution) is equivalent to using the Medium-high Confidence ratio scale multiplier of 21%, so let’s use that.  Since I’m using mental math at this point, not even a scratch pad or Windows calculator, I’ll round it to 20%.

The SPERT standard deviation formula, then, is:  [70 – 25 * 20%] = 45 * 20% = 9 years.  I did that all in my head as I typed this out!

Now let’s use the well-known 68-95-99.7 Rule in statistics.  One standard deviation from the mean covers 68% of the area under the curve.  The PERT mean is 46 years.  Let’s add and subtract one standard deviation of 9 years from the mean; that creates a range between 38 years and 55 years.  The remaining 22% of the area is evenly split between the left-side and right-side tails, but we’re only interested in the right-side tail.  So, 11% of the area under the curve is to the right of 55 years.  That’s my answer!

In a room full of project managers, all of whom hold the PMP credential from PMI, about 11% of them will be age 55 or older, and 89% would be younger than 55.

The concepts behind Statistical PERT are easy enough you don’t even need Excel (but you get much better and robust results, naturally, when you do use Excel)!

# Agile Waterfalls

At work today I got an email from some research group peddling a white paper on agile adoption.  As it happens, I’m under a contract with Pluralsight to create a new course on agile adoptions – what you need to know beforehand.  So, I downloaded the white paper and gave it a quick scan.

The article didn’t really offer any new insight for me, but it did highlight a book written by another Davis (no relation) named Barbee Davis.  Her book is entitled, “Agile Practices for Waterfall Projects.”  How timely!

I ordered the book.  Sounds great, I hope it reads great, too!

# Agile Estimating and Planning

I’m continuing to read Mike Cohn’s book, “Agile Estimating and Planning.”  I’d like to get past the halfway mark by week’s end.

In Chapter 6, I liked how he pointed out that three common techniques for estimating are:

• expert opinion
• analogy
• disaggregation (fancy word meaning “to break down into smaller pieces”)

Mike points out that, “Each of these techniques may be used on its own, but the techniques should be combined for best results.”  I agree!

Certainly no one will complain about using expert opinion to make estimates — who wants a non-expert to size work efforts, agile user stories, expenses or revenues?  Expert opinion is bread-and-butter estimation.

Analogy is intriguing, though, because expert opinion might implicitly make analogies when the expert makes an estimate, but very often those analogies never see the light of day.  Agile estimation does make a point of comparing and contrasting one user story with other user stories, but in a traditional project, that may not be so overtly done.

Disaggregation (breaking down into smaller pieces) is what PMI calls “progressively elaborated” estimation.  As you learn more, and as you’re able to see the details more clearly, those smaller details can be sized up, then aggregated to create a larger estimate.

In Chapter 6, Mike introduces Planning Poker and explains why it works:

• it brings multiple expert opinion together to do the estimating
• it creates dialog between the experts, and experts have to justify their estimates among other experts
• like in my Lamborghini Huracan estimation example from last Friday, averaging estimates leads to better results than just selecting single estimates

All this got me thinking:  Why can’t Planning Poker be used for non-agile projects?  Why can’t traditional/waterfall projects use the same kind of estimation approach?  Why can’t a group of developers examine specific coding modules — web pages and web functions, or back-end database procedures — and use Planning Poker to estimate?

The few reasons I can think of why NOT to use Planning Poker for any type of project is that it would cause a cultural shift that some people wouldn’t like, and it would take more time to do the estimation (but, on the upside, more accurate estimates would ensue, and those estimates would be peer-reviewed in a well-functioning team).  But unless the teaming is good, Planning Poker would be a near-fruitless exercise — yet that’s true irrespective of the kind of project methodology that the project team uses.