Most workers in the business world believe that reconciliations are a routine task that only accountants deal with as part of their jobs. However, in the banking industry, whether a mega-bank with $750B in assets, a super-regional with $25B to $250B, or even a local community bank or credit union with assets from $750M to $25B, financial and operational reconciliations are a way of life for operations staff throughout the organization.

Routine reconciliation tasks are often perceived as required drudgery and the cost of doing business to make sure all business information is aligned. While that might be true, there are ways to reduce the manual labor required to constantly compare and “balance” information between multiple business systems.

In this long-form explainer, we will dive deep into what reconciliations in banks are, why they exist, and most importantly, how to automate banking-industry reconciliations using modern digitization strategies, such as robotic process automation.

What is the definition of automating banking industry reconciliations?

The automation of banking-industry reconciliations differs from “financial reconciliations” because banking-industry reconciliations are, as the name implies, specific to the banking industry—whereas run-of-the-mill “financial recs” occur in every industry.

Banking-industry reconciliations are spread out across the organization; they don’t simply reside in the hands of accountants in the Finance department. They are found in every operational business unit in banks, from loan operations to deposit ops. Automating banking-industry reconciliations is defined as the conversion of a manual bank transaction or product (internal or external) reconciliation into an automatic computer-driven process that requires minimal human intervention.

What is a reconciliation process in the banking industry?

A reconciliation in the banking industry is defined as the process of comparing and matching transactional data between different banking systems on a daily, weekly, quarterly, or annual basis.

Reconciliations are typically performed by back-office bank staff manually, using extracted banking-system data from multiple unintegrated systems. This data from disconnected systems must be matched against other system data in Excel. If any items are determined not to match, then the back-office worker must research and resolve why the system data does not reconcile. Sounds arduous, right? It gets worse: There are still some banks that perform this work… on paper! Yes, even in 2021, some banks still perform numerous recs on the way it was done 100 years ago.

What are the different types of reconciliations that happen in banks?

Financial Reconciliations – most commonly thought-of recons

These reconciliations are most commonly thought of and reside at the top-of-mind for bank execs. These are recs that accountants under the CFO, CAO, or Controllers process which directly plug into the financial close process. Of all reconciliations that occur in banks, these are typically the most advanced, the most automated, and tend to be more centralized.

Operational Reconciliations: The most pervasive and “hidden” of banking-operations recons

These common banking reconciliations differ from financial reconciliations in the sense that they are related to individual business processes, and most often occur at the daily banking operations transaction-level of detail. Some of these recs impact the general ledger directly; others do not.

Banking operations reconciliations are defined as daily, weekly, monthly, quarterly, and annual comparison of two (of more) sets of data, records, or transactions, from different banking systems, in an effort to confirm that the information agrees. If the information does not match, bank staff must then manually research, determine why the balances aren’t in parity, and correct/update the system once the issue has been resolved.

Additional Characteristics of Banking Industry Reconciliations

Banking reconciliations are not all the same in terms of frequency, the reason for their very existence, or why they need to be processed.

Routine Banking Reconciliations. The majority of bank-operations reconciliations take place on a daily basis. These are commonly referred to as “expected” and “process-related” reconciliations.

Non-Routine Banking Reconciliations. Other ad-hoc reconciliations that occur are typically used in problem-resolution scenarios. Example: A customer calls in with a missing-wire information request. A bank staff member then must manually research and reconcile this one-off request to find out where the missing wire is.

Project-based Banking Reconciliations. During core-system migrations, the system providers, consultants, and bank staff will always need to reconcile data between the old system and the new to make sure that everything ported over correctly.

Examples from the banking industry

Now that we have the basics of banking reconciliations out of the way, let’s dive into specifics.

Each day, banks must perform hundreds of reconciliations, which are spread out across the different departments and business units.

Each flavor of departmental recon is slightly different, because a multitude of different banking transactions or process data are being reconciled. But, all banking reconciliations benefit from standardization, automation, and centralization (where possible).

Importantly, almost every bank has the exact same types of reconciliations that they must process. This is because all banks process similar types of transactions. However, each bank does each reconciliation slightly differently. This is due to the fact that all banks have different combinations of core applications (FIS, Jack Henry, Fiserv), ancillary applications (nCino, Salesforce, Centrix, Finastra), and general ledgers (SAP, Oracle Peoplesoft, Lawson, etc.).

Now let’s look at some specific, real-world examples.

Operational banking reconciliations by business unit:

Retail Branch

Daily Teller Transaction Reconciliation

