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.
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.
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.
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.
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?
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.