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.
Realistic ROI
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.
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.
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.
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.
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.
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.
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.
Related Reading
SaaS Integration Explained
The patterns behind any system to system connection.
Shopify to Xero Integration
Orders, payouts and fees into the ledger with correct GST.
Marketplace Integration
Commissions, reserves and settlement statements.
Loyalty and Gift Cards
Balances that are liabilities, not revenue.
Returns and RMA Automation
Where most refund entries originate.
Integration Monitoring
Knowing the day a settlement feed stops arriving.
FAQ
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.
All discussions held in confidence. Australian-based consultants.