06-reference

mostly metrics sales capacity model claude excel

2026-06-18·reference·source: Mostly Metrics·by CJ Gustafson
sales-capacityclaude-aiexcelrevenue-planningsales-ops

"How to Build a Sales Capacity Model Using Claude + Excel" — @CJGustafson

Why this is in the vault

This is the third in CJ's Claude-in-Excel tutorial series, and the most operationally dense — it shows exactly how to build a bottoms-up sales capacity model (rep-by-rep, segment-by-segment, month-by-month) using Claude as the FP&A co-pilot inside Excel. The "Claude eating Claude" workflow — designing the prompt in Claude Desktop first, then feeding it to Claude in Excel — is a directly transferable pattern for RDCO's COO-agent stack. The coverage analysis output ($9.85M realistic capacity vs $12M target = 82% coverage, $2.15M shortfall) is the exact type of decision-surface RDCO would want to produce for clients doing GTM planning.

The core argument

Sales capacity models fail not because the math is hard but because the assumptions are wrong and the data is messy. CJ's tutorial demonstrates that Claude handles both: it normalizes dirty rep roster data (nine label variants for three segments, PIP reps, resignees, bad OTE ratios), fills missing quotas using segment medians, logs every change for an audit trail, and then builds a multi-tab model from a well-structured prompt. The how-to runs as follows:

Data setup — four tabs:

The "Claude eating Claude" workflow:

  1. Paste all four tabs into Claude Desktop.
  2. Prompt: "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?"
  3. Claude surfaces data issues, asks clarifying questions in multiple-choice format, then co-authors a specific build prompt based on your answers.
  4. Paste the refined prompt into Claude for Excel sidebar. Let it build.

The workflow is superior to single-shot prompting because the build prompt has full data context before it asks Claude to construct anything.

Assumption decisions made on camera (the part tutorials skip):

Decision SMB Mid-Market Enterprise
Ramp curve 4 months 6 months 9 months
Over-assignment 130% 135% 150%
Expected attainment 85% 75% 65%
PIP rep 0% capacity all year — —
Resigned rep 0% from April — —

Over-assignment rationale: Enterprise gets most cushion because ramp is longer, attrition hurts more, and big deals push quarters. Attainment assumption: plan for 70% of reps hitting ~80%+, not everyone at 100%.

What Claude built (the six output tabs):

  1. Clean Roster — normalized segments, flagged PIP/resignation/bad OTE ratio/manager mismatch, filled missing quotas from segment median, logged every change.
  2. Ramp Schedule — every rep, every month, ramp percentage. New Enterprise hire starting February is only 50% productive by June; fully ramped October. Ramp percentages are editable inputs at the top.
  3. Effective Capacity — the load-bearing tab. Not raw quota (that number is fake). Each rep's monthly number is run through ramp schedule, then haircut for over-assignment and attainment. Rolls up by segment and total company.
  4. Coverage Analysis — by segment, by quarter, against revenue target. Result: Enterprise 60% covered from Q2 onward, SMB in the red all year. Total: $9.85M realistic capacity vs $12M target — 82% coverage, $2.15M shortfall.
  5. Sanity Checks — quota-to-OTE ratio per rep (one flagged red at 2.9x), implied deal volume per rep per quarter, BDR-to-rep ratios, SE-to-rep ratios, manager span of control, quota carrier % (59% — above the one-third minimum).
  6. Pod Economics — cost to run each segment's pod (reps + managers + BDRs + SEs) as % of that segment's revenue target. SMB: $0.61/dollar. Mid-Market: $0.60/dollar. Enterprise: $0.42/dollar (best unit economics). Which makes the Enterprise coverage gap the most painful: the cheapest segment to run is the one that's short on capacity.

Paywalled section (prompts + raw data file + completed model) requires a paid Mostly Metrics subscription: https://www.mostlymetrics.com/upgrade

Mapping against Ray Data Co

Direct workflow transfer — Claude eating Claude: The pattern of using Claude Desktop to audit messy data and co-author a build prompt before doing the actual Excel build is directly applicable to RDCO's COO-agent stack. When a client shares a CSV of headcount/quota data, the right first move is a Claude Desktop diagnostic pass ("what problems do you see?") before dispatching the Excel builder. This is already consistent with how RDCO's multi-step sub-agent pattern works — the subagent that does discovery should inform the subagent that does construction.

As a client deliverable: RDCO's DSA role at phData involves discovery/scoping on data-intensive deals. A pre-baked sales capacity audit — rep data in, coverage gap out — is a high-value entry-point engagement for any mid-market SaaS prospect. The six-tab model structure (Clean Roster → Ramp → Effective Capacity → Coverage → Sanity Checks → Pod Economics) is a plug-and-play deliverable template.

Coverage gap as the board-ready output: The $9.85M vs $12M framing (82% coverage, $2.15M shortfall) is the exact form a board or CRO wants to see. RDCO building this for a client — in under an hour using Claude in Excel — is a concrete demonstration of the AI-native analyst value prop.

The 2.9x quota-to-OTE flag: CJ's model flags reps with quota-to-OTE ratios below 5x as structurally misaligned. This is a useful heuristic for any RDCO client review of sales comp structure, complementing the comp plan design framework from the 2026-06-16 Mostly Metrics note.

Limitation — solo-founder context: Ray has no sales reps, so this tutorial isn't directly self-applicable today. But (a) the workflow technique generalizes to any multi-tab data normalization task, and (b) this becomes directly relevant the moment RDCO is advising a client with a sales org or building a GTM model as a consulting deliverable.

Related