Automate Commission Calculation After a Key Person Quits (Simply Explained)

The Day the Spreadsheet Walked Out the Door

A window-covering company called me with a problem that always starts the same way. Their operations admin quit. No drama, two weeks notice, friendly goodbye. Then payroll came around and nobody could pay the sales reps.

She had been calculating commissions by hand, twice a month, in a spreadsheet. Not a clean one. A massive five-section monster she had built in her head as much as on the screen.

Here is what those five sections did. One was the base commission. One handled sales that got split with an outside partner. One tracked corrections when an order went sideways. One was a "forgiveness" balance that softened those corrections over time. And one tracked changes to orders after they were booked.

Five sections. For every single rep. Twice a month. All from memory.

When she left, the process left too. No instructions. No written rules. The whole thing lived in one person's habits, and those habits were now down the street at a new job.

The owner could open the file and see numbers. He just couldn't explain how any of them got there. If a rep argued about their check, nobody on staff could defend the figure.

This is the nightmare in its purest form. One person held all the knowledge, and it walked out the door inside her head.

So here is the question I had to answer: can I rebuild that knowledge, fast, and get it right.

Why One-Person Processes Always Blow Up Eventually

A manual commission process like this is a single point of failure. The rules aren't written anywhere. They're encoded in someone's muscle memory.

She knew that one rep was on a different pay tier than another. She knew that on certain orders, credit card fees came out before the split. She knew the forgiveness balance refilled every quarter. None of that was a document. It was just in her hands.

That's the trap. The process works perfectly right up until the person running it is gone. Then it works not at all. There's no in-between.

This company had an extra layer of pain. They had two different spreadsheet formats, an old one and a new one, and only she knew how to bridge them. Older orders lived in one format. Newer ones in another. She switched between them without thinking.

The real cost here isn't her salary. It's that mistakes in a manual process hide. Nobody is checking the checker. A small error in how a split gets applied can run for months before anyone notices, if they ever do.

That's the part most owners miss. Fixing this isn't really about speed. It's about getting the rules out of one person's head and into a system you can actually read and trust. Speed is just a bonus.

Step One: Read the Real Spreadsheets, Mess and All

When you inherit a mess like this, the instinct is to redesign it. Build a clean new system. Make everyone enter data the right way going forward.

That instinct is wrong. It throws away the only record of how things actually worked.

So I started with the real, messy files, both formats. I built software that could read both of them and translate them into one consistent shape, without anyone re-typing a thing.

This matters more than it sounds. The spreadsheets ARE the instructions. Whatever she did is sitting right there in those files. Every weird quirk was a clue about a rule she applied. So I treated each oddity as something to reproduce, not clean up.

By the end of this step, I had every past pay run in one clean, organized place. Read correctly from both worlds.

Step Two: Pay Each Order at the Right Rate for Its Date

Here's where most shortcuts quietly break.

Reps get raises. They move up tiers. The rate that applies to a sale made in March is the rate that was in effect in March, even if you're cutting the check in September and the rep is now on a higher rate.

A lazy system grabs today's rate and applies it to everything. The math looks clean. It spits out a number. And the number is wrong.

A raise in June can't reach back and rewrite what someone earned in April. The past is settled. So I built the system to store each rep's full rate history with dates. For every order, it looks up the rate that was valid on that order's sale date. April order gets April's rate. July order gets July's rate.

Step Three: Let the Math Be Math, Not a Guess

The calculation part of the system is pure math. Tiers, fees, splits, corrections, the forgiveness balance, all applied the same way every time. Same inputs, same answer, always.

No AI ever decides a dollar amount. The AI never touches the money.

I think of it like a restaurant. The AI is the manager who walks the floor and notices when something looks off. But the recipes, the exact measurements, are written down and followed precisely. You don't want the manager improvising the ingredients.

Then came the moment that turned a skeptical owner into a believer. Once the system was built, I ran all the historical pay data back through it to confirm it matched what she had paid out by hand.

It didn't match. Not because the system was wrong. Because the system was right.

It caught a roughly $2,400 overpayment the manual process had missed. Somewhere, a rule had been applied loosely, and a rep got paid more than the rules allowed. Nobody had ever caught it because nobody could check the work.

That's the real trust-builder. The system didn't just match the human. It found money the human missed.

Step Four: AI for Judgment, Plus a Backup

So where does AI actually belong here? After the math is done.

Once the system calculates a pay run, an AI reviewer reads the whole thing and explains it in plain English. It flags anything unusual. It notices when a rep's payout jumps and explains why. It spots a correction that doesn't fit the pattern and says so in words the owner can read.

What it does not do is calculate. It reads the numbers and decides whether they look sane. That's judgment, and that's what AI is genuinely good at.

And because AI can occasionally hiccup or go offline, there's a backup. If the AI isn't available, a simpler set of rules still runs the safety checks. Unusually large payouts, splits that don't add up, corrections over a certain size, all flagged automatically. The system never goes blind.

Step Five: A Human Always Approves

Nothing pays out on its own. Let me be very clear about that, because it's the thing a careful owner worries about most.

Every pay run is calculated, reviewed by the AI, then handed 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.

Let me be honest about what this does NOT do. It doesn't get rid of the human. Someone still clicks approve twice a month.

But that's the point. The goal was never to remove the person. It was to remove the single point of failure. The old process had one person who WAS the system. The new process has a system that one person reviews. Completely different risk.

If the approver quits tomorrow, the next person can step right in by reading the rules and the summary. The knowledge no longer walks out the door.

The result for this company: commissions now run automatically. Problems get flagged on their own. There's a full record showing exactly which rate applied to which order and why. And it caught $2,400 the old way missed.

They went from one person's memory to a system anyone can inspect. I rebuilt the whole thing over a weekend.

If there's a process in your business that only one person truly understands, a spreadsheet that would be a crisis if its owner quit, that's 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 fits.

Book a Discovery Call