Over the last few posts, I have been covering the reporting challenges for a PMO when the Agile project methodology is being used.  One of the popular ways of representing progress being the use of project burn-down charts.

 

In this post I want to provide a simple guide to how you can build a project burn-down chart using Excel.  In order to illustrate this I am going to use the example of an Agile Sprint.

 

Sprint Example

  • 5 day duration
  • 8 tasks
  • 80 available hours

Step 1 – Create Data Table

 

All reporting requires an input, typically data.  This date must be stored in a structured way so that it can be efficiently processed and used.

The date table for the sprint needs to contain the following:

  • Daily hours used by task
  • Daily total hours used
  • Baseline hours by day (the available hours known as ideal hours)

Example project burn-down chart data tableStep 2 – Define Tasks

Each task that is to be included in the sprint should be entered into the table.  It is important to use consistent naming so that each task is clear and links back to the item in the Product Backlog (this contains all of the features / requirements of the project).  Note: there can be many items in the Sprint that link to a single item in the Product Backlog.  This is because the tasks in the sprint represent the decomposition of the item in the Product backlog to allow the tasks to be allocated.

 

In the example I have simply entered them as Task 1 to Task 8.

Step 3 – Enter Time Estimate for Task

 

As part of the sprint planning, the team should have estimated how much each task will take.  The sprint is then adjusted so that the total effort estimated for the sprint is equal to the sprint budget (this is the available hours taking number of resources multiplied by available working hours).

 

The estimate for each task is then captured in the table.  This is important as it is used to create the baseline.

Capture estimated hours for each taskStep 4 – Create Estimated Effort

 

This will represent the ideal baseline for using the available hours over the sprint.  So in the simplest for this is the available hours divided by number of days.  In this example, 80 hours over 5 days equating to 16 hours a day.

Example of baseline

In order to create the project burn-down chart, the data needs to be captured as a daily running total starting with 80 hours than 64 hours left at end of day 1 (80 – 16), 48 hours left at end of day 2, etc.

 

Step 5 – Track Daily Progress

 

The daily progress is then captured in the table against each task.  It is important to remember that the value captured for each day is the estimated effort to complete the task, not the actual effort.

Example of project burn-down data table with sample dataStep 6 – Actual Effort

 

The total remaining effort needs to be captured at the end of each day.  This is the total (sum) of all of the estimated time remaining at the end of each day.

Example of project burn-down chart actual effort remainingStep 7 – Create Project Burn-Down Chart

 

When the data is available, the project burn-down chart can be created.  This is relatively simple using the line chart option available within Excel.

Highlight the summary table that contains the daily total for baseline effort and estimated effort.  You should also capture the heading of time period (Day 0, Day 1, etc).

 

From the insert option in Excel select the Bar Chart option and this will create a burn-down chart for your data.

Example project burn-down chart

Then if you update any data point in the data table, the graph will update.

 

You can format axis, legend, colours, etc to suit your requirement.

 

Congratulation’s you have a project burn-down chart.

 

Other Types Burn-Down Chart

 

The same principle applies if you want to track other data types in a burn-down chart.  For example, for a milestone burn-down chart the baseline will be created by the number of milestones to be achieved at each time segment.  The progress will be captured as the number of milestones remaining at the same time segment.

 

Free Project-Burn-Down Chart Template Download

 

To compliment this guide, you can download the supporting template by using the link below.  It will also save you time if you do want to create a burn-down chart.

Click Here – Project Burn Down Chart Template Download

Summary

 

It is very simple to create a project burn-down chart as long as you know what data you are tracking.  The result is very powerful.

 

I trust you have found the guide and template helpful.  Please share with friends and colleagues who may find this information useful.

 

If you are interested in fully integrated frameworks and templates with guides and training tutorials, please take a moment to visit the PM Member’s Area.

Agile Burndown Chart Presentation

Agile Template Framework