Construction Cash Flow Forecast: Build One in a Spreadsheet With AI

Put every receipt and payment in a ledger against the week cash will actually move, not the week the work happens: stage payments after the client's lag and less retention, supplier bills after their terms. Then total 12 weekly rows and carry the balance forward. ChatGPT or Copilot can write the formulas; the dates and terms are yours.

Profit does not pay wages. A job can be on budget and still leave you short in week five, because a groundworker's invoice, a window deposit and the payroll arrive before the second stage payment does. The forecast below shows exactly that, then shows how to test it against a late client. It is built in Excel, with Copilot's side pane as the helper, and not with the retired COPILOT worksheet function.

Follow me on Instagram@sagnikteaches

Cash moves on payment dates, not on work dates

A monthly profit-and-loss report tells you whether a job made money. It cannot tell you whether you will have the money on the Friday the payroll runs. On a building job three things push cash away from the work: the client pays after a certification or invoice lag, the client holds back retention, and suppliers and subcontractors are paid on terms that rarely match the client's. Each is a date problem, so the forecast is built on dates.

Connect on LinkedInSagnik Bhattacharya

The method has two sheets. The first is a ledger with one row for every cash movement, each carrying the week it is triggered and the number of days until the money actually moves. The second is a 12-week grid that adds up the ledger by week and carries the balance forward. Nothing is typed into the grid, so a changed payment date flows through by itself. That is also what lets an assistant help safely: it writes formulas, and you check them against numbers you can verify.

Subscribe on YouTube@sagnikteaches

The job we are forecasting

The illustration is a single-storey rear extension built by a small firm with three employees. Every figure is a made-up round number. The contract is $240,000, with stage payments invoiced when each stage is complete:

• Deposit 10% on signing (week 1), with no retention taken on it.
• Foundations and slab complete, 15% (week 4).
• Walls to roof level, 20% (week 7).
• Roof watertight, 20% (week 10).
• First fix complete, 15% (week 14), second fix and plaster 10% (week 17) and completion 10% (week 20), all outside the 12-week window.

The client pays 14 days after each invoice. Retention is 5% of each stage payment after the deposit. Opening cash is $15,000. Own labour is $3,300 a week and overheads are $1,150 a week. Everything else is a ledger row with its own terms, shown next.

Sheet one: a ledger with one row for every cash movement

Make the ledger an Excel table (select the range and press Ctrl+T) and call it Events. Give the columns short names with no spaces or symbols, so the formulas stay readable: ID, What, Type, Gross, RetPct, Net, EventWeek, Terms and CashWeek. Two columns are formulas and the rest are typed.

Net        =[@Gross]*(1-[@RetPct])
CashWeek   =[@EventWeek]+ROUNDUP([@Terms]/7,0)

Terms are in days, and ROUNDUP turns them into whole weeks, so 14 days is two weeks and 30 days is five. The ledger for the first twelve weeks and a few events just beyond it looks like this. Type is In or Out, and weekly wages and overheads live in a separate small table, shown after it.

A second table, called Recurring, holds the weekly items: Item, Weekly, StartWeek and EndWeek. Own labour is $3,300 from week 1 to week 12, and overheads are $1,150 over the same weeks. Recurring costs do not need twelve ledger rows each; one row and a SUMPRODUCT does the job.

Supplier terms stated as "end of the month after invoice" do not fit a simple day count. Convert them by looking at your last few invoices: count the days between invoice date and payment date, and use the average. If that is too rough for a large bill, type the cash week directly instead of using the formula.

Sheet two: the 12-week grid that adds the ledger up

Put the week numbers 1 to 12 down column A, starting in row 5, with the opening cash in B2 and a minimum buffer you are comfortable with in B3. Then fill each row from five formulas. Weeks run down the sheet here so the table reads on a phone; if you prefer weeks across, the same formulas work with the references turned round.

Opening   B5   =$B$2            (week 1 only; B6 onwards: =F5)
In        C5   =SUMIFS(Events[Net],Events[Type],"In",Events[CashWeek],$A5)
Out       D5   =SUMIFS(Events[Net],Events[Type],"Out",Events[CashWeek],$A5)
               +SUMPRODUCT((Recurring[StartWeek]<=$A5)*(Recurring[EndWeek]>=$A5)*Recurring[Weekly])
Net       E5   =C5-D5
Closing   F5   =B5+E5
Lowest    B18  =MIN(F5:F16)
Its week  B19  =INDEX(A5:A16,MATCH(MIN(F5:F16),F5:F16,0))
Flag      G5   =IF(F5<0,"Below zero",IF(F5<$B$3,"Under buffer",""))

With the ledger above, the grid produces this forecast:

Read it from the bottom up. Over 12 weeks $149,400 comes in and $146,700 goes out, so the 12 weeks finish $2,700 ahead of the opening balance. But the balance dips to −$3,450 in week 5, when the groundworker's $12,000, the windows deposit of $6,000 and a normal week's $4,450 of wages and overheads all land before the second stage payment arrives in week 6. A profitable job needs one week of overdraft, and the forecast tells you which week.

The second lesson sits in the last row. Stage 4 ($45,600 after retention) arrives in week 12. If it slips one week, week 12 closes at −$27,900 instead of $17,700. The final week of any forecast carries the most risk, because it depends on the payment that has had the least time to be confirmed. Mark it.

Three checks that catch most grid errors

• The In column over all 12 weeks should equal the sum of the ledger's In rows with a cash week of 12 or less. Here that is $24,000 + $34,200 + $45,600 + $45,600 = $149,400.
• The Out column should equal the ledger's Out rows in the window plus 12 × ($3,300 + $1,150) = $53,400 of recurring cost, a total of $146,700 here.
• Pick two weeks and add them up by hand from the ledger. Week 5 is a good one: $4,450 + $6,000 + $12,000 = $22,450.

Retention: money you have earned and cannot spend

Retention is a percentage the client keeps back from each payment as security that the work will be finished and defects put right. One reference on construction contracts gives the usual rate as often 5 per cent, released in two stages: half when practical completion is certified and the rest when defects have been made good, after a defects period that typically runs six to twelve months. Contracts differ, and some small jobs have none, so take the percentage and the release dates from your own.

In the example, retention is 5% of every stage payment after the deposit. Across the whole contract that is 5% of $216,000, or $10,800. By week 12 the client has held back $1,800 + $2,400 + $2,400 = $6,600, and none of it is released inside the window: half comes back at practical completion and half after the defects period. A cell can track it for you:

Retention held to week 12 =SUMPRODUCT((Events[Type]="In")*(Events[CashWeek]<=12)*Events[Gross]*Events[RetPct])

Three consequences follow.

• Gross invoice values overstate cash. If the Net column is skipped and gross amounts go in, week 12 shows $24,300 instead of $17,700. The forecast looks $6,600 healthier than the bank account will be, and the week 5 dip is unchanged, which hides how little room there is.
• Release dates are outside the window. Track them in a separate list with the date each tranche falls due, and chase them. The retainage tracking tutorial covers reminders for exactly that.
• Retention can run the other way. If you hold 5% from a subcontractor, you pay $19,000 on a $20,000 invoice now and owe $1,000 later. Enter the net payment now and a separate Out row for the release week, so the liability does not disappear from view.

Let Copilot in Excel build and check the formulas

Excel's =COPILOT() worksheet function is gone: Microsoft's support page says that from 14 September 2026 the function is no longer available, results already calculated stay as stored values, and any cell that recalculates shows #NAME?. What remains is the Copilot pane, and that is the one to use here. Microsoft's own advice for the old formula is to describe the task to the pane in plain language.

Before you start, check these points against Microsoft's current FAQ and tips pages:

• A qualifying plan. Microsoft's FAQ lists a Microsoft 365 Premium subscription, a Personal or Family subscription with an AI credits plan, a commercial Microsoft Copilot subscription, or a business or enterprise subscription eligible for Copilot Chat. In a business, your organisation's settings decide whether the pane appears.
• The file format and location. Save the workbook as a normal .xlsx. Microsoft's FAQ says a Strict Open XML file triggers an "Unsupported file state" message, and that if your SharePoint site forces check-out for editing, Copilot in Excel on Windows or Mac will not work on its files (Excel for the web still does). Older guidance also required OneDrive or SharePoint storage, so if the pane is unavailable on a local file, save a copy to OneDrive first.
• Table data. Copilot works best on a table with a single header row, a single type of data in each column and no merged cells. That is why the ledger is a table.
• Automatic calculation. Copilot cannot edit a workbook set to manual calculation. Check Calculation Options on the Formulas tab.
• AutoSave. Older guidance required it. Microsoft's tips page now treats it as optional, and turning it off lets you see what Copilot does to a copy of the forecast without saving the result.

