Skip to content


Calculating planning uncertainty for agile projects (Excel 2010 template included)

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 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.

Posted in Project Management.

Tagged with , , .


One Response

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

Continuing the Discussion

  1. Gantt Chart linked to this post on 2012/12/07

    [...] Calculating Planning Uncertainty for Agile Projects (Excel 2010 template not included) [...]



Some HTML is OK

or, reply to this post via trackback.


five × 2 =