A Proven Method to Easily Project Your Next Sprints Velocity in SCRUM [Spreadsheet Template Included]

TL;DR

I am presenting a simple method to calculate a team’s sprint capacity and project the velocity for the next sprints provided the team’s project backlog of work items exists and it’s estimated in story points. A spreadsheet template is included for you to consult, use or get some inspiration from.

Background

I have been working with SCRUM for quite some years and have developed over time a simple method to keep track of the team’s capacity and to project the next sprint(s) velocity. It worked pretty well for me so I’m making it public hoping that it will enhance the way you run your sprint planning.

Note: This article assumes that your team has a backlog of work items already in place and they are estimated in story points.

Scrum Framework

Your First Sprint

Say you are a Scrum Master and you need to run your first sprint. What tools can you use to help you? Most probably the company you work for has such tools and you can reach your peers for help. If not, you can develop your tools. And since sprint planning involves a few calculations, the most used software for that is spreadsheets.

What do you need to capture?

• Sprint start and end date
• List all team members
• List the working days in the sprint (0.5 for part-time, 0 for days off)
• Fill in the availability for each member on each working day. This will sum up to the total number of working days in the sprint = sprint capacity.

That’s all! These are the bases for the simple math that will help you plan your sprint.

Your First Sprint

Roll up Your Sprints into an Overview Sheet

To have a clear and simple view of all your sprints you need to roll up your sprints into a master sheet, the Overview. To accomplish that you must list your sprints in a column and each refers to the capacity calculated in each sprint sheet.

Overview Sheet

Overview sheet. Refer to each sprint capacity in the first sheet

To project the velocity, you need historical data. But, in the first sprint, you don’t have historical data so you cannot use a formula. What you can do is put a reasonably realistic number there such as the number of sprint working days in the team: the capacity.

Sprint 1

Sprint 1. Projected Velocity = Sprint Capacity (only for Sprint 1)

At the end of sprint 1, you will have the real velocity (actually completed story points). You use that value to project the Sprint 2 velocity:

Sprint 2 Projected Velocity = (Sprint 2 Capacity) * (Story Points per Day in Sprint 1)

Note: Story Points per Day = (Completed Velocity / Sprint Capacity)

For the next sprints, the Projected Velocity will be Sprint Capacity multiplied by Average Story Points per day in Previous Sprints.

Sprint 4

Sprint 4. (Projected Velocity) = Sprint Capacity * (Average Story Points per Day in Previous Sprints)

After 5 sprints your projected velocity will be very realistic. Also, the average completion rate will be closer to 100%.

Completion Rate

Completion Rate. As your sprints are progressing the average completion rate will converge towards 100%.

That’s it! All you need is to (1) at Planning fill the team presence & days off, plus (2) fill the Committed Velocity if it is different from Projected Velocity, then (3) at Sprint End fill the Completed Velocity in the Overview sheet. Three manual steps and the rest are formulas! By the way, don’t worry, all the formulas are already in the Google Spreadsheet template provided at the end of this article.

Summary Table – Columns Explainer
The Overview table above has a good number of columns and they need a brief description.

Sprint # is the Sprint Number or the Sprint Name.

Sprint Start is the start date of the sprint.

Sprint Capacity is the sum of available working days for your members: developers or developers plus testers if you want to include testers in the Capacity calculation.

Projected Velocity is the estimated story points that the team will deliver at the end of the sprint. It is an ideal value, the actual one (completed velocity) will be smaller or higher.

Committed Velocity is the number that your team commits to deliver at the end of the sprint. It may be the same as Projected Velocity, or different. Your team might decide to under-commit, to allow for some work to be injected into the sprint. For example, you can decide that your committed velocity to be 80% out of your projected velocity, to allow for 20% work variation – items that are added in the sprint during the sprint, due to the nature of your project.

Completed Velocity is the number of story points your team delivered at the moment of sprint end.

The Completion Rate is a percentage and represents how much your team has delivered compared to the ideal (projected) velocity. Completion rate = completed velocity / Projected velocity.

Velocity / Capacity is an output quotient that offers you an idea about how many story points are delivered per day, used only to project the next sprint’s velocity.

Become pro: include availability and ramp-up

Your team is not fixed. Some members might not be 100% dedicated to a single team. If a member is part-time, 4 hours out of 8, you can account for that very simply: your daily capacity is not 1 but (e.g.) ½., or they might be in the team Monday to Wednesday, and in the other week Thursday to Friday, etc.

Sprint with Ramp-Up

When you have a new joiner, her first sprint will have a ramp-up factor of 0-10%. That is, her expected measurable output will be close to zero, as she is learning. Each sprint you will increase the ramp-up factor by 10%-20%, so after 5-10 sprints the recent joiner will be reasonably up to speed. This has to be monitored and adjusted for each person, as they adjust and learn at a different pace.

Sprint with Availability

Member Capacity = (Days at Work * Ramp-Up Factor)

And done. Your sprint planning will run smoothly on some simple input data and simple formulas. All your sprints data will be nicely stored and the business can use it in various ways.

As promised, here’s the Sprint Planning Template:

View the template.

Copy the template.

The Romanian translation was published in Today Software Magazine – Estimarea vitezei sprinturilor în SCRUM.

Thanks to Stefania Isaila and Ovidiu Rus for reading drafts of this and providing valuable feedback.

Written by Vasile Tomoiaga