Using AI for Compensation Analysis

This post is about how I use AI for compensation analysis as a working comp practitioner starting from security setup, audit trails, formula QC, and when to step out of the chat.

Excel has been the cornerstone of comp analysis for decades, and nothing has seriously threatened that. I know that, and my test for any future hires was always whether they use Vlookup, Xlookup or Index. And like many others I am dreaming of a day when it all gets easier and faster. As you can notice from my blog, I like coding, but I don’t like to actually write code.
Earlier AI models couldn't read a spreadsheet without choking on a merged cell, let alone produce one. So, I initially gave up on trying to use AI with Excel, there was too much work needed after to verify results. And one thing that I like even less than writing code is to QC code that somebody else wrote. QCing Excel formulas is the worst version of it, cause there is no debugging tool.
Thank you technology gods, that's changed. AI companies pivoted from building AI characters that can tell you about anything using goblins as terminology to creating practical tools that can actually help you be more productive without understanding the difference between goblins and gremlins.

The current generation of models treats Excel as a first-class format, they can ingest files with thousands of records, run real analysis on them, and hand you back a workbook that opens cleanly. For me as a comp practitioner, this means I can now do sophisticated equity distribution analysis end-to-end without spinning up Power BI or Tableau and stitching together five follow-up sheets to support them. The AI does the analysis, the visualization, and the file production.

It's not perfect by any means. Models still drift, hallucinate column headers, and occasionally invent a formula that looks right and isn't. The technology is improving constantly through RLHF and other methods, which is a polite way of saying it's also changing under you constantly.

But it’s at the point now where if you can manage the workflow you have massive productivity upside.

Here's how I actually work with AI on comp analysis.

1) Stay inside the security perimeter.

Before anything else: make sure the AI you're using sits inside whatever data boundary your IT and security teams have drawn. Pasting a roster of base salaries and equity grants into a consumer chatbot is how comp data ends up training a model and, eventually, surfacing somewhere it shouldn't. This isn't paranoia, it's the same instinct that keeps you from emailing a comp file to your personal Gmail (I hope). If you are not sure about the current AI setup at your company - ask your IT team first.

Even with the perimeter set, think about what actually needs to enter the AI session. Three practical moves.

First, talk to legal to confirm which fields the company doesn't want flowing into AI under any circumstance — typically SSN, DOB, home address, sometimes performance ratings or termination reasons. Get the list documented before you upload anything.

Second, before you anonymize anything, ask whether you even need it. HRIS exports are notoriously bloated — they'll hand you forty columns when your analysis uses twelve. Drop the fields you don't need rather than anonymizing them. It's faster, cleaner, and removes the data risk entirely instead of just masking it.

Third, for everything that does go in and contains identifying information, work off pseudonymized identifiers — replace names with EE001, EE002 before upload, and keep the name-to-ID mapping in a local file outside the AI session. The mapping never enters the chat. And yes, this has to happen before upload, not by asking the AI to anonymize for you — if AI is doing the anonymizing, it's already seen the raw data. For large dumps you can't scrub by hand, this is a one-time script your analytics team can write once and you reuse. The discipline is upstream of the AI, not inside it.
If you need to analyze personal data — e.g. compare SSNs in your HRIS with data from a 401(k) provider — and you don't have the bandwidth to do it manually, make sure everything else is unidentifiable. And check your assumptions with the legal team.

2) Audit files, always.

When I'm doing large data dumps — think 700 payroll periods merged with equity and headcount snapshots — I ask the AI to produce an audit file alongside the analysis and keep it as a persistent artifact. Row counts in, row counts out, dropped records logged, joins documented. It takes the AI thirty seconds and saves you the afternoon you'd otherwise spend reconstructing what happened when someone questions a number.

3) Make it show its work in formulas.

This is the one I'd put on a poster. Models tend to run calculations in Python and then drop the results into Excel as static values — clean numbers, no traceable math. Let them do that first, because Python is genuinely the stronger calculation engine and the underlying math is likely solid. Then ask the AI to rebuild the same calculations as Excel formulas in the file. Now you have two independent computations of the same result, and the AI can QC the formula output against the Python output. It catches errors in both directions, and frankly, it's nearly impossible for a human to verify formulas across a large analysis file by eye. This double-pass is the closest thing to peer review you get when you're working alone. It also means the finance partner, auditor, or HRBP who reviews your work — and who probably doesn't use AI the way you do — can audit the math the way they always have, by reading formulas in cells. Your analysis stays reviewable by people who don't share your toolkit.

