How to Prepare a WIP Report from QuickBooks Online

Preparing a construction work in progress (WIP) report can feel like several accounting problems arriving at once. Start with one job. Bring its costs and billings together, calculate the revenue earned, and separate this year’s activity from prior years. Then repeat the same process for the remaining jobs.

This guide walks through an eight-job example using QuickBooks Online reports and an Excel WIP template. You will build the schedule, record an underbilling and overbilling adjustment, and prove that the finished WIP agrees with the P&L and balance sheet.

By Bryce Wisan, CPA, CCIFP.

Download the practice files

Download the practice pack. It contains the source reports, the prior-year WIP, current job information, and Levvigo’s blank WIP template. All workbooks are Excel files.

Download the separate answer key when you are ready to check your work. It contains the completed current-year WIP, the adjustment and reconciliation, and the financial statements after the adjustment.

You can also download each file individually:

• Profit and Loss by Customer, January–May 20CY
• Company P&L, January–May 20CY, before the WIP adjustment
• Balance sheet at 12/31/20PY
• Balance sheet at 5/31/20CY, before the WIP adjustment
• Completed WIP at 12/31/20PY
• Current contract amounts, estimated total job costs, and job status
• Levvigo WIP schedule template
• Answer: completed WIP at 5/31/20CY
• Answer: WIP calculation, journal entry, and reconciliation
• Answer: P&L after the WIP adjustment
• Answer: balance sheet after the WIP adjustment

Sample Contractor is fictional. The company, jobs, and amounts in these files were created for this exercise. The sample QBO reports use the formatting of QBO Excel exports. The screenshots show the sample Excel reports and workpapers.

20CY means the current year. 20PY means the prior year. Our reporting date is May 31 of the current year. When you run reports in QBO, enter actual calendar dates. The training files use these labels so you can work through the example in any year.

The template’s separate Contracts in Progress and Completed Contracts tabs use Excel’s FILTER function. Use an Excel version that supports FILTER, such as Microsoft 365 or Excel 2021 or later.

For an overview of the workbook’s features, see the Construction WIP Template for Excel resource.

What you will do

1. Understand what the WIP is calculating.
2. Run and check the QBO reports.
3. Bring forward the prior-year WIP.
4. Enter current contract amounts and estimates.
5. Build billings and costs from the start of each job.
6. Calculate earned revenue and current-year results.
7. Review underbillings and overbillings.
8. Prepare and record the WIP adjustment.
9. Tie the WIP to the financial statements.
10. Troubleshoot differences and review the finished report.

Step 1: Understand the three amounts you are keeping separate

For each job, the WIP compares costs incurred, amounts billed, and revenue earned. These amounts answer different questions.

• Costs incurred: What job costs have been recorded through the reporting date?
• Billings: How much has been invoiced to the customer through that date, net of applicable credits?
• Earned revenue: How much contract revenue should be recognized based on the work performed?

Customer payments are a fourth amount. Collecting an invoice changes cash and accounts receivable. It does not establish the job’s percentage complete.

In this exercise, invoices are recorded in the income account Contract billings. The WIP adjustment uses the separate income account Revenue - WIP adj. Together, those accounts produce earned revenue on the adjusted P&L.

We use a cost-to-cost measure of progress. Costs incurred from the start of the job are divided by the latest estimate of total job cost. The resulting percentage is applied to the contract amount.

Percentage complete = job costs to date ÷ estimated total job cost
Earned revenue to date = contract amount × percentage complete

The example assumes the contracts qualify for revenue recognition over time and that cost-to-cost appropriately measures their progress. That accounting decision comes before the spreadsheet calculation.

To keep the first exercise focused, all job costs are assigned to jobs, and the costs and estimates use the same direct-cost basis. There are no indirect job costs, retainage, loss contracts, or special uninstalled-material adjustments. General and administrative expenses stay on the P&L. Your own company’s job-cost and revenue-recognition policies may require additional treatment.

