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. 

Sales people priorities
1. Closed Deals
2. Commissions on closed deals
3. Food
4. Shelter
5. Etc

Time Spent on priorities:
1. Closing deals- 75%
2. Calculating & re-calculating commission payments in Excel- 15%
3. Food- 5%
4. Shelter- 4%
5. Etc- 1%

15% is a number based on my personal sales experience. We had semi-complicated plans with 4 measures both personal and team. Every month Linda would mail our commission sheets. We would compare them to the plan sheets we had on our desktops, looking at every deal, making sure we were getting paid correctly.

Depending how many deals had closed, how complicated they were, etc, it could take the better part of a day to get everything figured out, that doesn't even count if they were wrong, that could stretch into 2-3 days of back and forth with my boss, other reps, and poor Linda the payroll woman. All the while, my productivity on the phones was going down the drain, because as you see above. It's important to get the deals in, but it's almost as important to get paid on those deals.

And I was lucky. I actually got a commission statement; many of my colleagues at other companies wouldn't even know how much they were making on each individual deal. They just got a random chunk of money attached to a check two weeks after the end of a quarter.

This is why we are introducing a new feature in Makana Motivator Pro. It's the Sales Portal. Now, sales reps and their managers can login at the end of the pay period and see how team members are performing against their plan. Sales people also get to see what their commission payments will be (minus taxes). It saves time at the end of the month or quarter, and it keeps everyone motivated towards their goals. It's an easy way to make sure no one is surprised at the end of the month or quarter. Check out the new sales portal feature here.