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.