Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. If you’re familiar with Agile Project Management, then you’ll know that these charts are useful for monitoring the progress of a project against expectations. While the terminology is specific to Agile Project Management, the concept is relevant to managing any project and can be applied to measures of time, number of tasks, costs etc.
Watch the Video
Download Workbook
Enter your email address below to download the sample workbook.
Burn Down Charts Explained
A Burn Down chart tracks the total expected effort (think tasks, milestones, time, costs), vs the actual effort, over time. For example, let’s say we have 60 hours of effort to complete 10 tasks in a 10 workday period. If we work at a steady pace, we’d use 6 hours per workday.
It’s called a burn down chart because it starts with the total effort available and over time that amount reduces until all effort is used up.
In the chart below the expected effort is the ‘ideal burn down’ line and the actual effort is the ‘remaining effort’ line.
If the remaining effort line is above the ideal burn down line, the project is falling behind. Conversely, if the remaining effort line is below the ideal burn down line, the project is ahead. In the example above the team were initially behind, but by day 4 they had pulled ahead only to fall behind again on day 6. However, they managed to finish in line with expectations.
This isn’t always the case and these charts are used to enable the team to adjust their original targets and communicate with stakeholders early on of any changes to the original plan. For example, in the chart below we can see that after 3 days of work the average hours is closer to 4 per day instead of 6, and if we extrapolate that out the project will take 15 days to complete, rather than the planned 10. At this point we can either add resources to bring the project back in line with the time deadline but at an increased cost, reduce the scope of the project to bring it in on time and budget, or accept it will take longer and cost more.
Building Excel Burn Down Charts
In the table below we have 10 tasks and in column C we have the estimated hours each task will take. As each day passes the actual hours of work completed are entered in columns D through M.
Row 15 – Remaining Effort: Each day the cumulative hours of work completed are subtracted from the total estimated hours to show how many hours are remaining. The formula in cell D15 is:
=$C$15-SUM($D$5:D14)
Copied across, the sum increments by one column at a time to return the cumulative effort, which is subtracted from the total estimated hours.
Row 16 Ideal Burn Down: is the total hours evenly distributed over the 10 days on the assumption that the same amount of work will be completed each day. The formula in cell D16 is:
=$C$15-$C$15/10*COUNT($D$15:D15)
Copied across, the count increments by one column at a time to increase the hours subtracted from the total estimated hours in cell C15.
In the image below you can see the chart is simply plotting rows 15 and 16 in a line chart:
Burn Up Charts Explained
Burn up charts plot the ideal effort at a constant rate and display the cumulative actual effort over time. Their purpose is the same i.e. to identify if a project is off track and unlikely to meet its goals. Under Agile Project Management, burn up charts typically quantify progress in points, rather than time, hence the terminology in the chart below. However, if you’re not bound by Agile, you can use them for costs, hours, milestones etc.
In the chart above, at day 5 the scope of the project increased. Hence the jump in the Ideal Effort line from 60 to 70. This helps give context to the project to the stakeholders reading the chart.
Like the burn down chart, the burn up chart also plots the last two rows of data, with the cumulative effort simply summing the effort to date for each day with a sum formula in cell D15:
=SUM($D$5:D14)
Which is then copied across to increment by one day at a time.
Like burn down charts, with burn up charts you can forecast a few days into the work to see if you’re on target and make changes accordingly.
I hope you find these charts useful. If you’d like more Project Management ideas, please see my Excel Project Management Dashboard tutorial.