Your first checkpoint: You can explain why a job’s billings can differ from its earned revenue. You do not need to understand every construction accounting issue before completing this example.

Step 2: Run the QBO reports using the same dates and accounting basis

For your own company, start with these reports:

• Profit and Loss by Customer: January 1 through May 31, 20CY.
• Profit and Loss: January 1 through May 31, 20CY.
• Balance Sheet: May 31, 20CY.
• Balance Sheet: December 31, 20PY.

Use Accrual for all four reports. Use the same company, reporting currency, and reporting cutoff. A cash-basis report can omit recorded unpaid activity and will not provide the basis used in this example.

Export the reports

1. Open QBO’s Reports area and find the required report by name.
2. Set the reporting dates and select Accrual.
3. Run or refresh the report. For the customer report, show the individual jobs, the total, and any Not specified column. Include the full population needed to reconcile to the company P&L.
4. Use Export/Print and choose Export to Excel. In classic report layouts, use the export control.
5. Save the exports in one period folder. Keep the original exports and use separate working copies for calculations.

QBO’s report controls vary by report view and subscription. Intuit explains the current Excel export steps and customer tracking for income and expenses.

For the practice case, open files 01 through 04. They are already prepared. The customer report has eight jobs and four direct job-cost categories: job labor, materials, subcontractors, and job equipment rental. Its five administrative expense categories are outside job costs.

Check the source totals before using them

The sample company P&L before the WIP adjustment shows:

• Contract billings: $1,465,000.
• Revenue - WIP adj.: $0.
• Direct job costs: $1,148,000.
• General and administrative expenses: $192,500.
• Net income before the WIP adjustment: $124,500.

Add the eight job columns in the customer report. Their billings must equal $1,465,000, and their job costs must equal $1,148,000. The Not specified job-cost total is zero. Administrative expenses in Not specified are expected here; they are not missing job-cost assignments.

Your checkpoint: The customer report and company P&L agree before you enter anything into the WIP. If they disagree, use the troubleshooting section before proceeding.

Step 3: Open the prior-year WIP and identify the carryover jobs

Open file 05, the completed WIP at 12/31/20PY. Three jobs had activity before the current year:

• J101 Office Renovation: costs of $120,000, earned revenue of $150,000, and billings of $150,000.
• J102 Warehouse Addition: costs of $272,000, earned revenue of $320,000, and billings of $300,000. This job had $20,000 of underbillings.
• J103 Clinic Fitout: costs of $112,500, earned revenue of $150,000, and billings of $170,000. This job had $20,000 of overbillings.

The prior-year balance sheet in file 03 also shows $20,000 of underbillings as an asset and $20,000 of overbillings as a liability. Confirm that these agree with the finalized prior-year WIP. For the current AJE, use the current general-ledger balances as explained in Step 8.

The equal totals do not mean the two accounts disappear. Underbillings and overbillings are calculated contract by contract. Keep the asset and liability separately for the different jobs in this example.

In the current-year WIP template, the Prior Years Revenue column receives the prior WIP’s cumulative earned revenue. Prior Years Cost receives cumulative costs through the prior year-end. Billings are carried forward separately when building inception-to-date billings.

All three carryover jobs began in 20PY. If your own job began earlier, the prior-year WIP must include its full history through 12/31/20PY. Using only last year’s annual activity would leave earlier activity out.

Your checkpoint: You have prior costs, prior earned revenue, and prior billings for each carryover job. Those three amounts are not interchangeable.

Step 4: Enter the jobs, current contract amounts, and current cost estimates

Save a working copy of file 07, the blank Levvigo WIP template. Enter Sample Contractor and 5/31/20CY at the top. In an actual company file, use the actual period-end date.

Open file 06, the job-information sheet. Enter all eight jobs on the WIP tab, starting in row 7. Use the same job IDs as the customer report so the names can be matched reliably.

• Job # and Description: Enter the ID and job name.
• Status: Select In Progress or Complete.
• Contract Amount: Enter the current contract amount used for the revenue calculation.
• Estimated Cost: Enter the latest estimate of total cost for the entire job, including costs already incurred and costs still expected.

