Profitable companies run out of cash all the time. The accounts show a healthy profit, but a big customer pays three weeks late, the quarterly tax bill lands in the same week as payroll, and the new equipment was paid for upfront. For a few weeks the bank balance falls below what the business needs to operate, and nobody saw it coming because the only forecast was a monthly budget built in January.
A 13-week cash flow forecast is the tool finance teams use to see those weeks before they arrive. It starts from today's bank balance and lists the cash expected in and out each week for the next quarter, so a shortfall shows up while there is still time to act. This guide explains how to build one in Excel, how to time receivables and payables realistically, and how to keep it accurate week after week.
Why 13 weeks, and why weekly
Thirteen weeks is one quarter. It is long enough to see the large, lumpy payments that sink cash plans, such as quarterly taxes, annual insurance and equipment purchases. It is also short enough to forecast with reasonable accuracy, because most of the cash moving in that window is already known: invoices you have sent, bills you have received, payroll dates and rent.
Weekly matters because cash problems are weekly problems. A month can end with a positive balance and still hide a week in the middle where the account goes negative. Payroll, supplier runs and customer receipts rarely line up neatly, and a monthly view averages that away.
A 13-week forecast uses the direct method: actual receipts and payments by category. That is different from a cash flow statement in the annual accounts, which usually starts from profit and adjusts it (the indirect method). The direct method answers the question owners and lenders actually ask: how much cash will be in the bank next Friday?
The structure of the forecast
Each week is a column, and every column follows the same logic:
- Opening balance (last week's closing balance, or today's bank balance for week 1)
- plus receipts, by category: customer collections, card sales, other income
- minus payments, by category: suppliers, payroll, rent, loan repayments, taxes, one-off items
- equals closing balance
Closing balance = Opening balance + Receipts − Payments
In Excel, the first week's opening balance points to a cell where you type the bank balance. Every later week's opening is simply the cell to its left: =H20 for example, where H20 is the previous week's closing balance.
Step 1: set the starting point and the safety line
Three inputs anchor the whole forecast:
- Opening cash: the actual bank balance on the start date, across the accounts you use for operations.
- Minimum cash: the lowest balance you are willing to hold, as a buffer for surprises. Many businesses set it at a few weeks of fixed costs.
- Credit line: any overdraft or revolving facility you can draw on, and how much of it is available.
A status formula then flags each week:
=IF(Closing < -CreditLine, "Short", IF(Closing < 0, "Using credit line", IF(Closing < MinCash, "Below minimum", "OK")))
That one column turns a grid of numbers into a list of weeks that need attention.
Step 2: time customer receipts realistically
Receivables are where most forecasts go wrong, because invoices do not get paid on their due date. Forecast each open invoice on the day you actually expect the money:
Expected date = Due date + average collection delay
If customers typically pay 10 days after the due date, an invoice due on 15 March is forecast for 25 March. For customers with a known pattern, set their delay individually, or type a promised payment date when they give you one.
Overdue invoices need a decision. A practical rule is to place them in week 1 if you expect them soon, and to leave out anything genuinely doubtful. Counting money that will never arrive is the fastest way to make a forecast useless.
To place each expected date in the right week:
=SUMIFS(Amount, ExpectedDate, ">=" & WeekStart, ExpectedDate, "<" & WeekStart + 7)
Step 3: time supplier payments by when you will pay
Payables are the opposite case: you control the timing. Forecast each bill on the date you plan to pay it, not just its due date. If you pay suppliers in a weekly run every Thursday, put every bill in the run before its due date. The same SUMIFS formula places them in weeks.
This is also where the forecast becomes a planning tool. If week 7 looks tight, you can see exactly which bills could move a week later without damaging a supplier relationship.
Step 4: add recurring and one-off items
Not everything comes from an invoice. List the regular commitments with their frequency and first date:
- Payroll: every 2 weeks, or monthly
- Rent: monthly
- Loan repayments: monthly
- Subscriptions and utilities: monthly
- Sales tax or VAT: quarterly
- Insurance: annually
Then add one-off items on their dates: equipment purchases, a tax bill, a loan drawdown, an asset sale, a large customer deposit.
For recurring items, a simple test decides whether an item falls in a given week: work out the number of days since the first date and check whether a payment date lands inside the week. For monthly items, EDATE(FirstDate, n) returns the date of the nth payment, which is easier than counting days.
A worked example: 13 weeks for a small distributor
A fictional distributor starts with 42,000 in the bank, wants to keep at least 15,000 and has a credit line it would rather not touch. Each week brings customer collections plus 6,000 of card sales. Payroll of 12,000 goes out every second week, rent of 6,500 monthly, a loan repayment of 2,200 monthly and a quarterly tax payment of 22,000 in week 7. The owner has also planned an 18,000 equipment purchase in week 6.
| Week | Opening | Receipts | Payments | Net | Closing | Status |
|---|---|---|---|---|---|---|
| 1 | 42,000 | 17,000 | 13,500 | 3,500 | 45,500 | OK |
| 2 | 45,500 | 15,500 | 21,000 | −5,500 | 40,000 | OK |
| 3 | 40,000 | 18,000 | 8,700 | 9,300 | 49,300 | OK |
| 4 | 49,300 | 16,000 | 20,000 | −4,000 | 45,300 | OK |
| 5 | 45,300 | 16,500 | 14,000 | 2,500 | 47,800 | OK |
| 6 | 47,800 | 17,000 | 38,000 | −21,000 | 26,800 | OK |
| 7 | 26,800 | 15,000 | 33,200 | −18,200 | 8,600 | Below minimum |
| 8 | 8,600 | 18,500 | 19,000 | −500 | 8,100 | Below minimum |
| 9 | 8,100 | 17,000 | 15,000 | 2,000 | 10,100 | Below minimum |
| 10 | 10,100 | 16,000 | 19,500 | −3,500 | 6,600 | Below minimum |
| 11 | 6,600 | 18,500 | 10,200 | 8,300 | 14,900 | Below minimum |
| 12 | 14,900 | 17,000 | 19,000 | −2,000 | 12,900 | Below minimum |
| 13 | 12,900 | 17,500 | 14,500 | 3,000 | 15,900 | OK |
Over the quarter the business takes in 219,500 and pays out 245,600, ending at 15,900. A quarterly summary would only show cash ending just above the minimum. The weekly view shows what happens in between: the equipment purchase and the tax bill land back to back, and cash sits below the 15,000 minimum for six straight weeks, bottoming out at 6,600 in week 10.
Now the owner can act. Moving the equipment purchase from week 6 to week 13 changes the total by nothing, but keeps every week above the minimum: the lowest point becomes 15,900 at the end of the quarter. Other options would be financing the equipment, asking the largest customers to pay on time, or agreeing a later payment date with a supplier. The point is that the choice is made in week 1, not discovered in week 10.
Step 5: roll it forward with actuals
A forecast that is never updated becomes fiction within a month. The habit that makes a 13-week forecast work is a weekly update:
- 01Enter last week's actual receipts and payments by category.
- 02That week switches from forecast to actual, and the balance rolls forward from real figures.
- 03Move the start date forward one week, so there are always 13 weeks ahead.
- 04Update the receivables and payables lists, and adjust anything you now know better.
It takes about half an hour a week once the structure is in place.
Step 6: compare actual with forecast
Variance analysis tells you how much to trust the forecast. For each category, compare what you forecast with what happened:
Variance = Actual − Forecast
For receipts, a positive variance is favourable. For payments, a negative variance is favourable, because you paid out less than expected. Example: week 1 receipts were forecast at 17,000 and came in at 15,200, an adverse variance of 1,800. Payments were forecast at 13,500 and came to 12,900, a favourable variance of 600.
Patterns matter more than single weeks. If collections come in below forecast every week, your collection delay is too optimistic, so increase it. Within a few weeks the forecast gets noticeably more reliable.
From 13 weeks to a 12-month projection
The 13-week view is for managing cash. A 12-month cash flow projection is for planning: hiring, investment, financing and seasonal stock. The same inputs carry over. Run the receivables, payables, recurring and one-off items by month instead of by week, and the two views stay consistent because they share one set of assumptions.
Use the 12-month view to see where the next tight spot is likely to be, and the 13-week view to manage the weeks in front of you.
Mistakes that make cash forecasts unreliable
- Using invoice due dates as receipt dates. Add a realistic collection delay.
- Counting doubtful debts. Leave out money you do not really expect.
- Forgetting irregular payments: quarterly taxes, annual insurance, bonuses and deposits.
- Monthly buckets only. They hide the weeks that cause trouble.
- Mixing profit with cash. Depreciation is not cash, and loan repayments and equipment purchases are.
- Never updating with actuals. The forecast drifts further from reality every week.
- No minimum cash line. Without a threshold, a falling balance looks fine until it is too late.
A ready-made 13-week cash flow forecast
The structure above can be built in Excel, but connecting receivables, payables, recurring items, actuals, variance and a 12-month view without a broken reference takes many hours. The 13-Week Cash Flow Forecast is an Excel workbook with all of it built.
Enter the bank balance, minimum cash and credit line, then list what customers owe, what you owe suppliers and your regular commitments. Open invoices are expected on the due date plus the collection delay you set, or on a date you type, with overdue invoices in week 1. Supplier bills go out on the due date or a planned pay date. Recurring items repeat weekly, every 2 or 4 weeks, monthly, quarterly, twice a year or annually, and one-off items go in on their date.
The forecast shows opening balance, receipts and payments by category, net cash flow, closing balance and a status for every week. The Actuals tab switches completed weeks to real figures, the Variance tab marks each line favourable or adverse, and the 12-Month View carries the same model through the year. The Dashboard shows the lowest balance and the week it happens, weeks below minimum, charts, receivables and payables with overdue amounts and the biggest payments. Categories can be renamed for any business. It holds 500 receivables, 500 payables, 100 recurring items and 300 one-off items, locks the formulas without a password and contains no macros.
If you need something simpler, these templates may be enough:
Common questions
What is a 13-week cash flow forecast?
It is a week-by-week forecast of cash receipts and payments for the next quarter. It starts from today's bank balance and shows the closing balance for each week, so a shortfall is visible early enough to act.
How is a cash flow forecast different from a cash flow statement?
A cash flow statement reports what already happened, usually starting from profit and adjusting it. A cash flow forecast looks ahead and lists the cash you expect to receive and pay, week by week or month by month.
How do I forecast customer payments?
Expect each invoice on its due date plus the number of days your customers usually pay late, or on a date they have promised. Place overdue invoices you still expect in the first week and leave out doubtful ones.
How often should I update a 13-week forecast?
Weekly. Enter the actual figures for the week just finished, move the start date forward and adjust anything you now know better. That keeps 13 weeks of visibility at all times.
What is a good minimum cash balance?
It depends on the business, but a common starting point is enough to cover a few weeks of fixed costs such as payroll and rent. Set it with your accountant or finance lead, and revisit it as the business grows.
Does it work in Google Sheets?
The workbook is built for Microsoft Excel 2016 or later (desktop or Excel for the web). You can import it into Google Sheets, but sheet protection does not carry over, so the locked formulas are easier to overwrite there by accident.
Cash surprises are almost always predictable a few weeks ahead, as long as someone is looking. You can preview the 13-Week Cash Flow Forecast free; downloading it is included in the OpenWorksheet monthly plan, along with the rest of the library. All figures in this guide are examples for a fictional business, not financial advice.
