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
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
((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
(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.
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).
Copyright © 2016, Progress Software Corporation and/or its subsidiaries or affiliates. All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks or appropriate markings.