Automate Commission Calculation After a Key Person Quits

The Day the Spreadsheet Walked Out the Door

A window-covering company called me with a problem that started the way these things always do. Their operations admin quit. Friendly exit, two weeks notice, no drama. And then payroll came around and nobody could pay the sales reps.

The five-panel commission spreadsheet structure

She had been calculating commissions twice a month by hand. Not in some tidy payroll system. In a spreadsheet. And not even a normal spreadsheet, but a five-panel side-by-side monster, one block of columns per rep, that she had built and maintained in her head as much as on the screen.

Panel one was the base commission. Panel two handled trade-partner splits, where a sale got divided between the rep and an outside partner. Panel three was what they called "oops deductions," chargebacks and corrections when something went sideways on an order. Panel four was a quarterly forgiveness bank, a running balance that softened those deductions over time. Panel five tracked revisions, because orders changed after they were booked.

Five panels. Per rep. Twice a month. All applied from memory.

When she left, the process left with her. There was no documentation. No written rules. The logic existed as a set of habits in one person's hands, and those hands were now somewhere else.

The stakes were not abstract. Reps were going to get paid wrong, or late, or both. The owner could open the file and see numbers, but he could not explain how any of them got there. If a rep disputed a figure, nobody on staff could defend it.

This is the buyer's nightmare in its purest form. A key person took critical institutional knowledge with them, and it walked out the door in their head.

So here is the question this whole article answers: can AI rebuild that knowledge, fast, and get it right.

Why Manual Commission Spreadsheets Always Become a Single Point of Failure

The knowledge lives in one head

Manual commission calculation concentrates risk in one person because the rules are not written anywhere. They are encoded in habits. The admin knew that Rep A was on a different tier than Rep B, that card fees came off before the split on certain order types, that the forgiveness bank topped up every quarter. None of that was a document. It was muscle memory.

That is the trap. The process works beautifully right up until the person running it is gone, and then it works not at all. There is no halfway state. You either have the institutional knowledge or you do not.

Two formats, one process

This company had an extra layer of pain. They had a current sheet format and a legacy format from an older system, and only the admin knew how to reconcile the two. Older orders lived in one structure. Newer ones lived in another. She moved between them without thinking, applying the right logic to each.

Per-rep tiers, card fees, splits, all of it pulled from memory and applied across two incompatible formats. When she left, that reconciliation skill left too.

The real cost here is not her salary. It is the bus-factor risk, the single point of failure, and the fact that errors in a manual process compound silently. Nobody is checking the checker. A small mistake in how a split gets applied can run for months before anyone notices, if anyone ever does.

This is the part most owners miss. Replacing a manual spreadsheet process is rarely about speed first. It is about making the logic legible and auditable. You want the rules out of someone's head and into a system where they can be read, tested, and defended. Speed is a nice side effect. Survivability is the point.

Step One: Parse the Real Pay Sheets, Both Formats

The instinct when you inherit a mess like this is to redesign it. Build a clean schema, define the perfect data model, ask everyone to start entering data the right way.

That instinct is wrong, and it is how you lose the institutional knowledge you were trying to save.

I started by parsing the actual files. Not a hypothetical clean version. The real, messy sheets, in both the current and the legacy format. The engine had to read both and normalize them into one internal structure without anybody re-keying a thing.

This matters more than it sounds. The data is the documentation. Whatever the admin actually did is sitting in those sheets. If I redesign the input, I throw away the only record of how the process really worked. So I met the data where it was.

The parser pulled the fields that mattered, anonymized here but representative: order number, rep, sale amount, split partner, deduction, forgiveness bank balance, and a revision flag. Some of those lived in different columns depending on which format a given file used. The engine handled both and mapped them into one consistent shape.

You preserve institutional knowledge by encoding what the sheets actually contained, not what they should have contained in some ideal world. The legacy format was ugly. It stayed. The current format had quirks. They stayed too. Every weird edge in those files was a clue about a rule the admin applied, and I treated each one as a thing to reproduce, not a thing to clean up.

By the end of step one I had every historical run in a single normalized structure. No interpretation yet. Just the raw truth, read correctly from both worlds.

Step Two: Resolve Each Commission Rate As-Of Its Sale Date

The effective-dated rates problem

Here is where most automations quietly break. Each order's commission rate has to be resolved as of the date it was sold, not the rate the rep has today.

Reps get raises. They move up tiers. The rate that applies to an order booked in March is the rate that was in effect in March, even if you are calculating the payout in September and the rep is now on a higher tier.

This is the effective-dated rates problem, and it is everywhere in pay logic. Naive automations grab the current rate and apply it to everything. The math runs clean. It produces a number. And the number is wrong.

Why a raise can't rewrite the past

If a rep got a raise in June, that raise cannot reach backward and rewrite what they earned in April. The past is settled. A new rate applies going forward, not retroactively.

Effective-dated rates: applying the right rate as of the sale date

When you apply today's rate to old orders, you silently overpay or underpay, and you do it in a way that is almost impossible to catch by eye. The total looks plausible. Nobody flags it. The error just sits there.

So the engine stores a rate history per rep, with effective dates, and for every order it looks up the rate that was valid on that order's sale date. Order from April gets April's rate. Order from July gets July's rate. The raise is real, but it only affects what comes after it.

