👋 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 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.
You’ll have everything to try this yourself.
How to try this yourself
Same workflow as last time. Download the data file, open it in Excel, fire up the Claude sidebar, and follow along.
Step 1: Let Claude audit the data
Screenshot both tabs — the Employee Roster and the Hiring Plan. Open Claude desktop (or the sidebar in Excel) and type:
PROMPT:
My People team sent me this HRIS export and hiring plan. I need to build a fully loaded headcount model with monthly phasing for 2026, rolled up by department. Before we build anything — what decisions do I need to make and what problems do you see?
Claude will surface the currency issue, the messy department labels, the missing bonus targets, the future-dated employee, and ask you a series of questions. Make your calls.
Step 2: Make the decisions
Here’s what Claude asked me and how I answered:
Currency conversion — what rate? → Lock in budget rates for the year. I used: GBP 1.27, EUR 1.09, CAD 0.74, INR 0.012. Don’t use spot. Don’t change mid-year.
Benefits loading — what percentages? → US 25%, UK 20%, Germany 20%, Canada 20%, India 15%. Contractors 0%.
Payroll taxes? → US 8%, UK 12%, Germany 20%, Canada 8%, India 12%. These are approximations. Your finance team should have exact rates from your PEO or EOR.
Bonus accrual for people missing a target? → Use the department average.
Employee with a future start date? → Pull them out of current headcount, treat them as a hire in the plan.
Missing budget salaries on reqs? → Use the department median for that location.
Sales ramp? → Full salary from day one. This is a cost model, not a productivity model. Flag the ramp separately for quota coverage planning.
Step 3: The build prompt
Then I asked Claude desktop to help me make a clean prompt to feed it to its brother in excel. This is the one I prepped:
PROMPT:
I need to build a fully loaded headcount model for 2026 in USD. Here’s the plan:
Create a ‘Clean Roster’ sheet. Normalize department labels to six departments: Engineering, Sales, Marketing, Customer Success, G&A, Product. Add a ‘USD Base Salary’ column — convert all local currency salaries to USD using these budget rates: GBP 1.27, EUR 1.09, CAD 0.74, INR 0.012. Flag EMP-1045 as ‘NOT YET STARTED’ — move them to the hiring plan. Fill missing bonus targets with the department average.
Create a ‘Cost Build’ sheet. For each employee calculate fully loaded annual cost in USD: base salary (converted), bonus accrual (base x target %), benefits load by location (US 25%, UK 20%, Germany 20%, Canada 20%, India 15%, contractors 0%), payroll taxes by location (US 8%, UK 12%, Germany 20%, Canada 8%, India 12%). Show each component and the fully loaded total.
Create a ‘Hiring Plan - Clean’ sheet. Same currency conversion and cost loading for all 18 reqs. Fill missing budget salaries with the department median for that location. Keep the priority and status columns.
Create a ‘Monthly Phasing’ sheet. Show the fully loaded monthly cost for every person — current employees and planned hires — by month (Jan-Dec 2026). New hires start costing from their target start month. Prorate the first month if they start on the 15th.
Create a ‘Department Summary’ sheet. By department show: current headcount, planned hires by quarter, ending headcount by quarter, fully loaded cost by month, and full-year total. Include a row for total company.
Keep the original data untouched. Rename the current sheets ‘Raw - Roster’ and ‘Raw - Hiring Plan’.
This is the prompt that builds the entire model. Let Claude run. Check each tab as it finishes.
Step 4: Check the output
Things to look for:
Clean Roster — Did it normalize all the department labels? Did it convert currencies correctly? Spot check: a London employee at £130,000 should show ~$165,100 in the USD column (130,000 × 1.27).
Cost Build — Does the benefits load match the location? A US FTE should show 25% of base. A contractor should show 0%. Check one row end-to-end.
Monthly Phasing — Do new hires only show cost starting from their target start month? Is the first month prorated for anyone starting on the 15th?
Department Summary — Does the total company row match the sum of the departments? Does ending headcount equal current plus hires?
Step 5: Run scenarios
Once the model is clean, this is where it gets fun. Try these:
PROMPT:
What happens to the 2026 budget if we delay all P2 priority hires by one quarter and cancel the two reqs that are On Hold? Show me the savings by quarter and the full-year impact.
PROMPT:
What if we moved two of the open San Francisco engineering reqs to Bangalore instead? Same roles, same level. What’s the cost difference?
That last one is the prompt I asked live on camera. The answer will vary depending on which reqs Claude picks, but the magnitude of savings is always eye-opening.
The assumptions table
Here are the rates and percentages I used. Swap in your own for your real model.
BUDGET EXCHANGE RATES (2026):
GBP to USD: 1.27 - EUR to USD: 1.09 - CAD to USD: 0.74 - INR to USD: 0.012
BENEFITS LOADING (% of base):
US FTEs: 25% - UK FTEs: 20% - Germany FTEs: 20% - Canada FTEs: 20% - India FTEs: 15% - Contractors (all locations): 0%
PAYROLL TAXES (% of base):
US: 8% - UK: 12% - Germany: 20% - Canada: 8% - India: 12%
What I’d do differently on your real data
Your PEO/EOR has exact tax and benefits rates. My percentages are approximations. Ask your People team or international payroll provider for the real numbers by country.
You probably have more cost categories. I kept it to base, bonus, benefits, and payroll taxes. Your model might need equity/SBC, stipends, hardware costs, recruiting fees per hire, or relocation packages. Add them as columns in the Cost Build tab.
Attrition. I didn’t model anyone leaving. If your annual attrition is 15-20%, your ending headcount will be lower than “current plus hires.” Some CFOs build in an attrition assumption, some don’t. I typically don’t include it in the budget model because it’s too hard to predict by department — but I always note the expected attrition rate in the board presentation as a sensitivity.
Mid-year raises and promotions. This model uses a static salary. If you do annual comp adjustments in Q1, you’ll want to phase in a raise assumption starting in that month. Add a “merit increase %” assumption and apply it to the right months.
Next time, we’re building something else. Thinking variance analysis. Let me know.
CJ
