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:
Past team performance, expressed in number of Story Point produced per Sprint.
- Velocity estimate
A calculated guess what the future Velocity will be.
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’.
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?
This one is easy. Just add the past Velocity number into the spreadsheet. In this example we’ve just finished Sprint 2.
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|
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|
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|
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|
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.
You can find the template used in this example here.