Construction WIP Schedule Excel Template (Over/Under Billing & ASC 606 Percentage of Completion)
01_WIP_Over_Under_Schedule — Interactive Spreadsheet
| # | A | B | C | D | E | F | G | H | I | J | K | L | M | N |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | CONSTRUCTION WORK-IN-PROGRESS (WIP) SCHEDULE — ASC 606 OVER / UNDER BILLING | |||||||||||||
| 2 | Surety Bond & CPA Audit Standard (ASC 606 Cost-to-Cost Method) | Teal-Tinted Cells = Editable Inputs | Automatic Over/Under Billing & Margin Analysis | |||||||||||||
| 3 | ||||||||||||||
| 4 | Contractor Legal Entity: | Vanguard Civil & Mechanical Contractors Inc. | Revenue Recognition Method: | ASC 606 Cost-to-Cost Input | Reporting Period Ending: | 2026-03-31 | ||||||||
| 5 | Surety Carrier / Bond Agent: | Travelers Casualty & Surety Co. of America | Single / Aggregate Bond Limit: | $25M / $75M Bonding Line | Prepared By (Construction CFO): | D. Sterling, CPA, CCIFP | ||||||||
| 6 | WIP Workpaper Reference: | WP C-200 (Contract Assets & Liabilities) | Minimum Target Gross Margin: | External CPA Audit Firm: | Crowe Construction LLP | |||||||||
| 7 | ||||||||||||||
| 8 | TOTAL REVISED CONTRACT BACKLOG | EARNED REVENUE TO DATE (ASC 606) | PROGRESS BILLINGS TO DATE | UNDERBILLINGS (CIEB ASSET) | OVERBILLINGS (BIEC LIABILITY) | |||||||||
| 9 | $0 | $0 | $0 | $0 | $0 | |||||||||
| 10 | ||||||||||||||
| 11 | Job # | Active Construction Project Name | Original Contract ($) | Approved COs ($) | Revised Contract ($) | Cost to Date ($) | Est Cost to Complete | Total Est Cost ($) | % Complete | Earned Revenue ($) | Billed to Date ($) | Underbilled Asset ($) | Overbilled Liab ($) | Est Margin % |
| 12 | 26-101 | Mercy Regional Hospital MEP Central Plant | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 13 | 26-102 | I-95 Highway Overpass Structural Concrete | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 14 | 26-103 | Apex Semiconductor Cleanroom Ductwork | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 15 | 26-104 | Metro Transit Maintenance Facility Electrical | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 16 | 26-105 | Harborfront Cold Storage Warehouse Slab | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 17 | 26-106 | Oakridge High School Science Wing Addition | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 18 | 26-107 | Municipal Water Treatment Pump Station #4 | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 19 | 26-108 | Northgate Logistics Park Site Utilities | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 20 | 26-109 | Downtown Federal Courthouse Chiller Retrofit | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 21 | 26-110 | Riverside Multi-Family Podium Deck Framing | $0 | $0 | 0.0% | $0 | $0 | $0 | 0.0% | |||||
| 22 | TOTAL ACTIVE WIP PORTFOLIO | $0 | $0 | $0 | $0 | $0 | $0 | 0.0% | $0 | $0 | $0 | $0 | 0.0% | |
Free Instant Download
No email signup • Unlocked formulas
Built-In Excel Formulas & Calculation Logic
Unlocked native Microsoft Excel functions pre-programmed inside this workbook:
=IFERROR(MIN(1, F12/H12), 0) Divides actual job Costs Incurred to Date (Column F) by Total Estimated Cost at Completion (Column H = Cost to Date + Estimated Cost to Complete), capped at 100.0%.
=ROUND(E12*I12, 0) Multiplies the Revised Contract Price (Original Contract + Approved Change Orders in Column E) by the calculated Percentage of Completion (Column I) to determine GAAP earned revenue.
=MAX(0, J12-K12) Triggers when Earned Revenue to Date (Column J) exceeds Progress Billings to Date (Column K), recording a Current Contract Asset on the contractor's balance sheet.
=MAX(0, K12-J12) Triggers when Progress Billings to Date (Column K) exceed Earned Revenue to Date (Column J), recording a Current Contract Liability (deferred construction revenue) on the balance sheet.
Full Workbook Print Layout Preview (.XLSX)
Overview & Industry Standard Alignment
For commercial general contractors, specialty trade subcontractors, and heavy civil builders, billing a client via AIA G702/G703 progress applications rarely matches true physical project completion. Recognizing revenue strictly on cash or progress invoices distorts monthly gross margins and violates US GAAP ASC 606 and surety bonding covenants. This Construction WIP Schedule Excel Template (.xlsx) provides the exact 14-column Work-in-Progress report required by construction CPAs (CCIFPs), surety bond underwriters, and commercial bank lenders. Tab 01 tracks Original Contract Value, Approved Change Orders, Revised Contract Total, Cost to Date, Estimated Cost to Complete (ETC), Total Estimated Cost, % Complete, Earned Revenue, Progress Billings, Underbillings (CIEB Asset), Overbillings (BIEC Liability), and Estimated Gross Margin %. Tab 02 automatically translates the portfolio totals into a balanced Month-End Over/Under Billing Adjusting Journal Entry, while Tab 03 provides an ASC 606 and Surety Underwriting interpretation guide.
Practical Operational Use Cases:
Quarterly Surety Bond Underwriting Submissions
Deliver a clean, CPA-formatted WIP schedule to your surety bond agent to support single and aggregate bonding capacity increases.
Monthly Construction Financial Close (ASC 606 Compliance)
Adjust unadjusted AIA progress billings to true GAAP Earned Revenue using the automated CIEB / BIEC journal entry on Tab 02.
Early Detection of Job Profit Fade & Underbilling Risk
Spot jobs where Estimated Cost to Complete is rising and chronically underbilled jobs where unapproved change orders are straining cash flow.
Bank Line-of-Credit & External CPA Audit Reviews
Provide commercial lenders and construction auditors with a reconciled schedule tying directly to Balance Sheet Contract Assets (GL 1380) and Liabilities (GL 2380).
Designed For:
What’s Inside the Workbook (Tab-by-Tab Breakdown)
01_WIP_Over_Under_Schedule (14-Column Job-by-Job WIP Schedule)
Contains contractor & surety bonding header metadata, 5 portfolio KPI summary cards (Revised Contract Backlog, Earned Revenue, Progress Billings, Underbillings Asset, and Overbillings Liability), and a 10-job dynamic WIP table.
02_ASC606_Revenue_Journal (Automated Over/Under Billing Journal Entry)
Linked journal entry worksheet that pulls portfolio Underbillings (Col L) and Overbillings (Col M) to generate the exact month-end Debit/Credit entry to GL 1380 (CIEB Asset), GL 2380 (BIEC Liability), and GL 4190 (Revenue Adjustment).
03_Surety_and_ASC606_Guide (Formulas & Underwriter Red-Flag Guide)
Explains each ASC 606 WIP formula and details how surety bond underwriters evaluate overbillings, underbillings, and job profit fade.
Practitioner Tips & Best Practices
-
✓
Never Treat Cost-to-Complete (ETC) as a Plug Formula: The most common WIP mistake is setting Estimated Cost to Complete as (Original Budget - Cost to Date). Always require Project Managers to perform a fresh bottom-up forecast of remaining labor, material, and subcontract costs every month.
-
✓
Watch Out for Chronic Underbillings on Jobs Over 75% Complete: Surety underwriters view persistent underbillings late in a project as a major red flag—it often indicates unrecognised cost overruns (profit fade) or disputed change orders that the owner refuses to pay.
-
✓
Ensure Overbillings Are Backed by Actual Cash on Hand: While moderate overbilling (front-loading mobilization and early submittals) improves job cash flow, if your total Overbillings Liability exceeds your cash and Collectible AR, it signals 'job borrow'—using new jobs to pay old job bills.
-
✓
Recognize 100% of Projected Losses Immediately (GAAP Loss Provision): Under US GAAP ASC 606, if Total Estimated Cost (Column H) exceeds Revised Contract Value (Column E) on any job, 100% of the total projected loss must be accrued immediately in the current period, not prorated by % complete.
Frequently Asked Questions
How do you calculate Overbilling and Underbilling on a Construction WIP schedule?
First, calculate Earned Revenue to Date by multiplying the Revised Contract Value by the Cost-to-Cost % Complete (Cost to Date ÷ Total Estimated Cost). Next, compare Earned Revenue to Billed to Date: if Billed to Date > Earned Revenue, the difference is Overbilling (a Current Liability); if Earned Revenue > Billed to Date, the difference is Underbilling (a Current Asset).
Are Underbillings an asset or a liability on a construction balance sheet?
Underbillings (formally titled 'Costs and Estimated Earnings in Excess of Billings on Uncompleted Contracts' or CIEB) are recorded as a Current Contract Asset on the balance sheet because the contractor has performed and earned revenue for work that has not yet been billed to the project owner.
Are Overbillings an asset or a liability on a construction balance sheet?
Overbillings (formally titled 'Billings in Excess of Costs and Estimated Earnings on Uncompleted Contracts' or BIEC) are recorded as a Current Contract Liability (unearned/deferred revenue) because the contractor has billed the client ahead of physical cost completion and owes the remaining work.
Should Progress Billings to Date on the WIP schedule include or exclude retainage?
Under US GAAP construction accounting standards, Progress Billings to Date (Column K) must include 100% of gross approved progress billings (including the 5% or 10% retainage withheld by the owner), since retainage receivable is already recognized as a contract asset.
What is 'Profit Fade' on a Construction WIP report and why do sureties care?
Profit fade occurs when a project's estimated gross profit margin percentage declines month-over-month as Cost-to-Complete estimates rise. Surety underwriters monitor WIP schedules across consecutive quarters to verify that a contractor's initial bid estimates hold up during field execution.
Can I add more than 10 active construction projects to this Excel WIP template?
Yes. The workbook is 100% unlocked and macro-free. Simply insert additional rows above the Total row (Row 22) and copy the formulas in Columns E, H, I, J, L, M, and N down for as many active jobs as your company manages.