# Reconcile recurring management reports

Canonical page: https://agents.paretoid.com/workflows/reconcile-recurring-management-reports/

Category: Operations & Reporting

Practice relevance: cross-practice. A general operational method. Review the stated tool and jurisdiction limits before use.

Jurisdiction/provider scope: 

Combine approved exports with consistent definitions, dates, and control totals.

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

Combine approved exports with consistent definitions, dates, and control totals.

Why it matters: Reports can appear to disagree because they use different date bases, filters, or definitions. Preserving those controls helps the administrator explain discrepancies without mixing billed, earned, and collected amounts.

## Sources and evidence

- pain: [Pain evidence: Clio Software](https://www.reddit.com/r/LawFirm/comments/1dobgp6/clio_software/)
  Clio Software, 2024-06-25, older: a practitioner reports substantial manual work creating client-required reports. Removed Clio thread, surviving comments, 2025-10-23, describes pasting three reports into Excel. Original post is removed; this recipe relies only on visible comments.

- pain: [Pain evidence: Removed Clio thread, surviving comments](https://www.reddit.com/r/LawFirm/comments/1odnqtf/removed/)
  Additional self-reported public account; no independent identity or outcome verification.

- method: [Method: Clio reports, presets and history](https://help.clio.com/hc/en-us/articles/9290078155803-Clio-Manage-Reports-Presets-and-History)
  Clio reports, presets and history documents CSV exports and saved filters, and warns date filters can create apparent discrepancies. Power Query file combination documents repeatable same-schema import.

- method: [Method: Power Query file combination](https://learn.microsoft.com/power-query/combine-files-overview)
  Additional official capability reference. The complete adapted recipe has not been tested.

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: Weekly/monthly reporting cutoff

1. load approved exports, enforce common periods/keys and reconcile 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

- Named Clio Manage reports and access to each actual report; an accountant-approved metric definition for each comparison.

- Agreed period, date basis, timezone, currency, inclusion rules and duplicate keys.

- 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

- Clio warns that reports use distinct criteria; unlike reports cannot be assumed to reconcile. Activity, invoice and collection dates are different facts.

- A universal report-rows.csv export does not exist here. The workflow maps only verified fields from each named report; unavailable dates/IDs remain unknown.

Access and setup:

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

### 1. Generate each named source report separately

Tool: Clio Manage

Location: Reports > Classic reports > Reports > category > actual report name > Output format CSV > Generate report

Have the report owner identify the exact reports being compared and their business question. Generate each with its own documented filters. For an invoice-state comparison, use Accounts receivable and Billing history, retaining their different inclusion rules. Record source report name, generated time, filters, period/date basis, timezone, currency and native control total/count.

Expected output: Separate native CSV files and a control record for each source.

Next step: Preserve originals and their headers; only then build the editorial comparison tables.

Check before continuing: Do not assume the two reports must have equal totals or select a common date field merely to make them agree.

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/14330519112987-Clio-Manage-Billing-Reports

### 2. Map the observed records to the starter input

Tool: Excel and authorized staff

Location: Separate analysis workbook > Home > Format as Table

In report-rows.csv preserve source_report,source_row_id and actual record_id where available. Map only verified metric,activity_date,invoice_date,collection_date,amount,currency; do not substitute one date for another. Keep subtotal/control rows out of transaction rows but record them in source-controls.csv. Enter all source-controls and approved metric-definitions.csv fields from the report owner, including inclusion_rule,duplicate_key and approver_ref. Put scope and exclusions in a visible Scope sheet. Create named tables for ReportRows, SourceControls and MetricDefinitions. Put the policy and review scope in visible worksheets or paste them into the next prompt.

Expected output: ReportRows, SourceControls and MetricDefinitions; provenance retained per row.

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

### 3. 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: check scope compatibility first, then compare exact approved keys and totals with ordinary worksheet sums.

Prompt:

```text
First test whether SourceControls and MetricDefinitions make each proposed comparison compatible in date basis, period, currency, inclusion and granularity. If not, return not_comparable and the exact differences. For compatible metrics only, compare row counts, control totals, missing IDs and duplicate keys, citing source_report/source_row_id. Never force unlike report totals to agree, fill absent date fields or invent accounting adjustments. Separate explained scope differences from unexplained exceptions.
```

Expected output: Comparability verdict followed by bounded reconciliation exceptions.

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

### 4. Approve the review result

Tool: Responsible staff reviewer

Location: Reviewed local worksheet

The report owner/accountant validates definitions and every unexplained exception against original bills/activity/payment records. Approve the comparison or document why it cannot be made.

Expected output: Reviewed report bridge or explicit not-comparable finding.

Next step: Any external message, source change, payment or deletion requires its own authorized process.

Check before continuing: No workbook cleanup should erase raw rows or manufacture balancing entries.

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

report-reconciliation.csv: report_alias, date_range, filter_scope, source_row, observed_amount, comparison_amount, difference, explanation_evidence, unresolved_question, reviewer. Never invent a balancing entry or alter financial records.

## Human review and limits

Firm administrator/bookkeeper signs off definitions and totals. Plant a duplicate row and a next-month invoice for prior-month activity. Distinct from A20 flat-fee economics and growth attribution reports.

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

report-rows.csv: source_report, source_row_id, record_id, metric, activity_date, invoice_date, collection_date, amount, currency

source-controls.csv: source_report, generated_at, filters, period_start, period_end, date_basis, timezone, currency, control_row_count, control_total

metric-definitions.csv: metric, approved_definition, date_basis, inclusion_rule, duplicate_key, approver_ref

Synthetic example input:

```text
R1: duplicate source row
R2: next-month invoice for prior-month activity
R3: source control total differs from input sum
```

Expected synthetic output:

```text
R1: duplicate exception
R2: apply supplied date basis
R3: unresolved total, do not force a balance
```

Failure check: Firm administrator/bookkeeper signs off definitions and totals. Plant a duplicate row and a next-month invoice for prior-month activity.

## 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/s12-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/s12/START-HERE.txt) — Documentation-checked acquisition, explicit manual mapping, exact prompts and human review limits

- [report-rows.csv](https://agents.paretoid.com/assets/starter-kits/s12/report-rows.csv) — Blank input template with exact recipe headers.

- [report-rows.example.csv](https://agents.paretoid.com/assets/starter-kits/s12/report-rows.example.csv) — Separate synthetic fixture input. Do not mix these rows into real exports.

- [source-controls.csv](https://agents.paretoid.com/assets/starter-kits/s12/source-controls.csv) — Blank input template with exact recipe headers.

- [source-controls.example.csv](https://agents.paretoid.com/assets/starter-kits/s12/source-controls.example.csv) — Separate synthetic fixture input. Do not mix these rows into real exports.

- [metric-definitions.csv](https://agents.paretoid.com/assets/starter-kits/s12/metric-definitions.csv) — Blank input template with exact recipe headers.

- [metric-definitions.example.csv](https://agents.paretoid.com/assets/starter-kits/s12/metric-definitions.example.csv) — Separate synthetic fixture input. Do not mix these rows into real exports.

- [Expected fixture findings](https://agents.paretoid.com/assets/starter-kits/s12/expected-fixture-result.txt) — Read-only expected output and failure conditions for the included synthetic input files.

- [Setup and review checklist](https://agents.paretoid.com/assets/starter-kits/s12/README.txt) — Recipe-specific prerequisites, file mapping, prompt, setup steps, checklist and limits.

## 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
Build this management report from the supplied deidentified exports using only the metric definitions and reporting period provided. Preserve row IDs. State each source's filters and generation time. Reconcile counts and totals; explain unmatched rows, nulls and period differences without forcing them to balance. Do not mix billed, earned and collected figures, rank employees or change accounting entries. Mark incomplete inputs visibly.

INPUTS
report-rows.csv: source_report, source_row_id, record_id, metric, activity_date, invoice_date, collection_date, amount, currency
source-controls.csv: source_report, generated_at, filters, period_start, period_end, date_basis, timezone, currency, control_row_count, control_total
metric-definitions.csv: metric, approved_definition, date_basis, inclusion_rule, duplicate_key, approver_ref

OUTPUT
report-reconciliation.csv: report_alias, date_range, filter_scope, source_row, observed_amount, comparison_amount, difference, explanation_evidence, unresolved_question, reviewer. Never invent a balancing entry or alter financial records.

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.