Beyond Agile Estimation Part II: Improving Your Metrics

Yesterday I blogged about using a spreadsheet in your Agile Estimation efforts. We left off after the first sprint and had an estimated time to completion of 15.14 weeks. Now let’s take a look at how this works after another sprint.

The spreadsheet is the same, however, now we have data for sprint 2. Cell C2 represents the backlog size in story points after sprint 1. In this case if you remember from the blog post yesterday, our backlog is 106 story points. The team is going to commit to 8 story points worth of work (cell C3) and completes 8 (cell C4, told you the team gets better. ) This makes our Team Velocity 7.5 (cell C5), which is just the average of the total story points completed in sprints 1 and 2 (cells B4 and C4) or B4+C4/2.

The users added 4 story points worth of work to the backlog (OBTW, cell C6) and the bugs were 2 story points worth of work (C7). No items were removed (C8). Now time for the math. If you remember from yesterday, it looks like this:

Total Backlog/Team Velocity

or

((C2+C6+C7)-(C4+C8))/C5

or

((Total story points at sprint start  + OBTW in points + Bugs in points)- (Total points removed from backlog + Total Story Points Completed in This Sprint )) / Team Velocity

or

((106 + 4 + 2)-8) / 7.5 or

104/7.5= 13.87

This means that it will take approximately 14 sprints to complete this project, fairly consistent with the 15 in the last estimation. But what about allocating time for OBTW and bug assessments as well as rework. Meaning, we have to allocate time to asses the bugs and OBTWs and estimate the ones that we decide to add in the backlog. This takes time, usually in the first sprints you work overtime and your Team Velocity goes down, however, we don’t want that to happen for the rest of the project. The way to work some of that time back into your estimate is to discount the Team Velocity and redo the math. Let’s take a look at our spreadsheet again, this time discounting the Team Velocity.

What we are doing here is providing a second estimate (C11) that will take into account the time added to the project for assessment of bugs, OBTWs, research spikes, etc, and the time it takes to estimate them. If you remember that we got an estimate of sprints to completion as 13.87 by 104/7.5= 13.87 where 7.5 was our cumulative Team Velocity.

Now we will discount that 7.5 by 5% and recalculate. Why 5%? Gut feel, you will eventually replace that 5% with a more precise number, but in absence of any real data, I just discount by 5% up front. You could either discount the Team Velocity by an additional 5% every 1-2 sprints, or you can try to calculate your bug rate/OBTW rate and replace the approximation by a different number. To be honest, it is easier to just use the sliding scale of –5% every 2nd or 3rd sprint to get started.

So the new math looks like this:

104/(7.5 * .95) or 104/7.125= 14.60. Almost 15 sprints, slightly more than our original estimate at the end of this sprint of 13.87 or 14 sprints. Our new, more accurate estimate takes into account the time that will be added to the project for new items, bugs, and R&D spikes.

The second discounted or “Weighted Sprints to Completion” (C11) is optional, however, it is more accurate. I like using that number since it attempts to account of the unknown. While it is impossible to predict the unknown, it is a scientific way to at least acknowledge that there will be bugs, OBTWs, and lots of other unaccounted for stuff.

Lastly, let’s take a look at how this progresses over more sprints.

As the screen shot above shows, as you progress to the next sprint, you are going to do the exact same thing, except that over time you will discount your Team Velocity by a larger percent, for example, in sprint 4, we reduce Team Velocity by 10% and 15% in Sprint 5. You increase the discount rate to account for more uncertainty in your project, the longer the project goes on, the larger the bugs and OBTWs get. Again, it is up to you how to adjust the percent used.

Hope that this helps everyone out there looking for some guidance on the spreadsheet.