📎 This article includes 1 downloadable practice file ↓
Profit is an opinion; cash is a fact. A 13-week cash forecast (one quarter, week by week) shows when money gets tight, early enough to do something: chase a big receivable, delay a purchase, or arrange a limit with the bank.
Layout
Weeks across or down, with these lines:
| Line | Formula |
|---|---|
| Opening cash | Week 1: actual bank balance; later weeks: previous closing |
| Collections | From the receivables list by expected date |
| Payments | Suppliers, salaries, rent, GST/TDS, EMIs |
| Net flow | Collections − total payments |
| Closing cash | Opening + net flow |
Net flow (row 2): =C2 - SUM(D2:H2)
Closing (row 2): =Opening + I2 ' week 1
Opening (row 3): =J2 ' previous closingFind the danger weeks
Lowest closing: =MIN(J2:J14)
Week of the low: =INDEX(A2:A14, MATCH(MIN(J2:J14), J2:J14, 0))
Weeks below minimum:=COUNTIF(J2:J14, "<" & MinCash)Conditional formatting on Closing cash below the minimum (red) makes the problem weeks jump out. Excel 365 users can calculate running balances without helper columns using SCAN (the practice Answers sheet shows how).
Roll it forward
Every Monday: replace last week’s forecast with actuals, add week 14, and update the opening balance from the bank. Compare forecast vs actual collections each week; the gap tells you how much to trust the forecast.
Where people go wrong
| Mistake | Effect |
|---|---|
| Using the P&L instead of cash timing | Shows profit, misses cash gaps |
| Collections on due dates | Too optimistic |
| Not updating with actuals | Forecast drifts into fiction |
Practice
Download the workbook below. Build each figure in the yellow column of the Practice sheet; the Check column turns green when it matches, and the Answers sheet has a working formula for every task.
📎 Practice files for this article
- 📗Practice workbookWeekly collections and payments for 13 weeks from 5-Oct-2026, with opening and minimum cash.⬇ XLSX · 14 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.