👋 Hi, it’s CJ Gustafson and welcome to Mostly Metrics.
Tutorial number three. We’re rolling now.
Note: If you haven’t installed Claude for excel, say no more, Fam I got ya.
We did Net Dollar Retention Cohorts. We did headcount planning. Today we’re modeling out Sales Capacity.
This is the bottoms-up build of your company’s revenue potential. Rep by rep, segment by segment, month by month. It answers the question every board asks: do we have enough productive capacity to hit the number?
Most companies fail to build enough capacity because they hire reps too late in the year.
So I hit record and built the whole thing live. And being the third tutorial, I at least know not to use a terrible prompt on the first try.
The Tutorial
I started with four tabs of data:
Rep roster: 23 sales reps across SMB, Mid-Market, and Enterprise. You’ll see the usual mess. There are nine segment label variants for three segments. There’s a rep on PIP, a rep who just resigned, and one making $280K OTE on an $800K quota (that’s a 2.9x ratio. Rule of thumb is at least 5x). This person is expensive and not carrying a big enough bag.
Hiring plan: 8 planned hires staggered throughout the year. Two have no quota assigned yet. Classic.
Supporting staff tab: Contains BDRs, SEs, and managers. And there’s one Enterprise BDR slot that says “OPEN - promoted to AE.” Like many companies, we’ve promoted the BDR and didn’t backfill.
Revenue targets: $12M new ARR, heavily back-loaded ($3.9M in Q4 alone). So we need reps ramped and productive before the buying season hits. Anyone hired after August is basically a 2027 investment.
The video tutorial is free. Everyone gets it. Hit play. Yay.
Claude Eating Claude
Quick note on the workflow. I do this thing I call Claude eating Claude. I design the prompt in Claude desktop first, then feed it to Claude in Excel. You end up with a way better prompt because it has the full context of your data before you ask it to build anything.
I told Claude desktop: “here’s my data across four tabs, I need a sales capacity model, what decisions do I need to make and what problems do you see?”
It surfaced the issues, asked me the right questions in multiple choice format, and then helped me write a specific build prompt based on my answers. Then I pasted that prompt into the Excel sidebar and let it cook.
The Decisions That Matter
Before Claude builds anything, I made the following calls on camera. This is the part most tutorials skip. And your model is only as good as the assumptions.
Ramp curves: SMB ramps in 4 months. Mid-Market takes 6. Enterprise takes 9. I went conservative because I’ve been burned before by assuming reps get productive faster than they actually do.
Over-assignment: I want 130% quota coverage for SMB, 135% for Mid-Market, 150% for Enterprise. Enterprise gets the most cushion because ramp is longer, attrition hurts more, and big deals push quarters. Heavy cushion. Again… been burned.
Expected attainment: Not everyone hits their number. Full stop. I modeled 85% for SMB, 75% for Mid-Market, 65% for Enterprise. In reality, your lucky if five or six out of ten reps hit 100%. Seven out of ten hit 80% or more. Plan accordingly.
The PIP rep: Zero capacity all year. If she turns it around, great; that’s upside I didn’t plan for. But I’m not banking the board plan on a PIP.
The resigned rep: Zero from April.
The 2.9x quota-to-OTE rep: Modeled a quota increase to $1.4M. Flagged it for the CRO. Either the quota goes up or we have to have an uncomfortable conversation about what that seat actually costs us.
What Claude Built
Clean Roster: Normalized segments.
Flagged the PIP, the resignation, the bad ratio, and the manager mismatch (one Mid-Market rep had an SMB manager listed).
It filled missing quotas on planned hires using the segment median.
It logged every change it made, which is exactly what you want for an audit trail.
Ramp Schedule: It shows every rep, every month, showing their ramp percentage.
The new Enterprise hire starting in February? Only at 50% by June. Not fully productive until October. And October is when we need them most.
The cool part: Claude made the ramp percentages editable at the top of the sheet. So if you see a different reality in the field (reps ramping faster or slower) you can change the assumptions without rebuilding anything.
Effective Capacity. This is the tab that matters the most.
Not “how much quota is on the street”… that number is as fake as a three dollar bill.
This is how much we can realistically expect to produce, adjusted for ramp, over-assignment, and attainment.
There’s a big difference between raw quota and realistic capacity. Each rep’s monthly number gets run through the ramp schedule and then haircut for over-assignment and attainment.
The totals roll up by segment and total company.
Coverage Analysis: Shown by segment, by quarter, against the revenue target.
And there it is: Enterprise is only 60% covered from Q2 onward.
SMB is in the red all year.
Total company: $9.85M in realistic capacity against a $12M target.
A $2.15M shortfall.
So we’re at only 82% coverage.
Sanity Checks. I fed Claude a piece I’d written on the sanity checks I always run at the end of a capacity model. It flagged:
Quota-to-OTE ratio for every rep: one flagged red at 2.9x.
Implied deal volume per rep per quarter: can an SMB rep do 10 deals a quarter? Probably. Can an Enterprise rep close 3 deals at $150K per quarter? That’s a lot.
BDR-to-rep ratios. SE-to-rep ratios.
Manager span of control.
And the quota carrier percentage: 59% of the sales org are individual quota carriers, above the one-third minimum.
Pod Economics. Cost to run each segment’s sales pod (reps, managers, BDRs, SEs) as a percentage of that segment’s revenue target.
SMB costs $0.61 for every dollar of revenue.
Mid-Market is $0.60.
Enterprise is $0.42 (best ratio by far)
Which makes the coverage gap even more painful.
This is the segment with your best unit economics, and it’s the one that’s short on capacity.
For readers
Video is free. Below is everything you need to build this yourself.
The exact prompts: The discovery prompt I used in Claude desktop, the build prompt I pasted into Excel, and the scenario analysis prompts.
The raw data file: 23 reps, 8 planned hires, 16 supporting staff, and quarterly revenue targets. All the messiness baked in.
The decision walkthrough: Ramp curves, over-assignment targets, attainment assumptions, how I handled the PIP, the resignation, the bad ratio, and the missing BDR. Every call and why.
The assumptions table: Ramp percentages by segment by month, over-assignment targets, attainment rates, average ACVs for deal volume math. Swap in your own and run it on your real team.
The final file. The completed model with all the new tabs.
You’ll have everything to try this yourself.
Step 1: Design the prompt in Claude desktop
I call it Claude eating Claude. Open Claude desktop, screenshot your four tabs, and type:
PROMPT:
I need to build a sales capacity model for 2026. I have a rep roster, a hiring plan, supporting staff, and revenue targets. Before we build anything — what decisions do I need to make, what problems do you see, and do we have enough capacity to hit our number?
Claude will surface the segment label mess, the PIP, the resignation, the bad quota-to-OTE ratio, the missing BDR backfill, and the missing quotas on planned hires. It’ll ask you to make a series of decisions before building. Make your calls, then ask Claude to write you a specific build prompt based on your answers.
Step 2: Make the decisions
Here’s what I chose:
Ramp curves (conservative):
SMB 4 months (20/40/60/80/100).
Mid-Market 6 months (15/25/40/60/80/95/100).
Enterprise 9 months (10/15/20/30/40/55/70/85/95/100).
Over-assignment: SMB 130%. Mid-Market 135%. Enterprise 150%.
Expected attainment: SMB 85%. Mid-Market 75%. Enterprise 65%.
PIP rep: Zero capacity all year.
Resigned rep: Zero from April.
2.9x quota-to-OTE rep: Modeled quota increase to $1.4M. Flagged for CRO.
Missing quotas on planned hires: Used segment median.
Step 3: The build prompt
This is what Claude desktop helped me write, and what I pasted into the Excel sidebar:
PROMPT:
I need to build a sales capacity model for 2026. Here’s the plan:
Create a ‘Clean Roster’ sheet. Normalize segments to SMB, Mid-Market, Enterprise. Set REP-3008 (PIP) to zero capacity all year. Set REP-3020 (Resigned) to zero from April. Flag REP-3015 — quota-to-OTE ratio is 2.9x, below the 5x minimum. Model his quota at $1.4M instead of $800K. REP-3018 shows Lisa Tran as manager but she’s SMB — flag as ‘MANAGER MISMATCH.’ Fill missing quotas on HIRE-4003 and HIRE-4006 using the segment median quota.
Create a ‘Ramp Schedule’ sheet. For each rep show their monthly ramp percentage Jan-Dec 2026 based on start date and segment. Conservative curves: SMB 4 months (20/40/60/80/100). Mid-Market 6 months (15/25/40/60/80/95/100). Enterprise 9 months (10/15/20/30/40/55/70/85/95/100). Any rep who started before July 2025 is at 100%. Include planned hires at their target start dates.
Create an ‘Effective Capacity’ sheet. For each rep by month: annual quota × (ramp % / 100) × (1/12) = monthly effective quota capacity. Then adjust for over-assignment and expected attainment. Over-assignment: SMB 130%, MM 135%, Ent 150%. Attainment: SMB 85%, MM 75%, Ent 65%. Realistic capacity = effective quota ÷ over-assignment × attainment. Sum by segment and total company.
Create a ‘Coverage Analysis’ sheet. By segment by quarter: sum realistic capacity vs revenue target. Show coverage ratio. Flag any quarter below 100% in red. Show gap in dollars.
Create a ‘Sanity Checks’ sheet. Show: quota-to-OTE ratio for every rep (flag below 5x), implied deal volume per rep per quarter (ACVs: SMB $10K, MM $40K, Enterprise $150K), BDR-to-rep ratio by segment, SE-to-rep ratio, manager span of control, and % of sales org that are quota carriers (target: 33%+).
Create a ‘Pod Economics’ sheet. Show total cost of each segment’s sales pod — reps, managers, BDRs, SEs — as a percentage of that segment’s revenue target. Flag the open BDR backfill in Enterprise as a risk.
Keep original data untouched.
Fair warning: this one takes longer than the previous builds. We asked it to do a lot. But it comes back with everything.
Step 4: Check the output
Things to verify:
Clean Roster: Did it log the changes? PIP zeroed out, resigned rep flagged, quota override on the 2.9x rep, manager mismatch noted.
Ramp Schedule: Do tenured reps show 100%? Do new hires ramp from their start month? Are the ramp percentages editable at the top of the sheet so you can change assumptions?
Effective Capacity: Does the PIP rep show zero? Does the resigned rep drop off in April? Is the realistic capacity meaningfully lower than raw quota? It should be.
Coverage Analysis: Where are the red flags? Enterprise should be well under 100% from Q2 onward. Total company should show the $2.15M gap.
Sanity Checks: Is the 2.9x ratio flagged? Does the implied deal volume look reasonable? Are BDR and SE ratios balanced?
Pod Economics: Does Enterprise show the best cost-to-revenue ratio? Is the open BDR flagged?
Step 5: Run scenarios
PROMPT:
Given the current roster, hiring plan, and ramp schedules, what is our probability of hitting the $12M new ARR target? Where are we short? What would you recommend to close the gap?
PROMPT:
What if we accelerated the two Enterprise hires by one month each and added a backfill BDR for Enterprise? Show me the impact on Q3 and Q4 coverage.
Spoiler: the second one barely moves the needle. $109K recovery on a $2M gap. You can’t out-accelerate a 9-month ramp cycle. You need net new recs.
The assumptions table
Swap in your own numbers for your real model.
The raw data
What I’d do differently on your real data
Your ramp curves are unique to your business. Mine are approximations. Look at your last 10 hires by segment — how long did it actually take them to hit 75% of quota? Use that, not the textbook answer.
Attrition is real. I didn’t model anyone leaving beyond the one resignation. But annualized attrition on sales teams runs 20-30%. A lot of reps quit when they’ve determined they won’t hit their number. Build in a buffer or at minimum note it as a sensitivity.
New vs. expansion mix matters. I didn’t split quota between new logos and expansion in this model. If your reps hit their number through a mix of both, you need to model the appropriate split — especially for new hires inheriting no accounts. They’re making quota off 100% new deals. Make sure that’s reflected.
Don’t use efficiency gains as a plug. Every year someone says “the salesforce will just get better.” Better systems, longer tenure, brand awareness. It’s all wishful thinking to make the math work. Don’t do it.
BDR backfills are non-negotiable. If you promote a BDR to AE — which is great for morale and cost — you need to backfill that BDR immediately. Otherwise your AEs, new and old, won’t have enough leads to chase. It’s cheap talent. Don’t let it lapse.
Do the rep math. Even if the dollars work on paper, check the physics. How many deals does each rep need to close per quarter? If the implied volume doesn’t pass the smell test, either your quotas are too high or your ACV assumptions are off.
CJ