A frontier model can help you reason about this kind of edge case and spot it in messy data. But the actual lookup and computation have to be deterministic. You cannot have a language model deciding which rate applied on which date. That is exactly the kind of money-moving logic I keep deterministic by design, and effective-dated rates are the textbook case for why.

Step Three: A Math Engine With No AI in the Money Path

Pure functions, verified by tests

The calculation engine is pure math. Tiers, card fees, splits, deductions, the forgiveness bank, revisions, all applied deterministically. Same inputs, same outputs, every single time.

No AI decides a dollar amount. Ever. The model never touches the money path.

Every piece of the calculation is a pure function with tests behind it. I wrote test cases for the tier logic, the split logic, the card-fee timing, the forgiveness-bank rollover. When you change one rule, the tests tell you immediately whether you broke another. That is how the rules become legible and auditable instead of living in someone's habits.

This is the opposite of the spreadsheet. In the spreadsheet, you trusted the admin. In the engine, you trust the tests, and anyone can read them.

The ~$2,400 overpayment recompute caught

Here is the part that turned a skeptical owner into a believer.

Once the engine was built, I recomputed the historical payouts. Ran the real past data through the deterministic math to confirm it matched what the admin had paid out by hand.

It did not match. Not because the engine was wrong. Because the engine was right.

It caught a roughly $2,400 overpayment that the manual process had missed. Somewhere in the hand calculations, a rule had been applied loosely, and a rep had been paid more than the rules said they earned. Nobody had ever caught it because nobody could check the work.

That is the real trust-builder. The automation did not just match the human. It found money the human missed. When money moves, the logic stays deterministic, and this recompute is exactly why.

Step Four: An AI Reviewer for Judgment, With a Deterministic Fallback

Where the model actually helps

Now I will tell you where AI does belong, because it absolutely does.

AI judges, code calculates, the money path split

After the deterministic engine produces a run, a frontier-model reviewer reads the whole thing and produces plain-English judgment. It flags anomalies. It explains why one rep's payout jumped. It notices a revision that looks off, or a deduction that does not fit the pattern, and it says so in language the owner can read.

What it does not do is compute payouts. It reads the numbers the engine produced and reasons about whether they look sane. That is judgment, and judgment is what models are genuinely good at.

This is the split I build into every money system: let the AI judge and the code do the math. The model interprets. The code calculates. Cross that line and you get confident, fluent, wrong numbers.

The fallback when the model is unavailable

Models go down. APIs time out. Sometimes a model returns garbage. You cannot have payroll depend on a healthy API call.

So there is a deterministic fallback. If the model is unavailable or returns something unusable, a set of rules still runs the anomaly checks. Outlier payouts, splits that do not balance, deductions over a threshold, all flagged by code, no model required.

The AI reviewer makes the review richer and more readable. But the system never goes blind without it. AI payroll automation is only safe when the model interprets and the code both calculates and backstops.

Step Five: A Human Approves Every Run

Nothing auto-submits. I want to be very clear about that, because it is the thing a careful owner worries about most.

Every commission run is computed by the deterministic engine, reviewed by the AI, and then presented to a human who approves it before any money moves. The owner sees the numbers, sees the flags, sees the plain-English summary, and signs off. Only then does anything go to payroll.

This is the every-run-stops-for-a-human approach, and for commissions it is not optional. The cost of a wrong payout is real money and real trust with your sales team. You keep a person in the loop.

Let me be honest about what this does not do. It does not eliminate the human. There is still someone clicking approve twice a month.

But that is by design, not a limitation. The goal was never to remove the person. The goal was to remove the single point of failure and the silent errors. The old process had one person who was the system. The new process has a system that one person reviews. Those are completely different risk profiles.

If the approver leaves now, the next person can step in by reading the rules, the tests, and the flagged summary. The knowledge no longer walks out the door.

What It Takes to Rebuild a Departed Person's Process

So, can AI rebuild a manual process after the only person who understood it is gone.

Single point of failure vs. inspectable system, before and after

Yes. I rebuilt this one over a weekend. But the weekend only worked because of the discipline, not in spite of it.

You parse the real artifacts, both formats, exactly as they exist. You keep the money math deterministic and verified by tests. You encode the edge cases, the effective-dated rates, the legacy format, the forgiveness bank, the revision flags, instead of pretending they do not exist. You put a frontier model on judgment, never on calculation, with a fallback for when it is unavailable. And you keep a human in the approval seat.

Get those right and the speed follows. The same approach works for rebuilding any departed employee's process, not just commissions.

The outcome for this company: commissions now run as code. Discrepancies get flagged automatically. There is an audit trail showing exactly which rate applied to which order and why. The logic is readable, testable, and defensible. And it caught $2,400 the old way had missed.

They went from one person's memory to a system anyone can inspect.

If there is a manual process in your business that only one person truly understands, a spreadsheet that would be a crisis if its owner quit, that is exactly the kind of risk I rebuild as a system. Not to replace the person. To make sure the company survives the day they leave.

Want to explore what AI could do for your business?

Book a free 30-minute strategy call. No pitch deck, no sales team, just a real conversation about your operations and where AI actually fits.

Book a Discovery Call