Why is financial reconciliation so hard? Hint: your process isn’t the issue

Everyone understands reconciliation in their personal life. But financial reconciliation can be a different story.

At the end of the month, the balance in your bank account isn’t what you expected. What happened?

The difficulty of getting financial reconciliation right

The first thing you do is look at your bank statement.

At the beginning of the month, your checking account had a balance of $5,000. During the month, you got a $10,000 paycheck, paid a $5,000 credit card bill, and a $3,000 mortgage payment, leaving a $7,000 ending balance.

But you expected $6,500. What happened?

The mortgage looks right, your salary looks right, but the credit card looks lower than normal.

This is a bulk reconciliation. When everything reconciles, you don’t have to go further. But this month, it didn’t, so you have to look deeper.

So you look up your credit card statement, and it shows your total spend for the month was $5,500 – what you expected, but not what the bank shows.

Is this an irreconcilable difference? A gap that can’t be explained? If you’re an accountant, this is your worst nightmare.

To figure out what’s going on, you look at the individual transactions, and realize you had a $500 in  cash back that got applied as a credit against the balance. Although you put $5,500 on the card, only $5,000 was charged after applying the credit.

This is a transaction reconciliation. In some cases the charges themselves don’t all look familiar, so you go further and look at receipts, and figure out a restaurant charged a higher tip than you wrote, or a foreign currency conversion was less favorable than you expected, or you got charged a late fee on your auto loan.

How discrepancies in financial reconciliation affects businesses

At the end of the month, PiedPiper Co looks at its bank, and sees this, which represents cash on the balance sheet.

Meanwhile on the Income statement, how much revenue did PiedPiper get?

They look in thier monthly sales data from their order management system, Shopify, which looks like this:

Uh-oh: why does Shopify data show $1.55 million but Stripe data and Paypal data only add up to $1.5 million? What happened to the other $50,000?

This irreconcilable difference is an almost constant and extreme irritant, especially when transaction data volumes get so high that it’s hard to actually dig into each of the transactions in each of the operational data systems – Stripe, Paypal, and Shopify in this simple example.

Let’s look at the Stripe Payment data:

Because Stripe is set up to pay PiedPiper in US dollars, transactions 3 and 4 — where Shopify shows payment was taken in Canadian dollars and UK Pounds, respectively — are already converted to US dollars.

So one reason for the difference might be currency conversion. So they can go back and apply currency conversion to the Shopify data based on the date of each transaction, and that might modify the Shopify total balance to $1.53 instead of $1.55 million. Closer — but the data still don’t match. Of course the date Stripe converts the payment currency might be different from the Shopify order date, so there could be some further currency variance that is hard to nail down.

Next, they can try to reconcile each row in the Stripe (and Paypal) transaction data against the Shopify order data to spot which transactions are off, or missing from one system or another.

This reconciliation workflow is universal, and it’s the kind of thing accounting automation software vendors offer to help accounting teams apply consistent steps.

But when you go beyond 10,000 or 100,000 or 1,000,000 transactions a month, you can’t actually reconcile each and every transaction manually, even with a great workflow tool. In fact, the issue isn’t a workflow issue at all. When Stripe data, Paypal data, and Shopify data don’t match, it’s not a workflow problem. It’s a DATA problem.

And that’s why eliminating irreconcilable differences isn’t really possible for most companies today. They aren’t set up to be able to solve the fundamental problem which is that their data aren’t compatible and comparable.

And this has critical consequences.

For the finance team supporting executive decision making, it’s a critical failure. Executives relying on daily flash revenue data from the order management system don’t have confidence that the number they get will align to the cash in the bank and the revenue booked at the end of the month.

That’s really scary, especially in a volatile economic environment where understanding their company’s revenue and cost of revenue on a daily basis is essential for decision making. How can you decide the investment level in that new marketing campaign?

How deeply you have to cut the workforce or freeze hiring or other expenses? If you aren’t certain your numbers are right, you’re more likely to act conservatively, and miss opportunities, cut too deep, and hurt the business and its people.

For accounting, that needs to reconcile at least at month close, it’s a critical risk. Even with more time (but not much more) to close the books, without the ability to deal with the data problem, the data downloads from Stripe, Paypal, and Shopify might not even open in Excel, because they’re too big. it’s impossible to eliminate irreconcilable differences.

Conclusion

Most fast-growing companies with a high volume of transactions have no choice but to accept that they will have some irreconcilable difference at the end of the month, every month, and hope their auditors let it slide as being below a material threshold, because they can’t figure it out either.

But eventually, as they get bigger and their investors, lenders, and regulators get more demanding, they have to solve this accounting data problem.