The Branch Operations Help Desk staffers at this bank review a roll-up of teller-drawer differences daily, which includes cash dispenser/recycler uses, automated teller assignments, memo-posting, and others. They then process a report—manually, of course—to reconcile the differences and analyze profitability. This manual, redundant process is a ripe target for automation, since it can result in 60 hours per month of capacity savings.

ATM Network Reconciliation

This bank’s staff must manually reconcile ATM deposit clearing balances on a daily basis—for the bank’s entire ATM network. Two reports must be downloaded from the general-ledger system, and another must be downloaded from the document-management system. These reports are then manually analyzed and reconciled in Excel to determine if any mismatches exist.

Remote Official Checks Reconciliation from Internal Report to General ledger and Records Management

Affiliates and clients of this bank (title, escrow, insurance companies, law offices) can print official (certified/cashier’s) checks from remote locations. However, each of these on-demand official checks and account balances must be reconciled by banking staff, manually, on a daily basis after they receive that day’s “Official Check Transaction Report” from the IT Department. This report must then be reconciled—manually—by comparing it to eight different reports from the general ledger and document-management system.

Deposit Operations

Interest Official Checks Daily Reconciliation

CD and Money Market account-interest official checks, which are generated by the bank branches, must be manually reconciled against account balances on a daily basis. Two daily reports received from IT and Finance must be manually processed into an Excel sheet. Next, eight separate reports must be accessed and pulled from the general ledger system and a document-management system; these, in turn, must be reconciled and validated against each other. Final outputs must then be manually saved to a network drive.

Cash Suspense Reconciliation

Cash Suspense Accounts—used to temporarily carry discrepancies (e.g., invalid account numbers) for pending branchless (e.g., mobile and check-based) banking transactions—must be manually reconciled against the general ledger by bank finance staff daily. Here’s what it requires: Two general-ledger reports must be downloaded from one GL system. That data must then be combined into a reconciliation spreadsheet. Next, the bank staff must access the Cash Suspense Account to download the pending transactions and manually balance them against the general-ledger reports.

Certificate of Deposit Account Registry Service (CDARS) Report Reconciliation

At this bank, certificates of deposit (CDs) must be reconciled against the CDARS system on a daily basis. To do this, back-office staff must manually download the general-ledger report that corresponds to CDs that had been registered, using Promontory, an online CD registration provider.

Loan Operations

Deferred Income Letter of Credit Reconciliation

Daily Loan Reconciliation

On a daily basis, all loan reports in this bank’s document-management systems must be compared to—and reconciled against—its general-ledger system. This means that bank staff must manually extract these ten reports, one by one, to match against the general ledger. They must then manually reconcile these ten reports against the GL in Excel. And they have to do this every single day. That’s two hours’ worth of work!

Home Mortgage Disclosure Act (HMDA) Monthly Compliance Review and Reconciliation Process

During the HMDA monthly review process at this bank, mortgage business-unit staff must access the mortgage-origination system, extract all relevant mortgage data, paste it into Excel, and then manually format it for review. This formatted data is then manually reviewed and tested by compliance staff. Finally, it’s updated and uploaded into CRA WIZ (Wolters Kluwer) after the monthly review is complete, for final processing before upload.

MERS Balance and Reconciliation

Despite having a loan MERS compliance system, the monthly MERS reconciliation process at this bank still requires staff to extract loan information from multiple systems, and then manually work through hundreds of Mortgage Identification Numbers (MINs) before they are deemed compliant and ready to submit.

Treasury Management

ACH Holdover In Process Reconciliation

ACH requests with insufficient time to process them by the close of that business day are logged as holdover journal entries. Thus, on a daily basis, bank staff must manually reconcile two different general-ledger reports from the GL system, outside of the system, in order to determine the final balance and find the mismatched transactions.

Fiserv PEP+ ACH Clearing Reconciliation

At this bank, ACH payments were automated, using Fiserv PEP+. However, back-end reporting against the general ledger was not. Thus, in order to reconcile the daily ACHs processed through the Fiserv system against the general ledger and document-management system, bank accountants must manually download reporting data from each system separately. Then they must use Excel to balance them against each other to find any missing transactions.

Monthly Securities Interest Payment Matching Against Forecast Reconciliation

Treasury analysts must manually match routine monthly securities interest payments from five different websites, and check them against an external portal’s payment projections. Once reconciled, GL updates must be manually loaded into FIS Horizon and sent to Finance (again, manually) for review.

Business and Commercial Lending

Customer Cash Letter of Credit Reconciliation

