Law Firm KPI Dashboard: A Monthly Partner Pack Built With AI

A small law firm's partner dashboard should lead with four KPIs: realisation (how much recorded time becomes billed and collected fees), WIP days (how long work waits to be billed), debtor days (how long invoices wait to be paid) and matters opened. AI can turn a de-identified practice-system export into the monthly pack and its commentary.

The pack is only as good as its definitions. Two partners looking at "realisation" can mean different things, and an AI tool will calculate whichever one you describe, confidently. So the work sits in three places: fixing the definitions once, preparing an export that contains no client names, and checking the numbers before partners see them.

Follow me on Instagram@sagnikteaches

The four numbers that explain the cash

Each of these has a plain-English meaning and a formula. Write the formula into the first page of the pack, in small type, so no one has to guess later.

Connect on LinkedInSagnik Bhattacharya

WIP days plus debtor days gives lock-up days, the number of days it takes a dollar of work to become cash; accountants who advise law firms define lock-up in exactly this way, as the time taken to convert work done into cash received. Lock-up is the figure that tells partners whether the firm is funding its clients' matters out of its own pocket. For deeper reading on how billing software handles these ledgers, see which AI billing features for lawyers earn their cost.

Subscribe on YouTube@sagnikteaches

A six-fee-earner firm, one month, in numbers

Take an illustrative firm with six fee earners. At the end of October the export gives these totals (all figures invented for the sum):

• Value of time recorded in October at standard rates: $180,000
• Fees billed in October: $150,000. Fees billed in the previous two months: $142,000 and $158,000.
• Cash received in October: $138,000
• Unbilled WIP at 31 October: $210,000
• Unpaid invoices at 31 October: $265,000
• Matters opened in October: 31. The twelve-month average is 34.
• Hours recorded: 648 against a target of 720 (six fee earners at 120 hours each)

The sums, which the AI should show rather than just state:

1. Billing realisation: 150,000 ÷ 180,000 = 83.3%. That means about 17 cents of every dollar of recorded time did not reach an invoice this month, through write-downs or time simply not yet billed.
2. Collection realisation: 138,000 ÷ 150,000 = 92%.
3. WIP days: billing over the three months to 31 October is 142,000 + 158,000 + 150,000 = 450,000, and those months span 92 days (31 + 30 + 31). So 210,000 ÷ 450,000 × 92 = 42.9 days.
4. Debtor days: 265,000 ÷ 450,000 × 92 = 54.2 days.
5. Lock-up: 42.9 + 54.2 = 97 days, or just over three months from doing the work to being paid.
6. Utilisation: 648 ÷ 720 = 90% of target hours.

A partner reading these learns something specific. Recording is near target, so the shortfall is not idleness. Billing realisation is soft at 83%, and debtor days are the bigger part of lock-up. The firm's problem this month is not finding work; it is converting recorded time into invoices and invoices into cash. That points to actions (a billing-day rule, a debtor chase list) rather than to a general worry.

Preparing the export without client names

Practice systems differ in what they can export, but almost all can produce time entries, invoices, receipts and a matters list as spreadsheet files. Ask for these four, with the following columns only:

• Time entries: date, fee earner initials, matter reference, hours, rate, value, billed or unbilled.
• Invoices: invoice date, matter reference, amount billed, amount paid, date paid.
• Matters: matter reference, date opened, date closed, work type, responsible partner initials.
• WIP and debtor ledger balances at month end, by matter reference.

Leave out client names, time-entry narratives, addresses and anything else that identifies a person. The narrative column is the one to watch: it is free text, and it is where client names live. If the system insists on including it, delete the column before the file goes anywhere. The wider routine for stripping identifiers is in how to anonymise client data before you paste it into AI, and the question of where such files may go is covered in stopping AI tools training on your business data.

A useful trick: if the pack can be built with formulas in Excel, do that and use AI only to write formulas and commentary. The AI then never sees the data at all, only the column headings, which is the safest position. The same method works for cleaning messy data before it enters the sheet.

