Why Spreadsheets Are the Worst Transaction Matching Tool

Finance teams around the world are typically very familiar with the pain that comes with transaction matching financial data, such as your bank statement and general ledger transactions. Data sources are seldom easy to compare, especially when several different formats, date layouts, and transactions. The transactions rarely line up side-by-side and cancel out neatly.

This is when most accountants would get out the ‘Swiss Army Knife’ of accounting—spreadsheets.

Transaction Matching with Spreadsheets

Spreadsheets can easily format and rearrange data from two different sources, so comparing and matching is, to some degree, straightforward. The program’s formulas are design to complete calculations, convert currencies and find specific entries. However, spreadsheets have several flaws that make them an ineffective tool for transaction matching and the office of finance’s needs in general.

Shortcomings of Spreadsheets

Just like a Swiss Army Knife, spreadsheets can be a little inconvenient to use for certain tasks that they’re not really designed to handle. Overlooking these drawbacks can severely affect the accuracy and reliability of your accounts in the following areas:

1. Sources Exported in Different File Formats

Data to be compared and matched comes in many forms, and it’s usually preformatted. Certain file formats can negatively impact the entire transaction-matching process.

Always export your data as a comma-separated value (CSV) file. This is the simplest file type, as the formatting is very minimal, so you run into fewer errors importing it into a spreadsheet.

2. Spreadsheets Can Format Data Incorrectly

Most figures should be numeric, but they can easily be accidentally formatted as a text string or intentionally formatted to round figures. Likewise, copying and transferring data to different cells could lead to numbers being converted to text. For example, account numbers could automatically round to the nearest Euro. Ultimately, rearranging your data can quickly cause many inconspicuous formatting errors, especially if you used preformatted exports for the data you would like to match.

3. Human Error

Sometimes entries are incorrect due to a very slight, almost imperceptible mistake— perhaps a window was hidden below the bottom of the screen, leading the user to believe everything was copied or canceled. A single slip of a finger can add or remove a number, throwing your figures completely off.

Even in the hands of a highly-trained expert, human errors occur. Ray Panko, professor of IT Management at the University of Hawaii, states that the amount of spreadsheet errors is about 1% or more.[1] This percentage may sound miniscule, but when your transactions equal over a million euros/dollars/pounds, your monetary risk is quite high.

4. Roll back

It’s not uncommon to accidentally mismatch entries in spreadsheets. What if you matched two entries, but you later realize there is another, better-suited entry? As you probably know, it’s not easy to roll back the previous match. Which number needs to be reverted?

How to Overcome Spreadsheet Limitations

Because spreadsheets were created as a multi-purpose tool, they will always fall short in one area or another. Finance is a specialized trade, meaning that the best tools for it will also be specialized. In the age of the digital revolution, automation stands at the forefront of the financial transformation.

Automating your transaction matching process is especially important to the successful execution of this process because transactions continually roll in at different times on various accounts. Falling behind will cost your company a significant amount of time and money. Not only that, but your transaction matching process creates the base for the rest of your close. The close process is stressful and complicated enough without having to make up for discrepancies from weeks earlier.

Automating your financial processes is the key to overcoming considerable spreadsheet limitations. To learn more about automation and transaction matching, read about our solutions.

Written by: Ashton Mathai

[1] Olshan, J. (2013, April 20). 88% of spreadsheets have errors. Retrieved from https://www.marketwatch.com/story/88-of-spreadsheets-have-errors-2013-04-17