Agency Capacity Planning Spreadsheet

You promise clients fast delivery. Then your team is overloaded.

Capacity planning prevents this. You know exactly how much work your team can take.

The Spreadsheet Structure

Column 1: Team Member

List each person: [Name, Role]

Column 2: Hours Per Week

How many hours per week can they work?

Full-time: 40 hours
Half-time: 20 hours
Flexible: varies

Column 3: Billable %

What percentage of their time is billable (client work)?

Senior: 60% (40% on meetings, admin, training)
Mid-level: 70% (30% on admin, meetings)
Junior: 80% (20% on learning, admin)

Column 4: Billable Hours

Hours per week x Billable %

Example: 40 hours x 70% = 28 billable hours/week

Column 5: Project Allocation

What projects are they on this quarter?

Project A: 15 hours/week
Project B: 10 hours/week
Bench (available): 3 hours/week

Column 6: Capacity Used

Sum of Project Allocation

Example: 15 + 10 = 25 hours/week used

Column 7: Capacity Remaining

Billable hours - Capacity used

Example: 28 - 25 = 3 hours/week remaining

Column 8: Utilization %

Capacity used / Billable hours x 100

Example: 25 / 28 = 89% utilization

The Full Spreadsheet Example

Name	Role	Hours/Week	Billable %	Billable Hours	Allocated	Remaining	Utilization
Sarah	Designer	40	70%	28	28	0	100%
Tom	Developer	40	70%	28	20	8	71%
Jane	PM	40	40%	16	16	0	100%
Total				72	64	8	89%

What the Numbers Mean

0-50% utilization: Team has capacity. They can take on more work.

50-85% utilization: Good. You're busy but not overloaded.

85-95% utilization: Tight. No buffer for unexpected work or emergencies.

95%+: Overloaded. Someone's going to burnout.

Using This for Capacity Planning

When a new project comes in:

Estimate hours needed per team member
Check who has capacity
Assign to people with 50-80% utilization
Run the math to ensure team doesn't go over 85%

Example:

New project needs:

Designer: 20 hours
Developer: 25 hours

Sarah is at 100%, Tom is at 71%, Jane is at 100%.

Decision: Assign to Tom (he has 8 hours). For the additional 17 hours, hire a freelancer or push the timeline.

Monthly Updates

Update the spreadsheet monthly:

Remove completed projects
Add new projects
Adjust allocations as needed
Monitor utilization trends

If team is consistently 90%+ utilization, you need to hire or reduce scope.

Seasonal Adjustment

Some agencies are busier in certain seasons.

Example: E-commerce agency is busier before holidays.

Create separate spreadsheets for each season. Plan accordingly.

Buffer for Unknowns

Always leave 10-15% of capacity unallocated:

Emergency client requests
Team sick time
Professional development
Admin work that's not billed

If your spreadsheet shows 100% allocated, you're actually overbooked.

The Bench

Team members with no allocated projects are "on the bench."

This is where you:

Train people
Work on internal projects
Attend conferences
Handle admin

Bench time is healthy. It prevents burnout and keeps people sharp.

Healthy: 10-20% bench time

Unhealthy: 0% bench (everyone allocated to client work)

When Utilization Is Wrong

Too low (50-60%):

You're not selling enough
You're bidding too low and not winning projects
Your estimates are too high

Fix: Increase sales or reduce team.

Too high (90%+):

You can't onboard new clients
Team is burnout risk
No time for growth work

Fix: Hire more people or reject new business.

Hiring Based on Capacity

When should you hire?

When your team is at 80-85% utilization for 3+ months.

Hiring when you're at 90%+: It's too late. People are already burned out.

Hire when you can train them before you're desperate.

FAQ

Should sales people be on this spreadsheet?

Yes. They take time. Allocate them.

What about contractors/freelancers?

List them separately. They're variable capacity.

How do I account for meetings?

It's in the Billable % calculation.

If someone's in 8 hours of meetings/week, their billable % is lower.

What if someone works on 5 projects?

List them all in the Allocation column. Add them up.

If they exceed billable hours, something needs to give.

Should I include time off and vacation in the billable %?

Yes. If someone takes 3 weeks vacation, reduce their hours or billable % for that period.

Most agencies reduce billable % to account for expected PTO throughout the year.

How often should I update utilization targets based on market demand?

Review quarterly. If you're consistently at 95%+ utilization, you're understaffed. If you're at 50-60%, you need to hire, reduce team, or sell more.

These gaps tell you about staffing needs and sales performance.