Sales Commission Blog » Performio

Calculating Sales Commission in Spreadsheets: the Pros and Cons

Written by David Marshall | Jan 30, 2023 2:48:47 PM

Sealy Posturepedic or Beautyrest? Tempurpedic or Sleep Number? Purple or Casper? Microsoft Excel or Google Sheets. Whether we’re talking bed sheets or spreadsheets…we’ve got you covered 😉. And while going on your Goldie Locks adventure of finding that just right fit for you to get those precious 😴😴😴, when it comes to calculating sales commissions, there’s more to consider than simply Microsoft or Google. After all, we’d hate for you to lose any sleep over complicated pivot tables and formula errors. Let’s uncover the pros and cons of spreadsheets so you can be prepared to scale. 

When you use on-target earnings (OTE) in your business’s compensation plan, your sales reps have an idea of what they’ll be able to earn if they meet their targets, helping them stay motivated. Using OTE also makes it easier for you to forecast sales commissions and manage your business’s finances.

But it also means you’ll need a way to correctly calculate sales commissions in order to pay your sales reps. This is where many sales teams turn to spreadsheets.

While it’s true that spreadsheets are capable of managing and calculating commissions, you should be mindful of their limitations, especially as your sales team grows. Spreadsheets can be an attractive option to begin calculating sales compensations for a small team of 10 reps or less, but they become exponentially more difficult to manage for larger teams. And once you reach 50 reps, they are essentially unusable.

In this article, we’ll explain when using spreadsheets makes sense, we’ll look at their limitations in detail, and we’ll discuss what it means to move beyond spreadsheets.

What spreadsheets do well

Many businesses look to spreadsheets as their tool of choice for calculating, tracking, and visualizing sales team commissions. Here’s why.

Zero marginal cost

When it comes to expenses, spreadsheets have a marginal cost of zero. Spreadsheet software is usually either free outright or bundled with the office suite you’re already using. All the major platforms come with a tool for editing spreadsheets, including Excel from Microsoft Office, Sheets from Google Workspace, Numbers from Apple iWork, and Calc from OpenOffice.

Extensive customization

Spreadsheets can take on a wide range of functionality, and they’re easy to customize according to your unique needs. Using formulas, functions, and other features, you can create nearly any type of financial documents, along with calendars, planning documents, budgets, and more. And you can incorporate spreadsheets into any workflow—as long as you don’t mind a bit of manual data entry.

Minimal training

Spreadsheets have become nearly as ubiquitous as word processors, and most people already know how to use them, at least for basic functions. This means you probably won’t need extensive training. But keep in mind that when you start using complex formulas and functions, it may involve additional research into how they work and how they’re implemented.

Easy data analysis

Spreadsheets make it easy to manipulate and analyze data. At the most basic level, simple formulas allow you to add, subtract, divide, and multiply. Going a little further, you can create pivot tables, search for data, combine data, remove duplicates, and use tools for filtering data. And getting even more advanced, you can program scripts and macros to take spreadsheet functionality to the next level.

Insightful visualization

Spreadsheets allow you to visualize your business’s data using graphs and charts. When dealing with hundreds or thousands of data points, it can be difficult to grasp the big picture of what it really means. But in a few clicks, you can turn that data into easy-to-understand graphics, which you can use to gain insights, make better data-driven decisions, and support your decisions to other stakeholders.

But while they’re easy to implement, using spreadsheets for sales commissions comes with some major drawbacks as well.

Where spreadsheets fall short

When calculating and tracking sales commissions for a small sales team of around 10 people or less, spreadsheets make for an attractive starting point. However, their limitations surface as soon as your team grows beyond around 10 sales reps. Suddenly the “easy” option starts to become burdensome and unwieldy. And at around 50 people, spreadsheets become hazardous and untenable.

Before you reach the point where spreadsheets are creating more problems than they solve, you should consider moving beyond them for your sales commission calculations. With that in mind, let’s look at some specific challenges spreadsheets can create.

Time-consuming repetitive tasks

One of the major drawbacks of using spreadsheets to calculate sales commission is that it takes a lot of time and effort. It may be fine when working with only a few sales reps and a simple compensation plan, but as your team increases, you’ll find yourself scrolling through hundreds of rows and manually retrieving far too many data points to manage your sales reps’ commissions.

You’ll also need to create a new spreadsheet every pay period. This will often entail copying all the sales records into the new spreadsheet along with the formulas and tables needed to calculate commissions and make provisions for any adjustments. As your team grows in number (and as your comp plan grows in complexity), the time spent on copying and tweaking individual spreadsheet files grows, too.

Lengthy distribution process

A lot of work goes into the spreadsheet itself. But once you’re done, you can’t just share it with the sales department. Every rep needs their own custom copy of the spreadsheet that doesn’t include anyone else’s sensitive information.

This means more time that reps have to wait for insights into their sales commission calculations, and it leads to a lack of transparency in commission calculations.

Lack of real-time visibility for reps

Thanks to the lengthy process, your sales reps won’t have any window into how much sales commission they’ve earned until they receive their payouts. With no real-time visibility, reps have to rely on their own calculations to determine how much they’ll earn in commissions.

Not only is it unfair for sales reps to have to calculate commissions themselves, but they’re also likely to make mistakes. That can mean they’ll earn less than they expected, leading to frustration, disputes, loss of motivation, and wasted time as they try to reconcile their own calculations with the payouts they receive.

Another problem with the lack of visibility is that reps are unable to gauge their performance throughout the period. With no insight into how they’re doing, they won’t know if they need to adjust their strategies or make changes to meet their targets.

Inadequate integration with other systems

Your sales commission data needs to integrate with your other business systems, and spreadsheets generally lack any native ability to do so. This means you’ll have to either bring in third-party tools to make such integrations possible, or you’ll have to copy commission data manually to other systems and platforms.

And even where integration tools exist, they often restrict you to using specific spreadsheet templates. If your columns don’t match the input fields of the other software, it can lead to integration errors when you attempt to transfer the data.

Human error from manual data entry

All the manual copying, editing, and managing of data in sales commission calculation spreadsheets inevitably leads to human errors. And the complex formulas you’ll be using will result in faulty calculations if even a single piece of data is entered incorrectly. Additionally, spreadsheets are notoriously challenging to troubleshoot. If and when you encounter an error, you could lose hours trying to fix it.

To make matters worse, until you become aware of the error, you’ll be overpaying or underpaying your sales reps, resulting in disputes, a loss of motivation, and the eventual need to calculate the difference and facilitate reimbursements.

Poor scalability of spreadsheets

Spreadsheets only work properly for sales commission calculations if you have a very small number of sales reps. Beyond around 10, they don’t scale well at all. As your team grows, your commission management becomes more labor and time intensive. And the more data and formulas you use within spreadsheets, the easier it is to make mistakes.

Upgrade to ICM: all the strengths of spreadsheets, none of the weaknesses

As your sales team grows, how do you move past the challenges of using spreadsheets?

You need a dedicated, scalable platform that automates your sales commission calculations.

Performio was built by sales comp experts for sales comp professionals. For more than 15 years, we’ve helped hundreds of sales teams in dozens of industries. Our product strikes the perfect balance between flexibility and ease of use.

At Performio, we love helping businesses reduce the pain of calculating and managing sales commissions, freeing them to spend more time growing and serving their customers.

To find out more about Performio or how it can help you, contact us today to discuss your unique needs.