# Compare trust-reconciliation totals read-only Canonical page: https://agents.paretoid.com/workflows/compare-trust-reconciliation-totals-read-only/ Category: Operations & Reporting Practice relevance: cross-practice. A general operational method. Review the stated tool and jurisdiction limits before use. Jurisdiction/provider scope: Compare approved bank adjustments, the firm ledger, and client balances in one currency. Status: Proposed / not tested. The public source describes the problem. Documentation supports only the stated method components; no result from this recipe has been verified. Verification: 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. ## 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 and evidence - pain: [Pain evidence: Trust Account Question](https://www.reddit.com/r/LawFirm/comments/15ngrze/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: [Method: Operating and Trust Reconciliation](https://help.clio.com/hc/en-us/articles/21058568234267-Clio-Accounting-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. ## Suggested approach Start when: Accountant supplies same-period exports and approved outstanding-item adjustments 1. recompute adjusted bank total and compare firm ledger/client totals 2. Keep source references and report unknowns instead of filling gaps. 3. The responsible reviewer checks the report before any separately approved action. ## What you need - 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 - 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. Access and setup: - Actual account access, plan and source-data completeness have not been tested. ### 1. Collect the independent bank and ledger evidence Tool: Local document viewer and Clio Manage Location: 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. Execution status: Official documentation checked 2026-10-15; account and workflow not personally tested Step documentation: https://help.clio.com/hc/en-us/articles/9290078155803-Clio-Manage-Reports-Presets-and-History https://help.clio.com/hc/en-us/articles/14344842681115-Clio-Manage-Client-Reports https://help.clio.com/hc/en-us/articles/9290138047899-Trust-Reconciliation-in-Clio ### 2. Read the existing reconciliation history, if relevant Tool: Clio Manage Location: 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. Execution status: Official documentation checked 2026-10-15; account and workflow not personally tested Step documentation: https://help.clio.com/hc/en-us/articles/54824841490075-View-Reconciliation-Reports ### 3. Map the observed records to the starter input Tool: Excel and authorized staff Location: 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. Execution status: Official documentation checked 2026-10-15; account and workflow not personally tested Step documentation: https://support.microsoft.com/en-us/excel/tutorial-import-data-into-excel-and-create-a-data-model ### 4. Review the bounded evidence with Copilot Tool: Excel Copilot, optional Location: 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. Prompt: ```text Check 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. Execution status: Official documentation checked 2026-10-15; account and workflow not personally tested Step documentation: https://support.microsoft.com/en-us/excel/copilot/get-started-with-copilot-in-excel https://support.microsoft.com/en-us/excel/copilot/frequently-asked-questions-about-copilot-in-excel ### 5. Approve the review result Tool: Responsible staff reviewer Location: 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. Execution status: Official documentation checked 2026-10-15; account and workflow not personally tested Step documentation: https://support.microsoft.com/en-us/excel/tutorial-import-data-into-excel-and-create-a-data-model ## What you will get A list of balance differences and unresolved transaction IDs. Approved adjustments and source references remain visible for the bookkeeper’s review. ## Human review and limits 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. 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. What to measure: In a synthetic pilot, count correctly found exceptions, false alarms, and unresolved inputs. No time or cost saving is assumed. ## Original prepared-input schema 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 input: ```text Bank closing=120000 cents; approved outstanding-check adjustment=-20000; firm ledger=100000; clients C1=60000 and C2=39000. ``` Expected synthetic output: ```text 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. ``` Failure check: 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. ## Starter files Native blank scaffolds and original synthetic examples may use different schemas. Follow NATIVE-INPUT-MAPPING where supplied. These files do not connect accounts or install automation. - [Download starter kit](https://agents.paretoid.com/assets/starter-kits/q03-starter-kit.zip) — All inputs, synthetic examples and review notes in one ZIP. No automation is installed. - [Start here: get the sources and prepare the review](https://agents.paretoid.com/assets/starter-kits/q03/START-HERE.txt) — Documentation-checked acquisition, explicit manual mapping, exact prompts and human review limits - [reconciliation blank input](https://agents.paretoid.com/assets/starter-kits/q03/reconciliation.template.json) — Preserve nested input structure and validate supplied field types - [reconciliation synthetic fixture](https://agents.paretoid.com/assets/starter-kits/q03/reconciliation.example.json) — Preserve nested input structure and validate supplied field types - [reconciliation JSON Schema](https://agents.paretoid.com/assets/starter-kits/q03/reconciliation.schema.json) — Preserve nested input structure and validate supplied field types - [Setup and fixture review guide](https://agents.paretoid.com/assets/starter-kits/q03/README.txt) — Recipe-specific setup, input rules, offline prompt, expected result and boundaries ## Optional original prepared-input prompt Destination: Your firm-approved AI assistant · prepared files This original prompt uses its own prepared-input schema. Do not mix it with native-step tables without the supplied mapping. ```text 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 Published by Law Firm Agents. Proposed adaptation; not tested. Not legal advice.