Finance and accounting departments tend to rely on Excel spreadsheets to manage a growing business’s financial data because they know it, they understand it and old habits die hard. But excel errors are always a risk, in fact various studies report that nearly 9 out of 10 accounting spreadsheets (88%) contain human errors. Moving forward to today, the use of accounting spreadsheets does appear to be in decline, according to a 2018 Benchmarking the Accounting & Finance Function report by Robert Half and the Financial Executives Research Foundation (FERF) – at least in the USA. Their report indicates that executives report a 6% drop in the use of Excel as their company’s primary budgeting and planning tool, while small businesses reduced usage by 9% in the same period. However, accounting spreadsheets are not going anywhere fast. Despite the many excel errors, 68% of the large firms surveyed are still using accounting spreadsheets to solve complex problems, as are 69% of small businesses.

Examples of Excel errors

Back in 2012, news about Excel miscalculations at JP Morgan rocked spreadsheet users’ worlds. A simple cut and paste excel error cost JP Morgan $6 billion and resulted in dramatic headlines like “Why Excel is the Most Dangerous Software in the World”, but it wasn’t the first time accounting spreadsheets had caused massive business disruption, nor will it be the last. A similar cut and paste excel error had previously cost TransAlta $24 million and another Excel mishap – hiding cells in a spreadsheet instead of deleting them – cost Barclays bank millions during the 2008 meltdown. And when a flawed Department for Transport (DfT) forecast model was blamed for the botched bidding process on the £9bn West Coast Main Line rail franchise contract, similar headlines brought the danger of excel errors to mind once again. Most recently the UK failed to add nearly 16,000 confirmed cases of coronavirus to its national track and trace system due to an excel error. The technical error was caused by some Excel data files exceeding the maximum size after they were sent from NHS Test and Trace to Public Health England. It meant 15,841 cases between 25 September and 2 October were left out of the UK daily case figures.

What are the advantages of using Excel spreadsheets for rebate accounting?

Surely using accounting spreadsheets to calculate millions of dollars worth of transactions is not worth the risk, particularly when one considers that the money exchanged during the rebate process can make the difference between a company’s profit or loss? We tried to figure out why, more often than not, accounting spreadsheets are still the mainstay of the deal economy…Some of the immediate benefits of accounting spreadsheets include their low cost, ease of use, integration with other tools and the ability to manipulate data. Accounting spreadsheets are easy to get started with and have a lot of flexibility when it comes to creating what-if scenarios without affecting the real numbers.

• When calculating B2B rebates it’s logical to start with accounting spreadsheets

When manufacturers and suppliers first start managing and tracking deals, including rebates and special pricing agreements, accounting spreadsheets are a great tactical solution. Since they are a readily available answer to a short-term problem it’s logical to start there. Additionally, since the team looking after the numerical elements of these deals (typically finance and accounting) are heavy users of Excel, users tend to use the tool that they know.

• Accounting spreadsheets are the most immediate solution if you need to work outside of core ERP / accounting systems

Additionally, rebate management is nearly always managed outside of the core ERP / accounting management system due to the nature of its complexity. In order to keep track of lots of numbers, extract it from various systems and analyse it, accounting spreadsheets are probably the best solution available to most people involved in rebate management – at least, it used to be.

• Most systems struggle with the complex nature of rebate management

The complexity of the way in which rebates are calculated is also usually not manageable in an accounting system. For example, if suppliers are paid out retrospectively based on selling 103% of purchases on nine products compared with last year, they may get 5% discount. But if they only reach 102% of target they may only get a 3% discount. The nature of the deal itself is commercially creative and complex and cannot be represented in any way in an accounting system.

• Enterprise-level accounting systems can’t cope with the retrospective nature of rebate management

The classic problem with rebate management is that it has a retrospective effect. You might complete the a deal in December, but its payment date may be closer to February. Accounting systems hate that! For them, December is over and by February the accounts are closed. Accommodating the complexities of supplier and B2B customer deals in an accounting system with closed-off financial periods is counter-intuitive.

The problem with Excel spreadsheets 