From export to pack in six steps

1. Put the four files into one workbook

One sheet per file, with the first row as headings and no blank rows or merged cells. Add a sheet called "Definitions" containing the formulas from the table above. This is the sheet you will tell the AI to follow.

2. Ask the AI for formulas, not figures

The most reliable prompt asks for spreadsheet formulas that you paste in, rather than for the tool to calculate. Formulas can be audited; a number pasted from a chat cannot. A prompt along these lines works:

I have a workbook with sheets Time, Invoices, Matters and Ledger.
Time columns: A date, B fee earner initials, C matter ref, D hours, E rate, F value, G billed (Y/N).
Invoices columns: A invoice date, B matter ref, C amount billed, D amount paid, E date paid.
Write Excel formulas for: billing realisation for a month chosen in cell B1, collection realisation,
WIP days and debtor days using the three months ending in B1, and count of matters opened in B1.
Show each formula in its own row with a one-line explanation. Do not calculate any values yourself.

Paste the formulas into a "Calc" sheet and read the results. The AI helped build the calculation; it did not perform it.

3. Reconcile before anyone sees it

Compare three totals with the firm's accounts: fees billed in the month, cash received, and the closing debtor balance. If any differ by more than rounding, stop. The usual causes are credit notes, write-offs booked in another month, or disbursements counted in one source and not the other.

4. Build the one-page front sheet

Six to ten tiles, each with this month's figure, last month's, the 12-month average and a colour set by a threshold you choose. For the layout, a one-page KPI sheet built from last month's sales has a pattern you can copy directly.

5. Draft the commentary

Give the AI the front-sheet figures only, as text, and ask for commentary in a fixed structure. Illustrative prompt and output:

Figures: billing realisation 83.3% (last month 86.1%, 12-month avg 85.0%);
collection realisation 92% (last 94%); WIP days 43 (last 39); debtor days 54 (last 51);
matters opened 31 (avg 34); utilisation 90%.
Write a 120-word partner commentary: two sentences on what moved most, two on the likely
operational reasons I should check (do not assert causes), and three suggested actions.
Plain English, no jargon.

A typical response would say that billing realisation fell by nearly three points and WIP days rose by four, "which suggests more time is waiting to be billed than a month ago", and suggest checking whether a large matter has gone unbilled and whether write-downs rose. That is useful as a prompt for questions. It would be wrong to publish it as an explanation, because the AI cannot see why the figures moved.

6. Add what only a partner knows

Two lines at the top, written by a person: any large one-off that distorts the month (a big matter settling, a paused instruction) and the single decision the pack needs from the partners. Without them, the pack is a set of numbers; with them, it is a meeting agenda.

Four more cases that show how the figures mislead

A month with one very large invoice

If a single $60,000 invoice goes out in November, billing realisation for that month will jump above 100 per cent whenever recorded time was spread over earlier months. Realisation in a single month is volatile for exactly this reason. Show a rolling three-month figure next to the monthly one, and the swing disappears.

Debtor days that look good because invoices stopped

If a firm bills late, debtor days fall, because there are fewer unpaid invoices, while WIP days rise. Looking at debtor days alone would suggest the credit control is excellent. This is why the pack pairs them as lock-up days: the two together say how long it takes to turn work into cash, wherever the delay is.

Matters opened up, fees down

A rise in matters opened with no rise in recorded hours may mean a lot of small low-value instructions, or matters opened by admin without any fee-earner time yet. Split matters opened by work type in the second page, so the headline count does not hide a mix change. A prompt asking the AI to "group matters opened by work type and compare with the previous three months" will produce the table.

Utilisation that hides a person

Ninety per cent across six people could be six people at 90 per cent, or three at 130 and three at 50. Put a small per-fee-earner table on page two, using initials. Be thoughtful about who sees it: this is a management tool for a conversation, and published without context it can damage morale. A firm with a fee-earner time-capture problem might read how AI recovers unbilled hours before using the table in a review.

