Rebuild a contractor WIP schedule (over/under billing) in Excel with AI

Short answer

Use Copilot in Excel or Claude: The AI builds a three-tab WIP workbook with visible cost-to-cost formulas and loss flags, then drafts a month-over-month narrative from the numbers you paste. Banks and sureties read the work-in-progress schedule closely, yet many companies run it on hidden columns and nested IFs that one person understands. Have Copilot in Excel or Claude rebuild it as three tabs: raw job data, transparent cost-to-cost calculations (percent complete, earned revenue, over/under billing, projected gross profit, loss flags) and a clean report, then tie the totals to the general ledger yourself. A person still checks: totals tie to the general ledger and the prior month; estimates come from your PMs and revenue recognition policy from your CPA.

AI tie-in

Tool: Copilot in Excel or Claude

The AI builds a three-tab WIP workbook with visible cost-to-cost formulas and loss flags, then drafts a month-over-month narrative from the numbers you paste.

Prompt 1

Build an Excel WIP schedule with three tabs. Inputs: job number, contract amount, approved change orders, pending change orders, estimated total cost, cost to date, billed to date, retainage (leave rows blank for me). Calc: revised contract (excluding pending changes), percent complete = cost to date / estimated total cost, earned revenue, over/under billing (billed minus earned; positive = overbilled), projected gross profit, gross profit to date, cost to complete, and a LOSS flag when estimated total cost exceeds revised contract. One plain formula per column, no hidden columns, no nested IFs. Report: a printable summary with totals.

Prompt 2

Here are this month's and last month's WIP rows: [paste]. Write a short narrative for our surety: jobs whose projected gross profit moved most, new loss jobs, and the largest over- and underbillings. Use only these numbers, mark anything missing as TBD, and do not explain causes I have not given you.

Human check: Totals tie to the general ledger and the prior month; estimates come from your PMs and revenue recognition policy from your CPA.

The problem

Rebuild a contractor WIP schedule (over/under billing) in Excel with AI. This is a common, time-consuming task for a construction accountant.

What this workflow does

Banks and sureties read the work-in-progress schedule closely, yet many companies run it on hidden columns and nested IFs that one person understands. Have Copilot in Excel or Claude rebuild it as three tabs: raw job data, transparent cost-to-cost calculations (percent complete, earned revenue, over/under billing, projected gross profit, loss flags) and a clean report, then tie the totals to the general ledger yourself.

How it works: AI drafts or analyzes. A person operates the tools, checks the work, and takes action.

Your checkpoint: Totals must tie to the general ledger and the prior month. Estimates to complete come from your project managers; revenue recognition policy and loss treatment are your CPA's call. Not accounting or bonding advice.

Before you start

Access to the named tool; confirm license, version and cost with the vendor.
A small, non-confidential sample to try the workflow on first.
A qualified person to review the output before it is used.

Tools and access

Follow the steps

Export one row per open job from your accounting system: contract amount, approved change orders, estimated total cost, cost to date, billed to date and retainage. Keep pending change orders in their own column, not in contract value.
Ask Copilot or Claude to build three tabs: Inputs (the raw export only), Calc (one visible formula per column, no hidden columns, no nested IFs) and Report (what the bank or surety sees).
Calc columns: revised contract, percent complete (cost to date over estimated total cost), earned revenue, over or under billing, projected gross profit, gross profit to date and cost to complete.
Add flags for jobs with a projected loss and for jobs whose estimated cost grew without an approved change, so they get a look before the report goes out.
Paste this month's and last month's rows into a second prompt for a short narrative of the biggest movements, built from the numbers only; edit it before it goes to the surety.
Tie WIP totals to the general ledger and to last month's schedule yourself. Revenue recognition and loss treatment are set by your CPA, not the model.

Try a small example

Replace the bracketed fields with your own project details. No tested sample run is published for this workflow yet.

Sample input

Fill in the bracketed [fields] in the text below with your project data.

Starter prompt

Build an Excel WIP schedule with three tabs. Inputs: job number, contract amount, approved change orders, pending change orders, estimated total cost, cost to date, billed to date, retainage (leave rows blank for me). Calc: revised contract (excluding pending changes), percent complete = cost to date / estimated total cost, earned revenue, over/under billing (billed minus earned; positive = overbilled), projected gross profit, gross profit to date, cost to complete, and a LOSS flag when estimated total cost exceeds revised contract. One plain formula per column, no hidden columns, no nested IFs. Report: a printable summary with totals.

Expected output

Not published yet. Treat any output as a draft and check it against the source documents.

Check the result

Every value, citation or quantity traces back to a source document.
Missing information is marked as missing, not filled in.

Limits and recovery

Totals must tie to the general ledger and the prior month. Estimates to complete come from your project managers; revenue recognition policy and loss treatment are your CPA's call. Not accounting or bonding advice.
Check your organization’s data policy before sharing project files with third-party tools.

Stop or switch when: The source cannot be verified, permissions are missing, or the result contains unsupported facts.

Frequently asked questions

How does AI help with this?

Copilot in Excel or Claude: The AI builds a three-tab WIP workbook with visible cost-to-cost formulas and loss flags, then drafts a month-over-month narrative from the numbers you paste. A person still checks: Totals tie to the general ledger and the prior month; estimates come from your PMs and revenue recognition policy from your CPA.

How do I rebuild a contractor WIP schedule (over/under billing) in Excel with AI?

Export one row per open job from your accounting system: contract amount, approved change orders, estimated total cost, cost to date, billed to date and retainage. Keep pending change orders in their own column, not in contract value. Ask Copilot or Claude to build three tabs: Inputs (the raw export only), Calc (one visible formula per column, no hidden columns, no nested IFs) and Report (what the bank or surety sees). Calc columns: revised contract, percent complete (cost to date over estimated total cost), earned revenue, over or under billing, projected gross profit, gross profit to date and cost to complete. The full step list is above.

Does this use an AI agent or regular software?

It is labeled AI assists: AI drafts or analyzes. A person operates the tools, checks the work, and takes action.

What tools do I need?

Microsoft Excel, Microsoft Copilot, Claude. Confirm license, version and cost with the vendor, and try it on a small non-confidential sample first.

Has this workflow been tested?

Workflow guide · not independently tested. Written from vendor documentation and practitioner use. Labels apply to the steps described here, not every feature of a product. Links were checked when the page was written.

Evidence and maintenance

Written from vendor documentation and practitioner use. Labels apply to the steps described here, not every feature of a product. Links were checked when the page was written.