For finance teams matching deposits to orders by hand

Settlement Reconciliation: Why the Bank Deposit Never Matches the Sales

A customer pays one hundred dollars. Your bank receives ninety seven, two days later, bundled with three hundred other payments, less fees, less a refund from last week, and possibly less a chargeback you had not heard about. Multiply that across a card gateway, a buy now pay later provider, two marketplaces and the terminals in your shops, and you have the reason someone in finance spends the first week of every month in a spreadsheet.

This is a solvable problem, and solving it is mostly about design rather than effort: clearing accounts that model the delay properly, matching rules that work on settlement identifiers rather than amounts, fees separated as expenses rather than netted into revenue, and a short exception list instead of a full manual match. This page sets out how.

Book a Reconciliation Call (03) 9003 0111

Realistic ROI

1 to 3 days

Between the sale and the deposit

Which is why a clearing account is not optional bookkeeping theory, it is how the delay is modelled

Net of fees

Is how almost every payout arrives

The fee has to be recognised as an expense, not quietly lost out of your revenue line

5 to 10 sources

Of money in a typical multichannel retailer

Card gateway, instalment providers, marketplaces, terminals, cash, gift cards and account payments

Days per month

Recovered by a finance team when this is automated

Typical for a business doing this manually, and the accuracy improves at the same time

Four Reasons the Numbers Never Line Up on Their Own

None of these are errors. They are structural features of how money actually moves, and the ledger has to model them.

Money arrives later than the sale, in batches

Orders are recognised when they are placed or shipped. Cash lands one to three business days later, aggregated into a payout that has no relationship to any single order. If sales post straight to a bank account in the ledger, nothing will ever match, and the bookkeeper compensates with journals nobody can explain later. A clearing account per payment source is what makes the timing difference visible and manageable.

Fees are deducted before you see the money

Gateway fees, instalment provider fees, marketplace commissions, fulfilment charges, payment surcharges and chargeback fees are all removed before the deposit. Recognising revenue at the gross amount and the fees as expenses is what keeps your reported sales comparable across channels, and it also matters for your business activity statement because the tax treatment of a fee is not the same as a discount.

Refunds and chargebacks flow backwards

A refund issued today may be deducted from a payout tomorrow, or netted inside one, or debited separately depending on the provider. Chargebacks arrive weeks after the sale with their own fee, and may be reversed again if you win the dispute. Each of these needs to find its way back to the original transaction so the ledger tells a coherent story rather than a series of unexplained adjustments.

Not every payment is revenue

A gift card sold is a liability, not a sale. A gift card redeemed is a settlement of that liability. Loyalty points redeemed, deposits taken on preorders, store credits issued for returns and account payments against invoices all move money without being straightforward revenue events. Systems that treat every inbound dollar as a sale overstate revenue and understate what you owe customers.

How Automated Reconciliation Actually Works

Six mechanisms turn a manual month end into a short exception review.

Model the delay

Clearing account per source

Each payment source gets its own clearing account: card gateway, each instalment provider, each marketplace, each terminal or store, cash takings. Sales post in, payouts post out, and the balance at any moment is money owed to you but not yet received. A clearing balance that stops trending back to zero is the earliest signal that something has broken, and it costs nothing to watch.

Reliable matching

Match on identifiers, not amounts

Providers publish a settlement report listing every transaction in a payout with its own reference. Matching those references to orders is exact. Matching by amount and date is guesswork that fails the moment two customers spend the same figure on the same afternoon, which happens more often than anyone expects.

Gross revenue

Separate fees and taxes

The integration posts the gross sale, the fee as an expense with the correct tax treatment from the provider statement, and the net movement to the bank. Your accountant should confirm the treatment for each provider, because it varies, and getting it right at the outset avoids a reclassification exercise at the end of the financial year.

Refunds and disputes

Handle the reversals

Refunds are matched to their original transaction, partial refunds tracked against the remaining balance, chargebacks recorded with their fee and the associated stock or service decision, and any later reversal applied. Without this linkage, refunds appear as mysterious negative entries and the true margin of a channel is impossible to see.

Journal or invoice

Post to the ledger correctly

