Beyond Agile Estimation Part I: Measuring Key Data Points

During the recent conference season, I did a lot of presentations about Agile Estimation based on my Pluralsight course of the same name. At the end of the sessions I showed an Excel spreadsheet that shows how to take the concept of Agile Estimation one step further by measuring what actually happened and using that as part of your estimation.  I am assuming that you know something about Agile Estimation, however, at BarCamp Hong Kong, I got a request to further explain this spreadsheet in a blog post. As promised, here it is.  

Let’s take a look at the spreadsheet after your first iteration (Sprint 1). Let’s assume that our iterations (sprints) are two weeks long. To keep things simple, let’s say that our product backlog had only 100 story points worth of work in it. (Remember this can represent 10 or 25 user stories, doesn’t matter.) This is shown in cell B2.

I like to track everything. Cell B3 shows how many story points the team took out of the product backlog and put into the sprint backlog. This is what they committed to do during the sprint. Remember for Sprint 1 the team will always over commit and under deliver. Who cares? We care more about what they can do averaged out over time (the Team Velocity.) After two or three sprints, the team will start to commit to the correct number and Team Velocity (the average of the total story points completed) will even out. But let’s not get ahead of ourselves.

Cell B4 shows us how many story points the team actually completed in the sprint (in this case 7 story points) and cell B5 shows us the cumulative Team Velocity, which in this case is also 7 since it is the first sprint. After the next sprint we will start to average this number.

Now it gets fun. Cell B6 shows how many story points of work were added to the backlog during or after the sprint. This is what the users forgot or got inspired to add by looking at your work in sprint 1. I call these affectionately OBTWs, for “oh, by the way..”

Cell B7 shows us how many story points worth of bugs were added to the product backlog. (More on bugs in Part II.) Cell B8 show us how many story points worth of work were removed from the backlog during the sprint. (This *does* happen but not usually on the first sprint.) Cells B6-8 assume that the team had time to do an assessment and estimation in points (planning poker, etc) of the new items/bugs during or immediately following the sprint.

Now for the estimate. At the end of each sprint you have to re-estimate the duration of the project. You do this by dividing the total backlog size by the cumulative team velocity. This is done by:

Total Backlog/Team Velocity

or

((B2+B6+B7)-(B4+B8))/B5

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

(100 +10 + 3) – (0 + 7) / 7 = 15.14

Noticed that we had 100 points in the backlog and completed 7, bringing our backlog down to 93. However we added 13 points worth of work (OBTWs and Bugs), bringing our backlog up to 106 (93 + 13). So the math is factored down to:

106/7 = 15.14

What this means is that after the first sprint, our estimate is that the project will take 15.14 sprints to complete. Since our sprints are 2 weeks long, the project should be completed in 30 weeks. We also know that due to the cone of uncertainty and future bugs/feature requests, that this number is not super accurate (more on that in Part II tomorrow). That is ok, as you know from the theory of Agile Estimation, our estimate for the project completion will only get better over time and after about 5 sprints, it will be pretty dead on.

In Part II tomorrow, we’ll look at how this works over a few more sprints.