Cellwise Start free

Excel guide · 7 min read

How to build a 13-week cash flow forecast in Excel

Quick answerList the money you expect in (customer, expected date, amount) and out (supplier, due date, amount). Put 13 week-start dates down a column, then pull each week's receipts and payments with SUMIFS between the week start and the week start + 7. Add fixed rows such as payroll and rent from input cells, and make each week's closing balance (opening + receipts - payments) the next week's opening. =MIN() on the closing column shows your tightest week.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1CustomerExpectedAmountSupplierDueAmountOpening cashWeekly payrollMonthly rentMinimum cash
2Northwind HotelsNov 12, 20266800Metro PowerNov 5, 2026124018000220030005000
3Brightside CafeOct 28, 20263200Greenleaf SuppliesNov 18, 20263600
4Lakeview DentalNov 20, 20264150Atlas InsuranceDec 1, 20262750
5Northwind HotelsDec 3, 20267400Equipment FinanceDec 15, 20261100
6Corner BakeryDec 10, 20262900Greenleaf SuppliesDec 16, 20263900
7Lakeview DentalDec 22, 20264150Metro PowerJan 6, 20271380
8Harbor GymJan 8, 20275600Greenleaf SuppliesJan 15, 20273700
9Northwind HotelsJan 14, 20277400Equipment FinanceJan 15, 20271100
10Corner BakeryJan 27, 20273100Atlas InsuranceDec 30, 20264800
11Harbor GymFeb 3, 20275600Greenleaf SuppliesFeb 12, 20273800
12Lakeview DentalFeb 16, 20274150
13
14

Download this sheet as an Excel practice file (.xlsx)

Lay it out: two lists, some inputs and a week grid

A 13-week forecast is a quarter of a year, one week per row. It's short enough to be accurate (you know most of the invoices and bills already) and long enough to see a tight patch coming. The layout above has four parts: expected receipts in A:C (from your open invoices or AR aging, with the date you actually expect to be paid, not the due date), bills in E:G (from your AP list, by due date), input cells in I2:L2 (opening cash, weekly payroll, monthly rent, the minimum cash you want to keep), and the forecast grid in M:S.

Type the first Monday in M2 (9 Nov 2026 here) and put =M2+7 in M3, then fill down to M14. Thirteen rows take you to the week of 1 Feb, which ends on 7 Feb 2027. Every other column in the grid is a formula, so next week you only change M2, the opening cash and the lists.

The receipts column adds up everything expected on or after the week start and before the next week start. The "<"&M2+7 half matters: a week that ends on Sunday the 15th must catch a payment dated the 15th, and "<=" with M2+6 would miss anything with a time in it.

=SUMIFS(C2:C12,B2:B12,">="&M2,B2:B12,"<"&M2+7)

Result: 6800

6,800: only Northwind Hotels' payment on 12 Nov falls in the week of 9 Nov. In the grid (O2) the ranges are locked with $ so the formula fills down all 13 weeks.

Payments by due week, and overdue bills in week 1

Payments use the same formula on the bills list. One change: in week 1, drop the lower bound. A bill that was due last week is still owed, and if it only matched dates on or after 9 Nov it would vanish from the forecast altogether. Metro Power's bill was due on 5 Nov, so it belongs in week 1.

Receipts are the opposite case. Brightside Cafe's 3,200 was expected on 28 Oct and hasn't arrived. Don't assume it lands this week: give it a new expected date you believe (or leave it out until it's paid). The check in the last section shows you any receipt that isn't in a week.

=SUMIFS(G2:G11,F2:F11,"<"&M2+7)

Result: 1240

1,240: the overdue Metro Power bill. With the usual ">="&M2 lower bound, week 1 would show 0 and the forecast would look 1,240 better than it is. Weeks 2 to 13 keep both bounds.

Does the list need to be sorted by date?

No. SUMIFS checks every row, so you can paste bills in any order. The Atlas Insurance bill for 30 Dec sits near the bottom of the list and still lands in the week of 28 Dec.

Fixed rows: payroll and rent from input cells

Costs that don't come from a bill list (payroll, rent, loan repayments, subscriptions) get their own row in the grid, driven by an input cell. Weekly payroll is simply =$J$2 in every week. Change J2 once and all 13 weeks update.

Monthly rent is paid in the week that contains the 1st. A week contains the 1st exactly when its Sunday (week start + 6) falls on day 1 to 7 of a month, so DAY(M2+6)<=7 picks those weeks out without a calendar.

=IF(DAY(M5+6)<=7,K2,0)

Result: 3000

3,000: the week of 30 Nov ends on 6 Dec, so it contains 1 Dec and pays rent. In this quarter rent lands in the weeks of 30 Nov, 28 Dec and 1 Feb, 9,000 in total.

Payroll every two weeks

Count the rows instead of the dates: =IF(MOD(ROWS($M$2:M2),2)=1,$J$2,0) pays in weeks 1, 3, 5 and so on. Use =0 instead of =1 if your first payday is in week 2. For irregular amounts (a bonus month, a seasonal hire), type the number over the formula in that week and colour the cell so you remember it's a manual entry.

