Capacity Planning Calculator
Calculates maximum throughput based on available resources, identifies bottlenecks, and forecasts when current capacity will be exhausted given demand growth. Supports planning for equipment, staffing, or facility expansions.
Operations - Capacity Planning Calculator.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Not knowing when current capacity will be maxed out
- Bottlenecks identified reactively after they cause delays
- Expansion investments triggered too late or too early
- No framework for comparing capacity across different resource types
- Demand forecasts disconnected from capacity constraints
Who This Is For
- Operations managers planning production or service capacity
- Manufacturing directors forecasting equipment needs
- IT operations leads sizing infrastructure for growth
- COOs building capacity expansion roadmaps
Inputs
- textResource name or type
- #Current capacity (units per period)
- #Current demand (units per period)
- %Demand growth rate per period
- #Lead time for capacity expansion
- $Cost per unit of additional capacity
Outputs
- Current utilization rate per resource
- Periods until capacity exhaustion
- Bottleneck resource identification
- Recommended expansion trigger date
- Expansion cost estimate
- Demand vs. capacity forecast chart
How Calculations Work
Current utilization is demand divided by capacity. The calculator projects demand forward at the growth rate and identifies the period when demand exceeds capacity. Subtracting expansion lead time gives the trigger date for ordering or hiring. The bottleneck is the resource with the earliest exhaustion date. A chart overlays demand and capacity curves with the trigger point marked.
Example Use Case
Scenario: A fulfillment center processes 8,000 orders/week with 10,000 capacity. Demand grows 3%/week. Warehouse expansion takes 12 weeks and costs $200,000 for 5,000 additional units of capacity.
Result: Current utilization: 80%. Capacity exhaustion: week 8. Expansion trigger: now (week 8 minus 12-week lead time = already past). Expansion cost: $200,000 for 15,000 total capacity lasting until week 25 at current growth.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
What utilization level should trigger expansion planning?
Start planning at 75-80% utilization. This gives buffer for demand spikes while allowing time for the expansion lead time.
How do I handle seasonal demand?
Enter average demand and note seasonal peaks. The CONFIG sheet has a seasonality multiplier to stress-test capacity during peak periods.
Can I model multiple expansion options?
Yes. Enter different capacity increments and costs as separate rows. The OUTPUT compares options side by side.
What if demand growth is not linear?
Use a conservative average rate. The sensitivity table in OUTPUT shows outcomes at growth rates from half to double your estimate.
Should I include maintenance downtime in capacity?
Yes. Reduce available capacity by expected downtime percentage. If a machine is down 10% of the time, its effective capacity is 90% of rated capacity.
Download Capacity Planning Calculator
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.