Reconciling merchant settlements to the bank

The café's bank statement has a deposit almost every weekday. None of them is a round number, none of them matches a day's takings, and the description on each one is the same eleven characters of payment-provider shorthand. The client's bookkeeper has coded every one of them to "Sales".

Then you open the merchant portal export the client finally sent, and its gross sales figure for the year is higher than the total of those deposits. Not by a rounding difference. By an amount large enough that someone is going to ask about it.

Nothing is wrong yet. Every card deposit is a net figure: sales, less the provider's fees, less refunds, less any chargeback, paid out on a timetable the provider sets. The bank feed shows the last step of that sum and none of the working. The job is to put the working back, and the position here is simple: the sales figure comes from the merchant's records, never from the deposits, and the deposits are what you prove it against.

A deposit is the end of a calculation

Gross card sales are income. The provider's fees are an expense. Refunds reduce sales. A chargeback reverses a sale the business thought it had made, sometimes months later. A reserve or a rolling hold is money the business has earned and not yet been paid. Code the net deposit to sales and every one of those is either missing or in the wrong place, and the file looks finished while it is doing it.

That is the same failure described in bank reconciliation when the feed looks finished: a ledger that agrees with the bank statement has only proved that it copied the bank statement. Here the copy is of a number that was already net.

Start from the merchant's settlement report

The transactions report. Every sale, refund and chargeback, with its date and amount. This is where gross sales come from.

The payouts or settlements report. Each transfer to the bank, with the sales, fees and adjustments that made it up. This is the bridge between the transactions and the bank statement, and it is the report clients most often do not know exists.

Ask for them by name, for the whole financial year, for every provider the business used. A café with a bank terminal at the counter, an online ordering platform and a delivery app has three providers, three settlement timetables and three sets of deductions, and the client will usually think of the one at the counter first. "The settlement report from each payment provider, July to June, showing every payout" gets a useful answer. "Your EFTPOS records" gets a screenshot of a dashboard.

The ATO's overview of record-keeping rules for business says a business must keep records of all transactions relating to its tax affairs, and that a record generally needs at least a date, an amount and a description. A bank deposit labelled with a provider's name has two of those three for the payout. It has none of them for the forty sales inside it.

Match payouts to deposits, one by one

Three things go wrong often enough to check for every time.

Timing. Providers pay out on their own schedule, a day or several after the sale, and weekend takings often arrive together. The sales of the last few days of June are frequently paid in July. Those payouts belong to the next statement period and the sales belong to this year, so the year-end reconciliation will have a difference that is real, explicable, and should be written down as such.

The wrong account. A payout that should be in the business account and isn't has usually gone to an older account, or to a personal one. If a provider's report shows payouts that do not appear on any statement you were given, that is a question for the client, not a balancing figure.

Payouts that were never deposits. Some providers deduct fees, hardware rental or a loan repayment from the payout itself, or offset a chargeback against the next day's takings. The settlement report will show it. The bank statement never will, which is why the bank statement cannot be the starting point.

Gross up, and keep the pieces apart

Then check the fees against the provider's own statement of fees or tax invoice, if it issues one. Do not assume how GST applies to them; read the invoice and see what it says was charged. A fee figure you worked out by subtracting deposits from sales is not a fee figure. It is every other unexplained difference in the file sitting under one label.

This is also the point where cash comes back into it. A business that takes both card and cash has a sales figure that is the sum of two different trails. The card trail now has a source document behind every line. The cash trail needs one of its own, whether that is the point of sale system's daily report or the till reconciliations, and the two should not be blended into one total before each has been proved.

What the schedule should show

1. Gross card sales, from the transactions report, with the report and period referenced.
2. Refunds and chargebacks, from the same report, as their own lines.
3. Fees and any other deductions, from the settlement report, agreed to the provider's fee statement where there is one.
4. Net payouts, which is the arithmetic of the three lines above.
5. Bank deposits from that provider, from the statements, beside the net payouts.

Underneath, the difference between the last two lines, and what explains it: the late-June sales paid in July, the payout that went to the old account, the hold the provider is still carrying. A difference that is explained on the face of the schedule takes a reviewer a minute. One that has been absorbed into sales so the totals agree is found much later, by someone with less patience.

When the reports cannot be got

If they still cannot be obtained, say so on the schedule. Sales reconstructed from deposits are a different kind of figure from sales read off a transactions report, and the paper should not present one as the other. Grossing up deposits by an assumed fee rate is a judgement, and like any judgement it belongs in front of the reviewer as a review point with its reasoning, not inside a number that looks as though it was read from a document.

Where BeforeMay fits

The habit that matters does not need software: take sales from the merchant's records, prove them against the bank, and leave the difference where someone can see it.