Excel errors and problems arise when accounting spreadsheets get too complicated, too full of data, or too unwieldy behind the scenes — and the ability to use them grows all out of proportion. At some point the spreadsheet model starts creaking, or the person who created the genius spreadsheet in the first place moves on, illustrating the nature of a key person dependency. There are security risks as well — if a confidential spreadsheet is just lying, unencrypted, in a filing system somewhere. A serious data leak could reveal a massive security issue inherent in the storage and sharing of accounting spreadsheets. Then there is a question of information availability — if many people access the same spreadsheet, whose version is the latest? Sooner or later, those managing deals realise that there is an alternative to accounting spreadsheets: rebate management software.

Why choose rebate management software over Excel spreadsheets?

• Accounting mistakes because of spreadsheets have come to light

In public limited companies accounting mistakes could lead to real figures being reported to the stock market. Once this happens in a context related to rebate management, getting a rebate management system in place is almost a no-brainer.

• The rebate data gets corrupted in an accounting spreadsheet

Spreadsheets can be tampered with by the ignorant – malevolently (in the case of fraud) or accidentally. But they can also become lost, corrupted, or simply too unwieldy to use due to hidden rows or formulae, accidental deletions, transpositions or copy and paste excel errors.

• Acquisitions means accounting spreadsheets get too big

When businesses are acquired, rebate accounting becomes even more complex. We have been involved in situations where people choose to userebatemanagement software because their accounting spreadsheets contain so much data that they actually won’t open any more.

• The accounting spreadsheets need to be shared (safely) between multiple people

Unfortunately accounting spreadsheets are a one person solution, not a company solution. When spreadsheet complexity exceeds usability, that’s when things break. Accounting spreadsheets are not ideally suited to be shared and used by multiple people. Even with online versions of the software, sharing data between multiple users is fraught with the risk of misunderstandings, information overlap or simultaneous data-entry that can cause grave excel errors.

• Rebate accounting complexities add too much information over time

Unfortunately the requirement to manage deals is never short-term: it persists. Accounting calculations need to take place next year, too, and as new factors are in play each year, so this complexity is added to the accounting spreadsheet.

• The auditors say they have to do something

Auditors are not great fans of undocumented processes. We have helped people move to rebate management software on more than one occasion when their auditing and compliance risk is too great. When their internal auditors say the tools they are using to collect millions or hundreds of millions of dollars are simply not adequate they know they need a system to handle it and turn to rebate management software to help them.

•  Disasters happen and money is lost because of excel errors

Disasters can happen when someone does something as innocuous as creating and inserting new cells and find they’re missing the final rows of the table. When they realise that this simple excel error means that a third of their rebate money is missing, massive problems can arise. If the person who understood the accounting spreadsheet can help resolve this issue, the excel error may be addressed temporarily. However, if the only person who really understood the spreadsheet has since left the business, something has to be done.

• The team is becoming too inefficient

If the accounting spreadsheet has grown, and the only person who knew how it worked leaves the company, even if disasters like incorrect calculations don’t arise, eventually the new team gets to a point where it just takes too much time. Instead of doing the value-adding tasks they’re mean to do, if they are spending too much time trying to keep a spreadsheet going, it’s time to change. It’s not reasonable or scaleable to have a very complex Excel accounting spreadsheet that relies on a magician to keep it working.

What is the alternative to Excel spreadsheets for rebate management?

In short: security of data, robustness of process, and key person dependency are the main drivers for people moving beyond having a short-term fix in place to being able to manage deals with a proper rebate management system. In many companies accounting spreadsheets need to be replaced with a system eventually: which is where rebate management software comes in.

A few benefits of rebate management systems

‍Rebate management software helps companies calculate, accrue, collect and manage rebates. Our software gives finance teams greater certainty; keeping information on trading agreements accurate and up-to-date at all times. There are benefits for commercial teams too – like being able to negotiate the best deals and track performance daily. The C-suite can also be confident they are fully compliant, thanks to comprehensive audit trails and transparency of the retrospective income that affects margins.

‍You don’t have to swap over to a rebate management solution right away

‍No matter what role you play in rebate management, there is a solution. But if you’re still in love with spreadsheets for accounting, or are too short on time to create an RFP in order to get a new solution don’t be concerned: it’s still possible to carry on using Excel accounting spreadsheets for now. Although it will undoubtedly be better to use rebate management software in the long run, you don’t need to delete all your accounting spreadsheets and move to a new system straight away. Rather, use the expertise you have to make sure you get the right software. And while you go through the process of getting a new rebate management system in place, keep your accounting spreadsheets and assess the processes that you currently use. That way you can make sure to keep the processes in place (or streamline and improve them) when the time comes to move to a new finance system.