You would’ve heard this a number of times before, but it’s worth mentioning it again.
9 out of 10 spreadsheets have errors.
The majority of these errors are made by people like you and me. Yet, we continue to use spreadsheets to calculate one of the most complex things invented by mankind: commissions.
Calculating commissions can look like a straightforward thing at the beginning, but it doesn’t take too long before you lose your way. With spreadsheets, it is only going to get more complicated, especially as you scale and grow.
Let’s look at the 10 most common problems of calculating commissions in spreadsheets.
Problem #1 - Shareability Is Completely Out of Question
Sharing spreadsheets may seem like an easy task, but it isn’t that much of a cakewalk when you’ve to maintain the commissions of hundreds of your sales reps AND share them. There are going to be numerous back-and-forth with your reps, making it tough to communicate commissions using spreadsheets.
Even though the team’s commissions are all done in a single sheet, it needs to be replicated across multiple sheets and then shared separately due to the confidential nature of data. If a rep’s data needs some changes, then the manager's data also should reflect the same, and making sure that everything is in sync is a big hassle in itself. This situation gets even worse when you have different incentive plans for your reps.
Problem #2 - Manual Data Integration Wears You Out
To get your commission calculation right, you need to access the latest data from CRM, ERP, and Payroll systems. Spreadsheets aren’t connected, and you’ll have to do it all by yourself to keep your commission plan updated with the latest data. This is near to impossible, time-consuming, and above all, error-prone.
Here’s an example of one such situation. After you’ve done all the initial calculations in the spreadsheet, your Ops person says that $200K ACV got added last night in a region. What would you do now? You’d be left with no choice but to redo the calculations again.
Problem #3 - Audit Process Becomes a Chaos
Every compensation plan differs from one another and varies depending on a lot of factors. And so, it isn’t practically possible to keep a track of every change made in the spreadsheet.
Let’s consider a scenario. The finance team wants to check last year’s Q3 commissions. Do you think they’ll get it easily? The final one where you made exceptions may probably not be version controlled and stored in the audit folder that you maintain, and your finance team is going to have a difficult time sorting the situation.
Problem #4 - Even a Minor Change Can Be a Major Threat to Comp Plan
Your plans aren’t going to be the same throughout, you will need to do the necessary adjustments periodically. Be it adding new people, altering the quota or commission rates, SPIFFs (Can link the SPIFF blog), making the changes is going to cost the valuable time of your sales team. You need to spend extensive time recalibrating commission formulas in spreadsheets to make sure the MACRO works and picks up the deal data to calculate the output.
To understand the difficulty better, here’s an example. You have a rep who’s paid a specific commission rate, and they get promoted midway through the year. For every new deal he closes, he’ll be getting a higher commission rate. Now how would you make sure the commission is calculated correctly? It is definitely not easy when you depend on spreadsheets.
Problem #5 - Implementing Complex Calculations Are Incredibly Difficult
It is all well and good until your commissions remain simple, but they aren’t going to remain that way for too long. It is going to get complicated with the addition of different factors, and there may be just too many things going on with commissions.
To simplify the spreadsheet, you might end up saying no to any productive or effective comp plan changes that might come as a request from sales teams. This isn’t going to do any good for your reps, and for your business. Remember, commissions are there to motivate the sales team and not for Ops to get it done simply.
Problem #6 - Real-time Data is Inaccessible, Leaving Your Reps Anxious
When commissions are calculated in spreadsheets, your reps wouldn’t have real-time visibility and have to wait till the end of the month or quarter. They cannot point out the errors beforehand as well, which is going to make them frustrated.
Real-time data keeps the reps motivated in the process of attaining their quota, and transparency would mean that your reps know exactly how their commissions are calculated.
Problem #7 - With Team Changes Come Roll-Up Headaches
Every time a team changes or a rollup needs to change, the model breaks and you might need to alter everything. Deals get assigned to manager’s names all the time and you end up with a rep getting paid out at 10% despite doing 100% because of sales org mapping issues. As an Ops person, you don’t have clarity on these kinds of errors because it is not real-time and there are silos everywhere.
Problem #8 - Commission Continuity Becomes Messed Up
When an Ops person moves away from the firm, the complex calculations that are in the spreadsheet cannot be knowledge transferred easily. Any Ops person worth their salt is never going to trust an inherited spreadsheet, and they would end up creating things from scratch. It may take some weeks, or months to streamline this and is going to distract your Ops person from doing other things.
Problem #9 - Errors Easily Creep in and Are Impossible to Spot
You may have to change the formulas you use in your spreadsheet often. You create a complex model and come back after a week only to redo it from the start. As your team grows, it is only going to get complicated to do changes at scale in spreadsheets. All it takes is one error to cause havoc in your commission plan, and it is nearly impossible to avoid it.
For example, you see #NA errors in the spreadsheet, and to your misfortune, you can’t recollect the formulae used. This will surely cost you dearly.
Problem #10 - Resolving Disputes Can Keep You Up All Night
Are you at the helm of running a large sales team? How do you manage to communicate about commissions? Do you ever get to clearly answer your reps’ queries with facts? You’d have to spend the next few days going through the spreadsheets, CRM, reporting, to figure out an answer to their questions.
Let’s say that your rep would want to know how you’ve calculated a commission for one particular deal. Would you be able to answer it clearly with data? Of course not. You would’ve done changes on the go, and now it’d be difficult to access every change that was made.
Instead, if you use a tool, your data will be streamlined and you could save all your time with contextual dispute management. What’s even better is that reps can figure out the issue themselves and come to you with a solution or fix it themselves in CRM or reporting.
Final Thoughts:
Spreadsheets may work during the early days of your organization, but it definitely isn’t a long-term solution for commissions. You’d need something beyond it, like sales commission software, to handle complex commission plans.
Book a demo to know how Everstage can make your commission management hassle-free.
