borselaer.org

Thoughts on agile project management based on human values and behavior and using PRINCE2, Scrum and Lean principles.

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.

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Hyves
  • Add to favorites
  • Email
  • PDF
  • Print
Category: Project Management
Tags: > >

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.

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Hyves
  • Add to favorites
  • Email
  • PDF
  • Print
Category: Project Management, Whitebooks
Tags: > > >

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:

  1. Velocity
    Past team performance, expressed in number of Story Point produced per Sprint.
  2. Velocity estimate
    A calculated guess what the future Velocity will be.
  3. Scope
    Size of the Product Backlog (also expressed in Story Points).
  4. 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'.
  5. 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.

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Hyves
  • Add to favorites
  • Email
  • PDF
  • Print
Category: Project Management
Tags: > >
keep looking »

Switch to our mobile site