How do credit cards fit in the triangle of reconciliation?

In my previous blog, I wrote about how bringing payments to the CRM system impacts the application landscape with regards to the Account System (AS). I proposed two models:

• Relocation, where the payment data is stored in CRM which provides aggregated journal entries based on reporting dimensions to the AS.
• Replication, where the payment data is stored in both the CRM and AS.

That blog was mainly focused on payments originating from banks such as bank transfers and standing orders, many organisations also receive payments that are handled by external payment gateway or Payment Service Providers (PSPs).

Though many AS options can import bank statement files, this simply isn’t enough to accurately reconcile credit card and other PSP-based payments. This is due to the fact that PSPs often only pay out net amounts instead of gross amounts.

Let’s consider the following example: you have a customer paying a 100 Euro invoice using a credit card, processed through payment gateway X. The web portal the customer used marked the transaction as complete, so you would expect the money to come in and be reconciled against that invoice.

In the meantime, a dispute for a previous invoice of 30 Euros is being processed as well, so you expect to receive 100 – 30 = 70 Euros. As with most PSPs, gateway X pays out collected funds on a weekly basis. However, when the weekly pay-out comes around, they transfer only 63 Euros into your account!
[Triangle of reconciliation (5)]

If you would treat this as a bank transfer, the customer would have underpaid the invoice, and might trigger a collections process. (Ignoring the fact that this money comes from the payment processor and not the customer).

So, where did the missing 7 euros go? The answer usually is transaction & processing fees. In some cases, the gateway or PSP might even keep a minimum balance in your account to cover refunds and disputes, making the total payout even lower. Since many PSPs use a tiered pricing system where the transaction and processing fee depends on the total number or amount of the transactions, there is no simple solution for reconciliation without additional information.

In many cases, reconciling credit card transactions requires a lot of manual labour, pouring over file exports or digging through the PSP dashboard to find all payments, refunds and disputes and distill the actual cash flow for reconciliation.

Some PSPs even keep some amount of money back as an insurance against future disputes or refunds, making the reconciliation process even harder.

All of this means if you only have an AS for payment management, reconciliation is hard, especially when the number of transactions gets so high that manually comparing transactions isn’t really feasible anymore.

And of course, we have to register the full amount against the customer. They paid 100 Euro and we need to treat it as 100 euros in every step of the process. Transaction fees are not typically part of the invoice after all.

How can CRM make this easier?

Using Salesforce CRM with FinDock as an example, how does payment data in your CRM change the reconciliation picture? Well, we know that our customer paid their invoice with the credit card since that transaction was captured by FinDock and added to the CRM in real-time.

Any refunds or disputes would also be captured by FinDock since all events are sent from the PSP using webhook notifications.

As long as we make sure that “Payment Provider” is one of the reporting dimensions used when preparing this information for the AS, we can tell as part of the overall reporting that:

• An invoice of 100 euros was paid using a credit card via processor X.
• A dispute was processed for an invoice of 30 euros that was also paid using the same credit card via processor X.

This information can make the whole reconciliation process so much easier. Let’s see what this would look like from the accounting side with this new information in place:

• The CRM reports + 100 for X (invoice payment) and – 30 for X (dispute).
• Let’s book this against the subledger for Payment Gateway X, resulting in a debit position of 100 – 30 = 70 euros.

When the bank transfer of 63 euros comes in, we only need to look at the total amount of fees for that PSP (usually the PSP will either enable you to generate invoices for fees or list the fees as part of each pay-out in their payments dashboard) stating that you paid 7 euros in processing fees (2 for the invoice payment and 5 for the dispute) to reconcile the invoice payment, dispute and PSP pay-out. There is no longer a need to dig through all the transaction details to figure out how the pay-out is generated, or for which invoices the payments are, since all of that information is already provided by the CRM.

Expand the triangle?

In part 1 of this series, I started out with a triangle representing 3 main data sources or applications: Bank, CRM and Accounting. However, to reconcile PSP-based payments, we also need the PSP notifications.

This might seem to invalidate the triangle metaphor, after all a four sided triangle is actually a tetrahedron. Still, I’d like to make the case for keeping the triangle. There are many scenarios where we might need information in the reconciliation process that doesn’t originate from one of the big three: CRM, bank or AS itself. Simply tracking which payment form was used could be construed as external data. But in the end, the reconciliation happens as part of the triangle.

One question I encountered many times is, “Can’t we just record the processing fees as well in CRM? That way we have everything we need to reconcile all payments going through this PSP.”

There are use cases where this makes absolute sense! However, I usually advise against it for two reasons:

• You don’t always immediately know the transaction fees for every transaction . Especially if you have tiered pricing, the transaction fees might only be calculated at the end of the month.
• Transaction & processing fees are not customer payment data and as such don’t really belong in CRM.

The latter one might seem nitpicky, but if you add payments that are non-relational to the CRM, you will have to make sure that every process and report triggered or influenced by payments takes these kinds of payment records into account. Not only those processes and reports you have right now, but also those that will be created in the future.

So how do we implement the triangle?

Now that we have both bank based and PSP based payments “in the pocket” lets take a look into how you can actually implement a system to facilitate the triangle of reconciliation, my next blog, part 3 of this series, will look into tooling that can help you make this a reality.

I hope this gave you some insights on reconciliation of PSP based payments with CRM and accounting systems. If you have any questions or thoughts on this subject, don’t hesitate to reach out!