The WIP schedule and over and underbillings

The schedule of contracts in progress, almost always called the WIP schedule (work in progress), is the single most important report in a contracting firm. It lists every open job with its contract value, estimated total cost, cost to date, revenue earned, billings and the resulting over or underbilling. It is where the percentage-of-completion calculations of Lesson 4 are actually done, where the balance sheet's contract assets and liabilities come from, and where a surety or banker looks first to judge whether the contractor's profits are real. A contractor that produces an accurate WIP schedule every month, and reads it critically, will see most problems before they become crises.

Building the WIP schedule

For each open job, the WIP schedule starts with three numbers from the project and accounting records: the current contract value (including approved changes), the estimated total cost at completion (from the project manager's forecast) and the cost incurred to date (from the job cost ledger). It adds the amount billed to date from the billing records. Everything else is calculated.

Billings include the amounts held back, because holdback is part of what the contractor has billed and is entitled to; it is simply collected later. The holdback receivable is a separate balance sheet item, but it does not change the over or underbilling calculation.

jobs = [  # job, contract, est. total cost, cost to date, billed to date
    ("J-2407 Ashdale library",   14_600_000, 13_250_000, 9_940_000, 12_152_755),
    ("J-2411 Northgate clinic",   3_850_000,  3_520_000, 2_710_000,  2_849_062),
    ("J-2415 Riverside co-op",   21_400_000, 19_800_000, 7_120_000,  7_215_354),
    ("J-2421 Brookmere retail",   6_900_000,  6_780_000, 5_650_000,  5_400_000),
    ("J-2502 Fire hall",          9_200_000,  8_330_000, 1_240_000,  1_959_508),
    ("J-2506 School addition",    7_750_000,  7_900_000, 2_860_000,  2_600_696),
    ("J-2509 Parkade repair",     4_300_000,  3_850_000,   620_000,  1_152_467),
]
under = over = 0
print(f"{'Job':24}{'%':>7}{'Revenue':>12}{'GP to date':>12}{'Billed':>12}{'Under':>10}{'Over':>11}")
for name, c, etc, ctd, billed in jobs:
    pct = ctd / etc
    rev = pct * c
    gp = rev - ctd
    diff = rev - billed
    u, o = max(diff, 0), max(-diff, 0)
    under += u; over += o
    print(f"{name:24}{pct:7.1%}{rev:12,.0f}{gp:12,.0f}{billed:12,.0f}{u:10,.0f}{o:11,.0f}")
print(f"{'Totals':24}{'':7}{'':12}{'':12}{'':12}{under:10,.0f}{over:11,.0f}")

Job                           %     Revenue  GP to date      Billed     Under       Over
J-2407 Ashdale library    75.0%  10,952,755   1,012,755  12,152,755         0  1,200,000
J-2411 Northgate clinic   77.0%   2,964,062     254,062   2,849,062   115,000          0
J-2415 Riverside co-op    36.0%   7,695,354     575,354   7,215,354   480,000          0
J-2421 Brookmere retail   83.3%   5,750,000     100,000   5,400,000   350,000          0
J-2502 Fire hall          14.9%   1,369,508     129,508   1,959,508         0    590,000
J-2506 School addition    36.2%   2,805,696     -54,304   2,600,696   205,000          0
J-2509 Parkade repair     16.1%     692,468      72,468   1,152,467         0    459,999
Totals                                                              1,150,000  2,250,000

The totals of the underbilling and overbilling columns are the contract assets of $1,150,000 and contract liabilities of $2,250,000 on Kestrel's balance sheet in Lesson 3. Each job is assessed separately, and its net position appears in one column or the other; the standards do not allow an underbilling on one job to be netted against an overbilling on another.

Loss jobs on the WIP schedule

One of Kestrel's jobs, J-2506, the school addition, now has an estimated total cost of $7,900,000 against a contract value of $7,750,000. Percentage of completion shows a loss to date of about $54,000, but Lesson 4 requires the whole expected loss of $150,000 to be recognized now. The extra provision of about $96,000 is usually shown on a separate line or added to contract liabilities, and the job is flagged on the schedule. From this month forward the school addition will show no gross profit; any further cost increase becomes an additional loss when identified.

What underbillings and overbillings mean

Underbillings and overbillings are not good or bad in themselves. They are signals that need to be explained, job by job.

Sureties are particularly wary of underbillings. An underbilling is revenue the contractor has recognized but has not been able to bill. If the reason is that the estimated total cost is understated, the job is further along in cost than in value, and the revenue may never be billed or collected. A pattern of large underbillings on troubled jobs is one of the classic signs of a contractor that is about to report a large loss. Many sureties discount underbillings heavily, or deduct them entirely, when they assess working capital (Lesson 11).

Overbillings deserve scrutiny too. A modest overbilling position is normal and healthy for a general contractor, because owners pay for mobilization and early items and the contractor pays subcontractors after it is paid. But overbillings are cash collected for work not yet done. If the contractor has used that cash to pay overhead, buy equipment or cover losses on other jobs, it will have to find money from somewhere when the remaining work comes due. Comparing total overbillings with cash on hand is a quick test: Kestrel's overbillings of $2.25 million are covered by its $2.85 million of cash, but only just.

Fade and gain: reading the WIP schedule over time

A single WIP schedule is a snapshot. Its real power comes from comparing schedules over time. Profit fade is the decrease in a job's estimated gross profit from one period to the next; profit gain is the increase. A fade/gain analysis lines up each job's estimated gross profit at bid, at the previous WIP date and now, and explains every significant change.

Three jobs account for nearly all the fade this quarter, and two of them, Brookmere and the school addition, have faded every quarter. A pattern like that tells management where to look and tells a surety that the firm's estimates of profit on uncompleted work may still be optimistic. Most contractors see some fade; consistent fade across many jobs suggests a systematic problem with estimating, buyout or project control.

bid  = [1_240_000, 360_000, 1_780_000, 520_000, 870_000, 410_000, 450_000]
prev = [1_310_000, 345_000, 1_700_000, 310_000, 870_000, 120_000, 450_000]
now  = [1_350_000, 330_000, 1_600_000, 120_000, 870_000, -150_000, 450_000]
q_change = sum(n - p for n, p in zip(now, prev))
since_bid = sum(n - b for n, b in zip(now, bid))
print(f"Fade this quarter: {q_change:,}")
print(f"Fade since bid:    {since_bid:,}  ({since_bid / sum(bid):.1%} of bid gross profit)")
fading = [i for i, (n, p) in enumerate(zip(now, prev)) if n < p]
print(f"Jobs fading this quarter: {len(fading)} of {len(now)}")

Fade this quarter: -535,000
Fade since bid:    -1,060,000  (-18.8% of bid gross profit)
Jobs fading this quarter: 4 of 7

The schedule of completed contracts

When a job finishes, it moves from the WIP schedule to the schedule of completed contracts, which shows final revenue, final cost and final gross profit, alongside the gross profit expected at bid and at the start of the year. Comparing the final result with the earlier estimates measures the reliability of the firm's estimates. If jobs consistently finish below what the WIP schedule showed while they were open, readers will discount the current WIP schedule. A firm that consistently finishes at or above its estimates earns credibility, and that credibility translates into bonding capacity and better credit terms.

Reviewing a WIP schedule

Experienced controllers, sureties and bankers review a WIP schedule with a standard set of questions. Project managers should be ready to answer them.

Billing strategy and the WIP schedule

Because the WIP schedule exposes the billing position, it encourages good billing discipline. A contractor should bill promptly and fully for work done, including approved changes, and should avoid both chronic underbilling, which starves cash, and aggressive front-loading, which owners resist and which leaves the contractor short of contract value to finish the work. Many contractors set a policy target, such as keeping each job modestly overbilled while staying within what the payment certifier will accept as fair value for work done. CON-310 Lesson 11 covered the mechanics of the schedule of values and progress claims; the WIP schedule is where the company sees the result.