=IF(MOD(ROWS($M$2:M2),2)=1,$J$2,0)

Result: 2200

2,200 in week 1; filled down, it gives 0 in week 2 and 2,200 again in week 3.

Link the closing balance to next week's opening

Closing = opening + receipts - payments - payroll - rent. Week 1 opens with the cash in I2 (your actual bank balance on Monday morning); every later week opens with the closing balance above it: N3 is =S2. That link is what makes it a forecast rather than 13 separate weeks: a late receipt in December carries through every week after it.

Here the business starts with 18,000 and drifts down. The week of 28 Dec has no receipts, but it has payroll, rent and the annual Atlas Insurance bill all at once.

=N9+O9-P9-Q9-R9

Result: 2410

2,410: the week of 28 Dec opens at 12,410, takes in nothing and pays 4,800 + 2,200 + 3,000.

Find the lowest week and flag it

The single most useful number in the forecast is the lowest closing balance, and when it happens. MIN gives the amount; XLOOKUP finds the week it belongs to. Compare it with the minimum cash you want to keep (L2) to see how many weeks dip below it.

  1. Select S2:S14, then Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  2. Enter =S2<$L$2, click Format, pick a light red fill, and click OK twice. Every week below your minimum turns red.
  3. Add a second rule with =S2=MIN($S$2:$S$14) and a bold border, so the lowest week stands out even when several are red.
=MIN(S2:S14)

Result: 2410

2,410, the closing balance in the week of 28 Dec.

Which week is the lowest?

XLOOKUP the minimum in the closing column and return the week start. TEXT makes the date readable in a label.

=TEXT(XLOOKUP(MIN(S2:S14),S2:S14,M2:M14),"d mmm yyyy")

Result: 28 Dec 2026

28 Dec 2026.

Which weeks fall below the minimum?

FILTER the week starts where the closing balance is under L2. =COUNTIF(S2:S14,"<"&L2) counts them instead: 6 here, every week from 28 Dec to 1 Feb.

=TEXT(FILTER(M2:M14,S2:S14<L2),"d mmm")

Result: spills 6 rows × 1 column

28 Dec
4 Jan
11 Jan
18 Jan
25 Jan
1 Feb

28 Dec, 4 Jan, 11 Jan, 18 Jan, 25 Jan and 1 Feb spill down.

Check the forecast before you trust it

Two check cells catch most mistakes. First, opening cash + all receipts - all outgoings should equal the last closing balance: =I2+SUM(O2:O14)-SUM(P2:R14)-S14 returns 0 here. If it doesn't, a closing formula was overtyped or a row isn't linked.

Second, compare the lists with the grid. Anything in a list that isn't in a week is either overdue (receipts only, because overdue bills go into week 1) or beyond week 13. Look at it on purpose rather than by accident.

=SUM(C2:C12)-SUM(O2:O14)

Result: 7350

7,350 of expected receipts sit outside the 13 weeks: Brightside Cafe's overdue 3,200 and Lakeview Dental's 4,150 due on 16 Feb, after the forecast ends on 7 Feb. The same check on bills gives 3,800 (Greenleaf Supplies, 12 Feb).

How do I roll it forward each week?

Every Monday: type the new week start in M2, put the actual bank balance in I2, delete the receipts and bills that have been paid, and update expected dates that slipped. The grid recalculates and a new week appears at the bottom. Compare last week's forecast closing with the actual balance; a big gap usually means expected dates are too optimistic.

Common mistakes

Using invoice due dates for receipts

Customers who usually pay 15 days late will make every week look better than it is. Use the date you actually expect the money, based on how each customer pays.

Overdue items falling out of the forecast

A SUMIFS with ">="&M2 in week 1 skips anything dated before it. Put overdue bills in week 1 by dropping the lower bound, and give overdue receipts a realistic new date.

Typing the opening balance into every week

Only week 1 uses the input cell. Every other opening must be =the closing cell above, or a late receipt won't carry through.

Counting with "<="&M2+6 for the week end

Dates with times (from an export) fail that test on the last day. ">="&M2 with "<"&M2+7 catches the whole week.

FAQ

What is a 13-week cash flow forecast?

A week-by-week view of cash in and out for the next quarter, starting from today's bank balance. It shows the lowest point coming up, so you have time to act before it arrives.

How is a cash flow forecast different from a budget?

A budget plans income and costs, usually by month. A cash flow forecast tracks when cash actually moves: an invoice raised in November might be cash in January. The two use different dates for the same sale.

Should the forecast use weeks or months?

Weeks for the next quarter, because payroll, rent and big bills inside one month can push the balance below zero even when the month ends fine. Months suit a 12-month outlook.

How do I show receipts that are only likely, not certain?

Add a confidence column to the receipts list and use it as an extra SUMIFS condition, so the main row sums only the certain ones. A second row for "likely" receipts lets you see the forecast with and without them.

Functions used

Related guides

Updated