Recent Blog Posts

November 28, 2016

Sprint Capacity Planning Excel Template

Capacity Planning for Sprint based Agile projects is the process in which the amount of work that can be done in the sprints with available resources is planned. This sprint capacity planning excel template helps a Scrum Master or Project Manager in capacity planning for sprints and get a head start in agile projects. The planner has 3 sections - Capacity Planner, Resource Plan and Data Config.
Download This Template Now !
Sprint Capacity Planning Excel Template
Sprint Capacity Planning Excel Template

About the template

  • Capacity planner to do the planning of tasks and sprints as part of Agile Project Management.
  • A config section which allows changing the work streams and sprint names.
  • The template can handle up to 4 sprints of two weeks each, 5 work streams and 30 tasks in the backlog.
  • Task ID a primary key which can be used in task planning,
  • A detailed resource plan to capture resource available on a daily basis.
  • For each sprint shows the available and planned capacity for each work stream.
  • Highlights tasks which have not been allocated to any sprint and duplicate tasks in sprints.
  • Highlights gaps in available and planned capacity for each sprint.
  • Resource plan which includes a capacity plan on a daily basis.
  • This plan once finalized will feed into your agile project plans or detailed schedules.

Capacity Planner Tab

Sprint Capacity Planner
Sprint Capacity Planner
  • The capacity planning tab is the key in the template as it gives the sprint planner a view where there is a capacity shortage. 
  • It has a backlog which has the list of items to be done in the sprints.
  • Each item can have estimates for each of the work streams.
  • There are a total of 4 sprints which each sprint have the same item number, description and estimates field. 
  • To allocate an item to the sprint simply put the serial number in the # column. The item description and the estimates will appear in the relevant work streams.
  • Above each of the columns, there is a capacity available and capacity planned columns.
  • Available capacity comes from the resource plan. Planned capacity comes from the list of the items added in the particular sprint.
  • If the planned capacity is less than the available that means there is capacity in the sprint. This is indicated by green color.
  • If the planned capacity is more than the available then you either need to drop something. This is indicated by red color.
  • If the same task id is used multiple times the task id highlighted in red.
  • If you want to delete the task then enter ".". If you do not enter a "." and leave it blank then the columns will show n/a.

Resource Plan and Data

  • The resource plan captures resource name, resource type and daily allocations for 4 sprints.
  • The allocation is based on hours and working arrangements full time or part-time. So, if someone is working full time then it is 8 hours, a half day it is 4 and part-time could be 6 hours.
  • If someone is not available or is on leave then enter 0(zero).
  • Change the first date in D2 to change the sprint start date.
  • The data tab has a resource type, code, and capacity per sprint.
  • You can change the resource type and code as per your needs. 
  • The resource code is a short name which allows us to use the screen space optimally.
Sprint Resource Plan
Sprint Resource Plan

Capacity Planning Steps

Step 1: Create a backlog with items or tasks.
Step 2: Identify different work streams and their estimates.
Step 3: Identify the resources and populate the resource plan.
Step 4: On capacity planning tab move the items from backlog to sprints using the task ID.
Step 5: Move the items until the planned and actual capacity are the same.

  1. Capacity issues can also be discussed in Sprint planning meetings.
  2. If the task planned during sprint planning are more then capacity then move items to the next sprint.
  3. If any capacity issues were faced in the last meeting make sure to escalate them in sprint retrospective meeting so that do not repeat again. 
  4. Most of the times one of the work streams runs out of capacity. In that situation, you can break up an item to be split across multiple sprints.
  5. Also, worthwhile highlight any issues with capacity in project updates or dashboards.
Download This Template Now !

1 comment