Before You Calculate Commissions in Excel Read This
If you’re looking for a way or a formula to calculate commissions in Excel, you should be aware of a few shortcomings before you start filling up those cells. The reason that most commissions are managed in Excel is not that it’s the best solution for the job, but the best-known solution. And while it’s not difficult for smaller companies to set up a commissions structure within Excel, the software doesn’t account for the challenges that a growing team will eventually face.
Excel Is Manually Cumbersome
With Excel, there’s no easy way to clone. If you’re new to commissions, get ready to burn CTRL + C into your muscle memory as making copies to make multiple spreadsheets is just a part of Excel’s commissions workflow. For anyone dealing directly with commissions, manually multiplying plan templates is a dangerous proposition. Multiple versions mean you have to be wary of the security, audit-ability, and errors.
The manual demands of Excel for compensation also mean that comp admins are left with very little agility at their fingertips. Let’s say we have two sales reps, person A and person B, then we hired three or four more people. To make this work, you’re forced to literally copy and save the original Excel spreadsheet multiple times—then manually update everything.
Download our guide, "Designing Sales Compensation Plans," to learn how to structure your plans. Or, build a plan in minutes with our ready-to-use templates.
Excel Is Not Scalable
If you’re managing commissions within Excel, getting up and running on that system is the easy part. The challenge for those managing compensation is keeping Excel moving as the company continues to grow and business goals change.
Imagine your manager comes up like managers do and says, “Well, what if we actually inserted a new tier here that paid from 125% to 150%, add another new tier, so now you have four tiers and you need to go into that formula and insert that new variable, etc.” Even if you’re an Excel wizard with a black belt in VLOOKUPs, when you try to implement that formula there’s a chance this Frankensteined comp plan will introduce errors and break the commission engine you previously had chugging along.
Say your company doesn’t want to scale and turn into a behemoth, instead opting for an intimate team size. The rigid limitations of Excel shouldn’t be a big deal then, right? Well, unfortunately, yes, as even if you’re not interested in increasing headcount, your company will be forced to adapt to a changing market. Compensation is a critical extension of an organization’s business strategy, so being able to scale in relation to these demands is more than a nice-to-have. It’s an absolute necessity.
Excel = Complex Formulas
The unspoken truth about Excel is that, while it is a useful tool, it simply was not built for managing commissions. Certain formulas are incredibly difficult to execute in Excel. And with increasing complexity comes increasing limitations for users. Even common commission structures quickly become overly complicated in Excel.
Take accelerated rate tiers for instance. To build that pay structure in Excel you have to rely on nested formulas. These if-then functions always seem to quickly go from:
So what if you need to reuse the basic logic in this nested IF? You copy and paste and make the changes, right? Then you worry over every decimal place to make sure you haven’t introduced a costly error. Extrapolate this practice for 3 sales reps and now you have 3 spreadsheets to manage that become exponentially more complicated with the inclusion of new measures. At that point, you might as well be playing 3D chess. The surprising fact is that this approach isn’t necessary. SimplyComp, the industry’s first, do-it-yourself incentive compensation software, let’s comp managers just hit a button and select add tier and the tool automatically handles all the complexity on the backend. It’s a lot easier than this:
VLOOKUPs Aren’t Your Friends
VLOOKUPs can be an Excel users best friend one moment and their worst enemy the next. While most spreadsheet users rely heavily on this technique to transform data, they do so in spite of the significant room for error. This is what you have to forward to with VLOOKUPs when dealing with commissions:
- You’ll have to know the formula syntax to use it properly (a confusing demand for novice users)
- If you add data to the table you’re referencing, it can break your VLOOKUP formula unless you used relative ranges (as opposed to absolute)
- If you add additional data to the main table (that contains the VLOOKUP formula itself) you need to remember to manually fill the empty cell w/ the VLOOKUP formula itself
- VLOOKUPs are set at the individual cell and are only meant for static data
SimplyComp’s solution was to join the cross references and attributes a user would want to transform. So data from different systems are connected and automated as “lookups.” This automation means one important thing: the lookups don’t break.
Excel Doesn’t Know Comp
As mentioned previously, Excel was not created to calculate commissions. Besides lacking some functionality, this means Excel can’t really inform plan design with templates or best practices. If a new sales leader joins the team after a successful round of funding and is looking to hire new reps and change things up. Where does she start? Excel isn’t interested in giving you an answer, which means off to Google and commissions forums users go.
It doesn’t help that in the field of comp there is no standardization in the way we see among other disciplines. In marketing, automation follows a specific standard. Even terminology like leads and contacts have been established as a standardized way of talking about marketing and doing marketing. Same thing with CRM.
Since compensation technology is relatively new, it doesn’t have that standardization of terminology and process specifics. Even words like “quota” are less standardized than you’d assume. Some company may be using “quota”, another targets or goals, and yet another company might not utilize any of those terms. Excel is in no meaningful way equipped to handle comp for various companies using different terminology and approaches. SimplyComp got around this problem with “plan documents”, the one commonality most companies share in regards to sales compensation.
Typically you would have John’s plan document and his quota of 1.2 million. Then at the bottom of this document you’d have the terms and conditions. So even though companies don’t have a common dialogue or naming conventions they do document in a relatively consistent way.
The other commonality SimplyComp leveraged is that everyone is using Excel. And as limiting as Excel is, its consistency in an inconsistent world made it a necessary addition the SimplyComp solution. But instead of replicated Excel, what if you built spreadsheets from the ground up to handle commission sspecifically. The result looks like this.
Hopefully, this post helps you get a better understanding of the limitations you’ll face when calculating commissions in Excel. Again, Excel can get the job done. But as shown above, the most common convention isn’t always best practice. And when it comes to one of the most important components of your business, tedium and room for error isn’t something any organization can afford.
[Product Video] Xactly SimplyComp™
Xactly SimplyComp gives you spreadsheet-like familiarity - making it effortless to design, build, and manage incentive compensation plans. Purchase, create, and administer compensation plans in minutes, using out-of-the-box plan templates to maximize your incentive compensation plans.