Tick & Tie Finance Blog

Reducing Credit Card Reconciliation Time While Increasing Match Accuracy Through Intelligent Automation

August 6, 2019

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:

  • Duplicate or missing transactions generated by human or system error or fraud attempt
  • Charge backs due to insufficient funds or disputes
  • Time delays between charge and final amount > common with restaurants and tipped service industries
  • Timing delays due to Payment Service Processor (PSP) taking 2-4 days to post to merchant bank.
  • Data fields formatted and reported differently across systems: AMEX = American = American Express in different systems.
  • Debits and credits delivered in different configurations > single column no-modifier (pos & negative in same column) vs modifier column (amount in one column with DR/CR in next column) vs amounts in different columns

Specific Reconciliations in the Credit Card Processing Flow

Here’s a diagram of the major steps in the credit card data flow and the reconciliations that could happen within the process:

Credit Card Reconciliations Involve Many Systems Inserting Fees Along the Way

Credit Card Reconciliation Challenges - a Deeper Dive

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:

  • Fees getting added by different services in the process: Gateway, Payment Service Provider and Merchant Bank all add fees that create a new transaction layer on top of the credit card amount itself and change the total amounts that need to be reconciled.
  • Presentation of fees are different between the systems. Not only are fees added, but how those fees are calculated are different which contributes to complexity. Some will charge a % of transaction while other systems charge a set $ amount per transaction which creates inconsistency in the data.
  • Credit cards generate an enormous volume of raw transactions. Because of this, looking at batch totals with adjustments for fees tends to be used, but if there are exceptions - finding those reconciling items is really hard!
  • Large Many-to-One reconciliations - especially from the Merchant Bank as well as many smaller transactions settling as a batch with limited data to trace the transactions within the batch.
  • Payment Service Provider (PSP) Timing. A common lag is 2-4 days for settling a credit card transaction depending on card type. Further complications are geographic time zones and system time zones. Systems are not uniformly set to a time standard such as UTC, plus time cut-offs vary by geographic region. This creates a timing challenge across periods.
  • Different credit card issuers have different types of cards that drive different transaction fee amounts. A commercial card vs a consumer rewards card vs a consumer non-rewards card have different fee amounts which creates inconsistencies.
  • The greater the number of systems, the more opportunities for unique data formats and layouts. As we see in any reconciliation involving different systems, there is a huge divergence in data layouts, data formats and data source types that have to be handled in order to match transactions accurately and efficiently. Since the credit card process adds multiple new systems to the flow, the opportunities for data inconsistencies are multiplied for each and every transaction.

Four Options to Improve the Efficiency of Credit Card Reconciliations

Improving the efficiency of credit card reconciliation means one of two things:

  • Reducing the time it takes to reconcile
  • Improving the accuracy of reconciliation to minimize re-work, validation and research

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.

There is a Solution for You

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.

Let us show you how easy and powerful Sigma IQ's Machine Learning driven account reconciliation software is.

Related Posts & Resources

Reducing Time to Close through AI-driven Reconciliation

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
Cost Savings through AI-driven Account Reconciliation

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
Automating F&A and Operations through AI

It's time for finance and accounting operations to move beyond spreadsheets and fragile rules based systems. Next generation technology such as Machine Learning provide specific solutions to hard problems.

Read About F&A Automation with AI

Get the CFO Brief

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.

Categories

Tags

Build Your Knowledge

Sign up for the Sigma CFO Brief and let us help you advance your knowledge and career by delivering articles, insights, and other resources geared towards Finance & Accounting teams.

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
© 2019 Sigma IQ. All Rights Reserved.
Privacy PolicyTerms of Service