Thresholds that turn a number into a colour

A tile with no threshold is decoration. Agree thresholds in a partner meeting, write them down, and revisit them each year. The following are illustrative starting points for the firm in the worked example; yours should reflect your own history.

On those thresholds the example firm would see amber on billing realisation (83.3%), amber on collection (92%), amber on WIP days (43) and amber on debtor days (54), while matters opened stays green because 31 against an average of 34 is about 9% below. Having the rule written down also means the AI commentary can be told to flag only what crosses a line.

Turning red and amber tiles into actions

A pack that ends at the numbers wastes the meeting. For each tile that is not green, the pack carries one owner, one action and one date. For the example firm those might read as follows (illustrative):

• Billing realisation amber (83.3%). Owner: the head of litigation. Action: list every matter with more than $5,000 of unbilled time older than 60 days and bill or write down each one. Date: before the next partner meeting.
• Debtor days amber (54). Owner: the finance partner. Action: send the ten largest overdue invoices to the responsible fee earner for a call, using a reminder letter drafted by AI and reviewed by the fee earner. Date: within one week.
• WIP days amber (43). Owner: each partner. Action: agree a monthly billing day, so work does not wait for a "natural" end-point that never arrives.

Ask the AI to draft the overdue-invoice reminder, and keep it short, courteous and free of threats. A fee earner then edits it, because a letter to a long-standing client is a relationship decision, not just a collection step.

A before and after of the commentary

Here is a realistic first draft from the AI, and the version a partner would send. It shows why the editing step matters.

AI draft: "Billing realisation fell to 83.3% because fee earners wrote off more time.
Debtor days rose due to slower client payments. Matters opened were below average as the
market slowed."

Partner edit: "Billing realisation fell to 83.3% (last month 86.1%). Two large matters
have unbilled time of $31,000 between them; both are waiting on client sign-off, not write-offs.
Debtor days rose to 54: three invoices over 90 days make up $48,000 of the balance.
Matters opened were 9% below average, which is within normal range for October."

The AI draft states three causes it could not know. The edited version replaces each with a checked fact and, in doing so, tells the partners where to look. Add this rule to your prompt: "State only what the figures show; for anything about causes, write a question for a partner to answer."

A second firm, a different shape

Consider an illustrative three-fee-earner firm doing mainly fixed-fee work. Realisation against standard rates means less to them, because fees are agreed in advance and time is recorded only for pricing. They swap it for a "fixed fee margin" tile: fee agreed, less recorded hours at an internal cost rate. If a matter type is consistently under-priced, the tile shows it within two or three months. Their WIP is also different: it consists of completed but unbilled work, so a WIP figure over ten days is a red flag by itself. The point is that the core four KPIs are a starting set, and the firm's pricing model decides which of them survive. Keep the Definitions sheet honest about which model you use, and reuse the formula prompt above with the new column names.

Keeping the pack trustworthy

Every pack-building routine decays unless someone owns it. Keep a short checklist on the Definitions sheet and tick it each month:

• Totals reconcile to the accounts (billed, received, debtor balance).
• No client names or narratives in any file that left the practice system.
• Every figure on the front sheet traces to a formula, not a typed value.
• Month selector in cell B1 changed, not formulas rewritten.
• Commentary reviewed and edited by a partner, with the "what only a partner knows" lines added.
• Previous month's pack saved as a PDF so trends can be checked against what partners actually saw.

Test the whole routine by running last quarter's data through it and comparing with the packs you issued by hand. If a figure differs, find out why before the new pack goes anywhere near a partner meeting. Those differences are nearly always definition problems, and fixing them is the real value of the exercise.

The first three months, week by week

