Spreadsheets are amazing. They have made handling complex mathematical scenario modeling available for everyday people. Through spreadsheets, anybody can handle huge amounts of data from multiple sources, easily manipulate the data, do calculations, summarize, and visualize the results. The learning curve to get started is extremely low, and the possibilities of what you can do are nearly limitless.
This leads to spreadsheets becoming the stopgap in any scenario requiring tracking and manipulating data. Rightfully so, they are often the first place people go when they need to track orders, handle financial records for their business, or as we commonly see, manage commissions.
For early-stage commissions, it’s so easy to get started in spreadsheets and makes a ton of sense because of this fact. However, it doesn’t take long to track commissions in a spreadsheet before the challenges start to crop up, and you find yourself in commission purgatory. In the end, you will most feel the pains of managing commissions via spreadsheets in one of two ways: either in the snowballing amount of time you’ll continually invest into maintaining it or the accumulating errors that will inevitably work their way in.
This article is intended to provide some foresight into a handful of the most common challenges we have seen where spreadsheets fail at scale in managing commissions. Thus, if your organization for some reason thinks commission spreadsheets are the right way to go, you’ll at least have an idea of what to prepare for.
Without further ado, and in no particular order other than somewhat basic logical hierarchy, the top 10 ways your commission spreadsheet will fail you as you scale:
- Spreadsheet Design and Maintainability
- Temporary Payout Rules
- Simple Commission Functions
- Currency Conversions
- Transparency
- Traceability and Version Control
- Temporality
- Continuity
- Roll-Ups
- Errors
Commission Spreadsheet Design and Maintainability
If this sounds silly to you, then just wait–it’s a challenge that you’ll face in the near future. At Spiff, we’ve seen thousands of commission plans, and their designs typically fall into one of two categories, and they’ll each run into different limitations as you scale. Either of which will, in the end, make you question why you got into commissions in the first place.
The “Bulk” Commission Spreadsheet
We’ll call the first the “Bulk” commission spreadsheet. Typically what this looks like is a spreadsheet that has a list of every one of your reps in the first column, and then all the subsequent columns handling the intermediate calculations to finally arrive at the commissions in the last column.
The primary benefit of this design is it can be very easy to make changes to calculations. If you want to update rates, or modify the filters of what data qualifies for a payout rule, you can easily modify the formula in a single place, and quickly drag it down across all your reps. It also makes it super easy to run reports.
Where it falls down, however, is when you need to start handling overrides or exceptions, manage multiple month’s worth of commission data, or create individual commission statements.
If you want to create overrides or exceptions or modify a commission plan for a specific individual, it may work for a single month, but carrying that change forward becomes technical debt and opens up opportunities to make mistakes. It’s very easy to make a change to a calculation, drag the updated formula down your list of reps, and overwrite the exception you created.
To handle multiple months, you either need to create a commission spreadsheet for each month, or you’ll need to add a bunch of columns off to the right to carry all the calculations for additional months. I’m not sure what the highest column you’ve ever seen used in a spreadsheet, but the highest I’ve seen was one in this format that got all the way out to “HGZ”. That’s 5616 columns. Do you have any idea how long it takes to scroll out to 5616 columns? Let alone find the actual information you’re looking for.
To create individual commission statements, obviously, you can’t send all your reps a copy of the commission spreadsheet for privacy reasons. Therefore, you will have to either manually create statements, or do some kind of form fill letter. The form fill letter is the easier, but what happens when your reps have questions, or they want to trace the calculations, or they don’t see all the deals they expected to see, or you need to do some kind of manual comment/adjustment to their individual statement? And if that adjustment is supposed to carry from month to month, where do you keep track of that?
The “Individual” Commission Spreadsheet
The second type we’ve seen we’ll call the “Individual” spreadsheet. Typically what this looks like is an entire workbook where every single rep has their own commission spreadsheet on a different tab. The spreadsheet for the rep will often show all of their deal data, all the calculations, their relevant commission plan information (i.e. variable, base commission rate, quota), and whatever other details the rep needs to know about their commissions.
The primary benefit of this design is it’s a great experience for the rep. It helps to answer just about any question the rep will have, or could have.
Where does it fall down? Everywhere else. Managing this type of commission spreadsheet is an absolute nightmare and is a surefire recipe to endless hours of meaningless copy/pasting and calculation errors. What happens if you want to make changes to the calculations for all your SDR’s? What happens when you need to add a tier to your AE accelerators? What happens if you want to create a team roll up structure and have team commissions roll up to their manager? To their VP? What happens when you want to run reports across multiple reps and over a larger period of time?
True, if you want to waste days of your life every month going from tab to tab, manually making the changes to each individual spreadsheet or collecting data, it is definitely possible. However, at some point around day 4 of switching between tabs and making manual changes, you’ll accidentally close your workbook, and forget which tabs you have reviewed and which you haven’t. At this moment, you’ll ask yourself the existential crisis question of “what am I doing with my life?”, and hopefully, remember this article.
Temporary Payout Rules
For instance, suppose you want to run a spiff for the quarter for your BDR’s that ranks the total number of meetings generated each month by BDR, and pays a monthly bonus based on their rank. How would you handle this in your commission spreadsheet?
Besides being a somewhat tricky rule to write, often times the way we’ll see this handled is one of two ways.
The first way is the “catch-all” bucket, which is usually either a line item on the statement or an empty column in the calculations where the commission team can just enter in a hardcoded value. Then there’s usually either a “comments” column or they write in a comment onto the cell in the spreadsheet describing what the spiff was for.
This is a totally acceptable solution, however, it doesn’t provide any traceability for the reps to understand why/how they received the spiff, which if there’s ever a dispute between what the reps believe they should receive versus what they actually got paid will ultimately end up in additional work for your finance/sales leadership to help resolve.
The second way we see this handled is to create the custom logic and formulas to demonstrate how the spiff was calculated. This is awesome because it will provide the traceability for your reps to understand better where the number came from, but raises a handful of issues, like how do you make sure this new payout rule only applies to the people on the team where the spiff is relevant? If you’re using the “bulk” layout design, then you’re adding potential complexity to everyone’s calculation by needing to put rules in place that the calculation only applies to the right people. And if you’re using the “individual” layout design, then you have to go to every single person on the team/where the spiff applies, and add in the additional logic.
And that’s just for one spiff for one team in one month. What happens when you get multiple teams running these rules? Over multiple months? Or that they’re changing from month to month? And then syncing with the sales leadership to make sure the calculations are done correctly as the rule was intended? If the sheer effort of creating these temporary rules isn’t too much for you, then the mental effort and hours that will go into tracking and managing them over time will likely do you in.
Simple Commission Functions
Unless you write your own javascript commission functions in Google Sheets, spreadsheets are not out-of-the-box ready for handling common commission calculations. For instance, handling accelerators with marginal payouts. Building this out in a spreadsheet is no simple feat. Unless you want to create some of the ugliest, most undecipherable nested “if-then” statements known to man, handling accelerators in spreadsheets usually end up taking up huge amounts of real estate on a spreadsheet, which ultimately gets back to the issue of spreadsheet design and maintainability.
Transparency
The top benefit buyers of commission software are looking for is visibility. What does this mean? It means helping reps to know and understand their commission. You could divide into two sub-benefits: timeliness and transparency.
Timeliness means knowing what your commissions are in a way that motivates the best rep behavior. Real-time commissions motivate reps better than commissions provided infrequently after a commission period has closed.
Let’s define transparency as the difference of a rep only seeing a number on their paycheck representing the total commission dollars they earned versus providing a statement that shows every deal they closed and exactly how much commission they earned on that deal.
Suppose you do provide your reps with individual spreadsheets, and it shows them the actual calculations of how the commission was calculated. The next challenge you’ll face is helping your reps to understand the calculations. Imagine trying to explain something like this on the phone with a rep:
If your rep has never worked with a spreadsheet, then there’s a huge disconnect for your rep to understand how to reference columns and rows, understand the syntax of the spreadsheet formulas, and how to generally make sense of what is actually going on here.
It would be much cleaner to have the variables clearly labeled, and therefore more easily deciphered and discussed. Having a rep ask “Why is my draw percentage ‘0’?” is a much more productive conversation than trying to answer “Why is ‘N7’ 0?”.
Currency Conversions
If you’re just starting out, odds are that you haven’t had to deal with currency conversions yet. At first, you might be like “This isn’t a big deal–they’re both numbers, just add them up”. And if you ask your spreadsheet to do that, it will, and it won’t complain. If you have some units in dollars and others in euros, your commission spreadsheet will add them up and give you the best guess on the currency result.
But what happens when you start realizing that the exchange rate from dollars to euros isn’t one-to-one? What happens when your corporate currency is in euros, your reps sell in yen and are paid in dollars? Suddenly having controls around how currencies are handled becomes a huge deal, and necessitates some level of control to ensure the conversions are handled correctly, and flags are thrown when they’re not.
Currency conversions change the entire way you think about commission calculations–should we perform the currency conversion prior to starting calculations? Do we wait until the last mile and convert it for the reps individually? What if they want to see the calculations of how the numbers worked out? And what about that one deal that closed from last year when the dollar was stronger than the euro, but they didn’t pay until now. Which currency conversion rate do you use?
Spreadsheets just see numbers as numbers, they don’t care at all about the “type” the number is. Euros, dollars, dates, it doesn’t matter, and it will do whatever you want it to do without thinking twice. Like adding dollars and dates. In spreadsheets, time really is money.
Version Control & Traceability
Today, you might be the only person on your team working on commissions. But tomorrow, you might have 2-3 people from sales ops, a team in finance, and an analyst or two working on commissions with you. If all your data exists in a spreadsheet, you run the risk of version control.
If you’re using an online spreadsheet like Google Sheets, they actually have some really powerful version control tools that allow you to see every change made to the spreadsheet, who made the change, and when. And if there’s a mistake or problem, it’s very easy to trace down when it occurred and roll back the error.
Assuming your team is open to it, this can be the best way for your various organizations to collaborate on handling commissions. However, the tradeoffs are the online versions generally can’t handle as much data as offline (forget getting to column “HGZ”), and there’s some limited functionality (weaker pivot tables, fewer chart options, and no formula tracing to name a few).
However, if you’re using an offline application like Excel, then version control becomes like advanced physics and almost requires a project manager. With dedicated effort, it’s learnable, but it takes a lot of norms and coordination with your team to synchronize effectively. Usually what happens is organizations will maintain a single copy of the commission spreadsheets on a shared drive, and then everyone will have access to it there. The challenge with this is the access and again, versioning, with the single biggest issue being concurrency. If you have multiple people accessing the same file at the same time, then what happens when somebody makes a change and saves it? How do those changes get updated to everyone else?
To address this, Excel has “access control” which basically means one person can have edit access to a file at once, and everyone else will only have read access while the file is being used.
Maybe not a big deal when everyone is in the office. But what happens when it’s crunch time and everyone needs to access the file at the same time? Or the person leaves the file open on their company computer and goes home for the weekend, and you want to keep working? You get locked out. So you can either make a copy of the file, and continue working, or you can break it out into multiple files. And once again, you get back into the version control issue of determining whose changes are correct, and the design issues stated previously.
Let’s assume for just a moment that you’re able to train your team effectively to handle the access control issue, and get some solid norms in place. The next big, immediate challenge you’ll have is when you do come across a change that somebody has made, being able to trace it back to the individual that made it, and understand why that change took place. Was it intentional, or did they fat finger the change? And how long as that change been there?
Ideally having all the relevant details very obvious would help to eliminate these types of questions, reduce miscommunication and help your team to get on the same page much more quickly.
Temporality of Commission Spreadsheets
First, let’s discuss what I mean by “temporality”. Basically what I’m referring to is any element of a commission plan that has to do with time, and has the potential to change at some point midway through the commissioning period. Suppose your company runs commissions monthly and has quarterly quotas. Here are a couple examples to help make the temporality issues clearer in this type of situation:
- You have a rep that is an SDR for the first half of the month and then switches to AE for the second half. How do you make sure the rep gets the right commission credit for each role?
- You have a rep that is part of Team A, and then switches to Team B partway through the year. How do you make sure the reps deals count towards the correct team quota?
- The converse side of this, you have a manager that is over Team A for a while, and then switches to Team B. How do you make sure the deals roll-up correctly to the right manager?
- A rep has a specific commission rate for part of the period, gets promoted, and all new incoming deals have the higher commission rate. How do you make sure your logic accounts for the different commission rates, and potential downstream effects (like how does it impact accelerators)?
- A specific client has a discount rate that applies for a period of time and then goes away. How do you make sure your logic accounts for the fact the discount is only valid for a period of time?
Let’s dig into the first example a bit more, and how you might handle this in a spreadsheet. We’ve seen multiple ways this situation is addressed, and each has its own pros and cons.
One way to handle this is to create a new user in the commission spreadsheet with the same name as when the rep was an SDR, but then with the new role as an AE, and to have their “start date” be somewhere in the middle of the month. This is probably one of the easiest ways to handle the temporality because now all your logic is isolated and treats the individual as a new user. This allows you to keep the majority of the core commission calculations the same, and just reference the different properties (like quota or base commission rate). The one thing to be aware of is you still have to make sure the effective dates are in place, to ensure that old deals don’t count towards when they were an SDR, and that the new deals know to get applied to the AE role.
Another way we’ve seen people handle this is to just make sure you make all adjustments at the end of statement periods or quarters. This effectively will allow you to completely circumvent the majority of the issues. If you have team quarterly quotas, and wait until the end of the quarter to make changes effective, then you don’t have to worry about accounting for the change in your spreadsheet. What it does require is exceptional organizational discipline. You need to make sure your teams and hiring managers know that there will be no organizational changes that take place until the end of the quarter, or that if they do make changes, it won’t take effect in commissions until the next quarter. This has a handful of obvious implications, among them the fact that this is a very impractical expectation of a way to run and manage a team, as well as the challenge of managing the misaligned expectations of reps during the time when they know they are going to be transitioning to a new role.
My absolute favorite way I’ve seen people handle managing temporality is in code. Basically introducing a simple “if” statement that says something like “If this deal closed before January 15th, you get 10%, and if after, you get 12%”. It seems so reasonable, so logical, so simple! And yes, if you’re only handling a single temporality issue, that’s true. But what happens when that temporality change happens across multiple individuals, for multiple products, for multiple different plans? You end up with something like this:
This is literally a disguised formula from one of the commission spreadsheets we’ve seen. What does this formula do, you might ask? That’s exactly the point of this entire article–ultimately spreadsheets will fail you with formulas like this in the end, where communicating and deciphering the logic becomes completely unrealistic.
Nevertheless, look closely… you’ll see a lot of dates in there. Basically it’s checking for a handful of different scenarios and time frames, and determining which commission rate should apply. While this is my favorite example, we’ve seen countless other similar examples of companies where the first simple “if” statement accounted for the first situation, and over time ballooned into something like this.
Continuity of Commission Spreadsheets
We have seen the following scenario play out countless times.
An organization has an individual that owns commission. This individual has been owning commissions for the last few years, to the tune that everyone just knows commissions will be taken care of and forgets there’s even a person owning it. And then, that individual switches jobs, gets promoted, gets fired, moves to a different company, or moves to a different country. Either way, they’re no longer handling commissions, and so this responsibility is thrust upon a new individual to manage.
But due to the complexity of the commission plan, the poor documentation, and lack of clean data, commissions, which were once ran so smoothly it was essentially a forgotten function, comes to a grinding halt while that new person comes up to speed. And that time it takes to come up to speed? It can take weeks, even months. How do you think your organization would be impacted if you weren’t able to get commissions calculated for that long?
We have seen organizations combat this continuity issue a couple of different ways, but unfortunately, none of them are going to be easy or cheap.
We’ve seen companies get very meticulous about documenting the process. Once, one client I worked with sent me a 15-page document that described, step-by-step, their commission process. It literally had every single little step, from as specific as copying columns A-P from one spreadsheet to another, and telling the credentials to log in to the different data sources. While this can be very helpful the biggest challenge with this approach comes in the form of maintenance and dynamism. Because producing this type of documentation is a time-consuming process, it is difficult to make quick changes or test things out for short periods of time to see how they work. This usually doesn’t stop companies from trying to do so, and so it leads to the second big problem of this approach, which is the documentation just getting out of date. For instance, a company decides they want to try a spiff for a month, and everyone knows it’s only going to be for a month. So the person in charge of commissions/documentation decides to hold off documenting how it gets calculated until they know if the spiff is worth documenting, and then it eventually just gets forgotten about or put off indefinitely.
We’ve also seen companies address this by derisking the role, and getting multiple people involved in the commission calculation process. The more you can spread around the knowledge of the process, the better. This is a strong method to help get a “second set of eyes” on calculations, and so it provides the additional benefit of helping to reduce overall errors. The biggest problem with this approach is just the duplication of effort and additional cost of having multiple people involved. However as an organization scales, this can be a strong way to approach the continuity issue.
One of the last ways we’ve seen this issue addressed is through automation, but not necessarily through an automation platform like Spiff. We’ve seen scenarios where companies get a particularly technical person involved in commission calculations early on, and they create either a spreadsheet or write scripts that pull in all the right data in automatically from an API or data warehouse, and perform the calculations semi-autonomously, or at least with little-to-no required user input, and generate a user-ready commission statement.
What we find is this works fantastically while it’s working, but it’s very brittle. If a data system changes, an API changes, an authentication protocol changes, you need to change the commission plan, or even things as seemingly simple as a time zone change, it can throw off the entire system. And since it was built based on an individual’s knowledge, if they’re no longer around to maintain it, often what happens is they get retained on a “retainer” basis to help support the spreadsheet for when issues inevitably arise, or it gets scrapped and people move back to manual commission spreadsheets with all the glorious associated challenges.
Roll-Ups
We define a roll-up as anytime you have an individual (or group of individuals) that receive a commission on sales completed by a group they are over. For instance, a manager may lead a team of 10 sales reps, and the manager may get a percentage of all the sales completed by their team. We would call this a roll-up.
Typically within a spreadsheet, the way you handle this is by having some type of stated relationship between the sales rep and the manager. For instance, on each of the transactions completed by the sales rep, you may have a column that has the ID or name of the manager who receives “roll-up” credit for the transaction.
Another way we’ve seen this done is you have another tab in the commission spreadsheet that defines the relationships between all the sales reps and the managers. We’ve seen this where the tab will also have a list of dates to handle some of the temporality issues.
If you only have a few reps, or 1-2 tiers (i.e. maybe a sales manager and a VP of sales), this isn’t terribly difficult to manage. However, what happens when you start to have multiple tiers or local leadership, as well as territory and corporate leadership the deals are rolling up to? And then perhaps you introduce a sales-engineering or pre-sales organization that also rolls up and receives a portion of the sales. One of the most common ways we’ve seen his managed in a spreadsheet is just to add a new column for each of the roles, as seen depicted in the image below.
One of the biggest challenges of handling roll-ups this way is now you have to replicate the commission calculations for every single one of those roles on a single line basis. Remember above, the spreadsheet I referred to that went out to column “HGZ”? It was this company, and they were replicating the complex roll-up calculations for each of their 12 roll-up tiers, repeating it for every month of commissions, and employed 2 full-time individuals just to manage the complexity.
Errors and Commission Spreadsheets
Ultimately, this is where the biggest failure of managing commission plans in spreadsheets comes to play. Of the countless commission spreadsheets our team has reviewed, we have yet to find a single one without some type of error. Some are small, like perhaps truncating a calculated commission rate by hard coding “8.25%” as “8%” on a $1k transaction. Other times, we have seen errors that literally amount to hundreds of thousands of dollars that were either owed to reps but never paid out, or were actually paid out, but should not have been.
There was one company I saw where the commission was supposed to be split 50/50 between new business and renewals with quarterly statements. For some reason, the new business component was being multiplied by 50% two times, and when you’re dealing with potential new business commissions of $30-40k over the course of a quarter, that adds up to a ton of money those reps were losing out on.
Commission spreadsheet errors are such a common issue that we have internally discussed the idea of providing “spreadsheet audits” as a service, just so we can be that second set of eyes and make sure companies aren’t unnecessarily losing huge amounts of cash.
“How does this happen,” you might ask? I can’t provide an exact audit trail, but I can tell you the top culprits we see.
- Hardcoded values. Cells that once had a formula, and at some point along the way, perhaps you or someone on your team had to do a manual override because of a promise that was made, or the math just wasn’t adding up and you didn’t have the time to figure out why not. So you just write the correct number in, and move on, since commissions are due at the end of the month. Who’s to say the number you entered is right?
- Incorrect formulas. Cells that have either similar formulas as the ones around them, or are completely different to account for a change in a plan. Perhaps you inherited the spreadsheet from your predecessor, and due to some conversation they had in previous years, changed a formula. You don’t have notes as to why, but know they taught you it needed to be that way–and you never changed it.
- Perpetuated errors. This is when an error exists in a spreadsheet (hardcoded or incorrect formula), and because your team does a new commission spreadsheet every month, you don’t want to start from scratch–you take the previous month, copy it, clear out the deals, and start fresh. And while the data is fresh, the format and calculations are not–and those errors that existed in previous versions of the spreadsheet just get perpetuated in every new iteration.
- Deleted data. This is often tied to perpetuated errors, but sometimes in copying data either between different tabs in commission spreadsheets or from one system (i.e. Salesforce) to another (your spreadsheet), perhaps you forget to copy a row. Or a column. Or accidentally paste over other data that exists, unknowingly. Or maybe your right pinky was feeling particularly lazy one day and accidentally hit the delete key on an important call, and removed a step in the calculation. As long as final numbers show up, this rarely raises a flag and is easily overlooked.
- Incorrect logic assumptions. Programming is tricky, as computers do exactly what you tell them to do. And sometimes the way people intend a portion of a commission plan to work just doesn’t work the way they implement it. We most commonly see this with accelerator tables, especially in the instance where there are different values used to retire quota than what actually gets paid out in commission. I’ve yet to see one of these work exactly the way the company expected it to.
- Wrong approach. Sometimes commission spreadsheets are just plain wrong. The person who created the spreadsheet maybe didn’t have the experience or clear understanding of how the commission plan was intended to work, or may not understand how spreadsheets work. So they implement their best interpretation of how it’s supposed to work, and it’s plain wrong.
“Surely not my spreadsheet,” you might be thinking. And I don’t blame you; were I on the other side of the table, I know for certain that would be my perspective. Unfortunately, the odds are not in your favor and these errors do exist. They’re there, hiding in the shadows, waiting for that moment of discovery–potentially by you, and more likely by your sales reps. Sure, perhaps they’re small, maybe they won’t have a material impact on your business and aren’t worth fretting over. But odds are they exist, somebody is losing out as a result of them, and it’s a ticking time bomb waiting for discovery.
The fundamental challenge is that as your organization grows, your commission calculations are going to become more and more complicated, just by virtue of there being more numbers to work with. You’ll not only have more reps, but more deals, more team leads, more managers, more VP’s, more tiers. They’re going to want to try different commission plans, they’ll want to make changes, they’ll want to shift teams around, change the rules of commissions, promote top performers, they’ll want additional capacity, and out of pure necessity, it will introduce complexity to the design of your spreadsheets. And in that complexity, ultimately, is where the errors happen and hide.
The Case to Calculate Commissions in Spreadsheets
I do believe there is a place and time for managing commissions in a spreadsheet. The most obvious time is when your company is really early. Not only do you not have very many reps to manage commissions for, but odds are also you don’t know what your commission plans should be. We find in small companies, commission plans change frequently as they’re trying to figure out what levers to pull to both accelerate sales as well as grow the business. They’re in constant tension, and spreadsheets offer a world of flexibility and responsiveness that can manage the complexity far better and faster than any modern-day commission automation platform ever could.
So, if your sales team is small or you’re still working on nailing down your commission plans, you should keep using spreadsheets or the kind.
For the rest of you, meet Spiff. Spiff was specifically created to tackle every one of the issues highlighted in this article head on. The commission engine was designed from the ground up to calculate commissions utilizing the same fundamental, object-oriented software engineering principles that allow organizations like Google and Facebook to scale to billions of users. How would it be to have that same kind of power driving your commission? Drop us a line and find out.
