# Calculate effective rates on flat-fee matters Canonical page: https://agents.paretoid.com/workflows/calculate-effective-rates-on-flat-fee-matters/ Category: Operations & Reporting Practice relevance: cross-practice. Useful supporting administrative method; no claim of practice-specific legal instructions. Jurisdiction/provider scope: Clio productivity reports and Excel PivotTables; accounting comparison, not a prescribed legal fee calculation. Show fees per recorded hour, known costs, and gaps in the underlying time data. Status: Proposed / not tested. The public discussion supports the problem; the method source supports only the documented features. No outcome from this adapted recipe has been verified. Verification: The linked sources support the complaint and documented method components. We have not run this recipe or verified its results. The example below is synthetic, not a customer result. ## The problem Show fees per recorded hour, known costs, and gaps in the underlying time data. Why it matters: A flat fee can look healthy while the firm lacks a clear record of the hours behind it. Showing the effective rate alongside incomplete time capture and known costs makes the limits of the calculation visible. ## Sources and evidence - pain: [Pain evidence: Flat-Fee Trap](https://www.reddit.com/r/LawFirm/comments/1r6kgm1/flatfee_trap_how_are_you_guys_tracking_actual/) Flat-Fee Trap, 2026-02-16, older. OP says flat-fee time is poorly tracked and Excel/Trello upkeep is manual, leaving margins unclear. - method: [Method: Clio productivity reports](https://help.clio.com/hc/en-150/articles/14351524759707-Clio-Manage-Productivity-Reports) Clio productivity reports documents billed/unbilled activity reporting; Excel PivotTables summarizes structured data. Report definitions and date scopes must match. - method: [Method: Excel PivotTables](https://support.microsoft.com/en-us/excel/get-started/create-a-pivottable-to-analyze-worksheet-data) Additional official capability reference. It does not validate the complete proposed recipe. Source checked: October 15, 2026. Pain reports are self-reported and identities are unverified. Method documentation supports components, not a tested end-to-end workflow. ## Suggested approach Start when: Monthly review or matter completion 1. aggregate actual recorded hours, approved flat fee and explicitly supplied costs 2. Keep row or page references and mark missing or ambiguous evidence. Do not apply the suggested changes. 3. The responsible staff member checks the draft against the source records and approves any separate action. ## What you need - Clio Manage user with access to the selected matters and productivity reports; AI Launchpad access is optional. Exact report/AI entitlement varies with plan and region; confirm visible access before using the AI route. - Excel desktop or web; an eligible, firm-approved Copilot license only for the optional AI analysis. Microsoft 365 tenant settings can limit availability. - Original agreed fee/scope and any amendments, recorded time, and cost evidence. The firm must define period versus lifetime review and identify genuine work-time rows. - 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-verified native acquisition path; proposed law-firm calculation and prompts, not tested - Clio Grow-only is not the documented source for actual hours. No Grow AI data query is claimed. - A productivity report can mix time, expenses, flat-fee charge lines, group totals and differing date meanings. Do not blindly sum Quantity or Total. Missing time capture remains missing. - Approved fee, currency, capture completeness, scope status and cost scope are reviewed inputs; they are not a promised native CSV export. Access and setup: - Exact subscription/region, AI entitlement, report availability and filters in the firm account were not observed. - The source documentation does not guarantee that every flat-fee work hour was captured or that all overhead costs exist in Clio. ### 1. Ask Clio for the actual report Tool: Clio Manage AI Location: Main navigation diamond/gem > AI Launchpad > Ask a question Use the prompt, then open the report link in the response. Substitute the precise review dates and selected matter. If AI access is absent or fails to return the correct report, take the native report route in the next step. Prompt: ```text Generate the Matter productivity by user report for [MATTER] for [START DATE] through [END DATE]. Include the recorded work needed to review that matter. Give me the report link; do not calculate profit or change any data. ``` Expected output: A link to a native report, not an invented flat-fee profitability table. Next step: Open the linked report and confirm its name and scope. Check before continuing: Check selected matter, dates and accessible records. A prose AI summary alone is not the data source. Execution status: Official documentation checked 2026-10-15; product account and workflow not personally tested Step documentation: https://help.clio.com/hc/en-us/articles/29658606211483-Manage-AI-Generate-Reports-and-Monitor-Performance ### 2. Download the native source Tool: Clio Manage Location: Reports > Classic reports > Reports > Productivity > Matter productivity by user For either the AI-linked report or the manual fallback, set the available matter/date filters for the agreed scope. Choose CSV under Output format and Generate report. Preserve an untouched local copy with generation date and filter notes. Expected output: Native report with its own headers; documentation describes Date, Status, Description, Note, Quantity, Rate and Total, plus grouped matter/user information. Next step: Open the native CSV locally in a restricted source workbook. Keep it out of the later Copilot analysis workbook. Check before continuing: Separate actual time entries from expense quantities, flat-fee billing charge entries and subtotal rows. Confirm any uncertain row against the matter record. Execution status: Official documentation checked 2026-10-15; product 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/14351524759707-Clio-Manage-Productivity-Reports ### 3. Verify and map into the three kit tables Tool: Excel and original fee/cost records Location: Restricted source workbook plus a separate alias-only analysis workbook For each selected matter, assign one alias. Sum only human-verified actual work hours across the intended scope, including relevant non-billable time. Copy the agreed fee and currency from the approved engagement/amendment. Copy only evidenced costs. Retain source-row references in a separate mapping sheet. Paste results into the supplied kit tables; do not rename the raw export to pretend it already matches them. Keep the raw Clio report, identifying aliases map, client names and Notes in the restricted source workbook. Create a separate Copilot analysis workbook containing only approved alias-based Fees, Hours and Costs and non-identifying source-row references; do not leave the raw report on another tab. Expected output: matter-fees.csv: matter_alias,currency,approved_flat_fee,scope_status. actual-hours.csv: matter_alias,recorded_hours,capture_complete. known-costs.csv: matter_alias,currency,explicit_cost,cost_scope. All identifiers and completeness flags are staff-assigned. Next step: Select each range > Home > Format as Table; name the tables Fees, Hours and Costs. Keep one Hours row per matter. Check before continuing: Fee scope must match hours scope. Label open/partial matters provisional. Unknown costs are unknown, not zero. Verify that a flat-fee charge quantity of 1 has not become one hour. Execution status: Official documentation checked 2026-10-15; product account and workflow not personally tested Step documentation: https://help.clio.com/hc/en-us/articles/14351524759707-Clio-Manage-Productivity-Reports https://help.clio.com/hc/en-150/articles/13719345938843-Manage-Fixed-Fee-Matters https://support.microsoft.com/en-us/excel/tutorial-import-data-into-excel-and-create-a-data-model ### 4. Ask for a formula-visible calculation Tool: Excel Copilot, optional Location: Copilot icon at lower right > Chat only In the prepared workbook open Copilot and use Chat only for a read-only first pass. Paste the prompt. If the button is unavailable, calculate each reviewed row manually as approved_flat_fee / recorded_hours; leave zero/missing hours blank with a warning. Prompt: ```text Use only this workbook. For each matter_alias, join Fees to Hours by exact alias. Check duplicates and missing aliases first. Calculate approved_flat_fee divided by recorded_hours only when hours are numeric and greater than zero. Separately list evidenced Costs by matching alias and currency. Return matter_alias, fee, currency, recorded_hours, effective_rate, scope_status, capture_complete, cost_scope, warnings and source cells; show the Excel formulas. Label incomplete capture or scope provisional. Do not infer missing hours or costs, convert currencies, call the result profit, rank staff or edit this workbook. ``` Expected output: Proposed effective-rate rows and source references; exact formulas for a separate review sheet. Next step: Copy only reviewed formulas/results into a new review sheet. No upload to another service is needed for the manual route. Check before continuing: Check 3000 USD / 10 hours = 300 USD/hour; also test missing/zero hours and mixed currencies. Execution status: Official documentation checked 2026-10-15; product 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. Release the management report Tool: Finance reviewer and responsible lawyer Location: Reviewed local workbook Reconcile hours and fees to source records, approve the definitions, then save the final internal report with its data-quality warnings. Any pricing decision is a separate human decision. Expected output: Reviewed effective-rate report, explicit known costs and unresolved data gaps. Next step: Internal management review only. Check before continuing: Do not mark complete while fee/hour scope or time-row classification is unresolved. Execution status: Official documentation checked 2026-10-15; product account and workflow not personally tested Step documentation: https://help.clio.com/hc/en-us/articles/14351524759707-Clio-Manage-Productivity-Reports ## What you will get effective-rates.csv: matter_alias, currency, approved_flat_fee, recorded_hours, capture_complete, effective_rate_formula, effective_rate, known_costs, data_warning; calculations are not profit. No live changes. ## Human review and limits Finance checks inputs and definitions; lawyer decides scope/pricing. Test missing hours, partial matters and mixed currencies. No ROI or savings claim is made. 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: During the pilot, count findings confirmed by the reviewer, false alarms, and missing-input cases. Do not infer time or money saved. ## Original prepared-input schema matter-fees.csv: matter_alias, currency, approved_flat_fee, scope_status. actual-hours.csv: matter_alias, recorded_hours, capture_complete. known-costs.csv: matter_alias, currency, explicit_cost, cost_scope. Synthetic example input: ```text M1: USD3000 fee, 10 hours, complete capture M2: USD3000 fee, zero recorded hours M3: USD3000 fee, 10 hours, incomplete capture ``` Expected synthetic output: ```text M1: USD300 per recorded hour, not profit M2: rate unavailable, check hours M3: provisional USD300 per recorded hour ``` Failure check: Finance checks inputs and definitions; lawyer decides scope/pricing. Test missing hours, partial matters and mixed currencies. No ROI or savings claim is made. ## 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/a20-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/a20/START-HERE.txt) — Documentation-checked acquisition, explicit manual mapping, exact prompts and human review limits - [Setup and review checklist](https://agents.paretoid.com/assets/starter-kits/a20/README.txt) — Required inputs, file use, review steps and limits - [Matter fees blank CSV](https://agents.paretoid.com/assets/starter-kits/a20/matter-fees-template.csv) — Blank input headers for matter-fees.csv - [Matter fees synthetic example](https://agents.paretoid.com/assets/starter-kits/a20/matter-fees-example.csv) — Synthetic test records for matter-fees.csv - [Actual hours blank CSV](https://agents.paretoid.com/assets/starter-kits/a20/actual-hours-template.csv) — Blank input headers for actual-hours.csv - [Actual hours synthetic example](https://agents.paretoid.com/assets/starter-kits/a20/actual-hours-example.csv) — Synthetic test records for actual-hours.csv - [Known costs blank CSV](https://agents.paretoid.com/assets/starter-kits/a20/known-costs-template.csv) — Blank input headers for known-costs.csv - [Known costs synthetic example](https://agents.paretoid.com/assets/starter-kits/a20/known-costs-example.csv) — Synthetic test records for known-costs.csv - [Synthetic example expected results](https://agents.paretoid.com/assets/starter-kits/a20/expected-results.txt) — Compare a pilot result with the planted exceptions ## Optional original prepared-input prompt Destination: Excel Copilot, optional · prepared inputs This original prompt uses its own prepared-input schema. Do not mix it with native-step tables without the supplied mapping. ```text For each deidentified flat-fee matter, calculate fee divided by recorded hours and separately list explicitly supplied costs. Show formulas and source rows. Mark zero/missing hours and incomplete time capture. Do not call effective rate ‘profit’ unless all costs required by our approved profit definition are provided. Do not infer hours, change pricing or rank staff. INPUTS matter-fees.csv: matter_alias, currency, approved_flat_fee, scope_status. actual-hours.csv: matter_alias, recorded_hours, capture_complete. known-costs.csv: matter_alias, currency, explicit_cost, cost_scope. OUTPUT effective-rates.csv: matter_alias, currency, approved_flat_fee, recorded_hours, capture_complete, effective_rate_formula, effective_rate, known_costs, data_warning; calculations are not profit Use only the supplied synthetic or firm-approved inputs. Cite source row or page IDs for each finding. Keep unknowns unresolved. Return a draft for the responsible staff reviewer. Do not send messages, connect accounts, change records, move money, or make legal decisions. Keep source files unchanged. If an input or permission is missing, list what is needed and stop that check. ``` Proposed / not tested · editorial prompt Published by Law Firm Agents. Proposed adaptation; not tested. Not legal advice.