Construction financial model: costs, billing and cash

A construction financial model connects job costs, progress billing, retention and collections to the cash available to fund work. Start with separate cost and billing schedules, then join them by job and month. Gross billings, approved payment requests and money in the bank answer different questions.

Gather the job records before the formulas

Use the cost ledger, subcontractor and supplier invoices, approved billing applications, receipt allocations and accounts-payable export. Add the current estimate to complete from the project manager. Keep the authorized contract value and approved changes separate from proposed changes that the customer has not accepted.

For each billing, retain the gross amount, withheld retention, amount collectible now, approval status and expected receipt date. The federal construction payment clause addresses progress payments and retainage separately. Your job schedule should likewise follow its signed contract and approvals, rather than assuming one standard retention rule fits every project.

Agree a cutoff with project management and finance. Costs incurred through month-end need the same cutoff as the billing and collection reports. Keep later invoice postings visible as adjustments; silently changing the cost history makes the comparison unstable.

Establish the cost-to-complete view

Consider an illustrative US contractor with one job. The authorized contract is $1,000,000. Plan, actual and scenario refer to the same month; the scenario changes collection timing only. All monetary amounts below are USD.

The work-in-progress review starts with incurred costs and the remaining commitment. The remaining estimate includes both contracted work still to be performed and the project manager's estimate for uncommitted work; do not add outstanding purchase orders again if they are already included.

Percentages are rounded to two decimals. Cost progress is a planning indicator here. It does not calculate earned revenue, determine billable work or replace the approved accounting WIP schedule. The actual estimate has risen to $800,000, although the cost-progress percentage is close to plan; that is why the remaining-cost review matters.

Build the billing and cash schedule

In Excel, put the following row labels in column A and the three cases in B through D. Microsoft's SUM function reference documents the range addition used for receipts and receivables.

No retention is released this month. For this example, job costs all enter payables before payment; there are no separate payroll accruals, deposits or equipment payments. Add those schedules when your actual records contain them. Enter these formulas in B12:B19 respectively:

=B7*B8
=B7-B12
=SUM(B9:B10)
=B2+B13-B14
=B3+B12
=B4+B6-B11
=B5+B14-B11
=SUM(B15:B16)

Copy across through column D. Covirage is not affiliated with Microsoft.

The scenario collects an additional $30,000 from current billings. Closing cash increases by exactly $30,000 and collectible receivables fall by the same amount. Costs, contract value and retention stay unchanged.

Reconcile before interpreting the gap

Actual cumulative billings are $350,000 + $180,000 = $530,000. Actual cumulative collections are $250,000 + $115,000 = $365,000. Their difference is $165,000, matching $112,000 collectible plus $53,000 retained.

The payable check is $40,000 + $150,000 − $140,000 = $50,000. The cash check is $50,000 + $115,000 − $140,000 = $25,000. Tie each amount to its job ledger or receipt allocation, then reconcile the combined job cash schedules to company cash, including central overhead and financing.

Construction analytics covers the broader reporting context. For factory production rather than project billing, use the neighboring manufacturing financial model. Keep portfolio reporting questions with FP&A analysis.

Where it goes wrong

• Retention treated as overdue cash. Contractual release conditions differ from ordinary payment terms; track the balance separately.
• Unapproved changes funding the plan. Put proposed variations in a separately labeled case until authorization exists.
• Commitments counted twice. An estimate to complete that already includes open orders needs no second addition.
• Receipts assigned by customer only. Allocate receipts to the correct job and billing, including deductions, before reading job cash.

Compare your project schedules in Covirage

Bring a file or Google Sheet containing job assumptions, costs, gross billings, retained balances and receipt allocations; Covirage maps the columns, and its tools compute and check comparisons against the supplied plan. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.

See Covirage for construction for the industry context.