SaaS Headcount Planning & Forecasting Model
A comprehensive, dynamic tool for headcount planning and financial forecasting in SaaS implementation services
Model Overview
This Excel model provides a comprehensive, dynamic tool for headcount planning and financial forecasting in a SaaS company’s implementation services segment. The model allows users to plan resources, forecast costs, analyze margins, and run different scenarios to optimize business performance.
Designed with best practices in mind, this model integrates with Percentage of Completion (POC) accounting principles to provide actionable insights for unit economics, resource allocation, and headcount forecasting.
Worksheet Structure
Instructions
Purpose: Guide users on how to use the model
Content: Step-by-step instructions, color coding explanations, navigation tips
Features: Table of contents with hyperlinks to different sections
Dashboard
Purpose: Provide an executive summary of key metrics and visualizations
Content: KPIs, charts, and summary tables
Features: Dynamic charts that update based on scenario selection
Inputs & Assumptions
Purpose: Centralize all user inputs and global assumptions
Content: Base rates, utilization targets, available hours per FTE, project parameters, planning parameters, scenario parameters
Features: Data validation, dropdown menus, conditional formatting
Headcount Planning
Purpose: Plan and forecast headcount by role and time period
Content: Starting headcount, planned hires, attrition modeling, resulting headcount forecast
Features: Dynamic calculations, visualization of headcount changes
Hiring Plan
Purpose: Translate headcount needs into specific hiring timelines
Content: Detailed hiring schedule with start dates, ramp-up period tracking, productivity calculations
Features: Gantt chart visualization, hiring pipeline view
Labor Cost Forecast
Purpose: Calculate detailed labor costs based on headcount plan
Content: Monthly and quarterly labor costs by role, breakdown of salary, benefits, taxes, and overhead
Features: Dynamic calculations based on headcount changes
Capacity Planning
Purpose: Calculate available and billable capacity based on headcount
Content: Available hours calculation, utilization targets, non-billable hours allocation
Features: Capacity utilization visualizations, alerts for capacity constraints
Project Planning
Purpose: Plan project mix and resource allocation
Content: Project type definitions, project parameters, project mix by quarter
Features: Resource allocation optimization tools
Key Calculation Logic
Headcount Calculations
- Beginning headcount + New hires – Attrition = Ending headcount
- FTE calculations account for partial months based on start dates
- Attrition modeled as a percentage of average headcount with monthly distribution
Labor Cost Calculations
- Base salary × (Months employed / 12) = Prorated salary
- Benefits = Prorated salary × Benefits percentage
- Taxes = Prorated salary × Tax rate
- Overhead = Prorated salary × Overhead allocation rate
- Fully-loaded cost = Prorated salary + Benefits + Taxes + Overhead
Capacity Calculations
- Available hours = Headcount × Working hours per month × (1 – PTO/Training allowance)
- Billable hours = Available hours × Utilization rate
- Effective billable hours = Billable hours × Productivity factor (for ramping employees)
Revenue & Margin Calculations
- Revenue by project type = Number of projects × Average revenue per project
- Alternative calculation: Billable hours × Bill rate
- POC revenue recognition = Total contract value × Percentage complete
- Gross profit = Revenue – Direct labor costs – Other direct costs
- Gross margin percentage = Gross profit / Revenue