Sales Compensation Blog

Many companies use spreadsheets to calculate commissions because they offer great flexibility and since most employees already have Microsoft Excel on their computers it is essentially a "free" solution.  But there are significant hidden costs to using spreadsheets as your sales compensation planning and calculation solution.

A Direct Hit to Your Bottom Line

I have never audited a commission calculation spreadsheet and found it perfect. Just one single error can directly affect your bottom line.

Analysts estimate that the rate of errors in a commission spreadsheet is between 3% and 10%. If your company's monthly commission expenditure is $10,000, your rate of error is between $300 and $1,000 per month.

In all my years managing a compensation group, only once did a rep tell me he was overpaid. Typically you only hear from the rep when you owe them money, so the error always costs the company money. In addition, the reps are likely keeping a separate set of spreadsheets making sure yours are correct. See the blog on Shadow Accounting and Trust.

Error Types

• 1. Mismatch of spreadsheet and plan design

Often the plan document and the model used to create the plan overlook key rules for commission calculations. Those rules are left to the spreadsheet creator to figure out, causing the plan design and the commission calculations to be out of synch.

• 2. Data placed in the wrong cell in the spreadsheet

It is easy to put the order data in the wrong cell causing the calculation to be incorrect and incurring minor or major errors in the final result.

• 3. Crediting

Who closed the deal? In theory this should be easy to answer, but in practice there are complex crediting rules that are poorly documented, if it all.  The compensation analyst manages these rules "in his head", making crediting more of an arbitrary, rather than a disciplined, repeatable rule. 

• 4. Error with organizational logic

It is not enough to get the direct sales rep the correct credit and payment. You also need to understand the team and manager relationship.  In a spreadsheet it is hard to figure everyone who got credit for the sale and easy to miss a team relationship.

• 5. Formula errors

Spreadsheets are saved as for the next month. Commission calculations are complex and it is easy to make a mistake that gets carried forward.  It is also difficult to uncover a formula error because of the relationship of the data and complexity of the calculation.  And as the company and sales force grows, the complexity is exponentially compounded, worsening the error risk.

• 6. Missing data

I once worked with a company that manually entered their booking data directly from the hard copy order after it was approved by legal. It was very easy to miss an order or to have difficulty interpreting the value of the order or understanding special pricing and discounts.  All these contract items had a potential impact on the final commission amount, but they were often not entered into the spreadsheet.

• 7. Data duplication

If your spreadsheet includes a separate worksheet or tab for each rep, it is very easy to duplicate an order. Did you include the order on two separate worksheets by mistake?

• 8. Exceptions

With spreadsheets, you can have many exceptions and no tracking mechanism that would withstand an audit. Adjustments are explained in a note on a cell if at all, making them difficult to find and track.

• 9. Creating a worksheet for each rep

For reporting purposes, the compensation administrator emails a worksheet to every participant or sometimes the reps receive nothing.  This manual and tedious process is susceptible to error because the administrator must take the current month workbook, copy and save each worksheet and email the right worksheet version to the right rep.

Minimize Risk and Commission Overpayment

An automated sales compensation planning and calculation system will streamline the sales compensation calculation process, ensure that commission rules are consistently and accurately applied, and automatically provide auditable records and documentation. 

Lately I've been hearing about surprise ‘overpayments' and how distressing they can be. Helping companies avoid such surprises has been an important part of my goal of delivering software to help people manage their sales compensation.

In the past, I was focused on eliminating overpayments by reducing errors in commission calculations through automation. I have worked with many companies who found that when they replaced their spreadsheets with a commission system there were all kinds of overpayments to reps based on human error intrinsic in the use of spreadsheets. Of course, sales reps are MUCH less likely to report overpayments of this type so the automation process uncovered many such flaws. One company I worked with said "We know we are overpaying about $2M per year. We don't plan to recover it, but can you please make it stop?"

Now, with Makana's focus on plan design, I've discovered a more disturbing ‘overpayment'. Many of the companies we are working with now did not understand how to model their cost exposure prior to rolling out new sales compensation plans to their employees and channel partners. So they accidentally designed plans that would pay more than they intended for the results they were seeking to achieve. In many ways, these kind of ‘overpayments' are much worse to deal with. For starters, you are contractually obligated to pay them, so mere knowledge of the problem doesn't provide you any means of recovery. Furthermore, you typically don't find out about the problem until you are faced with signing a very big commission check. OUCH.

So this year, spend some time modeling the plans before rolling them out to the team. Look at your cost exposures for overlay positions, windfalls, quotas set too low etc. Correct the problem before it happens.