Estimated Cost is not cost remaining. J101’s total estimated cost is $400,000. Later we will enter $280,000 of costs incurred to date. Its cost remaining will then be $120,000.

J103 and J106 are complete at May 31. Keep them on this year’s WIP because they have current-year revenue and costs. Their final costs equal their total estimated costs, and they are fully billed in this exercise.

The sample contract amounts already include the approved changes, and the estimates are current at May 31. For your own jobs, obtain the latest contract and cost forecast from the person responsible for the project. A signed contract and an old budget do not automatically provide the current numbers.

Use the template’s input cells. Leave the formula cells intact. The template calculates estimated gross profit, percentage complete, cost to complete, earned revenue, and billing differences.

Step 5: Build inception-to-date billings and costs

Inception to date means from the start of the job through the reporting date. Your January–May QBO report provides only the current-year portion.

For each carryover job:

Inception-to-date cost = cost through 12/31/20PY + January–May 20CY job cost
Inception-to-date billings = billings through 12/31/20PY + January–May 20CY contract billings

Work through J101 first

The prior WIP shows $120,000 of costs and $150,000 of billings. The current customer report shows $160,000 of costs and $180,000 of billings.

1. Enter $280,000 in inception-to-date Cost: $120,000 + $160,000.
2. Enter $330,000 in inception-to-date Billings: $150,000 + $180,000.
3. Enter $150,000 in Prior Years Revenue.
4. Enter $120,000 in Prior Years Cost.

In the supplied template, these are row 7 cells P7, N7, T7, and U7, respectively. The column headings are the main reference if you later extend or rearrange your working copy.

Complete the remaining jobs

• J102: inception-to-date costs $544,000; billings $680,000; prior earned revenue $320,000; prior costs $272,000.
• J103: inception-to-date costs $225,000; billings $300,000; prior earned revenue $150,000; prior costs $112,500.
• J104–J108: prior revenue, costs, and billings are zero. Their inception-to-date costs and billings equal the current-year customer report amounts.

For costs, use the total of the four direct job-cost categories for each job. In file 01, Total for 5000 Direct Job Costs and Total for Cost of Goods Sold are the same subtotal. Use that subtotal once. Adding both would double-count job costs.

For billings, use Contract billings, not the sum of Contract billings and Revenue - WIP adj. The adjustment account changes recognized revenue without creating an invoice.

Your checkpoint: Total inception-to-date costs are $1,652,500, and total inception-to-date billings are $2,085,000. Subtract the prior-year costs and billings to recover the current-year QBO totals.

Step 6: Calculate percentage complete and earned revenue

Return to J101. The template uses the inputs you entered:

• Contract amount: $500,000.
• Estimated total cost: $400,000.
• Costs incurred to date: $280,000.

Percentage complete = $280,000 ÷ $400,000 = 70%
Earned revenue to date = $500,000 × 70% = $350,000
Cost to complete = $400,000 − $280,000 = $120,000

The 70% is a cost-based measure of progress. It is not the percentage billed or collected. It also depends on a reliable estimate of total cost.

J101 earned $150,000 of revenue through the prior year-end. The amount earned in the current year is therefore:

Current-year earned revenue = $350,000 − $150,000 = $200,000
Current-year job cost = $280,000 − $120,000 = $160,000
Current-year gross profit = $200,000 − $160,000 = $40,000

Do not enter the full $350,000 as current-year revenue. That would recognize the prior year’s $150,000 again.

Repeat this review for the other jobs. The template should calculate these current-year earned revenue amounts:

• J101: $200,000.
• J102: $320,000.
• J103: $150,000.
• J104: $240,000.
• J105: $200,000.
• J106: $250,000.
• J107: $50,000.
• J108: $30,000.

Your checkpoint: Total current-year earned revenue is $1,440,000. Current-year job costs are $1,148,000, leaving $292,000 of current-year gross profit.

