Calculating planning uncertainty for agile projects (Excel 2010 template included)
Posted on | November 18, 2012 | 1 Comment
I'm a great believer in visual management. I love brown papers with sticky notes and lots of charts. That's also why I love spreadsheets. Not because I think you can manage projects by crunching data, but spreadsheets give me the opportunity to visualize the project's status and progress by creating charts.
One of the most challenging parts of agile project management is managing trust and uncertainty. Of course the best way to create trust is to deliver software and demo it each sprint. Keeping status and progress transparent also is a "trust requirement". Still, sometimes going agile is a big leap of faith. Personally, I'm not accustomed to mature agile organizations. Each of my agile projects was an agile implementation also. May be that's why I'm extra sensitive to building trust early on.
In this 'episode' of my Excel series of post I'll explain my uncertainty chart. An excellent chart to visualize uncertainty about the product backlog size.
Note that this chart is an example only.Please determine you're own metrics, specific for your project at hand. |
So what's this chart all about?
The primary function of this chart is to show a 'product backlog size uncertainty'. The uncertainty percentage gives an indication how sure you're are about it's size estimation. Please choose you're own metrics here. In this example a 'done' story still leaves 5% of uncertainty, because it's still possible there's a new insight that leads to new requirements. In this example an epic is defined by the phrase 'it's something big and we haven't got a clue what it's about'. That's why the uncertainty percentage is 80. This metric is shown by the red line and yellow sticky notes.
The secondary function of this chart is to give more detail about the makeup of the uncertainties per User Story status. This is shown in the stacked vertical bars.
Both metrics are Sprint specific so that uncertainty can be tracked through time.
The underlying chart data looks like this:
| Sprint | 1 | 2 | 3 | 4 | 5 |
| Uncertainty | 67% | 63% | 29% | 19% | 11% |
| Done – 5% uncertainty | 10 | 20 | 30 | 50 | 70 |
| Ready – 20% uncertainty | 10 | 10 | 40 | 50 | 50 |
| Not Ready – 50% uncertainty | 10 | 10 | 50 | 20 | 0 |
| Epic – 80% uncertainty | 100 | 100 | 0 | 0 | 0 |
So how does it work?
That's not that difficult. Here's how Sprint 5 is calculated:
| Status | Uncertainty | Sum of SP | Sum of Uncertainty |
| Done | 5% | 70 | 3,5 |
| Ready | 20% | 50 | 10 |
| Not Ready | 50% | 0 | 0 |
| Epic | 80% | 0 | 0 |
| Average | 11% |
This data is based on this Product Backlog:
| User Story | SP | Status |
| As a… | 70 | Done |
| As a… | 50 | Ready |
For each status the sum of SP is calculated and then multiplied by the uncertainty percentage for that status.
The average uncertainty percentage of 11% is calculated by dividing the sum of uncertainty by the total sum of SP (13.5/120).
Excel template
The Excel file used for this example can be found here.
New Whitebook (Dutch): Agile releaseplanning met PRINCE2 toleranties en Scrum Business Value
Posted on | July 1, 2012 | No Comments
Last Friday my latest Whitebook was published, called 'Agile release planning with PRINCE2 tolerances and Scrum Business Value'.
It's in Dutch. For my international readers I'll try to post an English translation soon.
Excel template for agile projectplanning with Scrum
Posted on | June 11, 2012 | 2 Comments
Due to great demand I'd like to share my Excel template for agile project planning with you.
Though each project has it's own needs, I'll usually take the following into account when planning agile projects:
- Velocity
Past team performance, expressed in number of Story Point produced per Sprint. - Velocity estimate
A calculated guess what the future Velocity will be. - Scope
Size of the Product Backlog (also expressed in Story Points). - Business Value (on the project level)
Besides the Business Value determined for each User Story, on the level project there usually also exists chunks of 'Must Haves' and 'Could Haves'. - Uncertainty/Risk
If needed a correction factor for uncertainties and risks (I know, not an exact science but useful nevertheless).
So how does this all come together in a nice Excel chart?
Velocity
This one is easy. Just add the past Velocity number into the spreadsheet. In this example we've just finished Sprint 2.
| Sprint | Sprint Velocity |
| 1 | 30 |
| 2 | 35 |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 |
Velocity estimate
Based on your and the team's knowledge on past Sprint you estimate the future Velocity. For the chart you also need cumulative numbers. Also we need to connect the Ready line and Estimate line in the chart. So for Sprint 2 both numbers are filled. For this example half the team is on holiday during Sprint three, so we have reduced the estimate.
| Sprint | Sprint Velocity | Sprint Estimate | Ready | Estimate |
| 1 | 30 | 30 | ||
| 2 | 35 | 35 | 65 | 65 |
| 3 | 25 | 90 | ||
| 4 | 30 | 120 | ||
| 5 | 30 | 150 | ||
| 6 | 30 | 180 | ||
| 7 | 30 | 210 | ||
| 8 | 30 | 240 | ||
| 9 | 30 | 270 | ||
| 10 | 30 | 300 |
Scope
The total size of Story Points in my Product Backlog is 300 SP. Of course there's more to it, but for now let's assume that 300 SP is our target. You'll notice that Scope actually is an estimate also. For now we'll assume that this estimate hasn't changed since the last two Sprints and remains at 300 SP.
| Sprint | Sprint Velocity | Sprint Estimate | Ready | Estimate | Scope |
| 1 | 30 | 30 | 300 | ||
| 2 | 35 | 35 | 65 | 65 | 300 |
| 3 | 25 | 90 | 300 | ||
| 4 | 30 | 120 | 300 | ||
| 5 | 30 | 150 | 300 | ||
| 6 | 30 | 180 | 300 | ||
| 7 | 30 | 210 | 300 | ||
| 8 | 30 | 240 | 300 | ||
| 9 | 30 | 270 | 300 | ||
| 10 | 30 | 300 | 300 |
Business Value (project level)
We have an agile project at hand, so we do not aim to finish all of the User Stories. Based on the individual User Story Business Value a Must Have scope is defined. The sum of this part of the Product Backlog is 200 SP. Within the chart I need to make a distinction between Must Have scope and optional scope.
| Sprint | Sprint Velocity | Sprint Estimate | Ready | Estimate | Total Scope | Must Have |
| 1 | 30 | 30 | 300 | 200 | ||
| 2 | 35 | 35 | 65 | 65 | 300 | 200 |
| 3 | 25 | 90 | 300 | 200 | ||
| 4 | 30 | 120 | 300 | 200 | ||
| 5 | 30 | 150 | 300 | 200 | ||
| 6 | 30 | 180 | 300 | 200 | ||
| 7 | 30 | 210 | 300 | 200 | ||
| 8 | 30 | 240 | 300 | 200 | ||
| 9 | 30 | 270 | 300 | 200 | ||
| 10 | 30 | 300 | 300 | 200 |
Uncertainty and risk
The Product Backlog contains a number of Epics and unfinished User Stories (not 'Ready' User Stories). Besides that there a couple of other project risks that can have an effect on scope, resources, time, etc.
In some projects I add a risk percentage column within the Product Backlog when there a couple of high risk User Stories. Sometime I only add a percentage on the project level. Sometime I decrease specific Velocity estimates on specific Sprint. It's up to you to decide if and how you'll take risks into account for the project planning. By the way, the team decides of course on risk percentages, not the project manager
.
In this example I've have added a risk percentage on the Must Have scope. I didn't change the 'Total Scope' however. I'll explain that to the project board verbally.
| Sprint | Sprint Velocity | Sprint Estimate | Ready | Estimate | Total Scope | Must Have | Risk | Optimistic | Pessimistic |
| 1 | 30 | 30 | 300 | 200 | 15% | 170 | 230 | ||
| 2 | 35 | 35 | 65 | 65 | 300 | 200 | 15% | 170 | 230 |
| 3 | 25 | 90 | 300 | 200 | 15% | 170 | 230 | ||
| 4 | 30 | 120 | 300 | 200 | 15% | 170 | 230 | ||
| 5 | 30 | 150 | 300 | 200 | 15% | 170 | 230 | ||
| 6 | 30 | 180 | 300 | 200 | 15% | 170 | 230 | ||
| 7 | 30 | 210 | 300 | 200 | 15% | 170 | 230 | ||
| 8 | 30 | 240 | 300 | 200 | 15% | 170 | 230 | ||
| 9 | 30 | 270 | 300 | 200 | 15% | 170 | 230 | ||
| 10 | 30 | 300 | 300 | 200 | 15% | 170 | 230 |
During the project
Of course during the project lots of things changes. There are changes in scope, velocity and risks. The chart I use also makes these changes transparent. In the example below we have just finished Sprint 6. The Must Have scope grew in Sprint 3 and shrank again in Sprint 6. The risk percentage was decreased to 10%.
At this time the chart suggests that Sprint 7 is needed to finish the Must Have scope on a neutral risk scenario. Taking risk into account in the pessimistic scenario and given the fact that are more functionalities beyond the Must Have scope, I would advise to plan an additional Sprint. So Sprint 8 would be the last Sprint for this project.

Template
You can find the template used in this example here.
keep looking »
Note that this chart is an example only.





