# Sales Rep Forecast A 12-month bottoms-up plan across 13 cohorts: existing reps plus one cohort per hire month, each ramping linearly to full productivity over user-set months. Monthly bookings, deals closed, ending headcount, fully ramped reps, and attainment vs target sit on one summary tab. - Canonical: https://finamodel.com/templates/sales-rep-forecast - Excel download: https://finamodel.com/templates/sales-rep-forecast.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Beginner - Audiences: CFOs & FP&A, Founders & operators, CROs, Sales operations, Founders - Tags: sales, headcount, ramp, quota, attainment ## Overview 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's included - 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 - 12-month bottoms-up forecast across 13 cohorts (existing reps plus one per hire month) - Cohort active headcount and ramp factor matrices, both formula-driven from a single ramp parameter - Per-cohort monthly bookings = active reps × ramp factor × monthly productive quota per rep - Total monthly bookings, cumulative bookings, and deals closed at average ACV - Headcount summary: ending headcount, annual new hires, fully ramped reps at M12 - Attainment vs annual company target, plus a headcount check row that resolves to zero ## 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. ## Built for bottoms-up planning A top-down target tells you the goal - a sales rep forecast tells you whether the team you are building actually lands it. This template lays cohort headcount, ramp, and bookings on parallel sheets so any gap is visible by month. ## Designed for hire-plan reviews Every input - existing reps, quarterly hires, ramp months, full quota, attainment, ACV, annual target - is a single named-range cell. Flex any one and the cohort matrices and Summary attainment line update instantly. ## Audit-friendly mechanics Every formula is one or two operations, every Assumptions row is referenced downstream, and the Summary headcount check row resolves to zero so the cohort math ties to the hire plan. ## Built for bottoms-up planning A top-down target tells you the goal - a sales rep forecast tells you whether the team you are building actually lands it. This template lays cohort headcount, ramp, and bookings on parallel sheets so any gap is visible by month. ## Designed for hire-plan reviews Every input - existing reps, quarterly hires, ramp months, full quota, attainment, ACV, annual target - is a single named-range cell. Flex any one and the cohort matrices and Summary attainment line update instantly. ## Audit-friendly mechanics Every formula is one or two operations, every Assumptions row is referenced downstream, and the Summary headcount check row resolves to zero so the cohort math ties to the hire plan. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: headcount plan, productivity, deal economics, target benchmark. - Existing reps and quarterly hires (Q1–Q4) - Full annual quota per rep, ramp months, attainment % - Average deal size (ACV) - Annual company target as the attainment benchmark ### Roster 13-cohort active headcount per month: existing reps plus one cohort per hire month. - Period and quarter helper rows - Existing reps cohort active for every month - M1–M12 hire cohorts active from start month onward - Active headcount total and new hires per month ### Ramp Cohort ramp factor (0%–100%) per month using a linear curve. - Existing reps pinned at 100% from M1 - M1–M12 hire cohorts ramp linearly over Ramp_Months - Mirrors the Roster cohort layout for direct cell-for-cell multiplication ### Bookings Cohort × month bookings, total bookings, cumulative bookings, and deals closed. - Cohort bookings = active reps × ramp factor × monthly productive quota - Monthly productive quota = Full_Quota × Attainment ÷ 12 - Total bookings = SUM of all 13 cohorts per month - Cumulative bookings and deals closed at ACV ### Summary Annual rollup: bookings, deals, attainment, ending headcount, fully ramped reps. - Annual bookings, deals closed, target, attainment % - Ending headcount at M12 and annual new hires - Fully ramped reps at M12 via SUMPRODUCT of ramp = 100% × active count - Full quota per rep, ramp months, average deal size for context - Headcount check row that resolves to zero when the cohort math ties ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: headcount plan, productivity, deal economics, target benchmark. - Existing reps and quarterly hires (Q1–Q4) - Full annual quota per rep, ramp months, attainment % - Average deal size (ACV) - Annual company target as the attainment benchmark ### Roster 13-cohort active headcount per month: existing reps plus one cohort per hire month. - Period and quarter helper rows - Existing reps cohort active for every month - M1–M12 hire cohorts active from start month onward - Active headcount total and new hires per month ### Ramp Cohort ramp factor (0%–100%) per month using a linear curve. - Existing reps pinned at 100% from M1 - M1–M12 hire cohorts ramp linearly over Ramp_Months - Mirrors the Roster cohort layout for direct cell-for-cell multiplication ### Bookings Cohort × month bookings, total bookings, cumulative bookings, and deals closed. - Cohort bookings = active reps × ramp factor × monthly productive quota - Monthly productive quota = Full_Quota × Attainment ÷ 12 - Total bookings = SUM of all 13 cohorts per month - Cumulative bookings and deals closed at ACV ### Summary Annual rollup: bookings, deals, attainment, ending headcount, fully ramped reps. - Annual bookings, deals closed, target, attainment % - Ending headcount at M12 and annual new hires - Fully ramped reps at M12 via SUMPRODUCT of ramp = 100% × active count - Full quota per rep, ramp months, average deal size for context - Headcount check row that resolves to zero when the cohort math ties ## Features - **Cohort-level ramp:** Each hire cohort follows its own ramp curve from start month onward, so a Q4 hire still ramping at year-end is sized correctly against a January hire that is already fully productive. - **Attainment vs target:** An annual company target sits as a named-range assumption; the Summary attainment line reads bottoms-up bookings against it so the model immediately shows the gap a quota plan needs to close. - **Audit-friendly cohort math:** Roster, Ramp, and Bookings share the same 13-row cohort layout so any cell traces straight back to its source. The Summary headcount check row resolves to zero when the hire plan ties to ending headcount. ## Use cases - **Hire-plan to quota check:** Pressure-test whether a planned hiring schedule actually delivers the annual target. Flex the ramp months or quarterly hires until attainment lands where the company has committed. - **Sales capacity model:** Use the bottoms-up bookings line as the operational counterpart to a top-down sales target. The gap between top-down and bottoms-up is the size of the hiring or productivity gap the org needs to close. - **Quarterly hire-plan review:** Hand the Summary to sales and finance leadership: ending headcount, fully ramped reps, attainment, and deals closed sit on one tab so the conversation is anchored in numbers. ## Frequently asked questions ### 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. ## Related templates - [Sales Model](https://finamodel.com/templates/sales-model) - [Hiring Model](https://finamodel.com/templates/hiring-model) - [Budget vs Actuals Tracker](https://finamodel.com/templates/budget-vs-actuals)