Can I reconcile an auction trust account in Excel?

Yes, and for a single small auction a month it is often enough. It becomes unreliable once there are several auctions, many consignors, or a year of history to reproduce.

What tabs does a trust reconciliation spreadsheet need?

Receipts, disbursements, consignor balances and a summary computing the bank adjusted balance, trust control balance and consignor subledger total.

Why do spreadsheet reconciliations fail an examination?

Usually because a formula was overwritten, a name was matched by eye, a prior month was edited after filing, or the workbook cannot be reproduced from the source records.

Auction Trust Reconciliation Spreadsheet Guide

The workbook

Four tabs. Anything fewer and you're doing arithmetic in your head.

Tab	Columns	What it's for
Receipts	Date, auction, payer, amount, deposit date	Everything that came in. Each row tied to an auction.
Disbursements	Date, payee, check no., amount, settlement ref	Everything that went out. Each row tied to a settlement.
Consignors	Consignor, auction, gross, commission, fees, net owed, paid, balance	Who the money belongs to. This is the tab people skip, and it's the one that matters.
Summary	The three balances	Bank adjusted, trust control, consignor subledger — computed, never typed.

The three formulas

Bank adjusted balance = statement ending balance + deposits in transit − outstanding checks.
Trust control balance = opening balance + SUM(receipts) − SUM(disbursements).
Consignor subledger total = SUM of the balance column on the Consignors tab.

All three must agree. If they don't, the difference goes on an exceptions list with a name, a date, an amount and a decision — never into a plug cell. The routine around this is in the monthly reconciliation guide, and you can check your figures right now with the free three-way calculator.

When the spreadsheet is fine

One auction a month, a handful of consignors, one person doing it, no history to reproduce. Genuinely — don't buy software for that.

The four things that break it

Someone types over a formula. A cell that should be =SUM() becomes a number, and from then on the workbook agrees with itself and with nothing else. Nobody notices, because it still balances.
Names get matched by eye. "J. Smith Estate" in the settlement export and "Smith, J Estate" in QuickBooks are the same person to you and two different rows to the sheet. Either you spot it or a consignor's payout silently isn't counted.
Last month gets edited. You correct something in the prior tab to make this month work. Now the month you already filed doesn't match the workbook that's supposed to support it — which is exactly what an examiner finds.
You can't reproduce it. Eighteen months on, someone asks how you arrived at a figure. The exports have been overwritten, the formula history is gone, and the honest answer is "I don't know."

The first three are survivable. The fourth is the one that costs you, because a reconciliation you can't reproduce isn't evidence of anything — and reproducibility is the whole point of the exercise.

What to do instead of retyping

The work itself doesn't change — the three balances and the exception list are the same. What changes is where the numbers come from. Instead of keying the settlement export, the QuickBooks payout file and the bank statement into tabs, the files go in as they are and the arithmetic runs on them directly: the same three balances, every exception named, a check register written from the payout file rather than retyped, and each month kept as its own record so nothing back-edits.

That's what SaleLedger is — nothing more ambitious than the workbook above, done in a way that can't drift and can be reproduced two years later. See it on a fictional month in the sample report.

Reconciling a trust account in a spreadsheet.

The workbook

The three formulas

When the spreadsheet is fine

The four things that break it

What to do instead of retyping

Three files in, the whole reconciliation out.