A Burn-Rate Based Estimation Tool

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

[Tom Morgan]

As a full time project manager I can really appreciate the power of this tool and thank you for providing a template for us to use.

[Mesfin]

Dear sir,what I need was an S-curve made for road construction project,as you know it has 8 or 9 different construction activity starting from clearing and grubing ,excavation of cut and fill,subgrade formation,subbase layer,and base course layers,finally asphalt wearing course.And each activity will have amount of volume or square meter with the unit rate .Then therewill be a project amount total of 284,290,260Ethiopioan Br.therefore as a general guidance I think I need one curve for planned activities for a total of 42 months and another curve for actual work executed on each month.Also I need a cash flow,therefore please help me in getting the idea.Also I need a soft ware for scheduling and I tried to download the one you have sent but I couldn't to overload even the demo which was meant for two days demo.But if there is a possiblity to buy at your reach please help me to by the program called Goldenseal.
Thankyou,please reply me.

[Richard McBride]

I am doing some research into a definition and formula for "burn rate" with respect to projects and have found two conflicting answers. One states that "burn rate" is equal to the Earned Value calculation for CPI (Cost Performance Index). The other states that "burn rate" is the inverse of CPI. Can you provide your opinion on this?

Thank you.

Richard A. McBride, PMP, MCSE

[Mike Griffiths]

Hi Richard

Thanks for your enquiry. The discrepancies arise from a difference in what is being measured. This post was discussing how we can track budget, and I was calling the rate of budget consumption a “burn rate”. However, it gets confusing because agile project often track Stories remaining via burn down graphs and also Estimated Time remaining via burn down graphs. So depending on what you are measuring your formulae may well be inverted from what you expect.

To tie it back to Earned Value terms: Earned Value (EV) = Features delivered, Schedule Performance Index (SPI) = Completed Features / Planned Features for this time, Cost Performance Index (CPI) = Planned Costs / Actual Costs for this time.

I hope this helps, thanks for reading and commenting
Regards
Mike