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
((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
((106 + 4 + 2)-8) / 7.5 or
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
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
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.
Stephen Forte sits on the board of several start-ups including Triton Works. Stephen is also the Microsoft Regional Director for the NY Metro region and speaks regularly at industry conferences around the world. He has written several books on application and database development including Programming SQL Server 2008 (MS Press).