WIP Report Template for Service Firms: Build One From Timesheets

A work-in-progress (WIP) report for a professional services firm lists, by client or matter, the value of time and costs recorded but not yet invoiced, split by how old it is. Add this month's billed amounts, write-offs and a roll-forward from last month's total, so unbilled work is visible before it goes stale.

It is built from three things you already have: a timesheet export, a rate table and last month's closing figure. Nothing in it needs construction-style percent-complete accounting. The hard part is not arithmetic but discipline, because the report only helps if it is produced on a fixed date, read by the people who can bill, and followed by action on the oldest items.

Follow me on Instagram@sagnikteaches

What goes in the report, and why each column exists

A service-firm WIP report has five blocks. Each answers a different question for the partner reading it.

Connect on LinkedInSagnik Bhattacharya

1. Unbilled time and costs by matter, valued at standard rates. Answers: what are we owed for work done?
2. Ageing buckets (0 to 30, 31 to 60, 61 to 90, over 90 days). Answers: what is going stale?
3. Billed this month, at standard value and at invoiced value. Answers: how much did we discount at billing?
4. Write-offs with reason codes. Answers: where does value leak, and why?
5. A roll-forward: opening WIP plus time recorded, less billed, less written off, equals closing WIP. Answers: does the report reconcile?

Beyond that, add two measures partners understand quickly: lock-up days (how many days of fees are tied up in WIP and unpaid invoices) and billing realisation (invoiced value divided by standard value of the time billed).

Subscribe on YouTube@sagnikteaches

The worked example: a firm with four fee earners

Take a law firm with four fee earners: two partners at $300 an hour, one associate at $220 and one paralegal at $130. At 31 October it has six active matters with unbilled time. Every figure below is illustrative.

Block 1 and 2: unbilled time by matter and age

Read it the way a partner would. Matters B and D hold $9,000 of the $32,100 total that is over 60 days old: 28% of WIP. Over 90 days is $4,200, or 13.1%. Matter C is large but entirely recent, so it is a billing job for this week, not a worry. Matter D (probate) has $3,000 older than 90 days and nothing billed, which usually means nobody wanted to chase the executor for fees. That row is the conversation.

Block 3, 4 and 5: billed, written off, and the roll-forward

The roll-forward must tie to the ageing table total. If $32,100 does not match, something is double counted or missing, and you find it before anyone reads the report. In this example the invoices issued in October totalled $31,800 against $33,800 of standard-value time, so billing realisation is 31,800 divided by 33,800, which is 94.1%. The $2,000 gap is a discount applied at billing. Add the $2,000 written off and total leakage is $4,000 against $38,500 recorded, about 10.4%. Separate the two numbers: write-downs happen at invoice, write-offs happen on WIP that never gets billed.

Lock-up days: a sum you can do on a napkin

Say annual fees are $396,000 and unpaid invoices stand at $58,000. Daily fees are 396,000 divided by 365, about $1,085. WIP days are 32,100 divided by 1,085, which is 29.6. Debtor days are 58,000 divided by 1,085, which is 53.5. Lock-up is the two added together: about 83 days between doing the work and being paid for it. Watch the trend rather than the absolute figure; a rise of ten days in two months is worth a meeting.

Build it from a timesheet export

You need the export to carry at least these fields: date, fee earner, client and matter code, narrative, hours, whether it's billable, and the invoice number if billed. Most practice and time-tracking tools can export these as a CSV; check whether yours exports unbilled entries only or everything.

1. Export everything for the period you care about, including billed lines, so you can reconcile. Save the raw file untouched and work on a copy.
2. Clean it. Blank matter codes, misspelt clients, zero rates and non-billable entries marked billable are the usual culprits. The next section uses AI for this.
3. Add a rate column by looking up each fee earner in a rate table. Value equals hours times rate.
4. Add an age column: report date minus entry date, then a bucket label.
5. Filter to unbilled lines (no invoice number) and make a pivot table of matter by bucket, values summed.
6. Run the roll-forward with SUMIFS on date ranges and billed status, and compare with last month's closing WIP.
7. Add commentary for the five biggest matters and every matter with money over 90 days.

Useful formulas in Excel or Google Sheets, with columns as in your export (adjust the letters):

Value:      =D2*VLOOKUP(B2,RateTable,2,FALSE)
Age days:   =ReportDate-A2
Bucket:     =IF(F2<=30,"0-30",IF(F2<=60,"31-60",IF(F2<=90,"61-90","90+")))
Unbilled:   =SUMIFS(G:G,H:H,"",C:C,"Matter A")
Realisation: =InvoicedValue/StandardValueBilled
WIP days:   =ClosingWIP/(AnnualFees/365)

For help with the spreadsheet side, the ChatGPT Excel guide covers asking an assistant to write and explain formulas like these, and the older clean messy data tutorial covers the tidying. Check any formula the AI writes on a few rows you can verify by hand before you trust it across the sheet.

Where AI helps and where it should not touch the numbers

Keep the arithmetic in the spreadsheet. AI earns its place in three places: finding problems in the raw export, tidying narratives, and drafting commentary. It should never be asked to total a column.

