My work is to build financial reports using mostly Excel spreadsheets. This consists mostly of formulas and a good deal of VBA. Each person is built by a single person. We service many sub-departments under the finance / accounting department and we often have many projects lined-up. I have had a rough time in the last few months because of projects taking longer than expected, then projects overlapping and getting very close to critical deadlines.


Obviously, we try our best to plan our projects so that we have a little bit of room to breathe but I always feel helpless when I have to estimate how long a given project will take me to complete. Are there techniques that can be used to better estimate how long a project can take to complete ?


I am thinking that one way to get a better idea of the length of the project would be to look into our process for getting requirements which could be infinitely better. It would help to get a clearer picture but it would still not give us a measurement. Then I thought that maybe isolating and measuring the average development time of different tasks that need to be done for most reports (e.g. build the queries, write the functions, construct the report, automate the process) would be one way to achieve this but it does not take into account the variability in complexity of those tasks. Some reports have very simple queries while others are extremely complex. Obviously the more complex the query the longer it would take.


Most of the time we have to deal with legacy code / inefficient reports which can cloud the waters even more.


3 Answers 3


>Are there techniques that can be used to better estimate how long a project can take to complete ?

The problems that you are having aren't due to your estimating ability at all. The issue is that your estimates are becoming deadlines.

Repeat after me, and repeat in front of your client. Estimates are just that, estimates.

What happens is that estimates become solid. When you say "It'll probably take 2 weeks", people figure that it will be done and finished in two weeks.

What you need to do is periodically update your estimates and actualities, and effectively communicate them with the rest of the business. If something takes an extra week, you need to push everything back by a week. Either that, or drop something, or re-prioritise.


Establish Due Date Risk It doesn't sound like you're communicating the accuracy (or lack of it) of your estimations nor are you getting complete user expectations. Ask when they need it and why. You tell someone you can finish by Friday and they say, "Great." There is a chance you are off by 1-3 days and what you don't know, is they have to give the report to some regulatory agency on Monday or pay a penalty. This is a very high risk situation. A lesser one may be promising a client they'll get their report on Monday. Nobody want to have to call the client back with bad news.

Estimate in Smaller Pieces With reporting, I find you get better requirements after you the user can see some raw data (Can we add another column?). I'm usually pretty accurate when I can deliver this and they know they need to give me some feedback in a timely manner or the over-all creating of the report is going to get delayed. Also, there may be some very complex features that can be omitted because of time constraints. It's up to the user if they are willing to wait.

Managing How Long it Takes Versus How Long You Have Let's say you accurately estimated a report will take 8 hour and since you received the request first thing this morning, you promise to have it done by the end of the day. The only problem is you cannot commit the full day to this report because something came up. You can keep using your technique and tell the user it will take 10 hours ( +2 hours of "breathing" room) or you can let them know this estimate is contingent on nothing else coming up. When something comes up, just let them know it is going to be delayed.

It is very important to communicate. I recently had a home remodeling project go from a 2-3 day estimate to 5. Nobody bothered to tell me one of the key carpenter's wife was having a baby, so he was going back and forth to the hospital. Software takes such a bad rap for delayed projects, but I'm not convinced other industries are much better.


This looks like a "critical path" problem. That is, you have a major task that subsumes a bunch of subtasks.

The first thing to do is to get a handle on the subtasks. You need to start by estimating the time periods for each subtask, then "add them up" (end-to-end). Hopefully the sum will be less than the deadline date for the total task.

Keep in mind that some subtasks can be done in "parallel." That is to say, there are some subtasks that aren't as critical as others, because there is some "slack" between there completion time, and the overall completion time of some other, more critical task. If some subtask's "slack time" becomes zero (or less), it becomes the new "critical" task.

Hopefully, there will be some "slack" between the end-to-end sum of the subtasks, and the main task. This will give you some spare "emergency" time in case anything goes wrong. If there is no slack, you're on the critical path and must get everything done on "schedule" or else.

Maybe there is a way to "crash" some critical subtask. Like working evenings and/or weekends.

Not the answer you're looking for? Browse other questions tagged or ask your own question.