Profitable businesses run out of cash all the time. A big client pays late, quarterly taxes come due the same week as rent, and suddenly a business that looked healthy on paper is moving money between cards to make payroll. A cash flow forecast won't stop those weeks from happening… but it lets you see them coming far enough ahead to do something about it.
The 13-week forecast is a classic tool because it covers one full quarter, week by week. Here's how to build one in Excel, even if you're not a spreadsheet person.
What a 13-week forecast shows you
For each of the next 13 weeks, the forecast answers one question: how much cash will I have at the end of the week?
It does this by starting with today's cash, adding money you expect to come in, and subtracting money you expect to go out. Simple math, done consistently.
Step 1: Set up the layout
Open a new workbook and set up a sheet like this:
- Row 1: Week labels in columns B through N (Week 1 to Week 13)
- Row 2: The week starting date for each column. Put your first Monday in B2, then in C2 enter
=B2+7and copy it across.
Down column A, add these sections:
Beginning cash
Cash in
- Customer payments
- Other income
- Total cash in
Cash out
- Payroll
- Rent
- Inventory or supplies
- Software and subscriptions
- Loan payments
- Taxes
- Other expenses
- Total cash out
Net cash flow
Ending cash
Step 2: Add the formulas
The structure only needs a few formulas:
- Total cash in:
=SUM(B5:B6)(adjust to your rows) - Total cash out:
=SUM(B9:B15) - Net cash flow:
=B7-B16 - Ending cash:
=B3+B18(beginning cash plus net cash flow) - Next week's beginning cash: In C3, enter
=B19, the prior week's ending cash. Copy across.
Type your actual bank balance today into B3. Every other beginning cash cell flows from the week before.
Step 3: Fill in expected cash in
This is where honesty matters. Use when you expect to receive money, not when you send the invoice.
- Look at open invoices and when each customer usually pays. If a client always pays in 45 days, put their payment 45 days out.
- Add recurring income, like retainers or memberships.
- Be cautious with new sales you're hoping for. Many owners leave them out entirely, or add them on a separate "best case" row.
Step 4: Fill in expected cash out
Expenses are usually easier to predict:
- Payroll on its actual pay dates
- Rent on the day it's due
- Loan payments
- Subscriptions, using your last few bank statements as a guide
- Quarterly estimated taxes, if they fall in the window
- Annual bills like insurance renewals
Don't forget the irregular ones. Those are the ones that cause surprises.
Step 5: Add a warning flag
Set a minimum cash level you're comfortable with, say one month of fixed expenses. Put that number in a cell, like B22, labeled "Minimum cash."
Then select the Ending cash row and use Conditional Formatting > Highlight Cells Rules > Less Than, pointing to that minimum cell. Any week that dips below turns red. At a glance, you can see trouble ahead.
Step 6: Update it every week
A forecast is only useful if it's current. Once a week:
- Replace the first week's estimates with what actually happened
- Update the beginning cash with your real bank balance
- Add a new week at the end so you always see 13 weeks ahead
- Adjust timing on any invoices that are running late
This takes 15 to 20 minutes once you're in the rhythm.
An example
A small landscaping company builds a forecast in late June. Week 7 shows ending cash below the minimum. Looking closer, a quarterly tax payment, an equipment loan payment, and a large supply order all land in the same week, while a big commercial client typically pays a week later.
With six weeks of warning, the owner moves the supply order up a week, asks the commercial client about paying early, and sets aside cash from the busy weeks before. No panic, no late fees.
Common mistakes to avoid
Using profit instead of cash. A sale you invoiced today isn't cash until it's paid. Forecast when money actually moves.
Being too optimistic about collections. If customers usually pay late, plan for late.
Forgetting owner draws. If you pay yourself, include it.
Building it once and never touching it. An outdated forecast can be worse than none, because it gives false confidence.
Overcomplicating it. Start with a handful of categories. Add detail only when it helps a decision.
Make your forecast smarter over time
Once the basic version works, you can link it to a sheet of open invoices using SUMIFS by due date, or pull recurring expenses from a reference table. Those small upgrades save time each week and reduce typing errors.
Want to build tools like this with confidence?
Our Advanced Excel for Business Analysis course teaches the skills behind forecasts like this one: clean data layout with Tables, SUMIFS and lookups, conditional formatting, PivotTables, and charts. It's designed for small business owners and staff who want to make better decisions with the numbers they already have.