Monthly reconciliation is a post-mortem

Many finance teams find their differences weeks after they happened. This article follows the whole chain, from the first order to the journal entry in your ERP.

The Actuals teamActuals · 4 min read

When did your finance team last find a difference on the day it happened?

For many e-commerce finance teams, the answer is: not recently. Differences turn up at month end, sometimes at year end, when the transactions behind them are weeks old. Someone opens the order, PSP and bank exports, matches the totals and starts digging.

In effect, that is a post-mortem. You find out what went wrong weeks after it happened, when everyone involved is already busy with the next month.

The fix, in short: match every transaction the day after it happens, and make the journal entries from that same matched data. Then the month-end close only has to confirm the books.

Why monthly reconciliation stopped working

Monthly reconciliation made sense when a company sent a few thousand invoices a year. You could match them by hand, and a difference was usually one transaction with one cause behind it.

A high-volume webshop now processes tens of thousands of transactions a day, often through several PSPs. Each PSP has its own report format, its own IDs and its own payout schedule. At month end, the difference is made up of thousands of small causes.

Many teams still reconcile the way they did at a few thousand invoices a year, only with bigger spreadsheets.

From the first order to the journal entry

Reconciliation is one step in a longer chain. Every order creates financial events in several systems. Each of those events has to end up as a correct line in the general ledger. These are the six steps, from the data coming in to the journal entry being posted.

  1. The data comes in. Orders come from the platform or the order database. Payments, refunds, fees and settlement batches come from each PSP. Payouts come from the bank.
  2. The data is put into one format. Each source uses its own IDs, dates and fields, so every event is translated into the same structure. After that, refunds from Adyen and Stripe look the same.
  3. Every transaction is matched. The order, the payment, the settlement and the bank payout are linked to each other. When they do not match, the mismatch gets a reason: a fee, a refund, a chargeback, a currency conversion or a payment that has not arrived yet.
  4. Balances are calculated. From the matched events you can see, on any day of the month, what each PSP owes you and what you still owe others, such as refunds.
  5. Period, revenue and VAT rules are applied. Each amount is assigned to the month it belongs to, revenue is recognised when it is earned and VAT is split per country.
  6. The journal entries are made and posted. Only totals that are fully matched and explained go to the ERP, split into the right accounts.

Take one order of €59.95. Adyen keeps a fee of €0.29, so the bank receives €59.66. The order includes 21% Dutch VAT, so €49.55 is revenue and €10.40 is VAT. In the ERP, this order is part of a daily total. Its part of that entry looks like this. On the debit side, €59.66 goes to the bank and €0.29 to PSP fees. On the credit side, €49.55 goes to revenue and €10.40 to VAT payable. Both sides add up to the €59.95 the customer paid. The example is simplified, but every amount in it comes from matched data.

In many teams, steps 1 to 3 happen in spreadsheets at month end. Step 6 is then typed into the ERP by hand. Steps 4 to 6 need the matched data from step 3, so the whole chain waits until month end.

Is reconciling every day more work?

The usual objection is that daily reconciliation sounds like doing the month-end close thirty times. In practice, a daily check stays short because each difference is one day old.

A difference you see the next morning is small and recent. The refund is still in someone’s inbox, and yesterday’s settlement report is at hand. Explaining it takes a few minutes. The same difference found four weeks later can take an afternoon. If it cannot be explained at all, it goes to a suspense account.

What does take time is doing the matching by hand. Software can match orders, payments and bank lines, so accountants can spend their time deciding what an unexplained difference means. In many teams, the matching still takes up much of the accountants’ time.

What should a month-end close look like?

A close should only confirm the books. Suppose every transaction was matched the day after it happened, and the journal entries came from that same data. Then there is little left to find at month end, so you review the books and close.

Teams usually start with Actuals in two phases. In phase 1, Actuals collects the data from all your sources and matches each transaction. It gives each mismatch a reason and creates the journal entries. Your team then imports those entries into the ERP. In phase 2, Actuals maps the entries to your chart of accounts, VAT logic and booking rules, and posts them to your ERP automatically.

At Interrail, reconciliation took weeks at month end until the team started with Actuals. Grover, another Actuals customer, puts what Actuals saves them in staff terms: "If Actuals were to break down, we would need two to three additional full-time employees."

The question to ask instead

Finance teams often ask how fast they can close. A more useful question is how old the oldest open difference in your books is right now. If the answer is a few weeks, your month end is still mostly investigation.

Curious where those open differences come from? The Actuals scan takes 15 minutes and shows where your payouts and your orders first stop matching.

In practiceEvery payout matched the day it lands

Written by

The Actuals teamActuals