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.

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.

I’m a software guy. While I am more than comfortable rooting and flashing a custom ROM onto my wife’s Galaxy S III, I need help setting up a printer. Lucky for me, my career’s arc also coincided with the rise of software.

Back when I graduated university oh so many moons ago, software was literally rocket science. I entered the industry at the cusp of the transition from the mainframe/minicomputer eras to the client/server era. When I started coding at a Wall Street firm in the early 1990s, software was controlled by “men in white coats” in the mainframe room. I use to send jobs to CICS via JCL (not a Java class library for anyone under 40) and had to wait for approval, then for execution time. Software was complex to build, expensive to produce, and had way too many moving parts. In short, software sucked and only NASA and big banks invested in custom software development.

Lucky for me, that quickly changed and the client/server era, followed by the .COM era liberated millions of software developers like me. The last twenty years have seen a revolution in the ease of building software and the economics of software development, changing the lives of just about everyone on the planet. Software’s liberation from the men in white coats in the Mainframe room has made entrepreneurship far easier (and cheaper) as I have described here.

Over the past few months I have realized something, just as I thought that the software revolution was only catching its stride after 20 years of liberation, I noticed that everyone around me was building something physical. Maybe this is because I live in Hong Kong and the high tech manufacturing center of the world is a 30 minute train ride away in Shenzhen, China. Or maybe it is because my mentor is obsessed with 3-D printers and has had a 3-D printer the size of a washing machine in his basement for a decade. But no, something else is happening: Hardware is the New Software.

My eyes started to open on a day trip to Shenzhen earlier this year to the Huaqiangbei Electronics market. My friend who brought me to the market made it sound like a giant Frys or even Best Buy, however, what I encountered was astonishing. This is how I describe Huaqiangbei to people: imagine the largest shopping mall you have ever seen. Picture it filled with just a single component of motherboards. Then picture an identical one next to it containing just the internals of a USB port. Then picture an identical one next to it filled with just WiFi radios. Then one for cell phone screens, wires, LED displays, etc, etc. The place is enormous and supports the supply chain of the large contract manufacturers in Shenzhen, like Foxconn building your iPad.

The side effects of the radical growth of consumer electronics and its suppliers ecosystem are huge. Hardware has gotten cheaper and componentized. Hardware has been liberated!

Earlier this week, I was judging the Imagine Cup, an international software competition for university students. After well over 200 teams from 75 countries was narrowed down to six finalists, here was the breakdown:

3 teams were a hardware solution that was supported by custom software that they wrote

1 team was a software solution that had a Kinect component to it as well

1 team was a pure software solution

Five out of the six teams incorporated hardware, and four of those built their own hardware! For a software competition! By students!

Just as software was once hard to build and expensive to prototype years ago, as recently as three years ago, hardware was difficult and expensive. Just as software was liberated 20 years ago, hardware has been liberated, thanks to componentized supply chains, the economies of scale, and open hardware facilitation co-work spaces in many cities such as Dim Sum Labs here in Hong Kong and SEED Studio in Shenzhen. Now just about anyone can rapidly and cheaply prototype their hardware solution and seek a first production run by an eager factory in the developing world funded by putting the prototype up on Kickstarter. The DIY (do it yourself) revolution has begun!

The software revolution changed the world in some pretty dramatic ways. The hardware revolution will be even more dramatic.

The Imagine Cup is a worldwide software design student competition sponsored by Microsoft. Students are given a theme at the beginning of the school year and form teams in their university to build a project that is they can bring to market. The competition is about business viability as much as using the latest super cool technology. So students have to be the right blend of entrepreneur and geek. The competition has students compete in regional competitions and then have to win the right to represent their country in the worldwide finals. Last year the winner was “Team Hermes” from Ireland and they went on to start a business based on the project. The Imagine Cup is truly a transformative thing for those who participate.

This year there are 350 finalists from 75 countries on 106 teams. The finals start today in Sydney, Australia, and I am lucky enough to be one of the 56 judges evaluating the teams. I spent the afternoon Friday at the venue with the teams and fellow judges and attended the opening ceremony and am equally excited as the students. I’m even more excited since I was a judge at the first ever Imagine Cup ten years ago in Barcelona, Spain.

The first Imagine Cup was a truly inspirational event. The excitement of the students was amazing. Since the Imagine Cup was very small back then (only 15 finalists), it was held the day before TechEd Europe, so the students attended TechEd as well. I got to know them well during the week at TechEd and always wonder how many of them stuck with entrepreneurism. I always look back and say that Imagine Cup 2003 was the single most rewarding community event I have ever volunteered for.

In the past 10 years a total of 1.65 million students have participated from 194 countries. I wonder how big it will be 10 years from now (and if they will ask me to be a judge again. )