Credit/Debit cards now account for over 75% of consumer transactions but traditional methods of reconciling the steps in the process haven’t evolved nearly as fast - until now. Traditional tools like Excel driven matching or rules based reconciliation can help in lower volume, simple matching but credit card reconciliation - with it’s raw volume and many different systems in the processing flow - creates unique reconciliation problems that need a new approach. This article describes the process and opportunities for efficiency improvement through intelligent automation.
Whether you are reconciling against a revenue account, clearing, or other account, credit card processing injects multiple steps into the flow of funds from the point-of-capture (POS, online, or other) to gateway processor to payment service provider to merchant bank to ultimately the sub and general ledgers. During this flow, fees are calculated and added, timing discrepancies are introduced and multiple systems (and credit cards) mean multiple data formats and layouts.
The challenge around varying data source formats and file layout are similar to Bank Reconciliations, but the credit card flow poses unique challenges such as:
Here’s a diagram of the major steps in the credit card data flow and the reconciliations that could happen within the process:
With multiple hand-offs from capture to posting to the merchant bank, there are 6 or more different reconciliations that you could (should?) be doing between the different systems. At each point of system integration the main issues that make credit card reconciliation challenging are:
Improving the efficiency of credit card reconciliation means one of two things:
In a perfect world, you would have transactions from two or more different systems merge into a matching transaction process that would automatically detect matches and non-matches to generate exceptions. The matching transaction platform would need to be 99% accurate on transactions that SHOULD match so that you don’t have to manually match slightly wonky transactions. You would then be able to easily find reconciling items, identify the issues and fix or post or roll-over the transactions to the next period. This process would repeat for each one of the different reconciliations.
This is what Sigma IQ does and solves both the reducing time need and the accuracy need. However, you have other options to reduce credit card reconciliation time investments and gain efficiencies.
Option One - Reconcile at a Total or Sub-Total Level.
This is how most companies start trying to gain efficiencies with credit card (or other large) reconciliations. Instead of going through the process of matching at the detail level, period balances are pulled and compared across the different systems. Fees and other costs that are added on are calculated and applied to the appropriate balance.
Pros: No additional software or platform required to start implementing, relatively straightforward concept and can significantly save time.
Cons: Summary totals often fail to tie from transaction discrepancies, making the process incredibly time consuming and frustrating. It becomes a needle in the haystack type exercise to find the offending transactions and also does nothing to help with accuracy of matching.
Best Use Case: Small businesses that have a very simple process flow and a small number of transactions which makes it easier to find the exceptions when totals are out of balance.
Option Two - Excel Functions, Macros, PivotTables, and Visual Basic (VB) Scripting
Finance & Accounting (F&A) people are used to working in Excel and the software has a lot of capabilities with regards to organizing, calculating and aligning data. More advanced Excel users can use built-in tools like Functions (rounding, look-ups, VLookup, etc.), Macros (record what you do and then replay them - kind of like Robotic Process Automation inside of Excel), PivotTables (an analysts best friend) and even build code using Visual Basic that can help format and align data for visual/manual matching. Here is an articlethat outlines three methods to reconcile accounts using Excel for reference.
Pros: Excel is very flexible, F&A staff are used to working in spreadsheets, sorting/filtering/grouping are all available and can be combined to help the matching process, most everyone already has Excel and you can build simple programs as you need ad hoc.
Cons: If data changes/is missing/comes in a different format or layout then you have to re-build the routine, it takes someone with advanced Excel skills and possibly someone who can program to get the effect you want, accuracy is always a challenge, you are always updating the code and you still have to visually match transactions. The other big downside of this approach, especially for a public company or any other company that gets audited, is that the controls are poor. Calculation errors are common and it’s easy to inadvertently overwrite formulas and data. Excel is always a headache during the audit process as auditors carefully scrutinize Excel models.
Best Use Case: Companies that need to graduate to a detail level transaction matching that still has a fairly simple data source layout and pretty clean data but is experiencing more volume.
Option Three - Invest in a Rules-Based Technology
Financial Close Management platforms like Blackline, Trintech and ReconArt use boolean logic and rules to create a workflow for matching transactions. Functionally this means that based on the systems, accounts and data used in the credit card process - consultants, system integrators, or your most technically skilled staff will create a series of if/then rules, connectors, ETL processes, and data categorizations to match specific transactions and then identify reconciling items.
Pros: These are mature technologies that have been evolving for over two decades, account reconciliation is built into an integrated workflow and there is a robust ecosystem of system integrators to build the rules and implement the financial close platforms.
Cons: These platforms require a significant amount of time and investment to get matching enabled for each use case and they require maintenance to update the rules as data evolves or as the sources change and matching accuracy is relatively low.
Best Use Case: Enterprise level organizations already using one of these financial close platforms that want to add credit card reconciliation at the detail level. The interface is consistent and data will be managed consistently through the flow.
Option Four - Invest in Intelligent Automation
Intelligent automation is the combination of artificial intelligence (AI) and automation. In the context of credit card reconciliation, this may mean a combination of software, robotic process automation (RPA) and a form of AI called Machine Learning. Go deeper on machine learning and AI for CFOs here.Sigma IQ combines machine learning and enterprise-capable software to provide a very flexible, scalable matching reconciliation engine that handles credit card data complexity easily.
Pros: A machine learning approach means your reconciliations are implemented in a day instead of months, there are no rules to maintain so total cost of ownership (TCO) is much lower, accuracy is very high (often 99+%), can be used in all phases of the credit card process and is easy for staff to both implement and master. RPA is a relatively easy to implement software for replicating manual actions (like recording macros in Excel) that is useful for downloading, moving and manipulating files.
Cons: The use of Intelligent automation and specifically machine learning in the finance function is relatively new. There is room to improve as common batch file ingestion methods are supplemented with exposed APIs for direct integration with sources. RPA, as an alternate technical approach, is more mature technology and can help with specific repetitive tasks but is not built to assess or evolve given different inputs like AI is capable of. RPA vendors like UIPath are limited to the windows operating systems (no RPA for Mac OS) and RPA doesn’t help in an activity that requires a human assessment.
Best Use Case: Enterprise and mid-market organizations with involved reconciliation processes that generate a lot of transaction data. Especially powerful if a company wants to move to more frequent reconciliations (e.g. from monthly to weekly or daily) or move from a sub-total/total level to a detail level with a high volume of transactions across multiple data systems.
Intelligent automation technology is evolving quickly and is finally being applied to the F&A function in a significant way. Regardless of the size of you company or complexity of your credit card reconciliations, there are options to help improve the efficiency, lower staff costs and increase the accuracy of your reconciliations so that your team can minimize manual effort and focus on higher value activities.
According to the APQC General Accounting Open Standards Benchmarking survey (2,300 companies participated) - Cycle Time for Monthly close ranges from 4.8 days or less for the top 25% of companies to 10 days or more for the bottom 25% of performers.Learn How To Be a Top Performer
According to a study by Robert Half & the Financial Executives Research Foundation (FERF), only 13% of F&A teams have utilized advancements in technology solutions, with the majority of CFO’s admitting they still struggle with painful aspects of account reconciliation.Read About AI-Driven Cost Savings
Our newsletter is built for F&A professionals to deliver insight into new technologies, advice to advance your career and tools/tips to make your job easier so you get your evenings and weekends back.