Sales Rep Forecast
Corporate Finance Financial Model (Free Excel Download)
Plan sales capacity with hiring cohorts, ramp time, rep productivity, bookings, quota attainment, and payroll outputs aligned to revenue targets.
professionals from Deloitte
Used by professionals from
About this model
A sales rep forecast is a bottoms-up model that starts with the people on the sales team - existing reps plus a quarterly hiring plan - applies a per-rep ramp curve, and rolls each cohort's monthly productivity into a company-wide bookings forecast. The workbook is built around an Assumptions sheet that holds every driver as a named range, a Roster sheet that owns the cohort headcount matrix, a Ramp sheet that owns the ramp factor matrix, a Bookings sheet that multiplies the two through to dollars, and a Summary sheet that rolls the workbook into a one-page view.
The Roster sheet lays out 13 cohorts - existing reps plus one cohort per hire month from M1 through M12 - with each cohort's active count formula-driven from the period number and the quarterly hire plan. Cohort k's active count is the per-month hires for month k whenever the period number is greater than or equal to k, and zero otherwise. The Ramp sheet uses the same 13-row layout: existing reps are pinned at 100% productivity, and every hire cohort linearly ramps from 0% to 100% over Ramp_Months from the cohort's start month onward.
The Bookings sheet multiplies cohort active count × ramp factor × monthly productive quota (= Full_Quota × Attainment / 12) cell-for-cell, sums across cohorts for each month, runs a cumulative total, and converts to deals closed using the average deal size. The Summary sheet condenses the year into a one-page rollup: annual bookings, deals closed, annual target, attainment, ending headcount at M12, annual new hires, fully ramped reps at year-end (via SUMPRODUCT of ramp = 100% × active count), and a headcount check row that resolves to zero when the cohort math ties. CROs, sales operations, CFOs, and founders use the template to pressure-test whether a hiring plan delivers an annual target, to size the gap between top-down quota and bottoms-up capacity, and to anchor quarterly hire-plan reviews in numbers rather than narrative.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Sales Rep Forecast
- 12-month forecast across 13 cohorts (existing reps plus one per hire month)
- Quarterly hire plan split evenly into monthly cohort sizes
- Linear ramp from 0% to 100% productivity over Ramp_Months per cohort
- Cohort × month matrices for active headcount, ramp factor, and bookings
- Per-month total bookings, cumulative bookings, deals closed at average ACV
- Summary: annual bookings, deals, attainment vs target, ending HC, fully ramped reps
- Per-cohort monthly bookings = active reps × ramp factor × monthly productive quota per rep
- Headcount summary: ending headcount, annual new hires, fully ramped reps at M12
Sales Rep Forecast: How the Bottoms-Up Model Works
This sales rep forecast template builds a 24-month bottoms-up plan from individual rep cohorts, not top-down targets. It splits headcount by role and segment, applies role-specific ramp curves and scenario-driven attainment, then calculates bookings, compensation expense, and plan-versus-capacity gaps.
The public download contains a values-only preview; the underlying model captures the full calculation flow.
Operating Drivers and Scenario Architecture
The sales rep forecast is driven by a central Assumptions tab where every variable is scenario-toggled. A single dropdown selects Base, Bull, or Bear, and each scenario-sensitive input stores four values: three scenario columns and a Live column that chooses the active value.
- This means changing one dropdown updates hiring multipliers, attrition, ramp months, quota by role and segment, average deal size, win rate, expansion and renewal rates, and compensation accelerator. Hires can be entered quarterly by default or switched to a monthly grid for cell-by-cell control.
- An opening ARR figure anchors expansion and renewal calculations, while OTE and base-variable splits define compensation economics. The design keeps all drivers in one place, so readers can trace how a change in assumptions propagates through the model without hunting across sheets.
Calculating Ramp, Attrition, and Bookings
The model organizes reps into four role-by-segment grids: AE Enterprise, AE Mid-Market, SDR Enterprise, and SDR Mid-Market. Each grid contains an existing-reps cohort plus up to 24 monthly hire cohorts.
- On the Roster tab, every cohort applies a survival factor based on annual attrition, reducing headcount month by month. The Ramp tab converts each cohort's tenure into a linear productivity factor, capped at one when full ramp is reached.
- On the Bookings tab, those two factors multiply quota and weighted attainment to produce cohort-level bookings. Only AE grids feed new-logo bookings; SDR grids carry pipeline value for funnel sizing.
Below the grids, the model adds expansion and renewal bookings calculated from the opening ARR base, then tracks cumulative bookings, deals closed, opportunities required, and rolling NRR and GRR proxies.
Outputs and Summary Metrics
The Summary tab rolls everything into year-one and year-two columns. It reports gross bookings, new-logo attainment versus target, the gap to target, ending headcount, fully ramped reps, new hires, and reps lost.
- It also surfaces total compensation expense, compensation as a percentage of bookings, and a simplified CAC payback. A Plan versus Capacity block answers whether the ending AE headcount can carry the annual target, using a blended quota weighted by segment headcount.
- It shows capacity, the surplus or gap, and the number of additional reps needed to close any shortfall. The Dashboard and Cover tabs display key outputs and a bookings mix split by segment, expansion, and renewal.
The Checks tab runs integrity tests on headcount ties, cumulative bookings, attainment bucket sums, and named ranges.
Practical Use and Known Limitations
This sales rep forecast is useful for pressure-testing a hiring plan against productivity ramp and attrition assumptions. A Sensitivity tab provides two two-variable data tables that flex year-one bookings against attainment and hiring multiplier, and against deal size and win rate, so users can see how outcomes move across a grid.
- The model also exposes common pitfalls: late-year hires may not reach full productivity within the forecast horizon, switching to monthly hires without populating the monthly grid yields zero hires, and the annual target compares only to new-logo bookings unless the target is set to total bookings. Capacity uses ending headcount rather than average headcount, which makes the capacity view intentionally optimistic.
- The public download is a values-only preview, not a live calculation environment.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is a sales rep forecast model?+
A sales rep forecast is a bottoms-up plan that starts with the people on the team - existing reps plus a hiring schedule - applies a ramp curve to each new cohort, and rolls per-rep productivity into a company-wide bookings forecast. It is the counterpart to a top-down sales model that starts from a revenue target and back-solves the funnel.
How does the ramp curve work?+
Each hire cohort ramps linearly from 0% to 100% of full productivity over Ramp_Months from their start month onward. A rep hired in month k has ramp factor MIN(1, (current_month − k + 1) ÷ Ramp_Months). Existing reps are pinned at 100% from M1 because they are assumed fully ramped on day one.
Why are the per-month cohort hire counts fractional?+
Quarterly hires are split evenly across the three months of the quarter, so a quarter with three planned hires shows one rep arriving per month. The math is correct even when the per-month value is not an integer - the cohort active counts aggregate to the full headcount at year-end, which the Summary check row enforces.
Can I model attrition?+
Not in this version. The Roster matrix assumes a cohort retains all its hires for the rest of the year. Layer attrition by multiplying each cohort active count by a survival factor (e.g. (1 − monthly_attrition)^(months_since_start)), which slots into the Roster cohort formulas without changing the rest of the workbook.
How does this differ from the sales-model template?+
sales-model is top-down: it starts with an annual revenue target and back-solves monthly bookings, funnel stages, and pipeline coverage. sales-rep-forecast is bottoms-up: it starts with headcount and per-rep productivity and rolls up to total bookings. Use the two together to triangulate whether a hire plan supports the top-down quota.
Have more financial modelling questions? Contact us
Related templates
Sales Model
Top-down sales forecast with funnel volume and pipeline coverage analysis.
Hiring Model
12-month headcount, comp expense, and sales-productivity forecast across five departments.
Budget vs Actuals Tracker
12-month operating P&L variance tracker with YTD summary.
3 Statement Model
Integrated income statement, balance sheet, and cash flow forecasts.
