Thursday, September 20, 2012

Create a burn down chart using Excel in less than 5 min

Burn down chart prepared using an excel gives you distinct advantages to many fancy tools available. It's easy to prepare, understand. Also it doesn't consume any maintenance time from the team.

In case you are wondering how to prepare it, here is the simple three steps to prepare burn down chart using an excel.

Step 1 
Open your excel sheet and add the headers for the story, tasks and days in your sprint. I have taken sprint length as 10 days. You can add the actual date instead of mentioning as Day 1 to Day 10. I prefer starting with Day 10 and then to count down to the D-Day (helps in stand up meeting).

Fill in the project name, stories and task details.

(Click on the image to enlarge)

For this example, we are considering a team of 2 members with ideal working hours of 5 hours per day and working in a 10 day sprint.

Please note the two important rows 14 and 15 which is going to provide the data point for your burn down chart. For the time being, let's ignore them.

Step 2
It's now time to set the excel formula for Ideal work hours. 
(Click on the image to enlarge)

For easiness of understanding, I have filled the estimates for each task. Now the cell D15 should be capturing the total estimated ideal work hours where I have added the formula  to sum the cell from D2:D14. 

Now let's define the ideal burn downs in terms of effort hours in each day. What I have done is defining the formula
(Remaining burn down hours in day -1) - (total estimated burn down efforts for the sprint/no of days in sprint) 
in E15 and expanded that formula in the same row till Day 1 from day 10. As you can see at the end of day 1 your burn down efforts become zero.
(Click on the image to enlarge)

Now let's define the formula for burn down efforts remaining on each day. At the beginning of the sprint, actual efforts remaining is same same the estimated efforts remaining which in our case in 90 hours (D16=D15). 

Now on each day you are going to fill in efforts remaining for each task. Please note that you are going to fill how much efforts is remaining to complete the task. It's not how much effort you already put in. For the tasks  team has not worked on, you can copy the value of the cell from the previous day.

So the actual efforts remaining on each day is going to be the sum of the efforts remaining for the tasks. In our example, that is going to be the sum of cell from E2 to E14 for Day 10. You can expand the formula for the remaining days.

(Click on the image to enlarge)

Please note each day we are filling remaining efforts required to complete the task. It's possible that the remaining efforts required will go up. In the above example at Day9 the remaining efforts has gone up to 3 from 3 for Task 1.2 as the person working on that uncovered a new issue. This is not possible if you are focusing on capturing how much effort you have already put in.

Step 3
Now is the easier part. Select the data (don't select the headers, it's easier to do it after you have created the template. Only select the part highlighted in red) against the two rows (Ideal - remaining effort in ideal work hours and Actual - remaining effort in ideal work hours), go to insert in the menu options and select the chart. I prefer line charts and I have used the same for the example. Now once the graph is created, you can add titles for the Axis as well as for the lines. 

(Click on the image to enlarge)

Now you can go ahead and start using using you burn down chart.
[You can download a sample template (file -> download ) for creating a burn down chart in excel from

If you find this article useful, please share it using the social media tools shown below

You might also like..

Related Posts Plugin for WordPress, Blogger...