Time and again I find myself on projects where the effort needed to deliver on the requirements are far removed from the project budget. Tasks are added to the scope without consideration to the real effort they will require. Project overhead related to testing, deploying, clarifying requirements, and simply managing the tasks are all forgotten.
There are tools you can invest in to ensure projects are managed properly, and an experienced Project Manager will ensure no details are missed. A lot of the time however you need something quicker and easier to use. I built this little spreadsheet to deal with those cases and share it with you in hopes you find it equally helpful.
The first tab [Features] is your start point. List all your high level user features here. If you have a work task breakdown you can enter the individual tasks instead, the level of detail is only restricted by the information you have on hand and your preference. I tend to record use cases or user stories from the business perspective since at the stage I’m using this tool I’m typically scoping out the project. The columns for Domain, Description, and Dependencies are all optional, adjust them as you like. Often I’ll continue tracking the project tasks in this spreadsheet and will rename those columns to track Assignee & Status.
Estimating the effort for each task is a guessing game. If it’s feasible to engage a larger group in a Planning Poker session I’ll do that. It’s not only a great way to get a more complete picture of the effort for some tasks but provides an excellent opportunity to engage the development team into the project. Whatever your process, what is essential is to capture both the Optimistic and the Pessimistic estimates. If the effort (in hours) is an exact number enter the same value in both columns. The calculated Most Likely value in this tool is based on an adjustable percentage which at its default (70%) slightly skews towards the Pessimistic level.
I’ve also included a couple calculated columns which apply the PERT formula ((O + 4M + P)/ 6), and the standard deviation ((P – O) / 6) in the estimate range. The former is a familiar approach which actually expects the Most Likely value to have been manually identified. In the event you do identify your own Most Likely value then the PERT formula is available to factor in those estimate ranges for you. The standard deviation isn’t formatted, however the greater the number the greater the uncertainty in the supplied estimate.
Identify the priority for each task. I find the MoSCoW Methodology works great, and the spreadsheet will breakdown all your effort and costs against those 4 priorities (Must Have, Should Have, Could Have and Would Like). Generally I’ll plan a project around delivering the Must & Should Haves. Everything else is either “time allowing” or just a parking lot for a future project.
Burndown charts are often based on the number of features or (story points) completed in an iteration. I’ve simplified this to calculate velocity based on any actual hours on completed tasks. As the project starts to unfold this can be a very helpful gauge for whether you’ll finish ahead or behind schedule. It can also provide a quick ‘n dirty average when looking at any new scope creep items that might be discussed. Not that latter needs to be taken with a pretty massive grain of salt, however in my experience with each respective client project there generally tends to be some consistency in the form and scale of features and units of work (but quote that average with caution).
The second tab [Metrics] provides a summary of how all the hours flush out for the features under each priority, with a few automatic refinements. The numbers are all based on the Total Most Likely values.
Rows 11 through 14 factor in the overhead for ongoing Business Analysis, Project Management, Testing & Bug Fixing, and Training & Deployment. These percentages are also adjustable, however I default them to 5%, 20%, 30% and 5%. You can optionally capture that effort as we did with features. I like to use a percentage to keep it relative to the effort.
The impact of staffing is intended to capture the increasing overhead as more people are added to a project. Although more people will help to split the work it will increase the communications and coordination necessary on the project.
Rows 19 & 20 provide a sense of the project duration in days and weeks. Instead of Perfect Engineering Day and 5 day weeks I like to plan around my team having 6 productive hours a day, and with respect to stats, vacation and sick days about 4 days per week. You can adjust all those to suit your comfort level, but I like to make sure I’m producing an estimate for my client that I can rely on with some comfort.
The third tab [Costs] provides a breakdown of the costs for the various project team members. The proportions for testing, business analysis, project management, and deployment are tied to the hours from rows 11 through 14 on the [Metrics] tab. Their division into separate roles is defined in the [Refs] tab.
The actual role titles and their hours are adjustable, I explain how the percentages on effort are split later on. I recognize I’m not capturing the full breadth of different roles and contributions that you might need in your project team. UX designers for example I might here merge with the role of Business Analysts or Developers. However this tool is meant to be quick ‘n easy. You can repurpose any roles as you see fit, or extend the worksheet to introduce new roles if that works better for you.
The fourth and final tab [Ref] presents all the adjustable variables applied in the previous three tabs. When planning a project adjusting these to reflect your situation, team, and client would be necessary.
- Most Likely Variance – As described before I generally skew the average between Optimistic and Pessimistic closer to the Pessimistic side. This tends to be more accurate.
- Project Team Size – This value is intended to be adjusted to assess the impact of increasing or decreasing the number of people involved. The larger the team the greater the effort, but the lower the calendar duration.
- Staffing Increment – This value indicates the boundary points where I’ll apply an incremental impact to effort as the team size increases.
- Staffing Increment Impact – The impact to overall effort whenever the project team size grows by the defined increment. This is a generalization, however you can adjust it as you see fit.
- Dev Hours per Day – The number of actual development hours that I think I can reasonably expect on average for each calendar day.
- Dev Days per Week – The number of days I think I can safely plan for per week. You can adjust this as you like, but if you’re committing to a calendar estimate to the client I think it’s better to err on the side of caution rather than have to defend a missed deadline.
- Business Analysis – The amount of additional ongoing requirements gathering that will take place during the development.
- Project Mgmt Cost – The amount of time necessary to manage the project, track time, manage risks, prepare invoices, etc..
- Testing – The percentage of overhead to cover unit testing, quality assurance and bug fixing.
- Prod Deployment – The percentage of time related to deploying the solution to the production environment and to the client to use.
This final section needs a bit more explanation since its application is not as obvious. As mentioned earlier the actual role titles can be changed. Each section captures the roles involved in the development effort, plus the overhead for testing, project management, and deployment. Against each role are sample rates per hour that are used in calculating the project costs. The percentage split (column D) reflect how much of the effort will be split between those various roles. Each section starts with 100%, the more senior roles reduce that amount and the blue box reflects whatever remains. In the event the project team is 1 person the developer is assumed a Senior Developer so those splits will be ignored.
So for example if you decided your project team would include a Senior Tester for about 20% of the testing effort, then the remaining 80% would be inherited by the Tester. If testing was looking to be about 200 hours, then the hours & costs for the Senior Tester would be calculated to be 40 hours, and the remaining 160 hours would fall to the regular Tester(s). You can play around with those values to see how this works, its much less complicated than it sounds.
A couple of caveats are necessary. I have personally found having this spreadsheet at my disposal to be very useful and reasonably accurate. However there are lots of generalizations being made in the logic and calculations that it must of course be used with discretion. There are lots of alternative options for estimating effort and costs that will provide a much more accurate project plan for you. However more often than not I have still found it necessary to have something quick ‘n dirty on hand. At the very least this tool has allowed me to produce a project effort estimate with minimal effort, and prompted excellent dialog with the client on priorities and project overhead.
Enjoy! If you make awesome improvements please share what you produce with me.
p.s. You can unprotect any sheet, the password is blank.