A 12-Week Cash Flow Forecast You Can Build Yourself
Learn to build a simple rolling 12-week cash flow forecast in a spreadsheet, so that you can spot shortfalls early and act before they become a crisis.
Most cash problems do not arrive without warning. They build up over weeks as bills fall due and customers pay later than hoped. A short-term cash flow forecast is a simple tool that makes the problem visible in time to do something about it. A 12-week rolling forecast, updated weekly, is long enough to see trouble coming and short enough to be reasonably accurate. This guide shows you how to build one in a spreadsheet.
Why 12 weeks?
Twelve weeks, roughly a quarter, is a practical horizon. Beyond that, forecasts tend to rely too heavily on guesswork. Within it, you will typically know about most of your invoices, regular bills and major commitments. The “rolling” part means that each week you drop the week that has just passed, add a new week at the end and update the figures with what actually happened. As cash flow differs from profit, this forecast focuses on when money actually moves, not when it is earned.
Step 1: Build the layout
Open a spreadsheet and create twelve columns, one for each week, with the week-commencing date at the top. Down the left-hand side, create these rows:
- Opening balance (what is in your bank at the start of the week)
- Money in: customer payments, other income, loans or grants
- Money out: rent, wages, suppliers, tax, loan repayments, subscriptions, owner's drawings
- Net movement (money in minus money out)
- Closing balance (opening balance plus net movement)
The closing balance of one week becomes the opening balance of the next.
Step 2: Start with the known
Fill in what you know. Add each outstanding invoice in the week you realistically expect payment, not the week it is due. If a client usually pays ten days late, plan for that. Then add your regular bills on their usual dates: rent, insurance, software, loan payments and any tax payments you know are approaching.
Step 3: Add the likely
Next, add things you expect but have not yet confirmed: new work you are confident about, seasonal costs or an equipment repair you have been putting off. Be cautious with income and generous with costs. It is better to be pleasantly surprised than caught out.
A short illustration
Suppose you start week one with £1,500. You expect a £2,000 customer payment in week two and £1,200 of bills in week one, £900 in week two and £2,800 in week three, which includes a quarterly insurance premium.
- Week one: 1,500 − 1,200 = £300
- Week two: 300 + 2,000 − 900 = £1,400
- Week three: 1,400 − 2,800 = −£1,400
The forecast shows an overdraft or shortfall in week three. Seeing this weeks in advance gives you options: chase the invoice sooner, ask a supplier for a short extension, or move a payment date.
Step 4: Roll it forward
Every week, spend fifteen minutes updating. Replace forecast figures with actuals for the week just finished, correct your assumptions if they were wrong, drop the oldest week and add a new twelfth. Over time you will learn how reliable your customers are and how accurate your estimates tend to be.
What to do when the forecast shows a gap
- Chase overdue invoices promptly; see our advice on writing a clear invoice so that nothing slows payment.
- Delay non-essential spending.
- Talk to suppliers early about extended terms.
- Consider whether a short-term facility is sensible, and compare costs carefully.
This article offers general information and is not financial advice.
Frequently asked questions
Do I need special software? No. A simple spreadsheet is enough. Some accounting software includes forecasting tools as well.
How accurate does it need to be? It does not need to be exact. The aim is to spot problems early, not to predict every pound.
How is this different from a budget? A budget is a plan for the year or month. A cash flow forecast tracks the timing of money week by week.
Steps
- Set up weekly columns: Create a spreadsheet with twelve weekly columns and rows for opening balance, money in, money out and closing balance.
- Fill in expected receipts and payments: Use invoice due dates and regular bills, being cautious about when customers will really pay.
- Roll it forward weekly: Each week, replace the forecast with actual figures, drop the oldest week and add a new one at the end.