Agile
Agile Ideas: Tracking Your Project Burn Rate Using Excel
May 10, 2017 Anne Saulnier

"So how are we doing on hours for this project, anyway?"

 

When your team is burning the midnight oil to meet a tough deadline, are you confident in your ability to quickly and accurately report on your project's burn rate? Whether you are in charge of managing a vendor relationship for your company or with an agency working to stay within-budget for your client, knowing where you stand on hours remaining is mission critical.

At Coria, we track our project hours using a combination of Teamwork.com and a custom application built on top of it to fill in the gaps that make sense for our business. We pull information from Wolfram Alpha and TimeandDate.com to calculate working days and holidays, and generate a spreadsheet template that gives us up-to-the-minute details on where we are compared to where we should be for every active project. 

Project_Burn_Rate.png

This way, whether we are working on a demanding client initiative or an internal skunkworks proof of concept project, we always have our hourly burn rate available at the click of a button.

Even if your internal systems are not yet capable of automatically generating this type of report for you today, you can easily create (or borrow) a template in Microsoft Excel that will make it easy for you to answer the "how are we doing on hours" question with no stress.  


Short on Time?

Download our ready-to-go Excel template for tracking your project's burn rate.

DOWNLOAD TEMPLATE


Here's how to create a spreadsheet to accurately track how you're doing on hours for your project:

Step 1: Fill Your Bucket with Hours

"If you don't know where you are going, any road will get you there." Lewis Carroll

As a first step, you need to be sure to track how many hours are available to you in this project. In our Excel template, we include a place to enter available hours and working days so that we can add in formulas to track Full-Time Equivalents (FTEs) and remaining hours for each project.

KnowYourDestination.png

Step 2: Determine How Many Workdays Are Available

To calculate how many work days there are between your project's start and end dates, use a service like Wolfram Alpha or TimeandDate.com. Enter this into your spreadsheet under the "Working Days" cell so that you can use this number in several calculations.

Step 3: List Out All Available Days

In order to accurately account for weekends, holidays, and available work days for the duration of your project, list out every day in Excel.

ListOutDays.png

We list the calendar date, the day of the week, and the day of the year in our Excel spreadsheet.

Step 4: Mark Your Holidays

Using the output from Wolfram or Time and Date, tag each holiday with the word "Holiday" above it. You can use this string in your Excel formulas to skip adding hours for the days when your team is not scheduled to work.

Step 5: Calculate Your Ideal Burn Rate

We calculate our ideal burn rate using the number of available hours and number of available working days. This determines how many hours should be logged against the project, on average, for each day. The example Excel formula we use is:

=IF(L26="Saturday",0, IF(L26="Sunday",0, IF(L25="Holiday",0,$B$29/$C$29)))

Where:

  • Cell L26 = the cell designated to denote the day of the week that maps to the active date
  • Cell L25 = the cell designated to denote non-working holidays
  • Cell B29 = the number of hours available for your project in the given date range
  • Cell C29 = the number of working days available within your project's start and end date

We call this row, "Ideal Hours."

Step 6: Leave Room to Track Your Actual Burn Rate

Add in a row to enter how many hours you actually log for each day. We title this row "Actual Hours." If you're manually generating your spreadsheet, this row is where you'll enter in the sum of all your time logs for the project your tracking.

Step 7: Determine the Delta

Keep a row with a "Running Tally" to determine where you are trending. For each day of your project, you will have a number of hours you should be targeting to hit if you want to stay exactly on budget using a constant burn rate. Some days you may hit the target exactly, but most likely (if you are tracking actuals), you will be slightly above or below the target.

Here's the example Excel formula to use to calculate your delta between ideal hours consumed so far and actual hours consumed so far:

=SUM($I$14:J14)-SUM($I$15:J15)  

Where:

  • Cell I14 = the cell where you calculate your first ideal number of hours per day
  • Cell J14 = the cell where you are calculating your second ideal number of hours per day
  • Cell I15 = the cell where you are entering your first actual number of hours per day
  • Cell J15 = the cell where you are calculating your second actual number of hours per day 

Tracking a "Running Tally" of whether you're up or down on your burn rate allows you to see exactly where you stand on a given day of the project. It will allow you to answer the question, "So how are we doing on hours?" quickly with responses like, "We are currently over budget by 10 hours because we had to put in some extra hours last week, but we are managing it closely and should be back on track by Wednesday."  


Add Bells and Whistles If Needed

And now, the world is your oyster. Using this available data, add in as many fancy calculations as makes sense for your project. 

As an example, we include conditional formatting in our spreadsheet to call out anytime our burn rate exceeds the number of hours targeted for the project. Depending on your goals, you may add in different formatting rules to call more attention to specific days when you haven't met your targeted number of hours.

You could also add in details on profitability based on bill rate and recurring expenses.

Please reach out and let us know how you're using and modifying this template to help manage your projects.

Happy calculating! 


Short on Time?

Download our ready-to-go Excel template for tracking your project's burn rate.

DOWNLOAD TEMPLATE


Anne Saulnier
Project Manager, Digital Strategist