Build a 13-Week Cash Flow Forecast in Excel with Low-Balance Alerts

You get a 13-week cash flow sheet layout with committed and expected cash split apart, exact running-balance formulas, and red flags for weeks below your minimum.

Prompt

Prompt
You are a small-business finance analyst who builds 13-week cash flow forecasts in Excel and Google Sheets.

Your job: design a 13-week cash flow forecast I can build today. First ask me these in one message, then wait for my answers:
1. What is my opening cash balance, and what date does week 1 start?
2. What are my regular cash inflows and outflows, with amounts and timing?
3. Which items are committed (invoices sent, signed contracts, bills due) and which are expected or estimated?
4. What minimum cash balance do I never want to go below?

Rules:
- Split inflows and outflows into Committed and Expected lines.
- Use 13 weekly columns (C to O) with week-ending dates in row 2 and the minimum balance in cell B3.
- Give exact formulas for totals, net cash flow and closing balance; each week's opening balance equals the prior week's closing balance.
- Add a conditional formatting rule that turns any closing balance below B3 red, plus a flag row showing BELOW MIN.
- Use only my numbers; label any gap as an assumption.

Output format:
1. Layout table (row, label, contents)
2. Formulas table (cell, formula, purpose)
3. Conditional formatting steps
4. Preview of weeks 1-4 as a table
5. Flagged weeks with one action each

End with a short note on assumptions and missing data. This is not financial advice; see an accountant before borrowing or delaying major payments.

Example output

Example for: a small catering company, opening cash $18,400, minimum balance $10,000.

1. Layout

RowLabelContents
2Week endingDates in C2:O2
3Minimum balance10,000 in B3
4Opening balanceC4 typed in; D4:O4 formula
6Committed in: invoiced eventsAmounts by due week
7Expected in: pipeline eventsQuoted jobs likely to book
8Total inflowsFormula
10Committed out: payrollBiweekly 6,200
11Committed out: rent3,500 first week of month
12Committed out: van loan900 monthly
13Expected out: food and suppliesAbout 2,400 weekly
14Total outflowsFormula
16Net cash flowFormula
17Closing balanceFormula
18FlagFormula

2. Formulas

CellFormulaPurpose
D4=C17Opening = last week's close (copy to O4)
C8=SUM(C6:C7)Total inflows
C14=SUM(C10:C13)Total outflows
C16=C8-C14Net cash flow
C17=C4+C16Running closing balance
C18=IF(C17<$B$3,"BELOW MIN","")Text flag

Copy C8:C18 across to column O.

3. Conditional Formatting

  1. Select C17:O17.
  2. Home > Conditional Formatting > New Rule > Use a formula.
  3. Enter =C17<$B$3 and set a red fill.

4. Preview, Weeks 1-4

Wk 1Wk 2Wk 3Wk 4
Opening18,40010,80012,7007,400
Committed in4,5003,2001,8006,800
Expected in02,0001,5002,500
Total out12,1003,3008,6002,400
Net-7,6001,900-5,3006,900
Closing10,80012,7007,40014,300
FlagBELOW MIN

5. Flagged Weeks

  • Week 3 (7,400): Payroll lands before the 6,800 wedding invoice clears. Ask that client for a 50% deposit now.
  • Week 7 (9,100): Rent and payroll fall in the same week. Ask the landlord to move rent to the 8th.

Assumptions and gaps: Pipeline events assumed to pay within 14 days. Sales tax payments and owner draws were not provided, so they are not included.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Splitting committed from expected cash shows how much of the forecast is certain and how much depends on deals that may slip.
  • Linking each opening balance to last week's close creates a true running balance instead of 13 disconnected weeks.
  • A minimum-balance cell plus red formatting makes shortfalls visible weeks ahead, with one concrete action per flagged week.

Use this in ChatGPT, Claude or Gemini when cash feels tight or before a big purchase. Have your bank balance, unpaid invoices, payroll dates, rent and loan payments ready.

Common edits

  • Change the columns to months for a 12-month forecast instead of 13 weeks.
  • Add a best-case and worst-case row that applies a percentage to expected inflows.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets