How to Build a Headcount Model Using Claude + Excel

👋 Hi, it’s CJ Gustafson and welcome to Mostly Metrics.

We’re back with tutorial number two in our series on how to use Claude and Excel.

Note: If you haven’t installed Claude for excel, say no more, Fam I got ya.

Last time I built an NRR cohort model from a messy revenue export. Today we’re going cost-side. Because 75% of costs at a software company walk on two feet (well, up until the world became tokenized… not crypto). Still though - you get headcount wrong, you get the whole budget wrong.

I’ve built this model a million times as an FP&A leader at Series B and C companies. If you know, you know. It’s the one that eats your entire day during planning season. Base salaries, bonus accruals, benefits loading, monthly new hire phasing, department rollups. And bless your heart if it’s multi currency (apparently someone makes $5m a year. Wait. That’s in rupees.)

So I hit record and built the whole thing live. Again.

The Tutorial

I took an HRIS export

65 employees,
Five currencies,
18 department labels for 6 departments (classic)
A hiring plan I stitched together from talking to department leaders (18 open reqs, two with no budgeted salary, half of them international… oh my).

Then I built a fully loaded headcount model with monthly phasing and scenario analysis using Claude and Excel.

It’s free. Everyone gets it. Hit play. Yippee.

What’s different from last time

In the NRR video I learned the hard way that a vague prompt gets you a super mediocre result. So this time I skipped the bad prompt and went straight to the specific one. Prepped it in Claude desktop first, pasted it into the Excel sidebar, let it ripppp.

But this video has its own curveball… currency.

The HRIS export has salaries in pounds, euros, rupees, and Canadian dollars all mixed in with USD. Nobody converted them. So before you can build a cost model you have to make a decision: what exchange rate do you use?

What Claude built

Six tabs. Sexy. Each one is a step in the model.

Clean Roster. Normalized the 18 department labels down to 6. Converted all salaries to USD. Flagged the employee with a future start date. Filled in the 9 missing bonus targets using department averages.
Cost Build. Every employee’s fully loaded annual cost, broken into components — base, bonus accrual, benefits load, payroll taxes. Benefits and tax rates vary by country. US employees load at 25% benefits and 8% payroll tax. Germany loads at 20% benefits but 20% payroll tax. Bangalore loads at 15% and 12%. Contractors get zero benefits. Each row tells you exactly what that person costs to employ.
Hiring Plan (Clean). Same treatment for all 18 open reqs. Currency converted, fully loaded, missing salaries filled with the department median.
Monthly Phasing. Every person — current and planned — with their fully loaded monthly cost across Jan through Dec 2026. I love this view. New hires drop in when they start. You can see the step function: January is $850K/month, December is $1.1M once everyone’s onboard. That ramp matters for cash planning.
Department Summary. Helps you build the board slide. Current headcount, planned hires by quarter, ending headcount by quarter, fully loaded cost by month, full-year total. One row per department, one row for total company.

The “wow” moment

The scenario analysis is where it gets fun.

I asked Claude what happens if:

We Delay certain hires by one quarter
And cancel reqs that are On Hold.

It recalculated the whole model and showed me the savings by quarter.

Then I asked for some takeaways.

Claude flagged that Engineering is a disproportionate share of cost because we hired senior in San Francisco when the plan assumed mid-level.
Sales hiring is front-loaded but quota coverage doesn’t catch up until Q3.
The gap between what we told the board headcount would cost and what it actually costs fully loaded

Then I asked something I didn’t plan (it’s my video, I can make up shit on the fly): what if we moved two of the open SF engineering reqs to Bangalore? Same roles, same level. The cost difference was real.

Pretty cool to test a new scenario that would have been like a 45 minute rebuild.

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 and the build prompt I pasted into Excel. Plus the scenario analysis prompts and the follow-up.
The raw data file. 65 employees across five currencies, plus 18 open reqs. Messy department labels, missing bonus targets, international hires — all baked in.
The decision walkthrough. Budget exchange rates, benefits loading by country, payroll tax rates, how to handle contractors, missing data, the future-dated employee. Every call I made and why.
The assumptions table. A clean reference you can swap in your own rates and percentages and run the same model on your real data.
The final file. The final result with the six new tabs.