Do not try to launch the finished pack. A sensible schedule for the example firm runs like this. In the first week, write the definitions and have the partners sign them off in ten minutes at a meeting; disagreements surface here and are cheap to settle. In the second week, build the export and the Calc sheet, and reconcile one past month against the accounts. In the third week, produce the pack for that past month and ask each partner what they would want to know that it does not say. In the fourth week, run it live for the month just closed, with the checklist ticked. In month two, add the second page. In month three, review the thresholds against what actually happened, and drop any tile nobody has used in a discussion. Expect the pack that survives a year to be shorter than the one first designed.

Where the AI fits and where it does not

AI is good at four things here: writing and explaining formulas, spotting which figures moved most, drafting commentary, and building the layout. It is poor at knowing your firm's context and at arithmetic it performs in its head. So keep the arithmetic in the spreadsheet and the judgement with the partners. A business-tier plan on any of the main assistants will do the job, and nothing in the process needs a specialist legal analytics product until you have outgrown a spreadsheet. If you have no one to maintain it, how to build a KPI dashboard with AI when you have no data team covers the same approach without a specialist.

If you would rather agree the definitions and the export with someone on a call, that is part of what my AI implementation consultation covers. The monthly routine itself is small enough to run in-house once it is set up, and once the first three packs are right, later months are mostly a fresh export, a reconciliation and an edited commentary.

Questions partners ask about the monthly pack

How many KPIs should a small firm's partner pack show?

Between six and ten on the front page, led by the four that drive cash: realisation, WIP days, debtor days and matters opened. More than that and partners stop reading. Anything extra goes on a second page that is only opened when a headline number moves.

Can I paste the billing export straight into ChatGPT?

Not as it comes out of the system. Remove client names, narratives and anything that identifies a person, keep matter references and amounts, and use a business plan that does not train on your content. If you can do the sums in a spreadsheet, you may not need to upload anything.

Why do my realisation figures differ from the accounts?

The likeliest cause is that the definitions differ. Practice systems count recorded time at standard rates, accounts count invoiced fees, and write-offs may be booked in a different month. Agree one written definition per KPI and apply it to both sources before comparing.

Should AI write the commentary on the numbers?

It can draft it, and it is good at noticing which figures moved most. A partner should still edit it, because the AI does not know that October was low because two big matters were paused at the client's request.

How long should the pack take to produce each month?

Once the export and prompts are set up, about 30 to 60 minutes including the checks. The first month will take several hours because you are fixing definitions, not producing a pack.

Further reads

• What an AI Consultant Does for a Law Firm, and What It Costs — What outside help on reporting and automation costs a law firm.
• Urgent Care KPI Dashboard: Wait Times and Visits per Hour With AI — Another professional-service dashboard built from an export.
• AI Implementation Plan for a Small Law Firm: The First 90 Days — A 90-day plan that places reporting among other firm tasks.
• Retail KPI Dashboard in Excel: A Weekly Sheet From Till Data With AI — See the same export-to-sheet routine in a different business.
• How Small Law Firms Use AI to Answer Client Status Questions — Fewer status chases frees fee-earner time for billing.
• Medical Practice KPIs: A Monthly Dashboard Built With AI — A professional practice with a different set of KPIs.
• AI Readiness Checklist for Accountants, Solicitors, Consultants — Check the firm is ready before adding more reporting.
• AI for Fractional CFOs: Monthly KPI Packs and Client Briefings — How a fractional CFO can run monthly KPI packs for several clients with AI: client templates, export-based data, narrative drafts and a review checklist.
• Recruitment Agency KPIs: A Weekly Scorecard From Your CRM Export — The handful of weekly recruitment agency KPIs worth tracking, and how AI turns a CRM or ATS export into a one-page scorecard with ratios and commentary.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Kreston Reeves, lock-up management in legal firms (lock-up days = WIP days + debtor days), checked November 2026; the WIP-day and debtor-day formulas here are one common count-back method, and firms vary. Example figures are illustrative.