10 Confessions of a Sales Compensation Analyst

Is your sales compensation analyst using spreadsheets to administer sales commissions? This is still the case at far too many companies.

If you’re one of them, I hope you’ll keep reading.

We’ve blogged in the past about the dangers of using spreadsheets for sales commissions. We also created a handy comparison graph, for anyone who’s curious about the primary benefits of sales commission software vs. Excel.

But some people need to hear it straight from the horse’s mouth. So last month we went out and found one (not a horse -- a sales compensation analyst). Our real-life pro gave us the dirt on administering sales commissions with Excel.

What was it like to administer sales commissions using Excel?

Commissions that are not system-generated are a nightmare.

In order to do calculations through Excel, I first had to pull the data from our sales transaction system and export it into a massive spreadsheet (about 6,000 lines per month). Then I had to sort through it, and create a spreadsheet for every employee who was commissioned. The break-out alone was tedious -- having to make sure sheets were formatted correctly, and ensuring all the sales data was complete. Plus, I had to manually enter write-offs and pull out non-commissionable sales under a certain gross margin.

Another key challenge was crediting sales when several members of a sales team were commissioning on the same deal, but at different rates.

What did the QA process look like?

I would put the reports together, hand it off to someone else, and have them spot check. I guarantee the person who was verifying didn’t re-do each and every step.

Were you ever concerned about making a mistake?

Personally, I never make mistakes [laughs]. But sure, with a manual process, if you don’t catch an error, it will affect your financials, your employees’ wages ... everything.

Did sales reps ever complain they’d been underpaid as a result of a sales commission calculation error?

Yes. It happened a lot. At least once a month someone would contact us to complain that something didn’t look right.

What was your response or recourse in that situation?

You almost have to recreate the same sales report just so you have the proof. Usually the “error” was something they couldn’t understand or couldn’t see -- again, you’re talking about thousands of lines that account managers need to look through. 

How many sales reps complained about being overpaid?

None. Ever.

Do you think commissioned employees were taking up work hours to practice “shadow accounting?”

Definitely. I’d say about half of them work out numbers on their own, prior to everything being calculated.

Besides calculation errors, what else bothered you about administering commissions manually?

There’s always the danger of sending the wrong report, and exposing employee payroll data. With Excel files, the info was on our shared drive. We had to go to IT to make sure only certain users were able to view certain files. Excel puts everything out in the open, including sales commission rates.

How did the manual process affect you personally?

I knew there was a better way. I didn’t want to spend a whole day putting together commissions that should only take 20 minutes.

So if automating the sales commission process is so much easier, why are companies still using Excel?

I think some companies don’t automate because they want to preserve “black box” sales commission calculation, which allows them to do things arbitrarily. Smaller companies may not realize that they’ve outgrown Excel.

Scary, right? If you think it’s time to automate the process and/or revaluate your compensation plan overall, check out our free sales comp plan grader.

Editor's note: This post was previously published on the Cornerstone Software blog. It is republished here with permission.