Compare trust-reconciliation totals read-only

Practice relevance

Cross-practice idea · A general operational method. Review the stated tool and jurisdiction limits before use.

The problem

Compare approved bank adjustments, the firm ledger, and client balances in one currency.

Why it matters: Three records can disagree even when one pair of totals matches. Showing the differences and unresolved transaction IDs gives the responsible lawyer and bookkeeper a factual review without assigning funds or certifying compliance.

Sources & evidence

Proposed / not tested. The public source describes the problem. Documentation supports only the stated method components; no result from this recipe has been verified.

What’s been verified

Source methods and reported problems have been reviewed. We have not run the adapted workflow. The sample below describes expected behavior on synthetic data, not a customer result.

What to measure: In a synthetic pilot, count correctly found exceptions, false alarms, and unresolved inputs. No time or cost saving is assumed.

Pain evidence: Trust Account Question ↗Trust Account Question, 10 August 2023, older, original read. A closing practitioner maintaining QuickBooks plus general and client trust ledgers asks whether the approach is sufficient and describes money constantly entering/leaving trust. The October 3 thread in Q01 also reports unexplained old trust funds, but is not independent confirmation of misconduct.
Method: Operating and Trust Reconciliation ↗Clio Operating and Trust Reconciliation documents comparison of bank records, firm trust ledger and client trust balances, including uncleared items and unassigned client records. This recipe stops before any native reconciliation submission or accounting change. Jurisdiction-specific requirements must be supplied by the lawyer/accountant, not inferred from the Reddit post.

Source checked: October 15, 2026. Pain accounts are self-reported. Documentation supports components; this adapted recipe and prompt have not been tested. We wrote these prompts. The adapted workflow hasn’t been tested.

Suggested approach

Start when: Accountant supplies same-period exports and approved outstanding-item adjustments

recompute adjusted bank total and compare firm ledger/client totals
Keep source references and report unknowns instead of filling gaps.
The responsible reviewer checks the report before any separately approved action.

Limits and review

The assistant drafts or reports only. Check source records and review the output before a human performs any separately approved send or change. No automatic legal decision, money movement, account connection or publication is included.

Who reviews it: Responsible lawyer and qualified bookkeeper review original statements, timing and jurisdictional rules. Balances or differences alone do not prove misuse. No account access, journal entries, transfers, bill applications or completion of a legal compliance certification.

What you need

Your firm-approved AI assistant · prepared files

Bookkeeper/accountant supervision; one specified trust account, currency and cutoff.
Actual bank-issued statement, firm control/trust ledger, complete client subsidiary balances and approved outstanding-item adjustments.
Clio Manage report access. If the firm uses Clio Accounting, its distinct reporting route must be used and verified separately.
Firm-approved Excel; optional Copilot entitlement and tenant approval for the exact minimized data. Native Excel analysis can be done manually if Copilot is absent.
Keep raw exports and identifying source pointers in a separate restricted location. The AI workbook contains only approved alias-based tables and visible policy/scope text, never raw client records on another tab.
Use synthetic inputs first. Keep real data in firm-approved accounts and processing, with appropriate access, confidentiality and retention controls. Aliases alone do not anonymize records. Never paste credentials, API keys or unnecessary client details.

How to use

Documentation-checked proposed route; source collection and human approval required

Before you start

This is a read-only arithmetic/evidence check, not a completed regulatory reconciliation or authorization to move funds.
A Clio report’s bank-balance field entered by staff is not an independent bank statement. Missing client ledgers, unlinked funds or unapproved adjustments prevent a clean result.

Check access and setup

Actual account access, plan and source-data completeness have not been tested.

