• Home
• News
• Resources
• Books
• Glossary
• Archives
• Blog
• About
• Contact

A Burn-Rate Based Estimation Tool

April 18, 2007 @ LeadingAnswers: Leadership and Agile Project Management Blog from Mike Griffiths

(+1 rating, 1 votes)

 Loading ...

In my last post I suggested spending less time worrying about project metrics and more time on stakeholder concerns, so I thought a tool to simplify project tracking might be useful.

Having completed project estimating with the team, it is often necessary to convert effort estimates into cost estimates based on likely utilizations and then track actual hours billed against these projections. Today’s download is a simple burn-rate estimation tool that produces S-Curve graphs and let you track actual spend against burn-rate projections and planned budgets.

You can download the spreadsheet here:

Download estimation_and_budget_tracking.xls

The spreadsheet lets you enter likely utilizations for your team members as percentages of full time work.

Here we see a list of team role and names in column A, their hourly rates in column B and project utilizations throughout the project in columns D – O. Just overwrite them with your own figures to use it yourself. Row 12 calculates the monthly costs based on the projected utilization, hourly rate and hours per day field (B13). So we are projecting we will need Mark 20% of full time in January and 90% in February. Row 13 sums a cumulative spend figure month on month. This data is then also presented as an S-Curve graph to the right of the Baseline estimate section.

Then as project spend data comes in, you can track the actuals to planned expenditures using the “Actual Hours Billed” portion of the spreadsheet. While we estimate future usage as percentages of full time work, worked billed usually arrives as hours, so the actual tracking portion of the spreadsheet takes “hours per period” as its input.

Here we can see actual data has been entered for Jan-May. The originally planned budget expenditure for the project is depicted as the blue line S-curve, the overall budget is shown as the upper red line, and the spend-to-date is the green “Actual” figure.

Limitations
To simplify the spreadsheet I removed other costs you are likely to have on projects such as software licenses, hardware, travel expenses, etc, but these are easy to add back in if required. The data range for the green “Actual” spend figures needs updating each time you enter a new month’s worth of data.

Summary
If you do not have such tools in place I hope you find this useful. The idea is to automate the necessary, but mundane tasks of “riding the project bike” as much as possible so you can focus your time and attention on looking out for the rocks and potholes (issues and risks) down the road. 

Comments

Copyright © 2007 PMToolbox :: Tools and Resources for Project Management