How to Build a Sales Capacity Model Using Claude + Excel

👋 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 paid 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.