Use burn down Charts in your project management reports [bonus post]

Posted on July 21st, 2009 in Charts and Graphs - 26 comments

This is a bonus post in the project management using excel series.

Gantt charts are very good to understand a project progress and status. But they are heavy on planning side. They give little insight in to what is happening. A burn down chart on the other hand is good for understanding the project progress and how deliverables are coming along. According to Wikipedia,

A burn down chart is graphical representation of work left to do versus time. The outstanding work (or backlog) is often on the vertical axis, with time along the horizontal. That is, it is a run chart of outstanding work. It is useful for predicting when all of the work will be completed.

An Example Burn Down Chart:

Burn Down Charts show project progress and give an idea of when we can complete the delivery

Making a burn down chart in excel

Step 1: Arrange the data for making a burn down chart

To make a burn down chart, you need to have 2 pieces of data. The schedule of actual and planned burn downs. As with most of the charts, we need to massage the data. I am showing the 3 additional columns that I have calculated to make the burn down chart. You can guess what the formulas are. (Hint: there is a NA() too)
data for generating a burn down chart

Step 2: Make a good old line chart

Just select the Balance Planned and Balance Actual series and create a line chart. Use the first column (days) in the above table for x-axis labels.

Step 3: Add the daily completed values to burn down chart

Select the “daily completed” column and add it to the burn down chart. Once added, change the chart type for this series to bar chart (read how you can combine 2 different chart types in one)

Step 4: Adjust formatting and colors

Remove or set grid lines as you may want. Adjust colors and add legend if needed.

Step 5:

There is no step 5, just go burn down some work.

Download the excel burn down chart template

Click here [.zip version here] to download the excel burn down chart template. Use it in your latest project status report and tell me what your team thinks about it.

Download 24 Project Management Templates for Excel

What Next?

This is a bonus installment to the project management using excel series. We will revisit the burn down charts during part 6 of the series – Project Status Reporting Dashboards. Meanwhile, make sure you have read the remaining parts of the series:

Preparing & tracking a project plan using Gantt Charts
Team To Do Lists – Project Tracking Tools
Project Status Reporting – Create a Timeline to display milestones
Time sheets and Resource management
Issue Trackers & Risk Management
Project Status Reporting – Dashboard

Also check out the budget vs. actual charting alternatives post to get more ideas.

Where would you use burn down charts?

We have used burn down charts in a recent presentation to client and they loved the way it was able to tell them the story about status of Phase 1 of the project. We needed to deliver 340 items in that phase and we have finished 45 when we made the presentation. My client could easily understand where the project is heading and what is causing the delays (of course by asking questions).

What do you think about burn down charts? Where will you use it? Tell me about it using comments.

Resources for Project Managers

Check out my Project Management using Excel page for more resources and helpful information on project management.

Project Management Templates for Excel

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

