Three-way trust reconciliation
“check last month's trust account three ways: each client's ledger balance against the total of that client's trust ledger entries, and the bank statement ending balance against the trust ledger total”
Check last month's trust account three ways: each client's ledger balance against the total of that client's QuickBooks trust ledger entries, and the bank statement ending balance against the trust ledger total, using the bank statement Amount total and using QuickBooks trust client ledger balances for the listed client ending balances.
What the agent built
6 steps, each one an ordinary Mammoth step you can open and change.
- FileQuickBooks trust ledgerCSV export · 35 rows
- FileQuickBooks trust client ledger balancesCSV export · 10 rows
- FileQuickBooks bank statementCSV export · 30 rows
- StepTotal trust ledger by clientStep 1
- StepMatch client balance namesStep 2
- StepTotal bank AmountStep 3
- JoinAdd bank statement total10 of 10 paired
- JoinAdd client balances10 of 10 paired
- StepCompare the trust balancesStep 6
- ResultTrust Account Checks11 rows · 4 flagged
- ScheduleRuns when you sayProposed
Checked on the sample files: every step ran, 11 rows came out, and the join paired 10 of 10 rows; the join paired 10 of 10 rows.
- Download QuickBooks trust ledger sample (35 rows)QuickBooks: Reports > Ledger > Trust account detail > Export
- Download QuickBooks trust client ledger balances sample (10 rows)QuickBooks: Reports > Balance Sheet by Customer > Export
- Download QuickBooks bank statement sample (30 rows)QuickBooks: Banking > Register > Report > Statement export
What you get back
The result of that run, the rows to look at first.
| Check | Client | Client ledger balance | Trust ledger entries total | Bank statement Amount total | Trust ledger total | Difference | Status |
|---|---|---|---|---|---|---|---|
| Bank statement | · | · | · | 21,450 | 21,975 | -525 | Amount differs |
| Client balance | Alder Bio | 3,200 | 2,300 | · | · | 900 | Amount differs |
| Client balance | Ember Logistics | 4,100 | 5,000 | · | · | -900 | Amount differs |
| Client balance | Harborview Dev | 1,250 | 1,625 | · | · | -375 | Amount differs |
| Client balance | Blue Harbor | 1,450 | 1,450 | · | · | 0 | Agrees |
| Client balance | Cedar Peak | 2,750 | 2,750 | · | · | 0 | Agrees |
| Client balance | Dune & Co | 1,800 | 1,800 | · | · | 0 | Agrees |
| Client balance | Falcon Retail | 950 | 950 | · | · | 0 | Agrees |
| Client balance | Granite Health | 2,300 | 2,300 | · | · | 0 | Agrees |
| Client balance | Ion Legal | 1,675 | 1,675 | · | · | 0 | Agrees |
| Client balance | Juniper Studio | 2,125 | 2,125 | · | · | 0 | Agrees |
How it works
How to use the Trust Account Checks workflow
- 1Get early accessWe invite people in small batches, job by job. You'll land on this workflow, already built and checked on the sample files.
- 2Swap in your exportsUpload your own files as they come out of your tools. The agent checks the workflow on your rows.
- 3Change it in words, then turn it onSay what to change and see the new result before anything saves. Then choose when it runs.
Trust Account Checks: questions
How do you do a three way trust reconciliation?
What export should you use from QuickBooks?
What happens to rows that do not match?
How do you change the rules in words?
Get early access to this workflow
It will open already built and checked on the sample files. Swap in your own exports and see your result.
Workflows like this
Match two sets of records line by line, and see what agrees, what differs and what is missing on either side.
- Bank reconciliationEvery line matched, flagged for a date check, or missing from one sideSee the workflow Template
- Payment reconciliationEach payout matched to its bank deposit, flagged for a date check, or missingSee the workflow Template
- Cash reconciliationEach day's takings matched to a bank deposit, or flagged as not bankedSee the workflow Template
- Payment processing fees by methodTrack payment processing fees by method each month, total sales and fees, and flag fee rates that are too high.See the workflow Built by the agent
- Three-way matchEach PO line: matched, partly received, billed more than received, or price differsSee the workflow Template
- Invoice reconciliationEach invoice paid in full, part-paid, overpaid or not paid, and payments with no invoiceSee the workflow Template