Blog · Territory, capacity and quota planning · Construction
Lay out progress receipts, retainage releases, subcontractor payments and project cash gaps in one workbook. The example includes monthly formulas, an actual-versus-plan check and a delayed-receipt scenario.
A construction cash flow forecast template in Excel should schedule progress collections, retainage releases and project payments, then roll the cash balance forward. Use expected bank dates, not the dates work is performed or invoices are entered. Keep a frozen forecast beside actual movements so the project team can see which receipt or payment changed the cash position.
Use a control sheet for project name, reporting date, opening cash and minimum cash requirement. State whether the opening amount is an actual project bank balance or an internal cash allocation. Do not add allocations across projects and call the total bank cash unless finance has reconciled that total.
The receipts register needs the payment application or invoice ID, customer, gross billing, retained amount, expected collection date and expected cash amount. Record a retainage release as its own cash event linked to the original withheld balance. The payments register needs vendor, invoice or order reference, payment category, expected payment date and amount.
For federal fixed-price construction contracts, the FAR payment clause addresses progress payments and retainage separately. Your workbook should preserve that distinction, while using the terms of the actual contract being forecast. Do not assume a retainage percentage or release date applies to every project.
Use the construction project cash flow guide for the underlying method. This page supplies the workbook layout and its controls.
Put the opening balance in Control!B2 and the minimum cash requirement in Control!B3. The example below uses $150,000 and $50,000 respectively. In the schedule, put the first day of each month in column A and the next month's first day in column B; the end date is exclusive.
| Column | Heading | Row 2 input or formula |
|---|---|---|
| A | Period start | October 1, 2026 |
| B | Next period start | November 1, 2026 |
| C | Opening cash (USD) | =Control!$B$2 |
| D | Progress receipts (USD) | Expected collections net of withheld retainage |
| E | Retainage receipts (USD) | Expected releases actually collected |
| F | Subcontractor payments (USD) | Scheduled cash disbursements |
| G | Labor payments (USD) | Cash payroll and related project payments |
| H | Other payments (USD) | Materials and other cash costs outside F and G |
| I | Net movement (USD) | =SUM(D2:E2)-SUM(F2:H2) |
| J | Closing cash (USD) | =C2+I2 |
| K | Gap to minimum (USD) | =MAX(0,Control!$B$3-J2) |
In C3 enter =J2 and fill down. This makes the next month's opening balance equal the previous closing balance.
Suppose Receipts has cash dates in column A, category in B and expected amount in C. Calculate progress receipts with:
=SUMIFS(Receipts!$C$2:$C$10000,Receipts!$A$2:$A$10000,">="&$A2,Receipts!$A$2:$A$10000,"<"&$B2,Receipts!$B$2:$B$10000,"Progress")
Use the same formula with category Retainage for column E. Apply an equivalent calculation to the payment register for each payment category. Microsoft's SUMIFS documentation explains the multiple-criteria sum used here. The exclusive next-period boundary prevents a payment on the first day of a month from entering both months.
A US contractor has the following expected cash events. Collections are net of any amount still withheld; the January retainage release is additional cash, included only once. All amounts are whole USD, so no rounding is required.
| Period | Opening cash (USD) | Progress receipts (USD) | Retainage receipts (USD) | Subcontractor payments (USD) | Labor payments (USD) | Other payments (USD) | Net movement (USD) | Closing cash (USD) | Gap to minimum (USD) |
|---|---|---|---|---|---|---|---|---|---|
| October 2026 | 150,000 | 200,000 | 0 | 180,000 | 90,000 | 30,000 | -100,000 | 50,000 | 0 |
| November 2026 | 50,000 | 300,000 | 0 | 200,000 | 100,000 | 30,000 | -30,000 | 20,000 | 30,000 |
| December 2026 | 20,000 | 250,000 | 0 | 170,000 | 80,000 | 30,000 | -30,000 | -10,000 | 60,000 |
| January 2027 | -10,000 | 100,000 | 80,000 | 120,000 | 60,000 | 20,000 | -20,000 | -30,000 | 80,000 |
The unfinanced schedule ends below zero. That is a funding requirement to resolve, rather than a prediction that the bank will permit the balance. Total receipts are $850,000 of progress collections plus $80,000 of retainage, or $930,000. Payments are $670,000 to subcontractors, $330,000 for labor and $110,000 for other costs, totaling $1,110,000.
The full-period check is therefore:
Closing cash = $150,000 + $930,000 - $1,110,000 = -$30,000.
The largest gap to the $50,000 minimum is $80,000. Do not add the monthly gaps: they describe the cumulative position at successive dates. If funding is approved, record its expected receipt and any repayment separately, then recalculate the schedule.
Freeze the forecast before replacing completed periods with actuals. For October, suppose actual progress collections are $190,000 and actual payments are $175,000 to subcontractors, $90,000 for labor and $30,000 for other costs.
| October view | Opening cash (USD) | Receipts (USD) | Payments (USD) | Closing cash (USD) |
|---|---|---|---|---|
| Frozen forecast | 150,000 | 200,000 | 300,000 | 50,000 |
| Actual | 150,000 | 190,000 | 295,000 | 45,000 |
| Actual minus forecast | 0 | -10,000 | -5,000 | -5,000 |
Closing cash is $5,000 below forecast: receipts fell $10,000, partly offset by payments falling $5,000. Reconcile the actual $45,000 balance to the bank or approved project allocation before using it as the next forecast's opening balance.
Separately, test collection risk against the original forecast. Move November's $300,000 receipt to December and leave everything else unchanged. November closes at -$280,000, December still closes at -$10,000, and January still closes at -$30,000. The peak gap to the minimum rises to $330,000 even though total receipts and the final balance are unchanged.
That is why month-end totals sometimes need a daily cash schedule. A large receipt late in a month cannot fund an earlier payment without another source of cash.
You can transpose the schedule into a statement-style view: receipt and payment categories down the rows, months across the columns, with opening and closing balances at the bottom. It remains the same project cash schedule, with the same sources and rollforward. It is not a second statutory statement.
For a shorter weekly horizon, the existing 13-week cash flow layout provides a related structure. Keep project-level timing assumptions alongside the wider cash flow forecast so finance can reconcile the project contribution to the company position.
Covirage is not affiliated with Microsoft.
Bring the completed project cash schedule and actual movements in a workbook or Google Sheet. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for construction for the broader project review.
Include retainage only in the period when you expect its release and collection. Keep the withheld amount in a separate register until then. The release date should follow the contract and documented expectations, rather than the date the underlying work was billed.
It is a management schedule of expected project receipts and payments. You can present it with cash categories down the page and periods across it, but that presentation does not turn it into the company's statutory statement of cash flows.
Yes. Use weekly periods when collection dates or subcontractor payments create gaps hidden by monthly totals. Keep the same opening-cash rollforward and source references, and assign each cash event to exactly one period.