Every well-run business should maintain accurate and up-to-date financial records in order to avoid internal confusion and problems with auditors. Besides legal troubles, inconsistencies in financial records can lead to significant financial losses. A DataRails study has shown that poor financial reporting, especially manually performed reporting, had the potential to cost US businesses up to $7.8 billion.
Conducting regular payment reconciliations is an essential part of good financial accounting. Regular reconciliation can help merchants catch and resolve any discrepancies between reports as soon as possible. Discrepancies related to order ID, payment status, transaction amount and payment date can indicate anything from simple technical issues to unauthorized payments and fraud attempts. Combing through the data and matching it to each other is vital to maintaining the integrity of your financial operations.
Though manual reconciliation is currently the most common way for businesses to conduct their reconciliation processes, it eats up a tremendous amount of time. Not to mention it’s very easy for errors to creep in, especially human errors, which can have wide-ranging consequences for the health and quality of your financial records.
So why is manual reconciliation still so common? Are there any benefits to the process? And is an automated reconciliation solution a viable alternative? In this article we hope to answer these questions by taking a closer look at payment reconciliation. By comparing manual reconciliation and automated reconciliation, you will discover which solution makes the most sense for your business size, transaction volume and operational reach.
Different types of payment reconciliation
When considering transaction data, there are four main types of payment reconciliation that can be conducted by merchants, depending on their priorities.
Orders vs. Payments Reconciliation
The most common type of payment reconciliation, reconciling orders vs. payments involves matching the orders in a merchant’s internal database with the payment records from the payment service providers (PSPs) that a merchant has integrated. For example, if an order is marked as canceled in your internal system, then it should be labeled as refunded or canceled in the PSP records as well. If not, then the merchant needs to issue a refund in order to resolve the discrepancy.
Payments vs. Settlements
During payments vs. settlements reconciliation, merchants verify if they have been paid the correct amount in the settlement process. This involves checking if the right fees were deducted according to the processing agreement they have with each PSP.
Settlements vs. Accounting
After the payment has been settled, it’s important for merchants to verify if the revenue generated after settlement is also recorded correctly in their accounting software.
Orders vs. Accounting
Finally, during this type of reconciliation, merchants verify if all successful transactions are correctly recorded in their accounting records without any discrepancies.
How to reconcile payments manually
Manual payment reconciliation in a spreadsheet is currently the most commonly used method for reconciling internal payment records with reports obtained from PSPs. Below is a breakdown of all the steps involved in manual payment reconciliation.
- Gather payment data from all PSPs
The very first step of any reconciliation process is to collect payment data from all relevant sources. This means preparing your internal records as well as obtaining payment reports from all the PSPs you have integrated into your payment system. If you’re an enterprise, you should be working with at least four PSPs, which means you will need to export transaction reports from all of them separately.
Settlement reports vary from provider to provider. If you’re using Stripe, you can download what they call the Balance history, which includes a breakdown of all individual payments, refunds and disputes. They also allow merchants to download a Revenue Recognition report. You can obtain both of these in your Stripe account. If you’re an Adyen customer, you can download a Merchant Report for specific data ranges and in various file types. Just be aware that generating these reports can take several hours, especially for enterprise merchants with high transaction volumes. Checkout.com also offers a variety of reports, including settlement statements and balance reports, which contain essential information for the reconciliation process. You can access these reports via Secure File Transfer Protocol (SFTP) or via API.
- Combine data in a spreadsheet
Once you have collected the payment reports for the date range that you want to reconcile, it’s time to combine your internal data with the PSP data in one spreadsheet and standardize the values. Standardizing is important, because there can be discrepancies between each PSP in terms of language, currency, number format and so on. In order for manual reconciliation in a spreadsheet to be as easy as possible, all data must be uniform, or else this can lead to issues when deploying spreadsheet commands.
You can either consolidate all payment data manually one by one or you might want to consider using an extract, transform and load (ETL) software, like Nanonets or Fivetran, which pulls data from spreadsheets and adds it to your internal database. These tools scrape data and in some cases help you to standardize and refine the values so that they are all consistent.
However you choose to extract data, make sure the following values are consistent and have the same format:
- Transaction time format
- Currency format
- Timezone format
- Amount format, e.g. absolute vs. negative values
- ID field
Next, obtain your internal settlement data from your business intelligence tool and add it to the same spreadsheet. Make sure it matches the same standardization as that of the other data.
- Match transactions across internal and external records
The next step is both the most critical and time consuming – and also where human error is most likely to creep in. Using lookup formulas in your spreadsheet, compare the two datasets, i.e. your internal records against the external PSP records. What you are looking for are any and all discrepancies related to the payment amount vs. the settlement amount, the payments status, the payment date or the order ID.
For example: If your internal records show that an order amount was $25.00 but the data from the PSP that processed the payment shows $23.50 for the same order ID, you must highlight this discrepancy, investigate why it exists and make a plan to resolve it. Or say you discover that a transaction has the status ‘canceled’ in your records, however it’s marked as ‘captured’’ in the PSP records. Highlight this discrepancy and add it to the list of items you must reconcile.
- Reconcile discrepancies
After you’ve manually gone through every line of every report from every PSP for the desired timeframe and matched it against your internal records, you must now investigate the reasons for the highlighted discrepancies and make a plan to resolve them. In the last example above, where an order status was canceled in one column but not in the other, you must find out why the order hasn’t been canceled on the PSP side and then cancel the order and issue a refund to the cardholder if necessary. Only after you have reconciled all the differences between datasets is your reconciliation process complete.
- Repeat at weekly or monthly intervals
Payment reconciliation is an ongoing process. After you’ve completed one cycle, it’s already time to prepare for the next. For manual reconciliation, this means repeating all of the steps above, for all PSPs and all types of reconciliation that your business is conducting, on a weekly or monthly basis.
Manual reconciliation can be an incredibly time and labor intensive process. We’ve heard from one of our customers that manually reconciling all their settlement reports takes at least a week. And that is if everything is going well. Manual reconciliation is very prone to human error. Sorting through every single line in a spreadsheet manually can be tedious and tiring, and employees are bound to overlook discrepancies or make other mistakes while matching data.
Currently manual reconciliation is performed by either finance or IT teams, resources that could and should be better spent on other tasks. Some teams hire a full-time data control employee, which incurs additional costs and doesn’t resolve the error problem.
The pros and cons of manual reconciliation
As mentioned above, manual reconciliation is currently the most common and most cost-effective solution for payment reconciliation, as businesses only need a spreadsheet and to claim time from one or more members of their finance or IT teams – at least this is true for businesses that only have 1-2 PSPs. For enterprises with more integrations and therefore a bigger workload, one has to calculate the costs of human resources.
Still, although manual reconciliation might seem like a simple solution, it can lead to many problems and frustrations down the line. Below is a complete list of pros and cons of manual reconciliation.
How to reconcile payments automatically
Though manual reconciliation is still the de facto standard, it is by no means the only nor the best approach for reconciling payment data from multiple PSPs. Automated payment reconciliation performs all the same steps as manual reconciliation, but without the need for human intervention and therefore with significantly fewer errors. Enterprise merchants who work with three or more PSP integrations, who want to unburden their teams and who are looking to innovate their internal payment infrastructure should therefore consider implementing automated payment reconciliation.
- Choose a reconciliation solution provider
The first step here would be to find a suitable automated reconciliation solution provider. Though the fintech space has been dominated by innovations in payment technology over the last few years, there still aren’t that many players that recognize the importance and opportunity of automated reconciliation. Nevertheless, do your research, and ask yourself these questions for choosing a payment technology provider:
- What kind of data connectors does it have? Can I connect my PSPs, DWHs, or simply upload a CSV file?
- How complete is the solution, i.e. how much of the reconciliation process does it automate?
- What kinds of reconciliation can it perform? Is it specifically designed for payments and settlements?
- Is the solution scalable or able to handle the transaction volume of merchants with three or more PSPs integrated?
- How intuitive is the interface? How easy will it be to onboard the team to this new tool?
- What are the costs compared to the costs of manual reconciliation performed regularly by an internal team member?
- Integrate and set up preferences
Once you have made your choice and integrated the solution into your payment infrastructure, it’s time to configure the reconciliation process to match your needs. Do you want to perform payment reconciliation every week, month or at custom intervals? What types of payment reconciliation do you want to perform? Who on your team will be in charge of resolving the discrepancies that are highlighted during the reconciliation process? The integration and set up process take a bit of time initially, however it only needs to be performed once. After that, you will have a smart data solution that you can activate in a few clicks.
- Run automated reconciliation
As soon as everything is configured according to your preferences, automated reconciliation can begin. Reports from all of your PSPs are automatically pulled into the system for matching. Within minutes, the system will run through all the rows of available data, brought together automatically from scattered PSP sources, and will search for any discrepancies. It will then automatically flag these mismatches for the next step in the process.
- Reconcile discrepancies
One thing to note about automated reconciliation is that only the matching part is automated. Since the discrepancies that it highlights, like wrong order status or transaction amount, are varied and require further investigation, human intervention is required at this stage. However, a good solution will provide as much detail about the discrepancy as possible, which will definitely aid in the resolution process. A provider like Payrails goes the extra mile and provides details and suggestions on steps to take to resolve the discrepancy, automating 95% of the reconciliation process so that your team only needs to take care of the remaining 5%.
The pros and cons of automated reconciliation
Though manual reconciliation is easier to get started with as every business has access to their internal data and a spreadsheet, it’s certainly a hassle in the long run. Automated reconciliation eliminates this inconvenience as well as the potential for human error. Not only is it far more accurate than manual reconciliation, it also performs the process much faster, making it a scalable and reliable option for enterprise merchants.
The bottom line
Manual reconciliation may currently be the most common form of reconciliation, mostly because it’s easy for businesses to set up with tools they already have. But it certainly leaves a lot of room for improvement. Though automated reconciliation requires merchants to purchase a fitting software solution, the benefits far outweigh the costs – especially when you consider the substantial costs that human error can lead to.
Cost savings aren’t the only consideration: Manual reconciliation takes a lot of time, in some cases even weeks per session. Automated reconciliation, on the other hand, matches data and highlights discrepancies within minutes.
Combined, the simplicity, speed and scalability of automated reconciliation make it the only choice for merchants who process a high volume of payments across multiple PSPs and across multiple markets.
Automated reconciliation designed for payment settlement
Payrails offers the first automated reconciliation solution specifically designed for payment reconciliation and settlements. Created with enterprise merchants in mind, Payrails’ payment reconciliation tool relieves finance and IT teams from the stress and irregularities of manual data matching, freeing up 98% of their workload while significantly reducing human error.
+95% automatic matching
Payrails Reconciliation automates +95% of the reconciliation process, meaning it eliminates almost all of the manual work performed during manual reconciliation, including the tedious process of going through each row of data for every PSP report. It also highlights any and all irregularities, so that your team knows exactly what mismatches to investigate and how best to proceed.
No code solution
Integrating Payrails Reconciliation is easy thanks to your no-code solution. Simply create an account with Payrails and we will source the relevant data from your connected PSP and match them against your internal data.
Frees up 98% of manual workload
Say goodbye to the stress of manual reconciliation: Payrails frees up 98% of the workload currently performed by finance and/or IT teams. Our solution lets teams get back to their important work while also providing them a valuable data matching source that will help them in their financial reporting.
Flexible, sheet-like interface
Payrails Reconciliation is intuitive and features a flexible, sheet-like interface without any of the frustrations and limitations of an actual spreadsheet. Any type of payment reconciliation is possible, including payment vs. settlement, orders vs. payments, settlements vs. accounting and orders vs. accounting.
Scalable to any transaction volume
All of Payrails’ solutions are designed for enterprise merchants, meaning they are equipped to handle high transaction volumes from multiple sources and across multiple markets. Our solutions are created to scale with you and your needs.
Contact Payrails today to learn more
Our automated payment reconciliation tool is just one of many solutions designed to optimize and simplify payments for enterprise-level merchants. Discover in a personal consultation what solutions make the most sense for your unique business needs. Contact us today.
I am the text that will be copied.