At this bank, cash-secured letters-of-credit transactions must be manually reconciled by staff on a daily basis to ensure accounts balance against the general ledger and a document-management system. This reconciliation requires bank accountants to pull up to three different reports from two different systems, and then reconcile them, manually, in Excel.

Deferred Income Letter of Credit Reconciliation

Each day, bank staff must manually reconcile multiple types of letters of credit or LOCs. Deferred letters of credit are just another LOC on the daily reconciliation list for processing. This requires downloading PDFs of GL reports and transaction documentation from two different systems—and then moving that information into a spreadsheet to balance both.

How much do recs cost your bank annually?

Banks have hundreds, if not thousands, of daily, weekly, and monthly reconciliations.

Each of these reconciliations (that aren’t yet automated) require at least an hour of work to complete. A typical “low-complexity” banking-operations reconciliation requires one to two hours of daily work to complete. More complex reconciliations take two to three hours. So, most daily banking reconciliations take an average of two hours to process.

To calculate how much bank reconciliations are costing your bank on a continuous basis—and we know the above assumptions are correct, based on our extensive banking operations improvement experience—we can deduce the cost below:

  • If bank staff on average cost $36 per hour, fully loaded with benefits.
  • If there are 260 work days per year.
  • That means that each rec takes 260-783 hours of labor, or an average of 520 hours per reconciliation per year.

Following the above logic each reconciliation costs a bank, on average, $18,000 per year!

If your bank has 300 reconciliations spread out through the organization, that is costing you a whopping $5.4 million dollars.

This doesn’t even consider time off, sick days, turnover, and training of new staff!

How to automate reconciliations in banks

There are six major ways to automate banking reconciliations.

1. Financial Reconciliation Software

Hint: These focus almost entirely on financial reconciliations, and not operational recons.

A big player for “automation” in the financial-reconciliation space inside of bigger banks is Blackline. Other competitors include Oracle, Wolters Kluwer, and FloQast. In reality, these systems act more as a central place to store recon processes that you build out, as opposed to linking to core systems where the baseline data resides.

Pros: The biggest advantage of Blackline is that it centralizes financial reconciliations and aids in the process of standardizing them. And here at The Lab, we are very fond of standardization!

Cons: A major problem with Blackline (and other similar software platforms) is that they perpetuate the “Core Systems Blues” dilemma of just adding another system that does not easily integrate with others’ banking applications. Go figure. Also, staff must be trained on how to use the software, as these are all specialized accounting applications.

These reviews confirm the integration challenges:

2. Power Query

Pros

  1. Free/low cost
  2. Similar to a macro; easy to get started
  3. Integration with other tools or platforms such as Azure and PBI

Cons

  1. Need knowledge for dev and maintenance

3. Python

Pros: 

  1. Can download reports through UI automation (Selenium, Automate, Pywinauto, etc.).
  2. Easy language to learn, used a lot with data manipulation, can integrate with APIs.
  3. Can parse text and digital PDF reports.
  4. Open-source OCR available. Microsoft, Google, and Amazon APIs also widely available for AI capabilities.
  5. Libraries to work with Excel as well as handle large datasets (Pandas).
  6. Can be made into a standalone executable (.exe) that can be launched by users.
  7. Scripts can be launched through other RPA softwares.
  8. No software cost.
  9. Free.
  10. Full functionality.

Cons: 

  1. Dev time would be greater (particularly for UI automation and testing).
  2. Would require Python knowledge for maintenance.
  3. Higher coding skill is required for development and maintenance.
  4. Highly dependent on the environment. Might trigger more IS concerns.
  5. Speed Limitations: Python is an interpreted language, so you may find that it is slower than some other popular languages.
  6. Not native to mobile environment: Python is not native to a mobile environment and it is seen by some programmers as a weak language for mobile computing.

4. Powershell

Pros

  1. Can parse text reports and manipulate data (.txt files).
  2. Can natively automate Excel using Interop/COM objects.
  3. Scripts can be launched individually or through other RPA softwares.
  4. PowerShell is free to use.
  5. Native window feature/module. Not dependent on the environment.
  6. Support: It’s easy to find PowerShell support and resources; it has native .NET framework integration.
  7. Rich Cmdline-based interface: Cmdline creates a virtuous cycle between the user and the system.
  8. More secure scripting engine: PowerShell supports cert-based signed scripts and by default is super restricted in operation. PowerShell support-group policy based control of the environment.