The pane has three modes, named Allow editing (the default), Plan and Chat only. Use Plan when you want to see the steps before anything changes, and Chat only when you want an answer without edits. Prompts that suit this workbook:

1. In the Events table, add a column called CashWeek equal to EventWeek plus Terms divided by 7, rounded up.
2. Add a column called Net equal to Gross times one minus RetPct.
3. On a new sheet called Grid, build a 12-row forecast with Week, Opening, In, Out, Net and Closing. In and Out should use SUMIFS on the Events table by CashWeek and Type; opening for week 1 is cell B2.
4. Highlight any Closing balance below zero in red, and show the lowest closing balance and the week it happens.
5. Explain the formula in D7 and tell me whether it counts any event twice.

Microsoft's FAQ is blunt here: Copilot can get things wrong, and Microsoft tells users not to rely on it for decisions in sensitive areas, finance included. Treat what it builds as a draft and run the three grid checks above on it; the total of the In column against the ledger will catch a wrongly scoped SUMIFS. The Copilot in Excel tutorial covers what the pane does well and where it falls over.

ChatGPT instead: paste the ledger, get formulas and a test

Without Copilot, the same job works in ChatGPT, Claude or Gemini, and in Google Sheets as well as Excel. The trick is to ask for a test as well as the formulas, because a formula that looks right and sums the wrong range is the commonest failure.

I have an Excel table called Events with columns ID, What, Type (In or Out), Gross,
RetPct, Net, EventWeek, Terms (days) and CashWeek. Net = Gross x (1 - RetPct).
CashWeek = EventWeek + ROUNDUP(Terms / 7, 0). A second table called Recurring has
Item, Weekly, StartWeek and EndWeek.
Write Excel formulas for a 12-week grid with columns Week, Opening, In, Out, Net and
Closing. Out must include the recurring items. Opening cash is in B2.
Then test your formulas on the three ledger rows below and tell me what weeks 1 to 6
should show, with the arithmetic. If anything is ambiguous, ask me first.
[paste three Events rows and one Recurring row]

Check the reply the way you would check a junior's work. Does the In formula filter on both Type and CashWeek? Does the Out formula include the recurring part? Does the test arithmetic match what you can add up on a calculator? If the test and the formulas disagree with each other, the assistant has told you where to look. Replace client names with job codes before pasting. A forecast shows who owes you what and when, so paste it only into a business plan, which keeps content out of model training by default, or into a personal plan with its training switch turned off in the privacy settings.

For the wider toolkit, the ChatGPT Excel guide covers file upload and formula help in more depth, and Claude's Excel add-in offers a similar in-workbook route. The generic version of this method, for a business without stage payments, is in the 13-week cash flow forecast tutorial.

Stress-test it: late client, quick subcontractors, dearer materials

A forecast earns its keep when you change one number and watch the balance. Change a single input in the ledger and read the lowest closing balance and the weeks below zero. These are the results for the example job, with one change each.

The pattern is the useful part. Price rises and retention nudge the balance; a late client payment sinks it. Two weeks' delay on one stage takes the trough from −$3,450 to −$37,100, and a facility sized to that worst row, about $40,000 here, is the number to take to a bank, not the base case. Some subcontracts pay only after you have been paid; that can offset part of the squeeze, but only if the clause holds where you work, and the pay-when-paid tutorial covers what to check.

Then try the levers. Moving the windows payment from week 5 to week 8, by agreeing to pay on delivery instead of a deposit with the order, lifts the lowest balance to +$2,550 and nothing goes negative. Raising the deposit from 10% to 15% (with the last stage shrinking to match) lifts the lowest balance to $6,100 and week 12 to $29,700. Stretching supplier terms from 30 to 45 days adds $13,500 to week 12 but leaves the week 5 dip, because that dip comes from the groundworker and the window deposit, not from materials. Even with a 15% deposit, the two-week client delay still leaves a −$25,100 trough in week 10, which is why the payment schedule needs the same attention as the costs.

A Friday routine that keeps the forecast alive

A forecast that is not updated is a story about the past. Fifteen minutes on a Friday is enough:

1. Replace the cash week of anything that has moved with its actual week, and mark it done in a spare column.
2. Add the week's new events: invoices raised, orders placed, variations agreed, each with its own terms.
3. Read the lowest balance and its week. If it has moved earlier or lower than last week, find the row that caused it.
4. Paste last week's closing column beside this week's and ask an assistant to explain the differences in plain English, then check its explanation against the ledger.
5. Save a dated copy. Comparing three months of copies shows which of your assumptions keeps being wrong.

Once this runs, job costing is the natural next layer: the construction accounting tutorial lines cost to date up against what has been invoiced, job by job.

Questions about a builder's cash flow sheet

Can I build this in Google Sheets instead of Excel?

Yes. The ledger-and-grid method uses SUMIFS and SUMPRODUCT, which work the same way in Google Sheets. Replace the table names with plain ranges, such as $F$2:$F$60, because structured table references behave differently there. If you use an assistant, tell it which program you are in, because it will otherwise default to Excel syntax.

How many weeks should a construction forecast cover?

Twelve weeks is a good working horizon: long enough to catch a stage payment that lands after a supplier bill, short enough to stay accurate. Keep later events in the ledger even though the grid ignores them, and extend the grid when a job runs longer. Update it weekly, because an old forecast is worse than none.

Where do tax and other statutory payments go in the forecast?

Add them as ledger rows in the week you will actually pay them, and keep the dates your accountant gives you. Payments that fall monthly or quarterly are easy to forget in a weekly grid, and they often land in the same week as a supplier run, so they deserve their own rows rather than being buried in overheads.

What if my client pays on certified valuations, not stage milestones?

Use the same ledger. The event week becomes the week you expect the valuation to be certified, and the terms become the days between certification and payment. The extra uncertainty is the certified amount, so add a second row for the part you expect to be queried and give it a later cash week.

Further reads

• AI in Construction Projects: Weekly Early-Warning Flags for Cost and Delay — Weekly early-warning flags for cost and delay.
• Tradify for Builders: Set Up Quotes, Variations and Stage Payments — Set up stage payments in job software rather than a sheet.
• Construction Final Account: Variations, Deductions and Sign-Off With AI — Where the last retention release and final balance land.
• How Small Builders Use AI to Price Variations and Change Orders — Price variations before they become unpaid extra work.
• Builder Invoice Template: Stage Payment Requests Written With AI — Write the stage invoices that feed the ledger.
• Best AI for a Small Construction Company's Financials and QuickBooks — How AI connects to QuickBooks for a small construction firm: three routes compared, the plan job costing needs, and ten entries no AI should post.
• Fergus for Builders: Job Costing and Progress Claims, With Fergus AI — Set up Fergus phases, costs and claim billing for a small building job, with a worked six-stage example and the AI features that help on claims day.
• Construction Job Costing With AI: Find Which Jobs Actually Made Money — Export costs from Xero or QuickBooks, add timesheet labour, build a margin table by job and let AI flag the coding errors that hide losses.
• Construction Payment Notice Template: Draft With AI, Check the Deadlines — Payment and pay less notice templates, an AI prompt that drafts them from your facts, and a date check a quantity surveyor can run in a spreadsheet.
• Custom Home Builder Proposal: Draft Allowances and Exclusions With AI — Draft a custom home proposal in ChatGPT or Claude: allowance table, exclusions in full sentences, payment stages that match the work, and pre-send checks.
• Lien Waivers for Small Contractors: Track Conditional and Final Releases — A three-tab sheet that matches each conditional and final lien waiver to the payment it covers, with red-flag formulas and a worked HVAC example.
• Pay Applications for Subcontractors: Schedule of Values With AI — Build the schedule of values, report percent complete, add stored materials and retainage, then use AI to check the package against the contract.
• Practical Completion Certificate: What Each Clause Means, Then an AI Draft — Each clause of a practical completion certificate in plain English, a filled-in kitchen extension example, and an AI draft you check against the contract.
• Can AI Check a Construction Payment Application Before You Send It? — Run a payment application through AI before sending: what it catches in the maths, retention and stored materials, and what only you can verify.
• Construction WIP Report: What It Shows and How AI Builds One — What a work-in-progress schedule shows, a three-job worked example of over and under billing, and where AI helps and where it must not.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft Support pages on getting started with Copilot in Excel, its FAQ and tips page, and the COPILOT function retirement (14 September 2026); Designing Buildings Wiki on retention in construction contracts (often 5 per cent, released in two stages).