26 Responses to “Use burn down Charts in your project management reports [bonus post]”

  1. there seems to be a problem downloading the files for this example… this looks like a valuable tool

  2. Chandoo says:

    @Michael: Often some offices and networks block skydrive. Can you try from some other connection and let me know if you still face the problem?

  3. Pete says:

    Unable to open the file.

  4. Michael Pierce says:

    I think it’s an interesting view and relatively simple to generate. It can give a quick view of how your run rate is progressing against plan. The same can be done with the hours expended on a project as well.

    Your burn down approach is most valuable if all of your deliverables are relatively equal in size/complexity. In your example chart, although you’re currently behind, it looks as though you’ll be catching up quickly with the recent acceleration in delivery. If the delay was caused by tackling the larger/more complex deliverables first, I’ll have a lot of faith that you can catch up to plan quickly. On the other hand, if you’ve only completed small/simple deliverables, then I doubt you’re going to get back on schedule.

    But that gets in to more complicated earned value analysis…and the whole point of this was to do something quick and easy. I think the payback/effort is well worth it in this case and is a good balance to the hours being charged to your project against deliverables being produced.

    One final comment…did you consider putting the Daily Completed bar chart on a secondary axis? Since daily volume is plotted against the total volume, it’s always going to be difficult to see low volume days.

  5. Michael Pierce says:

    One other thought. If I were your customer, I’d be asking for a revised plan based on actuals to date to understand whether you think you’re going to get back on track or not. To do that, you’d need another column to plot. The original plan could be captured in a new column (“Baseline”) and the planned column could be updated with the new forecast.

  6. [...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Show Project Milestones in a Time Line Chart Excel Timesheets and Resource management Part 5: Tracking issues and risks [upcoming] Part 6: Project Status Reporting – Dashboard [upcoming] Bonus Post: Using Burn Down Charts to Understand Project Progress [...]

  7. Chandoo says:

    @Michael: I posted a reply to it a while back, strangely I find it missing…

    I have considered the secondary axis thing. Then all the issues of axis scale etc. seemed like a headache. Assuming the weekly productivity is prettymuch same, then excel quickly shows very tall bars (and adjusts the max accordingly). This creates an annoying effect. But may be for a given project we can find the usual pattern and set the axis max accordingly.

    “Your burn down approach is most valuable if all of your deliverables are relatively equal in size/complexity. ”

    I agree. It is better to chunk work in to meaningful portions and then make the chart. If the portions (or deliverables) are not equal, we can consider adding one more column where we can compute burn down units based on the weightage of individual items.

    Usually burn down charts work well for function point based estimate. The more complicated a function or deliverable is, the more function points it will have.

    “One other thought. If I were your customer, I’d be asking for a revised plan based on actuals to date to understand whether you think you’re going to get back on track or not. To do that, you’d need another column to plot. The original plan could be captured in a new column (”Baseline”) and the planned column could be updated with the new forecast.”

    Hmm… I thought Planned is supposed to convey that. If there is more than one version of plan, it is likely that the manager is interested in the deviation wrt latest plan. Everything else is sunk cost. :D

    That said, we can easily add one more line to this chart with “original plan” values and color it differently.

  8. [...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Time sheets and Resource management Issue Trackers & Risk Management Part 6: Project Status Reporting – Dashboard [upcoming] Bonus Post: Using Burn Down Charts to Understand Project Progress [...]

  9. [...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Part 3: Project Status Reporting – Create a Timeline to display milestones Time sheets and Resource management Issue Trackers & Risk Management Project Status Reporting – Dashboard Bonus Post: Using Burn Down Charts to Understand Project Progress [...]

  10. [...] Preparing & tracking a project plan using Gantt Charts Part 2: Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Time sheets and Resource management Issue Trackers & Risk Management Project Status Reporting – Dashboard Bonus Post: Using Burn Down Charts to Understand Project Progress [...]

  11. [...] Part1: Preparing & tracking a project plan using Gantt Charts Part2: Team To Do Lists – Project Tracking Tools Part3: Project Status Reporting – Create a Timeline to display milestones Part4: Time sheets and Resource management Part5: Issue Trackers & Risk Management Part6: Project Status Reporting – Project Management Dashboard Part7: Using Burn Down Charts to Un… [...]

  12. [...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Time sheets and Resource management Part 5: Issue Trackers & Risk Management Project Status Reporting – Dashboard Bonus Post: Using Burn Down Charts to Understand Project Progress [...]

  13. Sally Kennerson says:

    I am confused… I just ordered the dashboard template and the deliverables are in number of days but everything else is in number of weeks… what am I missing?

  14. Chandoo says:

    @Sally.. Thanks for getting a copy of this. You can change the column title to weeks and everything should be alright.

  15. Fred says:

    Why do you have to login to get the Excel tool? I Think he is phishing. People like him give the web a bad name!

  16. Fred says:

    Why do you have to login to Windows Live to get the Excel tool? I was already signed in in another window and his link is still is asking for my password to Windows Live. Think he is phishing. People like him give the web a bad name!

  17. Chandoo says:

    @Fred… It is a skydrive limitation. They want you to login to Windows Live before giving access to the files I have uploaded. If you dont want it, do not download.

    Why do you need to call me an idiot. I never asked you to share your login details with me.

  18. Dorian says:

    Is there a way that I can make the burndown chart dynamically adjust the length of the x axis (number of days) based on when the planned task balance in the fourth column reaches 0? I considered the offset function as outlined here: http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/#BasicRange

    But, I am not entirely sure how to properly implement it based on the given example. I am also not sure if it will work given that the actual balance and the daily completed (4th and 5th) columns will return #N/A values when the planned task balance reaches zero.

  19. Petre says:

    Why not have one online and shared for free like http://www.burndown-charts.com does ??

  20. Lisa says:

    How do you come up with the “Planned” numbers per day?

  21. RR says:

    Do you have explanations for the fields and how this tool should function with the multiple worksheets. I am a novice looking for a great project management tool and not quite sure where to start.

Leave a Reply