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”!