excel spreadsheets

Excel errors: why spreadsheets are so dangerous for rebate accounting

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

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 human entry error is always a risk which we saw a few years ago when 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 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 spreadsheets to mind once again.

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

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 spreadsheets to solve complex problems, as are 69% of small businesses.

Why do people still use Excel spreadsheets for accounting?

 

excel spreadsheets

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 for accounting

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 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, Excel 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 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 excel spreadsheets: deal or rebate management software.

Companies typically choose to use rebate management software over spreadsheets when:

  • 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 a 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 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 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 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 spreadsheets

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


OnlineDemoDealTrack

 

request a demo of Dealtrack software

 

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

New call-to-action

Topics: Rebate Accounting