High volume retail is usually best posted as a daily summary journal per channel and store rather than an invoice per order, which keeps the ledger readable and avoids contact record sprawl. Wholesale and account customers usually need invoice level detail. The two approaches can coexist, provided the split is deliberate and documented.

Short queue

Report only the exceptions

Anything that did not match within tolerance appears on a daily exception list with its reason: missing settlement line, duplicate reference, amount outside the variance threshold, unexpected fee, aged clearing item. Finance reviews a handful of items rather than reconciling everything, which is the entire point of the exercise.

Common Money Movements and How They Should Post

Task	Traditional	Reconciled Properly	Notes
Card payout arrives net of fees	Coded as sales, fees invisible	Gross sale, fee expense, net to bank	Keeps channel margin comparable and keeps the fee where your accountant can see it.
Instalment provider settlement	Matched by eye against a report	Matched on the settlement reference	Instalment fees are typically higher than card fees, so separating them matters commercially.
Marketplace remittance	One lump sum, one journal	Split into sales, commission and other fees	Marketplace statements carry several fee types plus reserves. Each needs its own treatment.
Customer refund	Negative entry, no linkage	Matched to the original sale	Linkage is what makes returns reporting and channel profitability believable.
Chargeback weeks later	Found during bank reconciliation	Recorded with fee and dispute status	Track the outcome too, because a successful dispute reverses the entry again.
Gift card sold then redeemed	Counted as revenue twice	Liability created, then settled	Gift card balances are money owed to customers, and they need to be visible as such.
In store terminal batch	Matched manually per store	Batch matched to till totals	Terminal batches, till declarations and cash variances all belong in the same daily check.
Cash takings and float	Reconciled weekly if at all	Daily variance by store and operator	A daily figure catches process problems while people still remember the shift.

Where Reconciliation Projects Go Wrong

Sales posted directly to the bank account

Without a clearing account, the ledger claims money arrived the moment the order was placed, which it did not. Every reconciliation then requires manual journals to bridge the gap, and those journals become the only thing holding the accounts together. Create a clearing account per payment source, post sales in and settlements out, and watch the balance. It is the single most valuable change most businesses can make here.

Matching on amount and date

Amount based matching seems to work in testing and fails in production, because identical amounts on the same day are common and refunds make the arithmetic ambiguous. Use the provider’s settlement report and match on transaction references, falling back to a tolerance based match only for the residue and reporting anything ambiguous rather than picking one silently.

Fees netted into revenue

Recording only the net deposit as sales understates revenue, hides the true cost of each channel, and misstates the figures in your business activity statement. It also makes it impossible to notice when a fee rate changes. Post gross, expense the fee separately with the treatment your accountant confirms, and report fee percentage by channel each month so an increase is visible.

No variance threshold, so everything is an exception

Rounding, foreign exchange movements and one cent differences will always exist. Without a tolerance, the exception queue fills with noise and gets ignored, which defeats the purpose. Agree a small variance threshold with your accountant, write off within it automatically to a dedicated account, and report the total written off monthly so a systemic drift is not hidden by the tolerance.

Aged clearing items nobody investigates

An item sitting in a clearing account for sixty days is telling you something: a payout that never arrived, a refund never processed, a duplicate, or a provider report that was never imported. Age the clearing accounts weekly, force investigation at agreed thresholds, and treat a growing balance as an incident rather than a curiosity. This is often where a genuine loss is discovered.

One person understands the whole reconciliation

Financial reconciliation built around a single staff member’s spreadsheet is both a continuity risk and a control weakness. The matching rules, the thresholds, the write off policy and the exception process should be documented, the automated steps should be visible to more than one person, and someone other than the preparer should review the exception list. Auditors ask about this, and they are right to.

How Yes AI Approaches Reconciliation Work

We start with your actual statements

Real payout files from each provider, real bank data and a real month of orders, so the design is built against the messy cases rather than the tidy ones. Most of the difficulty lives in the exceptions, and you cannot design for exceptions you have not looked at.

Chart of accounts agreed with your accountant

Clearing accounts, fee accounts, rounding and write off treatment and the summary versus invoice level decision are settled with whoever prepares your statements before anything is built. It is their reconciliation, so they should own the structure.

