Note: This blog post was originally written when I was a consultant for OpenSource Connections.

Recently I wrote a blog post on why software developers are typically so bad at estimation. I also gave a preview of the presentation that I am giving today to the Agile2010 conference on how to combat some of these problems by building range estimation techniques into a Scrum burndown. You can see that slide deck on slideshare.

To briefly recap, I pointed out that Agile team estimation and Planning Poker (or Scrum Poker) is great, but one big problem I have with it is the reliance on single point estimates in the burndown.

That’s why we have built range estimation into our Scrum Poker and burndowns at OpenSource Connections, and we find it to be more effective. By holding two cards instead of one, each team member is indicating a range estimate and communicating lot more about the risk and complexity that they see in that project.

So how does it work? In this blog post I’m going to describe how we do it using Google Spreadsheets.

How to build a burndown using ranges

Ingredients needed:

• Spreadsheet of any sort (I like google spreadsheets for easy sharing)
• Scrum Poker cards
• An engaged team!

Steps:

• Build a normal Scrum spreadsheet with a list of tasks on it.
• Estimate each task using Scrum poker. Have each participant hold up two cards to indicate the range of their estimate.
• The Scrummaster should encourage debate about the estimates when they vary widely in the size of the estimates or the width (ie risk) of the estimates.
• Once the team agrees on a range, the low and high values are entered in the spreadsheet.
• The spreadsheet calculates an estimate which is 2/3 of the way between the low and high estimate.
• Use the “2/3 estimate” as your ideal burndown to zero and to see how many tasks fit in your sprint.
• Use the low and high estimates as a band of reasonable deviation from the ideal burndown. They should both slope to zero at the same rate as the 2/3 estimate.
• Use the ranges to encourage more conservative adoption of tasks into a sprint, and to communicate risk and progress more clearly with customers.

In the end, we come up with a burndown similar to this, which represents the low and high estimates, and is a representation of the risk of the sprint or as a band of acceptable divergence from the estimate.

Make Google Spreadsheets (or Excel) do the Hard Work!

The next thing to show is how we represent this in a Scrum spreadsheet, and derive the formulas to build that pretty burndown. The vertical image is an example scrum spreadsheet like we use at OpenSource Connections, and the following sections will describe the major formulas being used in that spreadsheet.

This is a little tricky the first time, but then just copy the sheet from then on.

Low Estimate (D6-D8) and High Estimate (E6-E8) are entered by the team as they agree on estimates for each task in sprint planning.

Initial Estimate per task (F6-F8) is calculated by the spreadsheet, and is a single integer weighted 2/3 of the way from the low estimate to the high estimate:
F6 = (D6*0.33)+(E6*0.67)

2/3 Estimate (F2 and duplicated in F3) is just a sum of the initial estimates across all the tasks.
F3 = sum(F6:F8)

2/3 Estimate per day (G3-K3) is a calculated field, which decreases the estimate for each day down to zero by the end of the sprint. This provides the “ideal burndown” line for your chart at a 2/3 estimate.
G3 = F3-($F3/[Days in Sprint])
H3 = G3-($F3/[Days in Sprint]) … and so on

Low Estimate (F4) is a sum of all the low estimates: F4 = sum(D6:D8)

Low Estimate per day (G4-K4) is a calculated line showing the path down towards zero for our low estimates. It uses the slope of the 2/3 line.
G4 = IF((F4-($F3/[Days in Sprint]))>0,F4-($F3/[Days in Sprint]),0) … and so on

High Estimate (F5) is a sum: F5 = sum(E6:E8)

And High Estimate per day (G5-K5) is calculated similar to the low estimate per day:
G5 = F5-($F3/[Days in Sprint]) … and so on

Then developers update estimates each day as usual, and this makes the “Actual” line in the burndown.

That’s really all there is to it!

Once you’ve got your spreadsheet setup, you can just copy it from sprint to sprint, and then it really doesn’t take any more time in sprint planning to use ranges instead of single points. But using ranges with a 2/3 weighting like we do will help force your team to think a little more about the risk involved in their estimates and to estimate more conservatively. This allows you to better communicate with your customer and more reliably predict what you can commit to in a sprint.