Calculate effective rates on flat-fee matters

Practice relevance

Cross-practice idea · Useful supporting administrative method; no claim of practice-specific legal instructions.

Scope: Clio productivity reports and Excel PivotTables; accounting comparison, not a prescribed legal fee calculation.

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 & evidence

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.

What’s been verified

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.

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.

Pain evidence: Flat-Fee Trap ↗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: Clio productivity reports ↗Clio productivity reports documents billed/unbilled activity reporting; Excel PivotTables summarizes structured data. Report definitions and date scopes must match.
Method: Excel PivotTables ↗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. We wrote these prompts. The adapted workflow hasn’t been tested.

Suggested approach

Start when: Monthly review or matter completion

aggregate actual recorded hours, approved flat fee and explicitly supplied costs
Keep row or page references and mark missing or ambiguous evidence. Do not apply the suggested changes.
The responsible staff member checks the draft against the source records and approves any separate 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: Finance checks inputs and definitions; lawyer decides scope/pricing. Test missing hours, partial matters and mixed currencies. No ROI or savings claim is made.

What you need

Excel Copilot, optional · prepared inputs

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

Before you start

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.

Check 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.

Ask Clio for the actual report
Clio Manage AI · 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.
Clio Manage AI promptGenerate 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.
Step documentation 1 ↗
Download the native source
Clio Manage · 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.
Step documentation 1 ↗Step documentation 2 ↗
Verify and map into the three kit tables
Excel and original fee/cost records · 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.
Step documentation 1 ↗Step documentation 2 ↗Step documentation 3 ↗
Ask for a formula-visible calculation
Excel Copilot, optional · 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.
Excel Copilot, optional promptUse 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.
Step documentation 1 ↗Step documentation 2 ↗
Release the management report
Final human review
Finance reviewer and responsible lawyer · 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.
Step documentation 1 ↗

What you’ll 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.

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

Input files

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

M1: USD3000 fee, 10 hours, complete capture
M2: USD3000 fee, zero recorded hours
M3: USD3000 fee, 10 hours, incomplete capture

Expected output

M1: USD300 per recorded hour, not profit
M2: rate unavailable, check hours
M3: provisional USD300 per recorded hour

Test a failure

Finance checks inputs and definitions; lawyer decides scope/pricing. Test missing hours, partial matters and mixed currencies. No ROI or savings claim is made.

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 10 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
Setup and review checklist Required inputs, file use, review steps and limits
Matter fees blank CSV Blank input headers for matter-fees.csv
Matter fees synthetic example Synthetic test records for matter-fees.csv
Actual hours blank CSV Blank input headers for actual-hours.csv
Actual hours synthetic example Synthetic test records for actual-hours.csv
Known costs blank CSV Blank input headers for known-costs.csv
Known costs synthetic example Synthetic test records for known-costs.csv
Synthetic example expected results Compare a pilot result with the planted exceptions

Optional full review prompt for prepared inputs

Prepared-input review prompt with Excel Copilot, optional · prepared inputs

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. 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