Accurate project estimating is really more science than art. The reason estimates are often incorrect is because few people take the necessary time to apply science to the problem.
Accurate estimates depend upon several key ingredients:
- Historical precedence
- Time tracking
- Detailed scope of work
- Awareness of your biases and limitations
I know that last one starts to sound a little "artsy," but the biases and limitations I’m talking about are generally observable and consistent behaviors. They can be measured and accounted for in your estimating, as I describe in this article.
Historical Precedence
Historical precedence relates to the fact that the best way to estimate how long a project will take is to know how long it took you the last time you did it. It is difficult to accurately estimate how long a task takes you to do, if you have never done anything like it before.
However, if you keep track of how well you estimate work you’ve never done before, you will probably find a consistent pattern that you can apply in future estimates. For example, you may find that you underestimate the time it will take you to do new tasks by 50%. (In other words, it takes you twice as long as you expected and estimated.) Knowing your tendency to underestimate in advance means you can adjust your future estimates for new tasks by simply doubling them.
Time Tracking
If you don’t track your time and compare your actual hours to your estimated hours, it is virtually impossible to improve your estimates. Having a good time tracking tool is vital. Without the reality check of actual hours, you tend to use the same estimate on your next project as the one you came up with last time, regardless of how inaccurate it turned out to be.
Determining a "detailed scope of work" means you have to get as complete a picture as possible of what’s involved in designing, developing, and delivering the project. The trick here is to keep drilling down until you have a set of clearly defined tasks with readily estimated action items under each task.
At this point, most estimating breaks down. The tendency is to just throw out a number that sounds good without looking deeper into the problem. You need to keep asking, "What will it take to do that?" until you get down to a series of activities you can clearly envision and readily estimate. This process is eye opening. You’ll be amazed at how much more accurate your estimating becomes.
Biases and Limitations
Finally, you need to deal with your biases and limitations. You already got a glimpse of what I’m talking about in my comments on estimating tasks you’ve never done before. Everybody is different when it comes to enthusiasm, optimism, and realism. Those aspects of your personality play into your thinking when you put a time on how long it will take to get something done. Here’s the good news: you are probably quite consistent about it.
Over time, you can compare your estimated hours to your actual hours and get a feel for how realistic you are in your estimating. Time tracking comes into play here as well. Using your tracking information, you can readily determine your average margin of error. In future estimates, you adjust your figures by that empirically determined "confidence factor" and start to produce startlingly accurate estimates.
Of course, the term "confidence factor" sounds like a euphemism for "fudge factor," which seems like a highly unscientific way to go about things. However, I’ve found that it is generally easier to adjust the estimate than it is to adjust the estimator. Yes, you probably will get better at producing raw estimates as you gain more experience, but the confidence factor is helpful until that happy day arrives.
Create an Estimating Worksheet
You can easily create a simple worksheet in Excel that uses the estimating principles I just described. The more historical information you have to work with, the easier estimating becomes. However, this worksheet helps you improve your project estimates even if you don’t have historical data.
For illustration purposes, I created a simple example (see below). The sample worksheet estimates how long it would take to write this article.
The first thing you need to do is break down your project into tasks. A task is a single business requirement or capability you want to include in the project. My example breaks the article down into two tasks: "Write Article" and "Create Sample Worksheet."
You then break down each task into action items. An action item is a single step or "to do" item that must be performed to complete a task. The example breaks both tasks down into three action items each.
The idea is to break down the scope of work until you can easily apply a time estimate to the action items. If you find that your action items are too broad to readily estimate, make them tasks, and keep drilling down. In my experience, action items should represent 4 or fewer hours of work. The larger the block of work, the more difficult it is to estimate accurately.
For each task, create a subtotal of the estimated hours. You can use a simple SUM formula to do this. For example, the sum formula that calculates the total hours for the Write Article task is this:
=SUM(C6:C8)
Next, create a Total Estimated Hours cell in the worksheet that summarizes all of the task totals. In the example, that formula is this:
=SUM(D4:D17)
The confidence factor is a bit trickier.
The confidence factor represents how confident you feel that your project estimate is complete and that your estimated hours for your action items are accurate. The confidence factor is a percentage that should range from 1% to 100%. To keep the formulas simple, I express the percentage as a decimal (.01 to 1.0).
If you have previously done projects that are similar to the one you are estimating now, you might have a high confidence factor because you have a good idea of how long it actually will take. If you tend to be very accurate at estimating hours, you might have a high confidence factor. The more doubts you have about the project scope of work or your estimating abilities, the lower you should set the confidence factor.
As an example, I tend to start with a confidence factor of 75% and adjust up or down from there because I know that I’m generally too optimistic when I estimate how long something will take me to do.
To adjust your estimate with the confidence factor, you add the difference between 100% and your confidence factor. For example, if you are 75% confident in your estimate, you adjust the estimate up by 25% (100% – 75%). The formula for the adjustment looks like this:
=D18+((1-D19)*D18)
D18 is the raw estimate, and D19 is the confidence factor expressed as a decimal. Here’s what this formula does for you:
- It first calculates an adjustment multiplier by subtracting the confidence factor in D19 from 1.
- It multiplies the estimate in D18 by the adjustment multiplier, producing the adjustment amount.
- It finally adds the adjustment amount to the estimate to produce an adjusted estimate.
As you just saw, it doesn’t take much formula magic to assemble a quick estimate in Excel.
Conclusion
With a few simple formulas, you can easily create a functional estimating tool in Excel that takes your estimating confidence into account. The key to figuring out the right confidence factor is to track the time you spend on the project and compare your actual hours to your estimated hours. Fairly quickly, you will discover how to set your confidence factor and produce surprisingly accurate estimates.