4) Use Excel's memory logic, not the AI's.

If you'd naturally split an analysis across two workbooks, make the AI split it too. Models have a tendency to produce one enormous file containing everything, which then takes forever to open, freezes on recalculation, and becomes painful to share. Tell it explicitly how to partition the output. The AI will happily build a 40-tab monster if you don't push back.

5) Keep an insights markdown file running.

For any analysis you'll eventually have to present, ask the AI to maintain a markdown file of insights as you work. Every time you surface something that belongs in the executive summary — a distribution skew, an outlier cohort, a year-over-year shift — have the AI append it. The longer a chat runs, the more its working memory drifts, and at some point you can't trust it to recall what it found in tab 3 ninety minutes ago. A standalone insights file is your real deliverable scaffold.

6) Re-export anything that came in as PDF or screenshot.

If your inputs include market data from a PDF survey or a screenshot of a grant table, ask the AI to recreate that data as a clean Excel file and save it separately. This gives you something to return to when a number later doesn't reconcile. PDF extractions go wrong quietly — a misread digit, a column shifted one row — and having the structured version on hand is what lets you find the break.

7) Track your data mapping.

Separate from the insights file, ask the AI to maintain a markdown file documenting how data flows across your output files and any Python artifacts it generates. Source file → transformation → destination file → tab → column. This is the document that lets a colleague — or future-you — pick up the analysis cold and understand what came from where.

8) Make AI reconcile to source.

Audit files (point 2) tell you whether rows survived the analysis. Reconciliation is a different question — whether the totals tie back to your system of record. Tell the AI explicitly which numbers must reconcile: total comp dollars by department, headcount by location, sum of grants by grant date — whatever your CFO or auditor is going to ask first. Have it produce a reconciliation tab in the output workbook showing input total, output total, and variance. AI won't propose these checks unprompted; you have to specify them. This is the difference between "the analysis ran" and "the analysis is defensible."

9) Know when not to AI.

AI is a workhorse for exploratory work, draft analysis, and most operational comp tasks. It's the wrong tool for analysis you're going to sign your name to in a regulatory or board context — pay equity regression in particular. Regression results are easy to accept and very hard to defend if you didn't produce them yourself. For pay equity specifically, I'd build a standalone app with a verifiable algorithm rather than running it through an AI session — I wrote about how to do that here. Same logic applies to proxy and CD&A inputs, or anything landing in front of the comp committee. AI for speed; human-built tooling for accountability.

10) Build a starter kit for recurring analyses.

Quarterly comp analysis is coming back, and AI memory won't survive between sessions. Don't rely on it — externalize what you need into files. A recurring-analysis kit should contain: an input template (the exact column structure your data needs to land in), an output template (the file structure you want produced), a recipe markdown (the actual prompt and instructions you used last cycle, including calculation logic), the prior insights markdown, and the Python script the AI generated. Save them together. Next quarter, you feed the AI the kit plus new data instead of starting from a blank prompt and rebuilding from memory. This is the closest thing to reproducibility AI currently offers — you're not trusting the model to remember, you're moving the memory into version-controlled files you own.

11) Maintain a terminology file — build during, complete after.

Comp has a lot of terms AI consistently gets wrong: target vs. actual, grant date vs. vest date, FMV vs. strike, gross vs. net. You'll bump into misinterpretations mid-analysis. Two approaches, both with value. During the session: every time you correct the AI on a term, have it append the correction to a terminology markdown — definition plus where the term lives in your data ("vest date = column J in grants_input.xlsx"). After the session: do a summary pass once the analysis is verified, capturing terms that came up and how you defined them in this particular dataset. The in-session capture catches things in context, while their meaning is fresh. The after-session pass forces a complete review and tends to be cleaner. Most practitioners will end up doing both — append as you go, audit at the end. The terminology file becomes part of the starter kit in point 10 and travels with you into next quarter's work.

Conclusion

Excel isn't going anywhere this year, or probably next. But the trajectory is clear — at some point the workflow flips, and AI stops being the tool that helps you build the spreadsheet and starts being the environment the analysis lives in. We're not there yet. The models still drift, the sessions still forget, and the discipline of audit trails and reconciliation still has to come from you. What's changed is that the leverage is real today, not theoretical. A comp team that builds the workflow above can do analysis at a depth and speed that wasn't available eighteen months ago, with output a finance partner can still review the old way. That's the win to take now. The full replacement will arrive on its own schedule.