fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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 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 alternative 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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

28 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. 😀

    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.

  22. Jeetu says:

    I have one query that in that table how we calculate planned and actual balance in excel with formula?

  23. […] Learn more: Burn down Chart for project management […]

Leave a Reply