Use burn down Charts in your project management reports [bonus post]
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:
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)
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.
There is no step 5, just go burn down some work.
Download the excel burn down chart template
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.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Generating invoice numbers using excel [reader questions]||Sumif with multiple conditions [quick tip] »|