You work for a fast-growing company with a vibrant sales organization. You feel good about the company’s performance, but when it comes to managing sales commissions – your commission operations team slogs within a pile of spreadsheets to prepare commission statements for every individual sales rep. Every month, your ops team and sales management deal with tons of inquiries and disputes from the field, creating an unhealthy work environment.
Who is to blame? The sales team? The commission administration team? Or is it the spreadsheets system that you use?
While spreadsheets are very efficient in automating all types of calculations, when it comes to sales compensation, they fail miserably. What’s so special about sales compensation that spreadsheets don’t work? Let’s find out!
1. Manual Errors
During the 2012 London Olympics, a staff member made a manual error in some spreadsheets. It led to a sale of 10,000 extra tickets, which had to be later refunded or exchanged. Even worse, in 2012, JP Morgan lost around $6 million in trading due to copy and paste errors. The examples are numerous, which means you are not the only one who deals with manual errors in the spreadsheets. Companies using spreadsheets for sales compensation have to heavily invest in manual verifications and checks and balances to ensure that manual errors don’t go unnoticed.
2. Real-Time Reporting
Most organizations using spreadsheets for sales compensation can publish the commission statement only after the end of the pay period. This is because data files must go through several manual steps before the spreadsheet system can churn out the results. It is just not possible to publish the numbers more frequently. This leads to shadow accounting, which means execs spend numerous hours tracking their sales and earnings, instead of selling.
3. Auditability
Sales compensation calculations evolve throughout the year. The underlying math, the planning logic, and the data interpretations may change. In spreadsheets, it is almost impossible to track what was changed, by who, and when. This lack of auditability is a huge concern for finance and internal audit when it comes to signing off on the pay numbers. This is especially a huge problem when multiple compensation administrators are working on the same set of spreadsheets from a shared server.
4. Workflow
Sales compensation is often a large expense item, and the end-to-end process touches finance, IT, sales ops, HR, and of course sales. With so many stakeholders involved, and large sums of money involved, it is important to be able to communicate and seek approvals as needed. Unfortunately, spreadsheets are not designed to automate or manage a multi-stakeholder business process. While one can build complex VBA scrips to trigger outbound emails, overall, it remains a clunky and unscalable solution.
5. Time Dimension
Sales compensation plan logic evolves every year. The sales transactions come with a commission date already stamped on them. This date determines the plan period to which the transaction should be assigned, and the entire calculation for that transaction should be as per the logic setup for that plan period. This process of applying date-specific logic is very difficult in spreadsheets. As a result, the companies using spreadsheets find it very hard to make changes to their plan logic. This negatively impacts their ability to motivate the sales reps and overall business outcomes.
6. Dependency on Specific Individuals
Spreadsheet systems for sales compensation often entail gigantic workbooks with dozens, if not hundreds of tabs, and several reference sheets for plan logic. This setup allows only one individual at a time to make updates to the data or the formulae. Thus, in a spreadsheet environment, it’s almost always one single individual who can debug the logic or make any updates. This puts entire commission operations at risk.
Despite all the above shortcomings, spreadsheets are still a very popular tool for managing sales compensation. Primarily because spreadsheets are almost always the tool of choice if you have just a handful of sales reps. But as the size of the sales team grows, so does the complexity, and spreadsheets become unmanageable. It’s not easy to wean off the spreadsheets, but there are some proven methodologies to make the transition seamless and somewhat easy.
If your company is successfully using spreadsheets for sales compensation, please email me at mgupta@spectrumtek.com. I’d like to hear about your experiences.