Damin Mutti for ZeraBooks

Posted on Oct 18 Originally published at zerabooks.com

Building a construction WIP schedule in Python: percent complete, over and under billing, and the month-end entry

#accounting #python #construction #datamodeling

A construction WIP schedule computes earned revenue for each open job as contract price times percent complete, where percent complete is cost to date divided by estimated total cost. Billed minus earned is overbilling when positive and underbilling when negative. One journal entry a month moves the totals into the ledger, and debits must equal credits.

I work on ZeraBooks, and contractors send us exactly this report each month. Here is the computation with the figures from our contractor guide. All jobs are fictional.

What inputs does a WIP schedule need?

from dataclasses import dataclass
from decimal import Decimal as D, ROUND_HALF_UP

@dataclass
class Job:
    name: str
    contract: D        # including approved change orders
    est_cost: D        # the current estimate, not the original bid
    cost_to_date: D
    billed: D

def cents(x: D) -> D:
    return x.quantize(D("0.01"), rounding=ROUND_HALF_UP)

Estimated cost is the current estimate, not the original bid. That one field decides whether the whole schedule is honest.

How do you calculate over and under billing in code?

def wip_row(j: Job) -> dict:
    pct = j.cost_to_date / j.est_cost
    earned = cents(j.contract * pct)
    diff = cents(j.billed - earned)
    return {
        "job": j.name,
        "pct": f"{pct:.2%}",
        "earned": earned,
        "over": diff if diff > 0 else D("0.00"),
        "under": -diff if diff < 0 else D("0.00"),
    }

jobs = [
    Job("Kitchen remodel", D("200000"), D("150000"), D("60000"), D("95000")),
    Job("Office build-out", D("120000"), D("96000"), D("72000"), D("78000")),
]
rows = [wip_row(j) for j in jobs]

Job	Contract	Est. cost	Cost to date	Billed	Percent	Earned	Over	Under
Kitchen remodel	200,000	150,000	60,000	95,000	40.00%	80,000.00	15,000.00	0.00
Office build-out	120,000	96,000	72,000	78,000	75.00%	90,000.00	0.00	12,000.00

What is the month-end WIP journal entry?

Revenue in the ledger has been recorded from progress bills. The WIP entry adjusts it to earned.

def wip_entry(rows: list[dict]) -> list[tuple[str, D, D]]:
    over = sum((r["over"] for r in rows), D("0"))
    under = sum((r["under"] for r in rows), D("0"))
    return [
        ("40100 Contract revenue", over, D("0")),
        ("20300 Billings in excess of costs and estimated earnings", D("0"), over),
        ("11300 Costs and estimated earnings in excess of billings", under, D("0")),
        ("40100 Contract revenue", D("0"), under),
    ]

entry = wip_entry(rows)
assert sum(d for _, d, _ in entry) == sum(c for _, _, c in entry)

Debits equal credits by construction: $27,000.00 each side here. Net, revenue falls by $3,000.00 for the month.

A journal entry in ZeraBooks: it won't save until the difference between debits and credits is 0.00.

Most bookkeepers reverse this entry on the first day of the next month and post a fresh one at the next month end. That keeps the balances equal to the current schedule instead of a running sum of old adjustments.

What happens when the estimate changes?

Say the kitchen's estimate rises to $170,000 after a tile change and an extra week of labor. Nothing else changes.

jobs[0].est_cost = D("170000")
wip_row(jobs[0])
# pct 35.29%, earned 70,588.24, over 24,411.76

Overbilling jumps by $9,411.76, and revenue to date drops by the same amount, in the month the estimate changed. The job didn't get worse this month; the books caught up with it. If estimates are only updated at year end, this whole correction lands in December.

Which guardrails should a WIP script have?

def sanity(j: Job) -> list[str]:
    issues = []
    if j.cost_to_date > j.est_cost:
        issues.append("cost to date exceeds estimate: update the estimate")
    if j.est_cost >= j.contract:
        issues.append("estimated cost at or above contract: expected loss, talk to your accountant")
    return issues

Percent complete above 100% is always an estimate that wasn't updated. An estimated cost above the contract is a losing job, and how that loss is recorded is a call for the contractor's accountant.

Where does the WIP data come from?

Cost to date per job comes from wherever job costing lives: the estimating tool, project software or a spreadsheet. The general ledger carries totals by cost type: labor, materials, subs, equipment, permits. The rest of a contractor's month, including retainage and the chart of accounts, is in the guide to bookkeeping for contractors.

Key takeaways

Percent complete = cost to date / current estimated cost.
Earned = contract x percent. Billed minus earned is over (liability) or under (asset).
One balanced entry per month, reversed on day one of the next month.
Update estimates monthly; a stale estimate makes every report wrong until December.

FAQ

Is overbilling revenue?

No. It is billed but not yet earned, so it sits as a liability (billings in excess of costs and estimated earnings) until the work catches up.

Why reverse the WIP entry every month?

So the balance sheet always shows the current schedule's over and under billing, rather than a sum of every past adjustment.

Does ZeraBooks calculate job costing?

No. ZeraBooks has no job or class tags, so job costing stays in your project tool. Its WIP report feeds the month-end entry.

Disclosure: I'm on the ZeraBooks team. ZeraBooks bookkeeping services take the contractor's WIP report and post this entry monthly, from $150 a month in U.S. dollars, working from uploaded statements and invoices, with no bank feeds.

DEV Community