Finding dirty rows

Below is a timesheet export with these columns: date, fee earner, client, matter code, narrative, hours, billable, invoice no.
Without changing any values, list rows where: matter code is blank or looks like a typo of another code; hours exceed 10 in a day for one person on one matter;
narrative says "admin" or "misc" with more than 1.5 hours; billable is "no" but the narrative suggests client work; or the same fee earner, date and narrative appear twice.
Return row numbers and the reason. Do not fix anything.

Illustrative result: "Row 214: matter code 'LS-0O3' looks like a typo of 'LS-003'. Row 388: 3.5 hours with narrative 'misc'. Rows 402 and 403: identical fee earner, date and narrative." You then correct them in the source system, not just in the export, so next month's export is cleaner.

Making narratives billable-ready

Short or cryptic time narratives delay billing because someone has to rewrite them before an invoice goes out. Before: "call re docs". After a prompt asking for a professional one-line description using only the facts given plus the matter context: "Telephone call with client regarding outstanding documents for the lease renewal." Always tell the AI not to invent detail (who was called, what was discussed). If a narrative has too little to work with, the right output is a query back to the fee earner, not a guess. A tidy narrative is a draft, and the fee earner confirms it.

Commentary a partner will actually read

I'll paste a WIP table by matter and ageing bucket (dollar values, already calculated and checked).
For each matter with over $2,000 older than 60 days, write one sentence on what the figures show and one suggested action
(bill now, ask the fee earner for a status, review for write-down, call the client). Use only the numbers I give.
Do not recalculate totals. Plain English, no more than 35 words per matter.

Illustrative output for matter D: "Probate has $5,100 older than 60 days, including $3,000 over 90 days, with nothing billed this year. Suggested action: bill on account this week, and ask the fee earner whether the estate can pay." You check the $5,100 (2,100 plus 3,000) against your table; that takes five seconds and is the habit that stops errors reaching the partners.

Setting up the workbook so next month takes an hour

Build the report once as a template workbook with five tabs and reuse it. The tabs:

1. Export: paste the fresh timesheet CSV here and nothing else. Never edit this tab by hand.
2. Rates: fee earner, rate and effective date. If rates change in the year, keep both lines with dates so old time keeps its old value.
3. Calc: the value, age and bucket columns as formulas, copied down to the last row. Use a table object so formulas extend when you paste more rows.
4. Summary: the pivot table, the roll-forward and the measures table above. A single cell holds the report date, and every age calculation points at it.
5. Checks: three tests that must read TRUE: roll-forward equals pivot total, no rows with a blank matter code, no rows with a zero rate.

Illustrative effort: the first build takes about half a day. Each later month is roughly an hour to paste, clean, check, read and write commentary, against the several hours a hand-built report would need. The saving matters less than the habit: a report that takes an hour gets produced every month.

Reading one matter in detail

Totals tell you where to look, but decisions happen matter by matter. Take matter B, the employment dispute, with $11,100 unbilled across all four age bands. Pull its entries and look at who recorded what.

That is $10,950, not $11,100. The $150 difference (over a few entries) is exactly the kind of gap the Checks tab exists to find: perhaps a disbursement coded as time, or a rate that changed mid-month. Find it before the report circulates. Once it reconciles, the useful questions are about the work: is the associate's 21 hours proportionate to the partner's 12, was the paralegal time on research that the client will accept paying for, and has a bill on account been sent? Matter B also holds $1,200 older than 90 days, so ask whether that portion was ever agreed with the client. If not, it is a candidate for write-down before the next invoice.

Time worked but never recorded

WIP only counts what was recorded, so the report can look healthy while real work is missing. Compare recorded hours per fee earner against what you would expect for the month. If an associate recorded 92 billable hours in a month where the diary shows full days, the missing time is a quiet loss that no report shows. Prompt the team to record daily, and use calendar entries and sent emails as cues; AI time capture for solicitors covers tools that suggest entries from diaries and documents. Count the gap too: if recording slips by three days on average, the 0 to 30 band understates what is really in progress.

A month-end timetable that fits the billing run

The report only works if it lands before invoices go out. An example timetable for a firm that bills on the last working day:

• Working day minus 5: fee earners finish recording time for the period; the office manager exports the data.
• Minus 4: clean the export, fix codes in the source system, build the report.
• Minus 3: send each partner their matters with commentary and a due date for decisions.
• Minus 2: partners reply: bill, hold, write down, or ask the client. Write-offs get a code and an approver.
• Minus 1: draft invoices and narratives, tidied with AI and checked by the fee earner.
• Billing day: invoices go out; the report is re-run to confirm closing WIP and keep as the permanent record.

An email to chase the partners that gets answers

Write a short email to a partner asking for decisions on their unbilled matters.
I will paste a table with matter, total WIP, amount older than 60 days and the oldest entry date.
Write one line per matter with a suggested decision from this list: bill now, bill on account, hold with reason, write down with reason code.
Ask them to reply by [date] with a one-word decision per matter. Friendly, direct, under 120 words.

