How to Build a 13-Week Cash Flow Forecast in Excel

A 13-week cash flow forecast estimates when money will enter and leave a business over the next quarter. Unlike an income statement, it focuses on cash timing: when customers actually pay, when payroll clears, and when bills come due.

This guide explains a simple direct-method forecast that a small business can maintain in Excel. It is a planning tool, not a guarantee of future liquidity.

Step 1: Set up 13 weekly columns

Create one column for each week ending date, plus a column for row labels. Use a consistent week-ending convention and make sure the first forecast begins with the bank-reconciled cash balance as of the prior period end.

Recommended rows:

• Beginning cash
• Customer receipts
• Other cash receipts
• Payroll and benefits
• Supplier and operating payments
• Rent, insurance, and taxes
• Interest and debt principal payments
• Capital expenditures
• Net cash change
• Ending cash
• Minimum operating cash requirement
• Headroom above minimum

Keep actuals and forecast figures clearly distinguished.

Step 2: Forecast receipts when cash is expected

Start with open invoices and expected payment dates, not merely revenue recognized. Include recurring collections, customer-specific payment behavior, and known delays. Separate committed receipts from uncertain collections.

If a customer owes $15,000 but historically pays two weeks late, placing the full amount in the contractual due-date week may overstate available cash. Document assumptions and update them as information changes.

Step 3: Schedule actual cash outflows

Use payroll calendars, accounts-payable due dates, lease terms, tax dates, planned equipment purchases, and debt schedules. Include principal payments even though they are not income-statement expenses.

Avoid automatically spreading monthly costs evenly across weeks when real payment dates are known. A single large payment can determine the week's lowest cash balance.

Step 4: Calculate the weekly roll-forward

Suppose Week 1 begins with $40,000. The business expects $25,000 in receipts and $31,000 in payments.

Ending cash = Beginning cash + Receipts − Payments

$40,000 + $25,000 − $31,000 = $34,000

Week 2 begins at $34,000. If receipts are $18,000 and payments are $27,000, Week 2 ends at $25,000.

For a basic Excel layout, assume B2 contains beginning cash, B3 total receipts, and B4 total payments:

B5 = B2+B3-B4

For the next week in column C:

C2 = B5

Copy the roll-forward across the remaining weekly columns, making sure every week's receipt and payment totals reference the correct rows. If you use a detailed line-item template, sum each section before calculating net cash change.

Step 5: Measure headroom, not just ending cash

If management requires a minimum operating balance of $30,000, Week 2's $25,000 ending cash creates negative $5,000 of headroom. The forecast is identifying a potential shortfall before it occurs.

Check whether any restricted cash, unavailable credit, or bank processing delays mean the headline bank balance overstates usable liquidity.

Step 6: Build realistic scenarios

Create a base case and at least one downside case. Useful sensitivities include a major customer paying two weeks late, a large unexpected supplier bill, or lower-than-expected receipts.

Do not assume a revolving credit facility is available without confirming its borrowing base, covenants, and remaining capacity.

Step 7: Update the forecast every week

At the end of each week:

1. Replace the completed forecast week with actual receipts and payments.
2. Reconcile actual ending cash to the bank and cash ledger.
3. Explain significant variances, especially timing differences.
4. Shift the forecast forward and add a new thirteenth week.
5. Review the lowest projected cash balance and required management actions.

A forecast becomes more useful when it records why the previous week's cash movements differed from expectations.

Common mistakes

• Using booked revenue as if it were collected cash.
• Forgetting debt principal, taxes, or one-time capital spending.
• Mixing cash and accrual expense data.
• Double-counting transfers between company bank accounts as receipts or payments.
• Ignoring the timing of credit-card settlements or payroll.
• Treating the minimum cash threshold as optional.
• Failing to update overdue receivables and unpaid bills.

Connect the forecast to business reporting

A weekly liquidity forecast complements a monthly profit review. For the distinction between earnings and liquidity, read EBITDA vs. cash flow. For a repeatable management meeting, see the monthly financial review checklist.

Bottom line: The value of a 13-week forecast is not its complexity. It is its ability to reveal cash shortfalls early enough for management to respond.

Educational content. Examples are fictional and should be adapted and checked against your own reporting policies.

More from Business Finance →