Collect the independent bank and ledger evidence
Local document viewer and Clio Manage · Actual bank-issued statement; Reports > Classic reports > Reports > Client > Bank account activity / Trust ledger
Read the actual bank statement locally and confirm account/currency/cutoff. Generate the selected-account Bank account activity report for the firm control balance, with opening balance and required date filters. Generate the Trust ledger for the same account/cutoff with opening balance and bank-account summary as needed. Preserve all client/matter balances and unlinked/unknown amounts separately; the bookkeeper verifies the subsidiary population is complete.
Expected output
Three separately identified evidence sources: bank statement, firm control ledger and client subsidiary detail, plus approved adjustment evidence.
Next step
Keep originals restricted. Do not upload bank account numbers or raw client ledgers to AI.
Check before continuing
If the report filters cannot isolate the correct account or reconstruct cutoff balances, stop. Never substitute transaction-period net movement for closing balance.
Step documentation 1 ↗Step documentation 2 ↗Step documentation 3 ↗
Read the existing reconciliation history, if relevant
Clio Manage · Accounts > trust account > Reconciliations > View report > Export
Open only an already completed reconciliation for the relevant prior/current period. Check its summary, transaction details and client ledger details; Export downloads its PDF. Use it as supporting history, not as a replacement for the bank-issued statement or current ledger. Do not start, finish, void or alter a reconciliation.
Expected output
Existing reconciliation PDF and period reference, if available.
Next step
Bookkeeper checks outstanding items against their actual approval/source records.
Check before continuing
A previous zero difference does not prove the current period or every client balance is correct.
Step documentation 1 ↗
Map the observed records to the starter input
Excel and authorized staff · Separate analysis workbook > Home > Format as Table
Populate reconciliation.template.json manually from verified evidence: account_id alias,currency,cutoff_date,bank_closing_cents,firm_ledger_closing_cents; approved_bank_adjustments with adjustment_id,signed_cents,source_ref,approved_by; client_balances with client_id,balance_cents,source_ref; transactions with transaction_id,client_id,signed_cents,bank_match_id,classification,source_ref. This kit uses integer cents and is limited to currencies with 2 decimal minor units. Copy the same minimized data into visible Control, Adjustments, ClientBalances and Transactions tables; put approved sign convention and coverage in Scope. Create named tables for Control, Adjustments, ClientBalances and Transactions. Put the policy and review scope in visible worksheets or paste them into the next prompt.
Expected output
Completed editorial JSON plus corresponding Excel tables. No bank/ledger value is inferred or copied from another source just to balance.
Next step
Check the mapping against each original source before using AI.
Check before continuing
Aliases and missing policy fields are staff-created; do not relabel a native export as the kit schema. Unknown stays blank/unknown, not false or zero.
Step documentation 1 ↗
Review the bounded evidence with Copilot
Excel Copilot, optional · Approved analysis workbook > Copilot icon at lower right > Chat only
Use Chat only and submit the prompt. Without Copilot: use independent integer-cent sums for bank adjustments and client balances, then compare both with the separately sourced control balance.
Excel Copilot, optional promptCheck Control, Adjustments, ClientBalances, Transactions and Scope only. Validate one account/currency/cutoff and complete subsidiary coverage. Compute adjusted_bank_cents=bank_closing_cents+sum(approved signed adjustment cents); client_total_cents=sum(client balances); show both differences to firm_ledger_closing_cents. Flag missing sources, unapproved/duplicate adjustments, unmatched transactions, negative client balances and unlinked funds separately. Unknown inputs block a clean result. Do not create balancing entries, certify legal compliance or authorize any payment/transfer. Cite rows and reproduce arithmetic for independent checking.
Expected output
Three-way arithmetic worksheet and unresolved-evidence list.
Next step
Copy the draft exception list to the reviewer; retain row references.
Check before continuing
Check every flagged row and the scope. A fluent answer is not evidence of completeness; no automatic workbook or source-system edits.
Step documentation 1 ↗Step documentation 2 ↗
Approve the review result
Final human review
Responsible staff reviewer · Reviewed local worksheet
The bookkeeper/accountant independently recalculates totals and checks every outstanding item, client balance and source. The responsible lawyer reviews the trust-control result through the firm’s established process.
Expected output
Reviewed arithmetic exceptions or a limited no-difference observation for the supplied complete evidence.
Next step
Any external message, source change, payment or deletion requires its own authorized process.
Check before continuing
No live reconciliation, fund movement, balancing adjustment or compliance certification occurs in this workflow.
Step documentation 1 ↗

What you’ll get

A list of balance differences and unresolved transaction IDs. Approved adjustments and source references remain visible for the bookkeeper’s review.