Imports and matching hosted by us

Provider settlement files and transaction data are collected on a managed cloud automation layer we operate, matched automatically and posted to your accounting system, with record level logging and alerting when a source stops delivering.

A short exception queue, not a full match

Finance reviews what genuinely needs judgement: unmatched settlements, unexpected fees, aged clearing items and variances beyond tolerance. Everything else posts automatically and is available to inspect when someone asks.

From Spreadsheet Month End to a Daily Check

Five steps. Most businesses see the first payment source fully automated within three to four weeks.

Inventory every money source

Gateways, instalment providers, marketplaces, terminals, cash, gift cards and account payments, with how each reports, how often it settles and what fees it deducts.

Design the accounts and rules

Clearing account per source, fee and rounding treatment, summary or invoice level posting, matching keys, variance tolerance and write off policy, agreed with your accountant.

Automate collection and matching

Settlement reports and transaction data collected automatically, matched on references, with unmatched items queued rather than forced. Tested against a full historical month first.

Post and prove

Journals or invoices posted to the ledger, then a parallel period where the automated result is compared against the manual one until finance is satisfied the two agree.

Age, report and extend

Weekly clearing account aging, monthly fee percentage by channel, an exception queue with an owner, then the next payment source added to the same framework.

FAQ

What is settlement reconciliation?

Settlement reconciliation is the process of proving that the money deposited into your bank account matches the sales your systems recorded, once fees, refunds, chargebacks and timing differences are accounted for. It covers card gateways, instalment providers, marketplaces, card terminals in stores, cash takings and any other way customers pay you. Done manually it is a monthly exercise in matching lists. Done properly it is a daily automated check with a short list of exceptions.

Why do we need clearing accounts?

Because money arrives days after the sale, in batches, net of fees. A clearing account per payment source lets you record the sale when it happens, record the deposit when it arrives, and see the difference as an outstanding balance rather than an unexplained gap. When the clearing balance stops returning to near zero, something has gone wrong and you know about it immediately. Posting sales directly to the bank account removes that visibility and creates a permanent need for manual journals.

Should we post an invoice per order or a daily summary?

For high volume retail and ecommerce, a daily summary journal per channel and store is usually better: the ledger stays readable, contact records do not fill with one time buyers, and reconciliation works at the level the payouts actually arrive. For wholesale, trade accounts and anything with credit terms or partial payments, invoice level detail is necessary. Many businesses need both, which is fine provided the split is explicit and the two do not double count.

How are merchant fees handled for GST and the business activity statement?

The general principle is to record revenue at the gross amount and the fee as a separate expense, using the treatment shown on the provider’s statement or tax invoice, rather than netting the fee out of sales. Providers differ in how they report and treat fees, and some charges are treated differently from others, so the specifics should be confirmed by your accountant and then encoded consistently in the integration. What matters technically is that the data supports whichever treatment they specify.

Can this work across several payment providers and marketplaces?

Yes, and that is where the value is greatest. Each source gets its own clearing account and its own import of settlement data, all feeding the same matching engine and the same exception queue. Marketplaces usually need the most care because their statements contain several fee types, promotional adjustments and sometimes reserves held back for a period, all of which need separate treatment rather than being lumped into one commission figure.

What about cash and card terminals in physical stores?

They belong in the same framework. Terminal batch totals are matched to till declarations and to the bank deposit, cash variances are reported daily by store and operator, and any surcharge or tip handling is treated explicitly. Doing this daily rather than weekly matters, because a variance is far easier to explain while the shift is still fresh in someone’s memory, and a pattern by operator or by store is visible long before it becomes a serious problem.

Do we need this if our accounting platform already connects to our store?

Sometimes not, and we will tell you when that is the case. A standard connector between one ecommerce platform and one accounting system, with a single payment provider, often handles this well. The case for a built solution appears when you have several payment sources, marketplaces with complex statements, physical stores, gift cards and loyalty in the mix, or an ERP between the store and the ledger. That is when the standard connector starts producing journals someone has to correct every month.

Get the Month End Down to an Exception List

Book a call. We review your actual payout statements and ledger structure, show you where the manual effort really goes, and give you a design and a priced plan. The review is yours either way.