Step 7: Review underbillings, overbillings, and completed jobs

Compare earned revenue to billings from the start of each job:

Underbilling = earned revenue to date − billings to date, when positive.
Overbilling = billings to date − earned revenue to date, when positive.

J101 has earned $350,000 and billed $330,000. Its $20,000 underbilling is an asset in this example. J102 has earned $640,000 and billed $680,000. Its $40,000 overbilling is a liability.

An underbilling does not automatically mean an invoice is late. Billing terms and milestones may explain the difference. Review whether the company has a valid path to billing and collecting the amount. An overbilling does not mean the customer owes past-due money; it means the company has billed ahead of the revenue earned under this method.

At May 31, the eight jobs produce:

• Underbillings: J101 $20,000 + J104 $30,000 + J108 $10,000 = $60,000.
• Overbillings: J102 $40,000 + J105 $25,000 + J107 $20,000 = $85,000.
• Completed jobs J103 and J106: no remaining underbilling or overbilling.

The template’s Status column also populates the separate Contracts in Progress and Completed Contracts tabs. In this case, six jobs remain in progress and two are complete. The all-jobs current-year totals must include both groups.

The completed jobs contribute $400,000 of current-year revenue and $312,500 of current-year cost. Leaving them out would prevent the WIP from agreeing with the company P&L.

Review the Issues column as well. A blank Issues cell means the specific automated controls did not identify a condition. It does not prove the contract amount, estimate, or source report is correct.

Step 8: Calculate the adjustment from the balances already on the books

The WIP tells you the required ending balances. The journal entry is the amount needed to move the existing book balances to those ending balances.

Open the May 31 balance sheet before adjustment, file 04. It shows the current balances in the underbilling and overbilling accounts:

• Underbillings on the books: $20,000 debit balance.
• Overbillings on the books: $20,000 credit balance.

The starting point for the current WIP AJE is whatever balance is currently recorded in each underbilling and overbilling account, regardless of when those amounts were booked. Compare those existing general-ledger balances with the required ending balances on the current WIP schedule and book only the difference. WIP entries should not be reversed.

Compare the books to the WIP:

• Underbillings: required $60,000 less existing $20,000 = a $40,000 increase in the asset.
• Overbillings: required $85,000 less existing $20,000 = a $65,000 increase in the liability.

The offset belongs in Revenue - WIP adj. The net revenue effect is a $25,000 reduction:

$40,000 increase in underbillings − $65,000 increase in overbillings = ($25,000) revenue adjustment.

Prepare the AJE dated 5/31/20CY

• Debit Underbillings $40,000.
• Debit Revenue - WIP adj. $25,000.
• Credit Overbillings $65,000.

Total debits and credits are both $65,000.

Record the entry in QBO

For the practice exercise, prepare the entry in the answer-key workpaper or your own Excel file. You do not need to enter fictional transactions in your company’s QBO account.

When applying this process to your company, use your approved WIP and the actual pre-adjustment balances. The account names may already exist under descriptions such as Costs and Estimated Earnings in Excess of Billings and Billings in Excess of Costs and Estimated Earnings. Match the accounts to the schedule rather than creating duplicates.

1. Confirm that Underbillings is set up as an asset, Overbillings as a liability, and Revenue - WIP adj. as an income account. The sample uses Other Current Asset and Other Current Liability classifications.
2. Open + Create and choose Journal entry. If your navigation differs, use QBO’s search to find Journal entry.
3. Use the period-end date. Enter the three account lines, debits, and credits from the approved workpaper.
4. Add a description such as “Adjust WIP underbillings and overbillings to the May 31 schedule.” Use a unique journal-entry reference and retain the supporting WIP.
5. Confirm that debits equal credits, then save the entry.
6. Run a Journal report or reopen the entry to verify the saved date, accounts, and amounts.

Intuit provides the journal-entry workflow. This example uses a company-level WIP adjustment. It does not assign a company-wide adjustment to an arbitrary customer. If you maintain WIP adjustments by project, build the supporting entry by project and reconcile the same total.

