Recheck settlement-sheet arithmetic using approved amounts
Get the source data and prepare the review

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

WHAT WAS CHECKED
Official documentation checked; all routes are proposed and have not been run in a product account. No client data, account access, installs or account writes used. Native column names are distinguished from editorial kit contracts. Missing source evidence blocks completeness claims.

BEFORE YOU START
- Clio Manage Personal Injury add-on only for its native settlement export; otherwise actual draft and approved allocation documents must be available locally.
- Lawyer-approved final gross amount and each permitted deduction, with status/source/approver. No AI entitlement, lien or fee determination.
- 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.

DATA ACQUISITION LIMITS
- The settlement export reflects entered calculations, not legal approval, cleared funds or final lien resolution.
- The kit uses integer cents for a two-decimal currency and approved fixed amounts. Fee percentages, tax treatment, deduction order or unresolved allocations must first be settled by the responsible lawyer.
- Any missing/conflicted required approval, source, final status, gross evidence or line mapping blocks the final net comparison; only a clearly labeled partial subtotal may be shown.

DECLARED INPUT CONTRACT
settlement.json

STEP 1: Export the actual proposed settlement
Tool: Clio Manage Personal Injury
Open: Matters > selected matter > Settlement > top-right Export
Access needed: See prerequisites; actual subscription, tenant configuration and permissions are untested.

Download the native Settlement CSV. Inspect the Recovery summary and its refresh timestamp, recovery amounts, fees, expense groups, liens and outstanding balances. If the add-on is absent, open the actual draft settlement statement locally and manually transcribe its lines with page references.

Expected output: Actual proposed settlement export/statement, with original line structure and total.
Use it next: Separately collect lawyer-approved amount/deduction evidence; do not use the draft as its own approval source.
Check: Native grouping and default fee/deduction settings can differ from the approved arrangement. Preserve detail and avoid double-counting referral fees within legal fees.
Documentation limit: Official sources support named product features. Workflow-specific prompts, alias fields, mappings and reviewer rules are editorial adaptations, not native export promises.
Source documentation:
https://help.clio.com/hc/en-us/articles/18350703919899-Settlement

STEP 2: Map the observed records to the starter input
Tool: Excel and authorized staff
Open: Separate analysis workbook > Home > Format as Table
Access needed: See prerequisites; actual subscription, tenant configuration and permissions are untested.

Populate settlement.template.json: matter_id alias,currency,gross_cents; approved_lines with line_id,category,amount_cents,source_ref,approved_by,status from separate approval records; draft_lines with line_id,amount_cents from the actual draft; draft_net_cents from its stated net. Convert verified amounts to integer cents using the approved rounding rule. Put the same data in visible Control, ApprovedLines and DraftLines tables, with the approved deduction/sign convention and excluded unresolved items in Scope. Create named tables for Control, ApprovedLines and DraftLines. Put the policy and review scope in visible worksheets or paste them into the next prompt. Retain the actual gross-amount source and reviewer approval reference in visible Control/Scope; the JSON alone has no dedicated gross_source field.

Expected output: Editorial settlement JSON and reviewed tables, with separate approval provenance.
Use it next: Check the mapping against each original source before using AI.
Check: 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.
Documentation limit: Official sources support named product features. Workflow-specific prompts, alias fields, mappings and reviewer rules are editorial adaptations, not native export promises.
Source documentation:
https://support.microsoft.com/en-us/excel/tutorial-import-data-into-excel-and-create-a-data-model

STEP 3: Review the bounded evidence with Copilot
Tool: Excel Copilot, optional
Open: Approved analysis workbook > Copilot icon at lower right > Chat only
Access needed: See prerequisites; actual subscription, tenant configuration and permissions are untested.

Use Chat only and submit the prompt. Without Copilot: sum only the approved final deduction amounts, then compare exact line IDs and the draft net. The manual fallback has the same mandatory completeness/approval stop before any final net comparison.

PASTE THIS PROMPT
Check Control, ApprovedLines, DraftLines and Scope. First verify complete required-line coverage, unique approved line IDs, currency/integer cents, gross source evidence, source_ref, approved_by, final status and unambiguous draft mapping for every required item. If any is missing, conflicted or unresolved, STOP the final expected-net and draft-net comparison: report the missing evidence, and at most a clearly labeled partial subtotal that is not a final net. Only after this gate passes, compare draft deductions to the complete approved final lines; flag missing, extra, duplicate or changed lines, and recompute expected_net_cents=gross_cents-sum(approved final deduction cents) under the supplied sign/rounding rules. Compare draft_net_cents. Do not infer lien validity, fee entitlement, settlement approval or authority to disburse. Return exact arithmetic and source references.

Expected output: Settlement arithmetic and authorization-evidence exception list.
Use it next: Copy the draft exception list to the reviewer; retain row references.
Check: Check every flagged row and the scope. A fluent answer is not evidence of completeness; no automatic workbook or source-system edits. A partial approved subset may yield a labeled partial subtotal only, never an expected final net.
Documentation limit: Official sources support named product features. Workflow-specific prompts, alias fields, mappings and reviewer rules are editorial adaptations, not native export promises.
Source 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

STEP 4: Approve the review result
Tool: Responsible staff reviewer
Open: Reviewed local worksheet
Access needed: See prerequisites; actual subscription, tenant configuration and permissions are untested.

The lawyer verifies gross proceeds and all final deductions against actual signed/approved source records; accounting independently checks arithmetic and cleared-funds requirements outside this tool.

Expected output: Reviewed draft statement or unresolved-allocation hold.
Use it next: Any external message, source change, payment or deletion requires its own authorized process.
Check: This workflow neither sends a settlement statement for signature nor approves or executes disbursement.
Documentation limit: Editorial human-review gate; linked Excel documentation supports the worksheet used, not legal or accounting conclusions.
Source documentation:
https://support.microsoft.com/en-us/excel/tutorial-import-data-into-excel-and-create-a-data-model

ACCOUNT AND SOURCE CHECKS STILL NEEDED
- Actual account access, plan and source-data completeness have not been tested.

USING THE FILES IN THIS KIT
Use the exact files or source text named in the steps above. Native exports keep their own headers. The kit tables are editorial review worksheets; they are not a claim that a product exports those fields. Some routes use reviewed text directly or require additional original files. Never rename an unreviewed raw export to make it appear mapped. Keep expected-result files separate from inputs.

The source account, actual data, prompts and end-to-end workflow were not tested. Any final communication, filing, permission change, payment or publication needs its own authorized human action. This guide performs none.