Before You Calculate Commissions in Excel Read This

The reason that most commissions are managed in Excel is not that it’s the best solution for the job, but because it's the best-known solution. And while it’s not difficult for smaller companies to set up a commission structure within Excel, the software doesn’t account for the challenges that a growing team will eventually face.

Beyond that, with the speed of change and market demands, Excel simply isn’t agile enough. One miscopied formula, one random error, and boom—your Revenue team could be dealing with a ripple effect, affecting how Sales team members are paid, how success is measured, and whether corporate metrics are truly hit or not.

Let’s take a look as to why Excel simply isn’t sustainable for sales compensation management.

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 of 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 you have two sales reps, person A and person B, then you 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.

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 this way is scaling the process as the company continues to grow and business goals change.

Let’s take a probable scenario: your manager comes up and says, “what if we inserted a new tier here that paid from 125% to 150%, and added another new tier, so now we have four tiers, go into that formula and insert that new variable, etc.” Even if you’re an Excel wizard with a black belt in VLOOKUPs (more on those later), 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.

Compensation is a critical extension of an organization’s business strategy, so being able to scale in relation to demand is more than a nice-to-have. It's an absolute necessity. Companies across all industries are looking for the best talent, and change compensation plans to make it happen. Beyond that, Sales orgs are constantly changing; role changes, departures, hires, and changes to how they are measured and paid. With Excel, those changes can take a while to implement, leading to a competitive disadvantage in almost any market.

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:

=IF(C5<64,"F")

to

=IF(E4>35000,0.05,IF(E4>159999,0.03,IF(E4>74999,0.01)))*E4

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 made a costly error. Extrapolate this process for three sales reps and now you have three 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. An automated ICM solution is designed to calculate comp quickly, and accommodate whatever structures are necessary. To put it more blatantly, it's a lot easier than this:

=IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C",IF(C5<95,"B","A"))))

Excel Doesn’t Know Comp

When it comes to overly complicated formulas, VLOOKUPs aren’t an Excel user's friend. In fact, they can be their worst enemy as they leave significant room for error.

Compensation can be so varied, which means it doesn't have a standardization of process specifics. Even words like “quota” are less standardized than you’d assume. Some companies may use “quota”, some “targets” or “goals,” and others might not use any of those. Excel is in no meaningful way equipped to handle compensation for various companies using different terminology and approaches.

With the huge impact compensation plans have on attracting and keeping talent, hitting company goals, and competing in the marketplace, organization’s cannot afford room for error. Using an automated ICM solution, like Xactly Incent, provides companies with the tools they need to think outside spreadsheets to design, automate, and manage the most effective compensation plans for their GTM team, and align seller behavior with company revenue goals.