📌 Stage 4 · Overhead & WIP · Job Costing · WIP · 40–50 min

Cost-to-cost percent complete, earned revenue, and the billing position

Learning goals

  • Measure percent complete as costs incurred over estimated cost
  • Convert percent complete into earned revenue on a fixed-price contract
  • Read the under-billed and over-billed position and act on the flag

Concepts

How complete is a job, in numbers

Gut feel says the kitchen is nearly done. Job costing needs a number, and the cost-to-cost method is the standard first cut: percent complete equals costs incurred to date divided by total estimated cost. If you have spent 86% of the money, you are 86% of the way there - not perfect (money and progress are different things), but auditable, formula-expressible, and hard to argue with at month end.

The method leans on one assumption worth stating: the estimate was honest. If the estimate is wrong, percent complete is wrong in the same direction - which is why estimate discipline in Stage 2 was not paperwork.

=SUMIFS(Timesheets!$J$5:$J$1004,Timesheets!$F$5:$F$1004,$B5)+SUMIFS('Cost Log'!$I$5:$I$1004,'Cost Log'!$D$5:$D$1004,$B5)

Costs to date for the job in B5, straight from the two journals: labor cost (Timesheets column J where the job matches) plus materials and subs (Cost Log column I where the job matches). Two SUMIFS added together - the kitchen returns $2,880.00 + $63,610.00 = $66,490.00. OH timesheet rows never match a job number, so office time stays out automatically.

=IFERROR($F5/$E5,0)

Percent complete: costs to date over estimated cost - $66,490.00 / $77,000.00 = 86.4%. IFERROR guards the divide-by-zero on a job with no estimate, and the column formats as a percentage.

Earned revenue and the billing position

On a fixed-price contract, revenue should track work performed, not invoices mailed. Earned revenue equals contract price times percent complete - the value of the work you have actually delivered. Then the billing position writes itself: earned minus billed. Positive means you have built more than you billed (an asset - bill it); negative means you have billed more than you built (a liability - you owe work or money back).

=ROUND($D5*$G5,2)

D5 is the $96,250.00 contract price and G5 the 86.4% complete: earned revenue of $83,112.50. ROUND keeps the figure to cents so the totals row stays tidy on jobs whose percentage runs to many decimals.

=IF($D5<=0,"",IF(ABS($J5)>Settings!$C$8,"Review billing","OK"))

The flag column: ignore rows without a price, then compare the absolute billing position against the tolerance in Settings (C8, $2,500.00). ABS matters because both directions are problems - over-billing a customer invites a dispute, under-billing starves cash.

What the case shows

Read the three jobs on Jun 30, 2026. Kitchen: 86.4% complete, earned $83,112.50, billed $82,000.00, under-billed $1,112.50 - fine. Bath: 74.4%, earned $36,468.75, billed $34,000.00, under-billed $2,468.75 - just inside tolerance, worth a draw soon. Deck: 80.7% complete, earned $16,136.65, but billed $20,000.00 - over-billed by $3,863.35, past the tolerance, flagged Review billing.

The deck's flag is the system working: Ridgeline billed the deck to completion in April, then stain and final labor stretched into May. Either the next invoice waits until earned catches up, or the schedule of values gets revisited. Company-wide, earned $135,717.90 versus billed $136,000.00 nets to over-billed $282.10 - the two directions almost cancel, which is why the job-level flag, not the total, is what you review.

💡 Tips:

  • This is the percentage-of-completion method in miniature. Real GAAP versions get fancier, but every one of them still divides something incurred by something estimated.

Practice

Fixed case (Mar 1 – Jun 30, 2026). Pale-green cells are formulas. Cell F5 on WIP has been cleared - you will retype it.

  1. Retype the costs-to-date roll-up in F5 — Cell F5 on WIP is blank. Type the two-SUMIFS formula =SUMIFS(Timesheets!$J$5:$J$1004,Timesheets!$F$5:$F$1004,$B5)+SUMIFS('Cost Log'!$I$5:$I$1004,'Cost Log'!$D$5:$D$1004,$B5) and press Enter. The kitchen returns $66,490.00.
  2. Watch percent complete and earned revenue — G5 recomputes to 86.4% and H5 to $83,112.50 - earned revenue moved because you restored the costs, with nothing retyped on the reports.
  3. Read the billing position — J5 shows $1,112.50 under-billed and K5 reads OK. Then read row 6, the deck: J6 shows -$3,863.35 (over-billed) and K6 reads Review billing.
  4. Check the company row — Row 8 totals: $108,655.00 of costs, $135,717.90 earned, $136,000.00 billed, net -$282.10. Three jobs, three different billing stories, one formula.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed the roll-up into F5 and got $66,490.00 of kitchen costs to date
  • ☐ Confirmed G5 shows 86.4% complete and H5 earns $83,112.50
  • ☐ Saw the deck flagged Review billing at -$3,863.35 over-billed while the company nets -$282.10