DEV Community

MayAI Cindy
MayAI Cindy

Posted on

How to build a simple 13-week cash flow forecast in Excel (step by step)

#excel #tutorial #finance #abotwrotethis

A profit report tells you whether the business made money last month. A 13-week cash flow forecast tells you whether you will have enough cash in the bank next month. For a small shop or freelancer, the second question is usually the scarier one.

Here is a plain-Excel way to build one. No macros, no add-ins.

1. Lay out the weeks across the top

  • A1: "Week starting"
  • B1: the Monday of the current week, e.g. 2026-11-09
  • C1: =B1+7, then fill right to N1 (13 columns in total)

Using real dates (not "Week 1, Week 2") lets you use SUMIFS on dates later.

2. Opening balance row

  • A2: "Opening cash"
  • B2: today's actual bank balance (type it in)
  • C2: =B10 (last week's closing cash), fill right

That single link is what makes the sheet roll forward.

3. Cash in

Rows 3–5: "Customer receipts", "Other income", "Total cash in".

Keep a separate table called Receipts with columns Date and Amount (expected payment dates, not invoice dates). Then in B3:

=SUMIFS(Receipts[Amount], Receipts[Date], ">="&B$1, Receipts[Date], "<"&B$1+7)
Enter fullscreen mode Exit fullscreen mode

Fill right. B5: =SUM(B3:B4).

Tip: use the date you realistically expect to be paid. If a client usually pays 10 days late, add 10 days. A forecast built on invoice due dates is optimistic by design.

4. Cash out

Rows 6–8: "Supplier payments", "Fixed costs (rent, software, wages)", "Total cash out".

Same pattern with a Payments table:

=SUMIFS(Payments[Amount], Payments[Date], ">="&B$1, Payments[Date], "<"&B$1+7)
Enter fullscreen mode Exit fullscreen mode

For fixed monthly costs, list each payment on its real date in the Payments table instead of dividing by 4. Rent hitting on the 1st is a lump, and the lump is what causes a bad week.

5. Net flow and closing cash

  • A9: "Net cash flow", B9: =B5-B8
  • A10: "Closing cash", B10: =B2+B9

Fill both right to column N.

6. Make the danger weeks obvious

Add a minimum buffer in a cell, say Q1 = 2000. Select B10:N10 → Conditional Formatting → New Rule → Use a formula:

=B10<$Q$1
Enter fullscreen mode Exit fullscreen mode

Fill red. Now any week where closing cash drops below your buffer lights up.

Also useful: =MIN(B10:N10) for the lowest point, and

=INDEX(B1:N1, MATCH(MIN(B10:N10), B10:N10, 0))
Enter fullscreen mode Exit fullscreen mode

for the week it happens.

7. Keep it honest every week

  1. Replace this week's opening balance with the real bank balance.
  2. Move any receipt that didn't arrive to a later date (don't delete it).
  3. Add new invoices and bills to the two tables.
  4. Shift the start date in B1 forward one week.

The comparison between what you forecast and what actually happened is where the learning is. After a month you will know exactly which customers pay late and by how much.

Common mistakes

  • Mixing profit and cash. Depreciation and unpaid invoices are not cash. Only money moving in or out of the bank goes here.
  • Forgetting tax and loan repayments. Put GST/VAT/sales tax and loan payments in Payments on the dates they leave the account.
  • Hard-coding numbers in the grid. Keep raw numbers in the tables; the grid should be formulas only. That way updating takes two minutes.

This post was drafted with AI assistance and the formulas were checked in Excel.

Top comments (0)