Replies of a single word per matter are quick for the partner and easy for the office manager to action. The AI drafts the email; you check the numbers in the table before sending.

A write-off reason code list that shows where value leaks

A single "write-off" column is useless. Add a short code with every write-off so patterns show up month after month. A workable list:

• EST: over estimate or fixed fee, time exceeded the agreed price.
• INEF: inefficiency, such as rework or learning time that should not be charged.
• DISP: client dispute over the work or the bill.
• OLD: too old to invoice sensibly or client lost.
• GOOD: goodwill, a decision to give time away.
• ERR: recorded to the wrong matter or duplicate.

In the October example, suppose the $2,000 write-off splits as $1,200 EST, $500 INEF and $300 ERR. With three-fifths of it ($1,200 of $2,000) sitting on over-runs against estimates, the problem looks like quoting, not billing. The ERR amount is a small training point. Three months of codes will tell you more than a year of one lump.

The $4,700 gap a blanked invoice column caused

An illustrative firm built its first report from a full export and the closing WIP came out $4,700 higher than the practice system's own figure. The cause: some time had been billed in a later month than the export date but the "invoice no." column had been blanked when entries were moved between matters, so billed lines appeared unbilled. It was caught by the roll-forward, which didn't tie. The fix was to filter on invoice status from the billing table rather than the timesheet's own column, and to keep the roll-forward as a standing check. A WIP report without a reconciliation is a rumour.

Fixed-fee and disbursement work

Some work has no hourly bill. For a fixed-fee job, WIP is still time recorded, valued at standard rates, but the invoice will be the fixed fee, so the relevant measure is recovery: fee divided by standard value of time to date. Say a company-formation job has a fixed fee of $1,500 and $1,950 of recorded time; recovery is 77%, and every extra hour will lower it. Show a recovery column for fixed-fee matters and flag any under 80% (your own threshold) for review. Disbursements (court fees, searches, travel) go in their own column and are billed at cost, so treat them separately from fees in the ageing; old disbursements are cash you've already paid out.

Who reads it, and when

Run the report on the same working day each month, such as five working days before the billing run as in the timetable above, so there is time to bill before month end. The partner responsible for each matter gets only their own rows, with the commentary and a deadline. The managing partner sees the one-page summary: total WIP, percent over 60 and over 90 days, realisation, write-offs by code and lock-up days. Hold a 20-minute meeting; nothing in the report matters without a decision on the oldest items.

The flags are examples. Set yours after two or three months of your own numbers, so they reflect how your firm actually runs. The time-tracking side of the same story is in law firm time tracking with AI, and the related utilisation idea is in the utilisation rate calculator from timesheets.

Accounting treatment: a question for your accountant

Everything above is management reporting, not accounts preparation. How unbilled work is valued and shown in your accounts (as unbilled revenue, a contract asset or something else, and whether older items need a provision) depends on your accounting framework and fee arrangements. Agree the definition with your accountant, and ask whether old WIP needs a provision. Then keep the management report on the same basis so the numbers agree.

If your business is a contractor, the report is different: contract values, costs to date and percentage complete. The tutorial on a construction WIP report and how AI builds one covers that, and nothing here should be copied across. If you'd like someone to look at your own timesheet and billing set-up, that is what my AI implementation consultation covers, and you can contact me here.

Questions about WIP in a professional services firm

Is WIP the same as unbilled time?

Mostly. For a service firm, WIP is the value of time and costs recorded on client work that has not yet been invoiced. Some firms also include disbursements and work part-way through a fixed-fee job. Agree one definition with your accountant and keep it the same every month.

How often should I produce a WIP report?

Monthly is the minimum, timed just before you run invoices so you can act on it. Firms with large unbilled balances or fast-moving matters look weekly at a short list of the biggest items. A report nobody sees until quarter end tells you about problems too late.

When should WIP be written off?

When you no longer expect to bill it: the client disputes it, the work was inefficient, the matter was fixed-fee and overran, or the time is too old to invoice sensibly. Record a reason code for each write-off and have a partner approve it, so patterns become visible.

Does this differ from a construction WIP schedule?

Yes. Construction schedules track contract value, costs to date and percentage complete. A service-firm report is built on time, rates and ageing, and has no over- or under-billing against a contract percentage in the same sense. See the construction tutorial if that is your business.

Further reads

• AI Time Tracking for Small Teams: Hours Without Timesheets — Capture hours without manual timesheets.
• Agency Utilisation Rate: Track Billable Hours and Spot Overload With AI — Track billable hours and spot overload.
• Hourly Rate for a Tradesman: Work Out Yours Without Guessing — Check the rates your WIP is valued at.
• Should You Charge Clients Less When AI Speeds Up Your Work? — What AI-faster work means for what you bill.
• Temp Staffing Payroll Software: Timesheets, Pay Runs and AI Features — Timesheets feeding pay runs in staffing firms.
• Timesheet Software for Agencies: Budgets, Billable Hours and AI — Timesheet software that feeds project budgets.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: standard professional-services accounting practice for work in progress and realisation; published law-firm lock-up guidance (WIP days and debtor days on annual fees billed); all figures in the examples are illustrative.