Build a Work in Progress Report for Unbilled Time

In a consultancy, an agency or an accounting practice, time worked is money owed — but only once it is billed. Between the hours going onto a timesheet and the invoice going out, that value sits as work in progress, or WIP: an asset on the balance sheet, and a risk, because unbilled time that is left too long tends to get written off rather than paid. Every month end, finance needs to know how much WIP there is, on which projects, how old it is and how much of it is likely to be billable. The data is in the timesheet system and the billing system, and the calculation has a few traps: rates differ by person, fixed-fee projects cannot be billed beyond their fee however many hours are logged, and invoices have to be matched against the time they cover. This page builds the WIP report with pandas from timesheets, rates, project terms and invoices: value of time worked, less billed, capped for fixed fees, aged by month worked, with old WIP and over-budget projects flagged. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Four inputs: timesheet lines from June to September 2026 with date, project, person and hours; the charge-out rate for each person; the projects with their billing basis — time and materials, billed for hours worked, or fixed fee, billed at an agreed price — and the fee where there is one; and the invoices raised so far per project. The month being closed is September 2026.

Step 1 — The value of time worked

import pandas as pd

ts = pd.read_excel("timesheets.xlsx", parse_dates=["Date"])
rates = pd.read_excel("rates.xlsx").set_index("Person")["Rate"]
projects = pd.read_excel("projects.xlsx").set_index("Project")
invoices = pd.read_excel("invoices.xlsx", parse_dates=["Date"])

ts["Value"] = ts["Hours"] * ts["Person"].map(rates)
assert ts["Value"].notna().all(), "a timesheet line has no rate"
worked = ts.groupby("Project").agg(Hours=("Hours", "sum"), Value=("Value", "sum"))
billed = invoices.groupby("Project")["Amount"].sum().rename("Billed")
wip = projects.join(worked).join(billed).fillna({"Billed": 0})
print(wip[["Name", "Basis", "Fee", "Hours", "Value", "Billed"]].round(0).to_string())

Each timesheet line is valued at the person's charge-out rate — the rate the firm bills for that grade, not their salary cost — and the values are summed per project. The assertion matters more than it looks: a person missing from the rate table would turn their hours into blank values that silently drop out of the totals, understating WIP. Invoices raised so far are summed per project. The difference between value worked and amount billed is the raw WIP, before applying the terms of each engagement. At this stage the four projects show about £166,000 of time worked against £74,000 billed, but that gap overstates what can actually be invoiced, as the next step shows.

Step 2 — Apply the billing terms

def billable_wip(row):
    raw = row["Value"] - row["Billed"]
    if row["Basis"] == "fixed fee":
        return max(min(raw, row["Fee"] - row["Billed"]), 0)
    return max(raw, 0)

wip["Raw WIP"] = (wip["Value"] - wip["Billed"]).round(2)
wip["Billable WIP"] = wip.apply(billable_wip, axis=1).round(2)
wip["Over fee"] = (wip["Value"] - wip["Fee"]).where(wip["Basis"] == "fixed fee").clip(lower=0).round(2)
wip["Write-off risk"] = (wip["Raw WIP"] - wip["Billable WIP"]).clip(lower=0).round(2)
print(wip[["Basis", "Value", "Billed", "Fee", "Raw WIP", "Billable WIP", "Over fee"]].round(0).to_string())

For time-and-materials work, every hour can in principle be billed, so the billable WIP is simply value worked less billed. Fixed-fee work is different: the client will pay the agreed fee and no more, so the billable WIP is capped at the fee not yet invoiced. When a fixed-fee project's time value runs past its fee, the excess is time the firm will not recover — a write-off risk that belongs in the report, because it is the best early signal that a project is being run over budget. Here the systems review has logged £58,905 of time against a £55,000 fee, so £3,905 shows as both over fee and write-off risk, while its billable WIP is limited to the £27,500 of the fee not yet invoiced. The due diligence project, at £44,678 of time against a £60,000 fee, is still within budget, so all of its time counts and the full fee remains to be billed. Negative raw WIP — billed in advance of the work — is shown as zero billable WIP; on a balance sheet it is deferred income rather than WIP.

Step 3 — How old is the unbilled time?

