Account Reconciliations Beyond Spreadsheets

Spreadsheets are no doubt a great solution for data analysis and intermittent calculations, but their use as a long-term account reconciliation solution proves unsustainable.

Recent studies suggest that Excel is still the most popular reconciliation tool for roughly 70% of the world’s financial institutions. Undoubtedly, its versatility, flexibility and wide accessibility make it a “weapon of choice” for the majority of finance and accounting professionals. But does conventional spreadsheet software have the proper functionalities to serve as an end-to-end reconciliation solution?

As reconciliation tasks grow more complex and transaction volume expands, F&A specialists inevitably encounter numerous unforeseen issues down the road. Backlog is piling up, technical failures emerge, document owners and users change. As a result, business critical pieces of information may fall through the cracks, carrying unpredictable implications. It becomes evident that the long term usage of spreadsheet technologies as a permanent approach in the reconciliation process is far from a productivity enhancement.

Spreadsheets containing interrelated tables with thousands of rows and columns have innate limitations and tend to fail under excessive data loads. Manual manipulation of intricate formulas and advanced Visual Basic scripting are likely to produce flaws.

Changes in source systems and migration campaigns, or even a key user out of office could cause major disruptions in the workflow and data inconsistencies. Additionally, unrestricted control on user permissions, absent audit trail options and minimal data security easily constitute compliance lapses.

Top 6 reasons why commonplace spreadsheet technology can no longer meet the advancing reconciliation needs of mature business organizations

1 )  Lack of automation – a taxing compromise

As a rule, manual reconciliation is an overwhelming job. Input data is retrieved from various sources and in different formats. Compiling and transforming it to achieve normalization and enrichment on import, all done by hand, is tedious, time consuming, error prone and without auditable control.

Reliable, scalable and maintainable automation of those recurring tasks is beyond the scope of spreadsheets product features. However, a modern reconciliation tool can handle the entire process automatically, applying any necessary data clean-up and enrichment as soon as the data is available.

2)  Slow and unreliable performance in larger scales

Dealing with larger data sets than a spreadsheet application is designed for can really slow you down. Heavy duty documents take ages to load, update and save even with the simplest of changes. Almost every organization at some point generates such documents for a variety of purposes in different departments (Budgeting, Sales, Planning, etc.).

Also, in a regular spreadsheet, manual cell value updates may accidentally interfere with prior values, formulas and settings. The insertion of an additional field normally suggests that historic data should be related. However, maintaining historic relevance in spreadsheets is not always feasible.

Obviously, in those cases the capacity of customary spreadsheet software is exceeded. Paradoxically, growth in data loads (usually a measure of success) can become a significant challenge for which spreadsheet technology is ill-suited.

3)  Rudimentary matching capabilities

Reconciliation via spreadsheets relies excessively on the knowledge of the reconcilers. In spite of the numerous predefined financial, mathematical, statistical, and database functions, spreadsheets are incapable of applying complex business logic. A specialized reconciliation solution, on the other hand, accommodates a rule-based communication across data sheets and information sources. It also takes steps further with exception management features, thus ensuring reconciliation process continuity, independent of the human factor.

According to Ignat Chakaroff, Product Specialist at ReconArt:

“The only known way to find duplicates within Excel is to have them all on one single sheet, which not only creates a data and processing burden, but also limits your matching capabilities to one single column at a time.

Our data shows that three out of four new ReconArt clients today face one-to-many or many-to-many matching scenarios, sometimes even coupled with variance tolerances which is near-impossible to achieve just by using Excel.”

4)  Versions history and data accuracy

Employees regularly store documents locally on their PC for reporting purposes. When a handful of experts rely on the same Excel report exchanged over email or some form of shared repository, it is arguable which is the latest version. Rolling back changes is also out of question. Collaboration among multiple users and keeping track of document versions easily gets out of hand. The aftermath can involve overlapping modifications, conflicting redactions, as well as inaccurate and repetitive data.

Utilizing web-based applications hosted in the cloud, the proper mitigation of these complications is essentially effortless. They would allow smooth, secure, and synchronized collaboration while sparing troublesome deployment and upgrades.

5)  Tracking changes and auditability

Data entries and calculations output in a spreadsheet reconciliation framework often appear chaotic and unstructured. There is no clear workflow schedule visible or enforced. The separation of duties remains a vague concept. Changes administered on the formulas leave no definitive audit trail, although the reconciliation results can be affected dramatically.

Drilling down to the finest transaction details frequently relies on fleeting human recollections which renders basic understanding by uninvolved parties impossible. There are no comprehensive exceptions management procedures at place. Those factors seriously hamper audit readiness.

6)  Absent analytics functionality

Spreadsheet based reconciliation relies on daily / monthly snapshots of data and fails to fully capture the dynamic process. An essential functionality such as transaction dating, which flags and sorts out outstanding transactions, is unattainable with generic productivity software. Reporting on aging and tracking exceptions to resolution remains out of scope.

