Capacity vs Demand Planner
Compares current team capacity against projected client demand over a rolling 12-week horizon. Surfaces bottlenecks by role, identifies when to hire or reallocate, and flags projects at risk of understaffing.
Agency - Capacity Vs Demand Planner.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Projects starting without enough available staff to deliver on time
- No early warning system for capacity shortfalls by role or skill
- Reactive hiring that lags demand by weeks or months
- Resource conflicts between clients discovered too late to resolve
- Over-hiring in slow periods due to lack of demand forecasting
Who This Is For
- Agency resource managers planning team assignments
- Project managers forecasting staffing needs
- Agency directors making hiring and contractor decisions
- Operations leads balancing workload across departments
Inputs
- textRole or team name
- #Available hours per week (by role)
- #Committed hours per week (by project)
- #Pipeline hours (probable new work)
- #Hiring lead time in weeks
Outputs
- Capacity surplus or deficit by role per week
- Bottleneck roles flagged with severity
- Hiring trigger date (when deficit exceeds threshold)
- 12-week capacity vs. demand chart
- Projects at risk of understaffing
How Calculations Work
Available hours per role are summed and compared against committed hours plus a probability-weighted pipeline. A deficit in any week triggers a flag. If the deficit persists beyond the hiring lead time, a hiring trigger is activated. The planner rolls forward weekly, updating actuals and re-forecasting the remaining horizon. A heat map highlights which roles are over- or under-capacity.
Example Use Case
Scenario: An agency has 3 designers (120 hrs/week total capacity) and committed demand of 100 hrs/week rising to 140 hrs/week in 4 weeks due to two new projects. Hiring lead time: 6 weeks.
Result: Current surplus: 20 hrs/week. Deficit in week 4: -20 hrs/week. Hiring trigger: immediate (deficit arrives before a new hire could start). Recommendation: engage a contractor within 2 weeks or reallocate from another role.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
How do I estimate pipeline hours?
Use the expected close date and scope estimate from your sales pipeline. Weight by probability: a 50% likely project contributes half its hours to the demand forecast.
What deficit threshold should trigger hiring?
When the deficit exceeds 20% of a role's capacity for 3+ consecutive weeks. This threshold is configurable in CONFIG.
Should I include PTO in available hours?
Yes. Reduce available hours for known PTO, holidays, and training days to get an accurate capacity picture.
How far ahead should I plan?
12 weeks is the practical minimum for agencies. Extend to 26 weeks if your hiring lead time exceeds 8 weeks.
Can I use this for contractor planning?
Yes. Add contractor capacity as a separate role with a higher cost rate. The model will show when contractors are more cost-effective than waiting for a full-time hire.
Download Capacity vs Demand Planner
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.