Cons

  1. Cannot perform UI automation.
  2. Cannot extract data from PDF files.
  3. Reports would need to be downloaded using RPA software (or manually) first, then launch the script to automate Excel recon.

5. APIs

Pros

  1. Fastest and easiest way if certain system has API available to the devs.
  2. Usually have free access with purchased license of current service or products

Cons

  1. Limited to the existing features of the system.
  2. Specific to certain system. API is just a channel; you still need coding language to make it work.
  3. Mid-high level of coding skills is required.
  4. Adding enterprise features is impractical: Audit logs, error handling, event-based triggers, security features—these things can be accomplished with PowerShell scripts, but realistically they often aren’t. It takes a lot of time and a whole mess of scripts to create the robust feature set that robotic process automation comes with right out of the box.

6. Robotic Process Automation

Pros:

  1. Automated bank reconciliation can cut down on errors and ensure that reconciliation process delivers consistent results in a timely manner.
  2. It can keep qualified individuals from spending an excessive amount of time on this activity.
  3. RPAs can handle much higher volumes of work than humans.
  4. Rich experience and reusable components in UiPath for Excel-based recon.
  5. Easy to perform maintenance if the clients have internal devs.
  6. All team members have the skill level to handle the recon bot.

Cons:

  1. May have difficulties understanding multiple invoices.
  2. Would require professional installation of the RPA tool.
  3. System upgrades can throw off the RPAs

Robotic process automation for bank reconciliations

Automate your way out of banking recons  

When they perform recons, your back-office workers are reduced to the humbling job of “acting as human glue” between different systems that won’t, and can’t, talk to each other. That’s true, as you know, even among different products from by the same core-system provider!

Fortunately, you can quickly and easily automate your bank’s back-office recons with the help of robotic process automation or RPA from The Lab. RPA “bots” do all the drudge-work that humans hate. And they do it faster, non-stop, without ever making mistakes.

Let’s take a common example that you know your staffers despise: Reconciling commercial cash vaults against data from Brinks or Loomis. If you’d like to “skip to the good parts,” simply watch this little two-minute video about it from The Lab:

 

The top 20 reconciliations for your bank to automate

You have to contact The Lab for the top 20 robotic process automation reconciliation short-list and a demo!

We can’t post the list online because our competitors keep stealing our marketing material!

We will give you a hint, though. One of them is the ATM network reconciliation. And the others are all the reconciliations that your staff dread on a daily basis.

We have automated them for many of your peer banks. And chances are, you have even heard about our robotic process automation work at regional and community banking forums. Check out this video and see for yourself how bots can manage this tedious task for you and your staff:

Imagine – if you automate only the top 20 reconciliations with The Lab, you can recoup at 520 hours of labor per reconciliation of manual labor per year.

That’s well over $370,000 of direct operating cost.

With our reconciliation automations, you can recoup our project cost and have positive ROI in Year One. We typically are able to realize 25 percent savings for our client in Year One.

Year Two results in 75% reduction of operating cost with the automated reconciliations.

 

How The Lab implements Robotic Process Automation for bank recs

Robots don’t just work “out of the box”

Fact is, if you want a bot, you’ll need to configure it to your bank’s unique apps, processes, and even keystrokes. And that’s after you’ve identified the “bot-friendly” use cases where they can best be employed.

Big-name consultancies will tell you that this requires a million dollars, a year or two, off-shoring, and a built-from-scratch center of excellence.

Wrong, wrong, wrong.

We can—and do—implement bots, in groups or on an à la carte basis, every single day, for clients from the Fortune 500 on down, right from our offices in Houston. We could have your first one up and running in as little as four weeks.

Let us help you find the use cases.

At The Lab, we’ve spent 25-plus years helping clients to capture newfound efficiencies and knowledge-work capacity, using our patented Knowledge Work Standardization® process. That’s how we identify opportunities for installing RPA for you.

We’ve done the analysis—now we can apply it to your organization. We’ll find the low-hanging fruit that’s readily robot-friendly. We’ll also identify the processes that, with some tweaking, could become massively robot-friendly. And along the way, we’ll spotlight any wasteful processes that shouldn’t be there in the first place.

Real implementation results. Real fast.

The Lab can get your first banking bot up and running in just weeks. It’s an irresistible offer, made all the more enticing by the fact that you don’t have to force this change on your workforce. Once they see it in action, they’ll be clamoring for more.

Call The Lab at (201) 526-1200 or email info@thelabconsulting.com today for your free, no-obligation 30-minute screen-sharing demo. You’ll love it!

CONTACT US TO BOOK A DEMO

    * Required