asof = pd.Timestamp("2026-09-30")
ts = ts.sort_values("Date")
ts["Cum value"] = ts.groupby("Project")["Value"].cumsum()
ts = ts.join(billed, on="Project").fillna({"Billed": 0})
ts["Unbilled part"] = (ts["Cum value"] - ts["Billed"]).clip(lower=0).clip(upper=ts["Value"])   # oldest time is billed first
ts["Age (days)"] = (asof - ts["Date"]).dt.days
ts["Bucket"] = pd.cut(ts["Age (days)"], [-1, 30, 60, 90, 10_000], labels=["0–30", "31–60", "61–90", "90+"])
aged = ts.pivot_table(index="Project", columns="Bucket", values="Unbilled part", aggfunc="sum", fill_value=0, observed=False).round(0)
print(aged.to_string())

WIP gets harder to bill the older it is: clients query invoices for work done months ago, and memories of what was agreed fade. Ageing the unbilled time shows where that risk sits. Invoices are assumed to cover the oldest time first, so a running total of value per project, less the amount billed, tells which timesheet lines are still unbilled; each unbilled line is then placed in an age bucket by the date it was worked. The resulting table shows, for each project, how much unbilled time is less than a month old and how much has been sitting for more than sixty or ninety days. For time-and-materials projects, anything over sixty days is usually a prompt to invoice now; for fixed-fee projects the age matters less than the milestone the fee is tied to. The Crane tax advice is the clear case: all £4,692 of its unbilled time is from July, the project has finished, and nobody has sent the final invoice — exactly the kind of balance that, left another two months, ends up written off because the client no longer remembers the work.

Step 4 — The month-end WIP report

report = wip.join(aged).reset_index()
report["Action"] = [
    "over fee — review with partner" if r["Over fee"] > 0 else
    ("bill now" if (r.get("61–90", 0) + r.get("90+", 0)) > 0 else "")
    for _, r in report.iterrows()]
cols = ["Project", "Name", "Basis", "Fee", "Hours", "Value", "Billed", "Billable WIP", "Write-off risk",
        "0–30", "31–60", "61–90", "90+", "Action"]
with pd.ExcelWriter("wip_report_2026-09.xlsx") as xw:
    report[cols].to_excel(xw, sheet_name="WIP by project", index=False)
    ts[ts["Unbilled part"] > 0].drop(columns=["Cum value"]).to_excel(xw, sheet_name="Unbilled time", index=False)
print(report[["Project", "Billable WIP", "Write-off risk", "Action"]].round(0).to_string(index=False))
print("total billable WIP:", round(report["Billable WIP"].sum()), "| write-off risk:", round(report["Write-off risk"].sum()))

The report gives one row per project with the value worked, billed and unbilled, the billable WIP after fee caps, the write-off risk, the ageing, and an action: bill now for projects with unbilled time over sixty days, review with partner for fixed-fee projects that have overrun. The second sheet lists every unbilled timesheet line, which is what a project manager needs to draft the invoice. In September the firm carries £88,092 of billable WIP across four projects with £3,905 of write-off risk, all on the systems review. The total billable WIP is the figure that goes on the balance sheet, and the write-off risk is the provision finance may need against it; the partner review decides how much of that risk is real. Recharging time internally follows the same valuation step, as in Build a Staff Utilisation Report from Timesheets.

Common pitfalls

• Missing rates. A person without a rate drops out of WIP silently; assert every line has one.
• Fixed-fee time valued in full. Cap billable WIP at the fee not yet invoiced.
• Ignoring overruns. Time beyond a fixed fee is write-off risk; report it.
• Ageing the whole project by its start date. Age the unbilled time line by line, oldest billed first.
• Negative WIP shown as WIP. Advance billing is deferred income, not negative WIP.

Conclusion

A WIP report values every timesheet hour at the person's charge-out rate, subtracts what has been invoiced per project, and applies the billing terms — fixed-fee projects capped at the fee not yet billed, with any time beyond the fee reported as write-off risk. Ageing the unbilled time line by line, oldest billed first, shows where WIP is getting stale, and a per-project action column turns the report into the month-end billing list.

Frequently asked questions

How do I calculate WIP from timesheets in pandas? Multiply hours by each person's charge-out rate, sum by project and subtract the invoices raised for each project.

How is WIP treated on fixed-fee projects? Billable WIP is capped at the part of the fee not yet invoiced; time valued above the fee is a write-off risk.

How do I age unbilled time? Assume invoices cover the oldest time first, find the timesheet lines not yet covered, and bucket them by the date worked.

What is the difference between WIP and deferred income? WIP is work done but not yet billed; deferred income is billing received or raised before the work is done.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Project Budget Burn Report — budgets against time spent.
• Consolidate Timesheets from Many Workbooks — collecting the hours.
• Build a Deferred Revenue Schedule — billing ahead of the work.