Snake fall

Excel errors: why spreadsheets are so dangerous for rebate accounting

Posted by Andrew Butt on April 30, 2019 02:04:44

A few years ago news about Excel miscalculations at JP Morgan rocked spreadsheet users’ worlds. A simple cut and paste 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 spreadsheets had caused massive business disruption, nor will it be the last. A similar cut and paste error had previously cost TransAlta $24 million and another Excel mishap – hiding cells 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 spreadsheets to mind once again.

Despite the danger 68% of large firms are still using spreadsheets

Moving forward to today: the use of 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, spreadsheets are not going anywhere fast. Despite the drop, 68% of the large firms surveyed are still using them, as are 69% of small businesses.

Why do people still love spreadsheets?


Excel errors Dangers of spreadsheets in rebate accounting

Surely using 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, spreadsheets are still the mainstay of the deal economy…

  • A few obvious benefits of spreadsheets

The immediate benefits of spreadsheets include their low cost, ease of use, integration with other tools and the ability to manipulate data. They 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 spreadsheets

When manufacturers and suppliers first start managing and tracking deals, including rebates and special pricing agreements, 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.

  • Excel is 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, Excel is 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 deal 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. 

Why spreadsheets are so dangerous when it comes to supplier rebate accounting

The problem arises when 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 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 spreadsheet: deal or rebate management software.

Companies typically choose to use rebate management software when:

  • Accounting mistakes 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 deal management, getting a deal management solution in place is almost a no-brainer.

  • The rebate data gets corrupted

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 errors.

  • Acquisitions means spreadsheets get too big

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

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

Unfortunately Excel is a 1-person solution, not a company solution. When complexity exceeds usability, that’s when things break. Excel is 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 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 spreadsheet.

  • The auditors say they have to do something

Auditors are not great fans of undocumented processes. We have helped people move to deal 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

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 spreadsheet error means that a third of their rebate money is missing, massive problems can arise. If the person who understood the spreadsheet can help resolve this issue, the problem 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 Excel model 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 model that relies on a magician to keep it working.

So what is the alternative to 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 spreadsheets need to be replaced with a system eventually: which is where rebate management software comes in.



request a demo of Dealtrack software


A few benefits of rebate management systems

Rebate management software helps companies calculate, accrue, collect and manage rebates. Software like DealTrack 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 deal management solution right away

No matter what role you play in deal management, there is a solution. But if you’re still in love with spreadsheets, 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, for now.

Although it will undoubtedly be better to use deal management software in the long run, you don’t need to delete all your Excel spreadsheets and move 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 system.

 10 Reasons Rebate Accountants Fail to Claim 100% of Rebate Due




Topics: Rebate Accounting