Report effective rate and known costs on flat-fee matters
Get the source data and prepare the review

Documentation-verified native acquisition path; proposed law-firm calculation and prompts, not tested

WHAT WAS CHECKED
Public official product documentation only. No product login, account write, real client data, installation, or live workflow test. Prompts and schemas are proposed editorial adaptations; vendor documentation verifies component features, not these end-to-end recipes. Native exports retain their real headers; kit schemas require the explicit mapping steps below.

BEFORE YOU START
- 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.

DATA ACQUISITION LIMITS
- 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.

STEP 1: Ask Clio for the actual report
Tool: Clio Manage AI
Open: Main navigation diamond/gem > AI Launchpad > Ask a question
Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested.

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.

PASTE THIS PROMPT
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.
Use it next: Open the linked report and confirm its name and scope.
Check: Check selected matter, dates and accessible records. A prose AI summary alone is not the data source.
Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations.
Source documentation:
https://help.clio.com/hc/en-us/articles/29658606211483-Manage-AI-Generate-Reports-and-Monitor-Performance

STEP 2: Download the native source
Tool: Clio Manage
Open: Reports > Classic reports > Reports > Productivity > Matter productivity by user
Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested.

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.
Use it next: Open the native CSV locally in a restricted source workbook. Keep it out of the later Copilot analysis workbook.
Check: Separate actual time entries from expense quantities, flat-fee billing charge entries and subtotal rows. Confirm any uncertain row against the matter record.
Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations.
Source 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

STEP 3: Verify and map into the three kit tables
Tool: Excel and original fee/cost records
Open: Restricted source workbook plus a separate alias-only analysis workbook
Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested.

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.
Use it next: Select each range > Home > Format as Table; name the tables Fees, Hours and Costs. Keep one Hours row per matter.
Check: 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.
Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations.
Source 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

STEP 4: Ask for a formula-visible calculation
Tool: Excel Copilot, optional
Open: Copilot icon at lower right > Chat only
Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested.

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.

PASTE THIS PROMPT
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.
Use it next: Copy only reviewed formulas/results into a new review sheet. No upload to another service is needed for the manual route.
Check: Check 3000 USD / 10 hours = 300 USD/hour; also test missing/zero hours and mixed currencies.
Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations.
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 5: Release the management report
Tool: Finance reviewer and responsible lawyer
Open: Reviewed local workbook
Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested.

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.
Use it next: Internal management review only.
Check: Do not mark complete while fee/hour scope or time-row classification is unresolved.
Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations.
Source documentation:
https://help.clio.com/hc/en-us/articles/14351524759707-Clio-Manage-Productivity-Reports

ACCOUNT AND SOURCE CHECKS STILL NEEDED
- 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.

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.
