How to Automate Reconciliations in the Credit Union Industry with Robotic Process Automation
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 credit union industry, 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 credit unions are, why they exist, and most importantly, how to automate credit union-industry reconciliations using modern digitization strategies, such as robotic process automation.
What is the definition of automating credit union industry reconciliations?
The automation of credit union-industry reconciliations differs from “financial reconciliations” because credit union-industry reconciliations are, as the name implies, specific to the credit union industry—whereas run-of-the-mill “financial recs” occur in every industry.
credit union-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 credit union, from loan operations to deposit ops. Automating credit union-industry reconciliations is defined as the conversion of a manual credit union 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 CREDIT UNION INDUSTRY?
A reconciliation in the credit union industry is defined as the process of comparing and matching transactional data between different credit union systems on a daily, weekly, quarterly, or annual basis.
Reconciliations are typically performed by back-office credit union staff manually, using extracted credit union-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 credit unions that perform this work… on paper! Yes, even in 2021, some credit unions still perform numerous recs on the way it was done 100 years ago.
What are the different types of reconciliations that happen in credit unions?
Financial Reconciliations – most commonly thought-of recons
These reconciliations are most commonly thought of and reside at the top-of-mind for credit union 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 credit unions, these are typically the most advanced, the most automated, and tend to be more centralized.
Operational Reconciliations: The most pervasive and “hidden” of credit union-operations recons
These common credit union reconciliations differ from financial reconciliations in the sense that they are related to individual business processes, and most often occur at the daily operations transaction-level of detail. Some of these recs impact the general ledger directly; others do not.
Credit union operations reconciliations are defined as daily, weekly, monthly, quarterly, and annual comparison of two (of more) sets of data, records, or transactions, from different systems, in an effort to confirm that the information agrees. If the information does not match, credit union 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 Credit Union Industry Reconciliations
Credit union reconciliations are not all the same in terms of frequency, the reason for their very existence, or why they need to be processed.
Routine Credit Union Reconciliations. The majority of credit union-operations reconciliations take place on a daily basis. These are commonly referred to as “expected” and “process-related” reconciliations.
Non-Routine Credit Union Reconciliations. Other ad-hoc reconciliations that occur are typically used in problem-resolution scenarios. Example: A member calls in with a missing-wire information request. A credit union staff member then must manually research and reconcile this one-off request to find out where the missing wire is.
Project-based Credit Union Reconciliations. During core-system migrations, the system providers, consultants, and credit union 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 CREDIT UNION INDUSTRY
Now that we have the basics of credit union reconciliations out of the way, let’s dive into specifics.
Each day, credit unions 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 transactions or process data are being reconciled. But, all credit union reconciliations benefit from standardization, automation, and centralization (where possible).
Importantly, almost every credit union has the exact same types of reconciliations that they must process. This is because all credit unions process similar types of transactions. However, each credit union does each reconciliation slightly differently. This is due to the fact that all credit unions 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 credit union reconciliations by business unit:
Daily Teller Transaction Reconciliation
The Branch Operations Help Desk staffers at this credit union 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 credit union’s staff must manually reconcile ATM deposit clearing balances on a daily basis—for their 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 credit union (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 credit union 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.
Interest Official Checks Daily Reconciliation
CD and Money Market account-interest official checks, which are generated by the credit union 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) credit union transactions—must be manually reconciled against the general ledger by credit union 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 credit union 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 credit union, 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.
Deferred Income Letter of Credit Reconciliation
Daily Loan Reconciliation
On a daily basis, all loan reports in this credit union’s document-management systems must be compared to—and reconciled against—its general-ledger system. This means that credit union 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 credit union, 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 credit union 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.
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, credit union 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 credit union, 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, credit union 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
Member Cash Letter of Credit Reconciliation
At this credit union, 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 credit union 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, credit union 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 CREDIT UNION ANNUALLY?
Credit unions 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” credit union-operations reconciliation requires one to two hours of daily work to complete. More complex reconciliations take two to three hours. So, most daily credit union reconciliations take an average of two hours to process.
To calculate how much reconciliations are costing your credit union on a continuous basis—and we know the above assumptions are correct, based on our extensive credit union operations improvement experience—we can deduce the cost below:
- If credit union staff on average cost $36 per hour, fully loaded with benefits.
- If there are 260 workdays 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 credit union, on average, $18,000 per year!
If your credit union 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 CREDIT UNIONS
There are six major ways to automate credit union 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 credit unions 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’ credit union 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:
- “The integration with ERP system is very complicated”
- “The integration of Blackline Journal module and our accounting ERP always challenging.”
2. Power Query
- Free/low cost
- Similar to a macro; easy to get started
- Integration with other tools or platforms such as Azure and PBI
- Need knowledge for dev and maintenance
- Can download reports through UI automation (Selenium, Automate, Pywinauto, etc.).
- Easy language to learn, used a lot with data manipulation, can integrate with APIs.
- Can parse text and digital PDF reports.
- Open-source OCR available. Microsoft, Google, and Amazon APIs also widely available for AI capabilities.
- Libraries to work with Excel as well as handle large datasets (Pandas).
- Can be made into a standalone executable (.exe) that can be launched by users.
- Scripts can be launched through other RPA softwares.
- No software cost.
- Full functionality.
- Dev time would be greater (particularly for UI automation and testing).
- Would require Python knowledge for maintenance.
- Higher coding skill is required for development and maintenance.
- Highly dependent on the environment. Might trigger more IS concerns.
- Speed Limitations: Python is an interpreted language, so you may find that it is slower than some other popular languages.
- 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.
- Can parse text reports and manipulate data (.txt files).
- Can natively automate Excel using Interop/COM objects.
- Scripts can be launched individually or through other RPA software.
- PowerShell is free to use.
- Native window feature/module. Not dependent on the environment.
- Support: It’s easy to find PowerShell support and resources; it has native .NET framework integration.
- Rich Cmdline-based interface: Cmdline creates a virtuous cycle between the user and the system.
- 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.
- Cannot perform UI automation.
- Cannot extract data from PDF files.
- Reports would need to be downloaded using RPA software (or manually) first, then launch the script to automate Excel recon.
- Fastest and easiest way if certain system has API available to the devs.
- Usually have free access with purchased license of current service or products
- Limited to the existing features of the system.
- Specific to certain system. API is just a channel; you still need coding language to make it work.
- Mid-high level of coding skills is required.
- 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
- Automated credit union reconciliation can cut down on errors and ensure that reconciliation process delivers consistent results in a timely manner.
- It can keep qualified individuals from spending an excessive amount of time on this activity.
- RPAs can handle much higher volumes of work than humans.
- Rich experience and reusable components in UiPath for Excel-based recon.
- Easy to perform maintenance if the clients have internal devs.
- All team members have the skill level to handle the recon bot.
- May have difficulties understanding multiple invoices.
- Would require professional installation of the RPA tool.
- System upgrades can throw off the RPAs
ROBOTIC PROCESS AUTOMATION FOR CREDIT UNION RECONCILIATIONS
Automate your way out of credit union 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 credit union’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 CREDIT UNION 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 credit unions. And chances are, you have even heard about our robotic process automation work at regional and community credit union 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 CREDIT UNION 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 credit union’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 credit union 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 firstname.lastname@example.org today for your free, no-obligation 30-minute screen-sharing demo. You’ll love it!