Free settlement reconciliation worksheet
Reconcile a marketplace settlement from the report to the bank
One settlement or payout, from the marketplace's report to your bank: sales, refunds, fees, the reserve and the tax. The check cell must read 0.00 before you post it. The Excel file adds a log for every settlement in the month.
- Free, no email, no sign-up
- Opens in Excel, Google Sheets and Numbers
- The math is built in
Fill it in here, or download it
Settlement reconciliation worksheet, with the reasons for each part
Type over the underlined parts and print it or save it as a PDF. Or download the Excel file, which has the same layout and formulas plus room for more lines.
Swipe the sheet sideways to see all of it.
-
Positive numbers, in and out
Type each total from the settlement report as a positive number. The sheet knows which lines are money in and which are money out, so a sign typed wrong cannot hide a fee.
-
The marketplace's tax nets to zero
Tax a marketplace collected and paid for you goes in once and out once. It is not your liability and not your revenue. If the two lines differ, the check shows it.
-
A reserve is not a fee
Money held back is money the marketplace still owes you. It sits on the balance sheet until it is released. The log keeps a running balance per channel.
-
The report total and the bank
Type the payout total from the marketplace's report and the deposit from your bank statement. They are two different claims, and both have to agree with the lines.
-
The check must be zero
It adds three differences: lines against the report, the report against the bank, and tax collected against tax remitted. Anything but 0.00 means investigate before you post.
-
One invoice, one fee bill
What the settlement becomes in your books: a sales invoice at gross and a fee bill, plus the tax and reserve lines. Together they equal the deposit, so the bank line matches both at once.
Run this in SKU.io
Settlements that post as one invoice and one fee bill
SKU.io posts each Walmart settlement and each TikTok Shop payout as one sales invoice and one fee bill that add up to the deposit to the cent, and splits a settlement that crosses month end into one part per month.
| In the spreadsheet | In SKU.io |
|---|---|
| Type the totals from the settlement report | Walmart settlements read from Walmart's reconciliation report, daily |
| Track a reserve on the log by hand | TikTok Shop reserves and negative balances kept in a balance-sheet clearing account |
| Split a settlement across two months yourself | One part per month, and the parts still add up to the deposit |
| Tick off which Amazon settlements reached the bank | A Fund Transfer status on every Amazon settlement, and a month-end close check |
A deposit is not a sale
Marketplaces don't pay you per order. They pay one deposit for a batch of orders, after a day, a week or two weeks, with their fees, the refunds and anything they hold back already taken out. The bank sees one number. The books have to see all of it, or the fees vanish into a smaller revenue line and you can't see your fee rate move.
The sample is a two-week Amazon settlement. 12,480.00 of sales becomes a 7,682.35 deposit. Booked the standard way it is an 11,864.80 sales invoice and a 4,182.45 fee bill. The deposit pays both, and the P&L shows the sales and the fees instead of one line that tells you nothing.
What the check catches
I split the check into three because each difference has a different cause. When the lines don't add up to the report, you missed a line, usually a fee type the marketplace added this month. When the report doesn't match the bank, the money is late, was charged to your card instead, or never came. The log's Walmart row shows that case: a settlement with no deposit yet. And when the marketplace's tax doesn't net to zero, it collected tax it did not pay over, which is a question for the marketplace, not a line to post.
The full method, including the month-end split, reserves, negative balances and the switch from per-order invoices to payouts, is in the guide to marketplace payouts and the clearing account. Posting income this way is the first group of the ecommerce accounting standards checklist.
Questions people ask
How do you reconcile a marketplace payout?
Take the marketplace's settlement report, enter each total (sales, refunds, each kind of fee, tax and any reserve), and check that they add up to the report's payout total and to the deposit on your bank statement. In the sample, 13,478.40 in less 5,796.05 out is a 7,682.35 deposit, and the report and the bank both say 7,682.35.
Why does my marketplace deposit not match my sales?
Because a deposit is sales minus fees, minus refunds, minus anything the marketplace holds back, for a period that is not a calendar day or month. In the sample, 12,480.00 of sales becomes a 7,682.35 deposit after 615.20 of refunds and 4,182.45 of fees.
How do I book a marketplace settlement?
As two documents: a sales invoice at gross, less discounts and refunds, and a fee bill for every fee. Their net is the deposit. In the sample that is an 11,864.80 invoice and a 4,182.45 bill, which net to 7,682.35.
How is a marketplace reserve recorded?
As an asset, because the marketplace still owes you the money. The amount held back goes into a reserve account and comes out when it is released. It is not a fee and it does not reduce revenue.
What if a settlement crosses the end of the month?
Split it. Each month takes the lines dated in it, and the parts still add up to the one deposit, so the bank line still matches.
What does it mean if the check is not zero?
Lines vs report means a line is missing or mistyped. Report vs bank means the money has not arrived, is still in transit or was charged to your card. A tax difference means the marketplace collected tax it did not remit, which under facilitator rules should not happen.
Bring a settlement that will not tie
Bring the settlement report and the bank line that do not agree. We will go through it with you and show how SKU.io would post it.
Book a DemoFounder-led demo · 2-day onboarding · no onboarding fees