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