Inputs and a sample test Synthetic example · not a measured result

Input files

reconciliation.json = {account_id,currency,cutoff_date,bank_closing_cents,firm_ledger_closing_cents,approved_bank_adjustments:[{adjustment_id,signed_cents,source_ref,approved_by}],client_balances:[{client_id,balance_cents,source_ref}],transactions:[{transaction_id,client_id,signed_cents,bank_match_id,classification,source_ref}]}. Amounts integer cents in a single currency; positive approved adjustments add to bank closing. classification is accountant-supplied cleared|outstanding|unresolved.

Synthetic example

Bank closing=120000 cents; approved outstanding-check adjustment=-20000; firm ledger=100000; clients C1=60000 and C2=39000.

Expected output

adjusted bank=100000, firm=100000, clients=99000; bank/firm difference 0 and both minus clients=1000 cents unresolved. A transaction with blank client_id is also flagged; the tool must not assign the missing 1000 to either client.

Test a failure

Responsible lawyer and qualified bookkeeper review original statements, timing and jurisdictional rules. Balances or differences alone do not prove misuse. No account access, journal entries, transfers, bill applications or completion of a legal compliance certification.

Try with the sample files

Follow the tool steps above and use the inputs named in each step. Where provided, native-* files are blank scaffolds and NATIVE-INPUT-MAPPING explains their fields. Original examples and the optional full review prompt use a separate prepared-input schema. Keep them separate unless the mapping explicitly connects them. Downloading a kit does not connect accounts or prove the workflow works.

Download inputs and checklist 6 files

Instructions, blank templates and separate synthetic examples; these files do not install an automation. Blank templates still need verified source data.

Download starter kit All inputs, synthetic examples and review notes in one ZIP. No automation is installed.
Start here: get the sources and prepare the review Documentation-checked acquisition, explicit manual mapping, exact prompts and human review limits
reconciliation blank input Preserve nested input structure and validate supplied field types
reconciliation synthetic fixture Preserve nested input structure and validate supplied field types
reconciliation JSON Schema Preserve nested input structure and validate supplied field types
Setup and fixture review guide Recipe-specific setup, input rules, offline prompt, expected result and boundaries

Optional full review prompt for prepared inputs

Prepared-input review prompt with Your firm-approved AI assistant · prepared files

Use this synthetic reconciliation.json read-only. Check one account/currency/cutoff and integer amounts. Missing adjustment approval or source references are unresolved and prevent a complete comparison. Adjusted bank = bank_closing_cents + sum(approved signed adjustments). Compare it with firm_ledger_closing_cents and the sum of client_balances. Return all three totals, pairwise differences, adjustment IDs, missing client mappings, duplicate transaction IDs and unresolved rows. Never invent an adjustment, classify a transaction, net different clients/currencies, mark an account reconciled, post entries or move money. Label the result preliminary accountant/lawyer review.

INPUTS
reconciliation.json = {account_id,currency,cutoff_date,bank_closing_cents,firm_ledger_closing_cents,approved_bank_adjustments:[{adjustment_id,signed_cents,source_ref,approved_by}],client_balances:[{client_id,balance_cents,source_ref}],transactions:[{transaction_id,client_id,signed_cents,bank_match_id,classification,source_ref}]}. Amounts integer cents in a single currency; positive approved adjustments add to bank closing. classification is accountant-supplied cleared|outstanding|unresolved.

OUTPUT
A list of balance differences and unresolved transaction IDs. Approved adjustments and source references remain visible for the bookkeeper’s review.

Use only these synthetic or firm-approved inputs and preserve source row/page IDs. Keep missing or conflicting evidence unresolved. Return a draft for the named reviewer. Do not send messages, connect accounts, change records or permissions, move money, or make legal decisions. Keep source files unchanged. List missing prerequisites before continuing.

Proposed / not tested · editorial prompt. This prompt uses the original prepared-input schema shown in the sample test. Step prompts may use different tables. Follow NATIVE-INPUT-MAPPING where supplied; do not mix the original examples with native-step inputs without mapping and checking every field.

Read original method Read pain evidence