Use the actual balances on the books at May 31 before this adjustment, including any WIP entries posted earlier in the current year. Do not reverse prior WIP entries or automatically substitute prior year-end balances.

Your checkpoint: After this entry, Underbillings must be $60,000 and Overbillings must be $85,000. The current-year P&L must include a $25,000 debit in Revenue - WIP adj.

Step 9: Tie revenue and job costs from the WIP to the P&L

Refresh the company P&L and balance sheet after posting an actual entry. Keep the same dates and accrual basis. For the practice case, open answer files 10 and 11.

Revenue tie-out

The adjusted P&L must contain:

• Contract billings: $1,465,000.
• Revenue - WIP adj.: ($25,000).
• Total revenue: $1,440,000.

The WIP’s current-year Revenue total is also $1,440,000. The difference is zero.

This comparison uses current-year WIP revenue. The schedule’s $2,060,000 of inception-to-date revenue includes $620,000 recognized before the current year and does not belong in a January–May P&L comparison.

Job-cost tie-out

The adjusted P&L’s four job-cost accounts total $1,148,000. The WIP’s current-year Cost total is also $1,148,000. The difference is zero.

The revenue adjustment does not change job costs in this example. General and administrative expenses of $192,500 remain outside the job-cost tie-out. Do not add them to the WIP just to force the schedule to agree with the P&L’s total expenses.

Balance-sheet tie-out

Compare the WIP’s all-jobs totals to the balance sheet:

• WIP Underbillings $60,000 = balance-sheet Underbillings $60,000.
• WIP Overbillings $85,000 = balance-sheet Overbillings $85,000.

Then enter the adjusted general-ledger amounts in the template’s Tie-out to the general ledger section: CY revenue $1,440,000; CY cost $1,148,000; underbillings $60,000; overbillings $85,000. The difference row must show zero for all four.

The adjusted company results are $292,000 gross profit and $99,500 net income. The after-adjustment balance sheet balances at $595,000. The WIP entry changes revenue, the two WIP balance-sheet accounts, and current-year net income. It does not change cash, accounts receivable, job costs, or administrative expenses.

There is also a useful independent revenue check:

CY earned revenue = CY billings + increase in underbillings − increase in overbillings
$1,465,000 + ($60,000 − $20,000) − ($85,000 − $20,000) = $1,440,000.

These checks tell you the schedule and books agree. Reviewing the source inputs and job forecasts tells you whether the agreed amounts make sense.

Step 10: Troubleshoot the WIP when something does not agree

A difference is usually a clue to a specific part of the process. Identify whether the problem is in costs, billings, prior-year amounts, forecasts, or the journal entry. You can work through those categories one at a time.

Job costs are recorded in job-cost accounts but are not assigned to jobs

What you see: Company P&L job costs are higher than the total assigned to jobs, or the customer report’s Not specified column contains amounts in job-cost accounts.

What to do: Drill into the affected job-cost account and review the transactions. Find the missing Customer/Project assignment on the source transaction and correct it to the proper job. Payroll-originated amounts may require correction through the relevant payroll or job-cost allocation process. Preserve the account, period, and source records as appropriate. Rerun the reports afterward.

Do not simply remove the Not specified column, exclude the transactions, or spread the difference over jobs to make the total agree. The practice case has zero unapplied job costs. Its unassigned office expenses are general and administrative costs, which are intentionally outside the WIP.

Prior-year costs or earned revenue were omitted

What you see: A carryover job appears far less complete than it should, current-year costs do not agree with QBO, or revenue includes amounts recognized in a prior year.

What to do: Compare the carryover job to the last finalized prior-year WIP. Verify prior cumulative costs, prior cumulative billings, and prior earned revenue separately. Add prior costs and billings to current-year activity to build inception-to-date inputs. Subtract prior earned revenue and costs to determine the current-year results.

