Simple burn-up charts with a spreadsheet

[Update 16 June 2015: See my later article for a much easier version of this in Google Sheets.]

I’ve long wanted the ability to create burn-up charts for very, very simple projects, where a project tracking tool would be considered overkill. So I took some time out recently and I’ve come up with a spreadsheet that does this.

My criteria were: low tech (well, as low as a spreadsheet can be); must be able to track historical changes; must not rely on complicated spreadsheet formulas; must be very, very simple. Manual copying is allowed. Remember: this is for simple projects.

Oh, and of course it must product a burn-up chart which looks a bit like this:

That’s a pretty straightforward chart for a spreadsheet. So to generate that we want a simple grid that looks like this where the scope changes from time to time and total done slowly grows:

I’ll call that the “chart grid”. The “Day” column is a sanity check only.

But obviously it’s going to start off different. It will look a bit like this, with only the initial scope known, and no work done (yet):

But where does that “Estimated project size” of 20 come from? Well, we also need to show our (initial) scope, and the tasks that need to be done:

The 20 is the sum of all the tasks. I’ve not included an automatically calculated “Total” row, but you could. I pasted the sum (20) manually into the “Estimated project size” column for the present day and a few days into the future.

Then when the first task is done we record it, including the date done (for reference), how much was done, and the running total:

We also need to copy the total into the chart grid:

Again, the 5 is copied manually, as I decided that clever referencing formulas were just more trouble than they were worth.

As time goes on we complete more tasks:

…and we record these manually in the chart grid, too. We read the table above as “On 4 Nov the total done goes up to 7” and “On 7 Nov the total done goes up to 8”. So the chart grid then looks like this:

At this point the burn-up chart looks like this:

But then, on 10 November, we discover the scope needs to change. It’s important to me that we retain the information of the scope as it has been so far, so we do a few things. First, we change the title of the scope grid to reflect when it was valid from. We rename it to “From Fri 21 Oct 2011 to Thu 10 Nov 2011”:

Second, we move it down a few rows in our spreadsheet (or maybe move it to another tab) to make room for the revised scope.

Third we create the revised scope. This involves copying the work already done, changing the other task lines as appropriate, and naming it to show the date from which it is valid:

Fourth and finally we also record the new scope total (24) and work done so far on the chart grid:

The burn-up chart now looks like this:

And so it goes. Shortly the scope changes again. Again we revise the title of our scope table, move it down out of the way, copy progress so far into a new scope table, and show the rest of the revised scope in this new table, adding progress as we go:

We also record the revised project size estimate (29) and we record progress in the chart grid:

And our burn-up chart looks like this:

If you want access to the formulas (there aren’t many) then this completed example spreadsheet is available as a public Google Doc.



Comments are closed.