It is nearly impossible to generate advanced analytic cross-sections of matched transactions revealing important metrics such as volumes through different channels, fees paid, payment delays, etc. On the other hand, a specialized reconciliation tool offers enhanced analytics, sophisticated aggregations and dashboards, visualization of KPIs and integration with more advanced analytical solutions.

Automated reconciliations with enterprise-class SaaS solution: capabilities & benefits

An automated end-to-end solution is the smart choice to manage an elaborate reconciliation process in a professional manner. If you identify with the pain points outlined above, it could well be the right time to upgrade to an agile reconciliation and reporting solution.

ReconArt™ was developed with these challenges in mind and has successfully replaced retrograde spreadsheet-based tools with a solution capable of:

• Full automation under one integrated product – data sourcing & import, transformation & enrichment, matching, exceptions management, reporting & analytics
• Reconciliation of multiple data sources in various formats – fast & efficient
• Web-based, ERP integrated, business user friendly
• Super granular audit trail & full compliance
• High volume transactional matching with the support of a powerful rules engine capable of applying advanced rules across many accounts at once

Learn more about the challenges our clients faced and how ReconArt™ revolutionized their reconciliation process in the post-spreadsheet era.

TekSavvy: need for scalabilitiy and improved accuracy of month-end reconciliations

Prior to adopting new technology, TekSavvy was using Microsoft Excel to perform its monthly reconciliations. However, the single workbook created to handle them grew to be very cumbersome to deal with. Additionally, the way it was set up back then, only one person at a time could perform reconciliation tasks. There would be errors and sometimes, most likely because the workbook was so large, Excel would crash before the document could be saved. This would result in data loss plus all the ensuing wasted time to recover it.

As TekSavvy was expanding rapidly, the company knew it needed to get beyond Excel which wasn’t able to keep up with the level of activity that the company was seeing. Therefore, TekSavvy went out to market looking for a tool that would automate its month end close and account reconciliations.

University of Auckland: need for improved balance sheet account reconciliation controls

The University of Auckland has a decentralized finance function. This leads to over 2,500 individual reconciliations that have to be completed each month. The task of collating all the balance sheet reconciliations for the quarterly review was overly onerous and time consuming. Accountants would perform balance sheet reconciliations manually using Microsoft Excel spreadsheets. These reconciliations were then printed, reviewed and approved, and then stored as hard copies across multiple physical locations.

In summary, prior to embarking on the search for technology to address these issues, the University was experiencing major process inefficiencies, lack of visibility over key account balances and compliance with internal policy, internal control weaknesses raised by external auditors, risk of non-compliance with GAAP, and unnecessary printing and storage costs.

First National Bankers Bank: time savings & enhanced risk identification capabilities with the automation of key reconciliation

Prior to implementing ReconArt™, the bank used an internally IT-developed program only for the transaction matching and account reconciliation of its own account with the central bank (the Fed). All other reconciliation processes were done manually in Excel spreadsheets, with a heavy reliance on IT personnel when a mismatched item occurred.

FNBB has gained multiple advantages from the deployment of the ReconArt™ solution, such as:

• Automation of key reconciliations in Finance Division, which were previously Excel based.
• Time savings in several different areas – minimized IT support, 75-80% time saved in Fed reconciliations, speed up audit at month and year-end.
• Elimination of manual and semi-manual spreadsheet-based processes.
• Immediate identification of exceptions and their related risk.

Fiesta Insurance: immediate return on investment in automated reconciliations

As a direct result of deploying ReconArt™, Fiesta has gained multiple benefits, including automation of processes that were being handled manually using Excel spreadsheets and manual data input into QuickBooks.

“ReconArt™ offered excellent value and immediate return on investment. ReconArt as a partner demonstrated a clear and concise understanding of transaction matching. The modern design of the ReconArt™ solution means business users can configure new processes including data mapping and match rules with no more than Excel skills. This extreme ease-of-use makes ReconArt™ “revolutionary in the field of automating reconciliations” without IT involvement.”
says Diane Chaidez, Controller at Fiesta Insurance Franchise Corporation

New South Wales Treasury Corporation (TCorp): focus on value added tasks and eliminate operational inefficiencies

TCorp was spending time extracting information from a number of systems and importing them into spreadsheets manually. After that, users were required to print the information and manually match items line by line between reports to undertake a reconciliation. The main cash reconciliation could itself take up to half a business day each day for one staff member. The information was stored in spreadsheets, something that TCorp’s leadership team believed posed a risk.

TCorp went out to market looking at a number of options for an automated reconciliation solution. Its two primary objectives were to find software to:

• Reduce operational risk – move away from vulnerable spreadsheets, and
• Save time and resource – free up valuable headcount to focus on higher value tasks

The overall benefits are best summed up by TCorp’s Head of Finance & Reporting, Raffi Kalenderian:

“ReconArt™ has delivered significant time savings along with risk reductions. It paid for itself almost immediately. We will now continue to deploy ReconArt™ to other important reconciliations within the business as time progresses.”