Construction Accounting & Surety Job Costing
Construction WIP Overbilling & Retainage Formula
Calculate Cost-to-Cost Earned WIP Revenue, Overbilling Liability vs Underbilling Asset (Billings - Earned Revenue), Collectible Cash Net of Retainage Holdback, and Net Job Cashflow.
Reconcile GAAP overbilling against owner retainage holdbacks on contractor WIP schedules!
General contractors and surety bond underwriters audit two distinct numbers on monthly Work-in-Progress (WIP) schedules that project managers routinely conflate: (1) Under ASC 606 cost-to-cost percentage of completion, Earned_Revenue = (Actual_Cost_To_Date / Estimated_Total_Cost) * Contract_Value, so Overbilling / (Underbilling) = Progress_Billings - Earned_Revenue (positive = Billings in Excess of Costs & Earnings liability; negative = Costs in Excess underbilling asset, a classic warning sign of unapproved change orders or unrecorded job cost overruns!); and (2) even when a job is Overbilled on paper, project owners withhold 5%–10% Contractual Retainage until substantial completion—so Collectible_Cash = Progress_Billings * (1 - Retainage_Pct), and Net_Job_Cashflow = Collectible_Cash - Actual_Cost_To_Date!
Verify in a shared construction-controller WIP workbook—Contract_Price = $5,000,000 (B2), Estimated_Total_Cost = $4,000,000 (B3), Actual_Cost_To_Date = $2,400,000 (60.0% complete, B4), Progress_Billings_To_Date = $3,250,000 (B5), and Owner_Retainage_Pct = 0.10 (10.0%, B6): Earned WIP Revenue is 0.60 * $5,000,000 = $3,000,000.00 (E2); Overbilling Liability is $3,250,000 - $3,000,000 = +$250,000.00 (E3); Collectible Cash Net of 10% Retainage is $3,250,000 * 0.90 = $2,925,000.00 (E4, with $325,000 locked in retainage receivable); and Net Job Cashflow is $2,925,000 - $2,400,000 = +$525,000.00 (E5)!
| Contractor WIP Metric ($5M Contract, $4M Est Cost, $2.4M Actual, $3.25M Billed, 10% Ret) | Formula | Verified Output |
|---|---|---|
| Earned WIP Revenue (60% Complete) | (Cost_To_Date / Est_Cost) * Contract | $3,000,000.00 |
| Overbilling (+) / Underbilling (-) | Progress_Billings - Earned_Revenue | +$250,000.00 (-$400k @ $2.6M Billed) |
| Collectible Cash Ex Retainage ($) | Progress_Billings * (1 - Retainage_Pct) | $2,925,000.00 ($325k Held Back) |
| Net Self-Financed Job Cashflow ($) | Collectible_Cash - Actual_Cost_To_Date | +$525,000.00 (-$60k @ $2.6M Billed) |
Contractor WIP Net Job Cashflow & EAC Overrun Guard Formula
Guard Actual_Cost_To_Date > Estimated_Total_Cost ("Revise EAC: Cost > Estimate"—if actual job costs exceed total estimated cost at completion without updating EAC, percentage-of-completion exceeds 100%)!
=LET(ct_p, B2, es_c, B3, ac_c, B4, bl_p, B5, rt_p, B6, IF(OR(ct_p <= 0, es_c <= 0, ac_c < 0, bl_p < 0, rt_p < 0, rt_p >= 0.5), "Invalid WIP Input", IF(ac_c > es_c, "Revise EAC: Cost > Estimate", LET(cs_n, bl_p * (1 - rt_p), ROUND(cs_n - ac_c, 2)))))Underbilling Surety Red-Flag Audit Formula
When a project manager invited as a suggester proposes $2,600,000 in progress billings (B5 = 2,600,000), peer reviewers spot both -$400,000.00 Underbilling (E3) and -$60,000.00 Negative Job Cashflow (E5) before approving!
=IF(ISTEXT(E2), E2, ROUND(B5 - E2, 2))Try the example
Use the original values or change them to test your understanding.