In this article, we discuss best practices and challenges associated with using spreadsheets to manage sales commissions. Don’t get me wrong – spreadsheets are amazing:
- They are extremely versatile
- They make it easy to combine data
- They let you summarize & visualize data
- They support complex formulas
- Everybody knows how to use them!
Opportunities for using spreadsheets are endless. This is why every organization uses spreadsheets to analyze sales, track financial information, and often manage commissions. When dealing with a simple commission program and a handful of sales reps, using spreadsheets to track commissions makes total sense. In fact, we used one of our free commission spreadsheets as a featured image for this post.
However, challenges typically emerge as commission programs mature. Here are some of the symptoms you’ll likely experience:
- Your work is becoming very repetitive in nature
- You spend more and more time generating & sending spreadsheets
- Reps want real-time access to their commissions
- Managers want visibility on their team’s performance
- Spreadsheet formulas are becoming increasingly hard to manage
- You’ve accumulated many different spreadsheet / versions
- You have financial reporting & legal compliance issues
Did you know there was a better way? In this article, we describe common challenges associated with using spreadsheets to manage commissions:
- Generation & distribution
- Versioning & time-dependency
- Transparency & reporting
- Accounting & financial compliance
- Calculation errors
Generation & Distribution
Let’s examine a typical manual commission spreadsheet workflow. You typically have one or more master spreadsheet, with some formulas and lookup tables. For each pay period and for each rep, you:
- Copy a set of sales records to your master spreadsheet
- Run through formulas to generate commissions
- Generate a private copy for each rep
- Delete information reps should not see within their own copy
- Share spreadsheets with the right individuals
- Archive spreadsheets
There is no way to generate spreadsheets “in bulk”. You’re generating individual commission statements from a template. If your sales force doubles, you will spend twice the amount of time generating commission spreadsheets. And you will need to do this over and over – forever!
Each time an adjustment is required, you must re-generate new spreadsheets, and share them once again. If sales data was incorrect or missing, you must start all over again. If the adjustment requires updating formulas, there is no way to document the change you’ve made. You might lose track of logic over time. You may also need to remember to undo “temporary” changes to formulas.
From a reporting perspective, individual spreadsheets are a bit of a nightmare. Sales managers may need to open 10 individual spreadsheets to review individual commissions. Unless you manually generate additional spreadsheets for them, managers don’t have a central way to understand their team’s performance. Managers don’t have a way to visualize trends, or to look at numbers in aggregate.
Finally, generating commission spreadsheets takes significant time, so you’re probably only going to do it once – when all sales data is available. As a result, your reps might wait an entire month before they can view their next commissions. This could mean bad surprises for you, for managers, and for your reps.
Compare this with an automated commission solution – using Sales Cookie, you can:
- Eliminate manual generation of spreadsheets
- Always use the latest sales data (synched from your CRM or accounting system)
- Calculate commissions for all reps in “one shot”
- Provide each rep with an online dashboard with their current & past statements
- Adjust commissions and document the exact reason
- Empower managers to review both individual and combined statements
- Generate reports spanning multiple reps and time periods
Here is an example of an online rep dashboard replacing manual commission spreadsheets. This only shows you ~10% of the functionality Sales Cookie’s incentive dashboards can deliver to your reps. Reps gain real-time access to their current and past statements, from any device.
Versioning & Time-Dependency
Using commission spreadsheets, what are some of the things which could change from period to period, and that you should keep a record of?
- New sales transactions
- New hires / terminated reps
- Commission adjustments
- Changes to formulas
- Changes to job titles
- Changes to various numbers (ex: salaries, quotas, commission rates)
- Changes to incentive plans
- Changes to currency exchange rates
- Changes to managers & territories
Spreadsheets don’t help you document those changes. All you can do is generate one more version of your master spreadsheets, with the “latest and greatest” formulas / lookup tables / sales data. To audit past commissions, you must locate the correct commission spreadsheet. You must then examine its content, review key numbers, and analyze how formulas were put together at the time.
Of course, you can leverage Excel lookup tables to manage time-dependent variables. For example, you could create a lookup table listing exchange rates and commission rates over time. You can then refer to this two-level lookup table in your formulas. However, this makes your formulas complicated. And you can’t version or track other changes (changes to formulas, changes to territories, changes to incentive plans, etc.).
Compare this with an automated commission solution such as Sales Cookie – you can:
- Define clear time-dependent variables
- Automatically pull the right value for the current calculation period
- Automatically track changes made to formulas, incentive plans, etc.
- Go back in time to view past payouts, rates, quotas, credits, sales, etc.
- Easily handle situations such a title changes, terminations, etc.
- Automatically track every change in an audit log
In this example, a per-rep, time-dependent variable called Quota has been defined (@@ means “magically pull the right value”). Sales Cookie will automatically pull the correct quota value for each rep for the period you want to calculate for. That’s magic!
Transparency & Reporting
When generating individual commission spreadsheets, you have two choices:
- Delete formulas and share spreadsheets
- Keep formulas and share spreadsheets
With the first approach, your reps won’t understand how their commission was calculated. They just have “numbers”, without a way for them to understand where they came from. The best you can do is include explanations / show intermediary numbers within individual spreadsheets. With the second approach, your reps may be confused by complex formulas. Most sales reps aren’t familiar with advanced Excel capabilities because that’s not their job. They also don’t have time to decode Excel formulas.
In addition, if a rep wants to compare their current payout with last month’s, they must locate their previous commission spreadsheet. If they are on the go, they may not be able to do this, unless your spreadsheets are accessible remotely. Even so, it may be a large download, and spreadsheets don’t render well on a mobile device.
Additionally, there is no good way for managers to understand how their team is doing. Sure, you can generate more consolidated spreadsheets for managers, but that’s more work for you. And remember – each time you adjust a rep’s commission, you should also re-generate, re-share, and re-archive manager spreadsheets.
Manually generating spreadsheets is time-consuming. You’re not going to do it every day. However, your reps are closing new deals every day! They want to know, real-time, which deals they were credited with, and what their commission may be. They don’t want to wait for a few days before payday to track their commissions.
Compare spreadsheets with an automated commission solution – you can:
- Define a clear set of commission components for each rep
- Auto-calculate commissions daily
- Allow your reps to see real-time how they are tracking against goals
- Allow reps to easily access their current and past statements online
- Deliver a mobile experience for reps who are on the go
- Empower managers to access individual statements
- Give managers global visibility on their team’s performance
Here is an example of a manager dashboard within Sales Cookie. Managers can visualize their team’s commission data many different ways. Of course, they can also access individual rep statements.
Accounting & Financial Compliance
Commission spreadsheets make it virtually impossible to comply with accounting standards. New accounting standards such as ASC 606 require organizations to amortize the cost of commissions over time – by any means, not an easy task. With commission data scattered around dozens of spreadsheets (with different versions and formulas), it’s hard to understand how one could pass even a basic financial audit. And given the fact that the average organization spends 8% of revenue on commissions (a form of employee compensation), commissions are guaranteed to be part of any financial audit. Failed audits can get in the way of investments or result in fines / penalties.
Another key aspect of compliance is legal compliance. Each US state enforces different legal requirements regarding sales commissions. In some jurisdictions, any commission declared as “earned” is owed and must be paid. Spreadsheets don’t help you deliver legal compliance because there is no way for reps to accept terms and conditions before they can view their payouts. Spreadsheets may seem convenient, but they come with significant legal risks. Ill-intentioned reps may leverage later corrected commission spreadsheets (or even fabricated commission spreadsheets) to support a claim that they weren’t paid correctly.
Compare this with a solution such as Sales Cookie – you can:
- Become compliant with ASC 606 requirements
- Avoid financial penalties / fines due to a lack of compliance
- Require reps to formally enroll in incentive plans
- Require reps to accept terms & conditions before they access statements
- Provide a single version of the truth which reps cannot alter
Here is an example of Sales Cookie’s financial compliance tool. Sales Cookie also makes it easy to draft commission-related terms and conditions, gather e-signatures from reps, submit & resolve disputes, and much, much more.
Spreadsheet formulas are notoriously difficult to troubleshoot. Should you be referencing cell range A7-A20, or A7-A21? What if someone adds another row of data or column? Is it still going to work – or is it going to break without you even noticing?
In addition, spreadsheets typically can’t connect to live sales data from your CRM or accounting system. Spreadsheets work with a stale copy of your sales data. This makes it difficult to deliver accurate commissions.
Calculation errors not only erode trust – they can be costly. Correcting calculation errors requires re-generating, updating, and sharing more spreadsheets. Some organizations have even experienced situations where hidden cells caused incorrect calculations or outright fraud.
To calculate commissions correctly, it’s critical to go through 4 specific stages, which only an automated solution like Sales Cookie can deliver:
- Valuation – correctly assessing each transaction’s value – for example, calculating margins, converting between currencies, scoring transactions, etc.
- Crediting – correctly attributing individual sales to reps / managers / territories based on their involvement in the sales process (potentially with rollups).
- Attainment – correctly assessing each measured rep or territory’s performance – for example comparing total attained revenue vs. quotas.
- Rewards – correctly calculating payout amounts and assigning them to the right person – for example, the territory’s manager.
This is well beyond what spreadsheets can do.
Excel spreadsheets are amazing! However, they were never designed for mass-delivery of commissions to modern sales organizations. You already knew that spreadsheets wouldn’t scale, but did you know that automated solutions such as Sales Cookie could solve so many commission-related problems? Visit us online to learn more. We’d love to help!