Hi, I’m Heather O’Cull, another program manager on the Project team. In case you’re wondering what happened to Treb, he is still here but has decided to pass the blog on to give more variety to it. Big thanks to Treb for all the great posts and hopefully he’ll still be up for doing some guest posts.
I work more on the client and client reporting so I’ll be giving the blog more of a spin in that direction. If there is anything you would like to see posts about, please let me know.
At the Project Conference I presented on client reporting and promised to post instructions on creating a burndown report through Visual Reports. Something like:
And here are the steps…
How to Create a Burndown Report:
1. Have the project you want to report on open.
1. Go to Reports – Visual Reports
2. Select New Template, Excel, Assignment Usage, and click OK.
Your report is now being created in Excel. Switch over to Excel.
3. Add Time Weekly Calendar to the Row Labels section.
4. Check Cumulative Work, Actual Work and Baseline Work.
5. Move Values to the Column Labels box. Your fields should be setup like this:
6. Expand the time dimension out to the weekly level (you can really choose to any time level you’d like).
7. Make sure subtotals aren’t showing (to remove in Excel 2007 go to the Design tab, Subtotals dropdown).
8. In the cell to the right of Baseline Work, type Remaining Actual Work, to the right of that type Remaining Planned Work, then Cumulative Actual Work, and Cumulative Baseline Work so you have the picture below. You now need to calculate all of these values.
10. For Cumulative Baseline Work, in J3 type =Sum($F$3:F3) and fill down the column for the number of weeks in your Project. You nave now calculated Cumulative Baseline Work for your project.
11. For Cumulative Actual Work, in I3 type =Sum($E$3:E3) and fill down the column like you did in the last step.
12. For Remaining Planned Work, in H3 type =(x-J3) where x is the total for the Cumulative Baseline Work column. Fill down the column.
13. For Remaining Actual Work, in G3 type =(y-I3) where y is the total from the Cumulative Actual Work column. Fill down the column. You’ve now calculate all the data that you need. If I switch to show formulas, you should have something that looks like this:
14. You’re almost there. You now just need to graph your data. To do this, insert a column to the left of remaining actual work. This is the week column. Now paste the week numbers there so you’ll have them in your graph.
15. Now just select the week column you just added, remaining actual work, remaining planned work, and choose to graph them as a line graph. You should have something like the picture at the top of the entry.
To make this look even better you can draw a status line to help demonstrate where you are in the plan. I also prefer to delete the values in Remaining Actual Work that are in the future to make the graph more compelling.