How to Create a Sprint Capacity Planner in Google Sheets
Sprint overcommitment is one of the most common causes of sprint failure. Teams commit to 40 points when they have capacity for 28, then carry over incomplete work, erode stakeholder trust, and feel perpetual pressure. A capacity planner forces the calculation before the sprint starts rather than discovering the gap at the sprint review.
This guide covers how to build a sprint capacity planner in Google Sheets that accounts for individual availability, PTO, historical velocity, and buffer for unexpected work.
Step-by-step guide
Create the team roster and baseline sheet
Create a sheet called "Team Roster" with columns: Engineer Name, Role (FE/BE/Full Stack/QA), Sprint Velocity (average story points per sprint), Daily Capacity (hours per day available for sprint work, typically 5-6 to account for meetings and overhead), and Focus % (what percentage of their time is allocated to this team vs. other commitments). This becomes the base data for the capacity calculation.
Build the sprint planning sheet
Create a new sheet for each sprint named by sprint identifier (Sprint 24 — Aug 25). Add a table with columns: Engineer Name (from Roster, dropdown), Working Days This Sprint (default 10 for 2 weeks), PTO Days, Public Holidays, Capacity Days (formula: Working Days - PTO - Holidays), Daily Points (from Roster lookup), and Adjusted Capacity (Capacity Days x Daily Points). Sum the Adjusted Capacity column for total sprint capacity.
Add a velocity trend chart
On the Roster sheet, maintain a table of the last 6 sprints with columns: Sprint, Committed Points, and Completed Points. Add a line chart showing committed vs. completed over time. If completed consistently falls below committed, this is the evidence that commitment calibration needs adjustment. Share this chart with stakeholders to contextualize why sprint commitments are being adjusted.
Calculate the recommended sprint commitment
Add a calculation below the capacity table: Recommended Commitment = Total Adjusted Capacity × 0.8. The 20% buffer accounts for: unplanned interruptions, code review time, meetings not captured in capacity, and test-fix cycles. Teams that plan to 100% of capacity consistently fail their sprints. Teams that plan to 80% consistently deliver and have buffer for polish.
Create an issue tracker tab for backlog alignment
Add a tab called "Sprint Backlog" with columns: Issue ID (Linear or Jira ticket number), Title, Assignee, Story Points, and Status. Import tickets manually or use a Google Sheets add-on (JIRA to Sheets, or Linear via Zapier). As tickets are added to the backlog tab, a running total of committed points updates automatically. When committed points exceed Recommended Commitment, the cell turns red.
Common mistakes
Using raw velocity without PTO and holiday adjustments
A team's 10-sprint average velocity is 42 points. Sprint capacity in a holiday week with two engineers on PTO is 20 points. Committing to 40 points because "that's our velocity" guarantees a sprint failure. Always adjust for actual available days before applying the velocity multiplier.
Not accounting for sprint ceremonies in capacity
Sprint planning, daily standups, refinement sessions, and retrospectives consume 4-8 hours per sprint per engineer. If you calculate capacity as 10 days × 8 hours and do not subtract ceremony time, you are overestimating available coding time by 10-20%. Use 5-6 coding hours per day rather than 8, or explicitly track ceremony hours as a capacity deduction.
Separate planners per team with no visibility across teams
When cross-team dependencies exist (the platform team's API needs to be done before the product team can build the feature), both teams need visibility into each other's capacity. Either share planners across teams or export capacity data to a shared tracker. Hidden capacity constraints are the root cause of most cross-team schedule failures.
Tips
Use Google Sheets named ranges for the Team Roster data so Sprint sheets reference it by name rather than cell coordinates — this prevents breakage when you add a row to the roster
Add a conditional format rule that highlights the sprint capacity cell red when committed points exceed 90% of recommended capacity and yellow when above 80% — visual warnings during planning catch overcommitment before the sprint starts
Create a quarterly "sprint health" tab that aggregates committed vs. completed across all sprints — show this to leadership to build the case for realistic commitments over heroic ones
How Vantage helps
Google Sheets capacity planners calculate how much the team can commit. Vantage connects sprint capacity to the product roadmap: when PRD requirements are converted to tickets with story point estimates, Vantage can surface whether the current sprint capacity supports the planned delivery timeline. EMs and PMs can see capacity constraints against roadmap commitments without a separate capacity spreadsheet.