For J101, entering only the current-year $160,000 cost would produce 40% completion rather than 70%. Entering the full $350,000 earned revenue as current-year revenue would recognize $150,000 a second time. These are different mistakes with different corrections.

Revenue and billings were treated as the same amount

What you see: The WIP revenue calculation is overwritten with invoices, customer collections are used as billings, or Revenue - WIP adj. is included in the billings input.

What to do: Trace billings to invoices and applicable credits. Trace earned revenue to the WIP calculation. Keep the WIP adjustment in its separate income account. Review customer receipts separately in accounts receivable and cash.

J102’s $680,000 of cumulative billings and $640,000 of cumulative earned revenue are both correct in this exercise. The $40,000 difference is the overbilling, not a number to eliminate by changing a contract input.

The contract amount or estimated total job cost is out of date

What you see: Percentage complete exceeds 100%, cost to complete becomes negative, expected margins look implausible, or earned revenue changes sharply without an understood cause.

What to do: Review approved contract changes, remaining purchase commitments, labor required to finish, subcontract commitments, and known cost overruns with the project team. Update total estimated cost as incurred cost plus a current estimate of cost remaining. Make sure both use the same job-cost scope.

For J101, if the current total estimate increased from $400,000 to $440,000 while incurred costs stayed at $280,000, the percentage would fall from 70% to about 63.6%. At a $500,000 contract amount, cumulative earned revenue would fall from $350,000 to about $318,182. The spreadsheet is reflecting the changed forecast. Investigate the business reason rather than overriding the result.

Keep previously finalized prior-year earned revenue as the historical amount when applying a current estimate change. A genuine prior-period error is a separate accounting matter. Do not rewrite the old WIP merely to make the current-year result look smoother.

The journal entry duplicates balances already on the books

What you see: The WIP looks right, but the balance sheet is off by the amount of a previous WIP entry, or revenue changes by more than the required difference.

What to do: Inspect the existing Underbillings, Overbillings, and Revenue - WIP adj. transactions through the reporting date. Verify the entries already posted and their dates. Recalculate the required change from the actual book balance to the approved ending WIP balance. Check whether the new entry has already been posted.

In this example, debiting the full $60,000 underbilling and crediting the full $85,000 overbilling would duplicate the $20,000 balances already present in each account.

The arithmetic ties, but the job still looks wrong

Check reporting dates, customer filters, duplicate parent-customer and job subtotals, missing completed jobs, and changes recorded after you exported the reports. Compare the same reporting population and basis on both sides of each reconciliation.

Also ask whether costs actually depict progress. Significant uninstalled materials, expected contract losses, disputed change orders, unusual billing terms, and other special circumstances may need accounting treatment beyond this introductory example. A WIP that adds correctly can still need a better forecast or an accounting review.

Finish with a short review you can repeat next month

• The report date, company, currency, and accrual basis are consistent.
• Every job with current-year activity is included, including completed jobs.
• Job-cost accounts reconcile, and missing job assignments have been resolved.
• Carryover jobs include the full prior-year history.
• Contract amounts, estimated total costs, and job status have been reviewed with the project team.
• Billings come from invoices and credits; earned revenue comes from the WIP calculation.
• The adjustment uses existing book balances and is recorded once.
• Current-year revenue and costs agree with the adjusted P&L.
• Underbillings and overbillings agree with the adjusted balance sheet.
• The final WIP, supporting reports, entry reference, and review notes are saved together.

For the next month in the same year, refresh the year-to-date QBO reports and current forecasts. Keep the prior-year cumulative revenue and cost columns tied to the finalized prior year-end WIP. Use the previous month’s WIP to understand changes, and use the actual pre-adjustment book balances to calculate the next entry.

You have now taken the process from recorded transactions to a reconciled construction WIP. The same sequence works each month: validate the reports, update the job information, calculate earned revenue, adjust the balances, and reconcile.

If you would like help preparing the schedule or establishing a monthly review process, explore Levvigo’s WIP Preparation Services.

‍