What is a good week cash flow forecast example in Excel for a small business?

A weekly cash flow forecast in Excel is a spreadsheet that projects the money moving into and out of your business over a rolling 13-week (or 12-week) horizon, broken down week by week. It answers one question: will you have enough cash in the bank to cover payroll, rent, suppliers, and tax obligations each Friday? Below is a complete, practical example you can rebuild in Excel in under an hour, along with the formulas, common mistakes, and alternatives worth considering.

What a Weekly Cash Flow Forecast Actually Looks Like

Also worth reading: Can AI Be Your Small Business's Personal Financial Advisor? · How Does an AI Cashflow and Savings Coach Transform Small Business Finances? · How Should a Small Business Budget for an AI Pilot in 2026?

The standard structure uses rows for line items and columns for weeks. Across the top, you have your opening balance followed by Week 1 through Week 13. Down the left side, you list every source of inflow and outflow relevant to your business. A typical small business template includes these rows:

• Opening bank balance
• Customer receipts (often split by payment method: card settlements, bank transfers, checks)
• Other income (interest, grants, refunds from suppliers)
• Total cash in
• Payroll and contractor payments
• Rent and utilities
• Supplier and inventory payments
• Loan repayments
• Tax payments (VAT/GST/sales tax set-asides)
• Marketing and software subscriptions
• Total cash out
• Net cash flow (in minus out)
• Closing balance (opening plus net)

Each column is self-contained: the closing balance of one week becomes the opening balance of the next. This chaining is what makes the forecast useful — if Week 4 shows a closing balance below zero, you know exactly when you hit trouble and how much of a buffer or credit line you need before that date.

A Worked Example With Real Numbers

Here is a simplified four-week slice for a small retail or service business with roughly $85,000 in monthly revenue:

Notice what this example reveals immediately: this business is burning cash at roughly $4,000 per week despite healthy revenue. That pattern — profitable on paper but negative in cash terms — usually points to slow customer payments or inventory sitting too long. The forecast makes the problem visible weeks before it becomes an emergency.

The Excel Formulas You Need

You only need five formula patterns to build this. First, closing balance: =B2+B14-B13 style arithmetic where opening balance plus total inflows minus total outflows equals closing. Second, the chain: in cell C2 (next week's opening), enter =B16, referencing the prior week's closing balance, then drag across all 13 columns. Third, totals: =SUM(B5:B7) for inflows and =SUM(B9:B12) for outflows. Fourth, net flow: =B8-B13. Fifth, conditional formatting to flag danger: apply a rule so any closing balance below your minimum threshold (say, one week of fixed costs, around $12,000 in this example) turns red.

For receipts, avoid hardcoding guesses. If your average collection period is 32 days, then roughly half of any month's invoiced revenue lands the following month. Model it explicitly: Week N receipts = 40% of invoices issued in Week N + 35% of Week N-1 invoices + 25% of Week N-2 invoices. Adjust those percentages against your actual bank data until the model tracks reality within about 5%.

Direct Forecast vs. Indirect (P&L-Based) Method

There are two legitimate ways to build a cash forecast, and choosing wrong wastes time:

For a 13-week operating forecast, use the direct method. The indirect method smooths over exactly the timing details — when customers actually pay, when suppliers demand settlement — that cause small businesses to run short. Many finance teams maintain both: direct for operations, indirect for strategy.

Step-by-Step: Building Your First Forecast This Week

Start Monday by exporting 90 days of bank transactions and categorizing them into your inflow and outflow buckets. Most businesses find that 80% of cash movement falls into fewer than ten categories; do not build a 40-line model on day one. Set your opening balance from Friday's actual bank statement, not your accounting software's book balance, because uncleared checks and pending card settlements create gaps between the two.

Next, populate receipts using your accounts receivable aging report. Every open invoice gets assigned to the week its customer is likely to pay it, based on that customer's historical behavior rather than the stated terms. Then layer in known obligations: payroll dates, rent due dates, loan amortization schedules, quarterly tax deadlines. For variable costs like suppliers, use the trailing 8-week average as a baseline and adjust for known orders.

Finally, stress-test it. Build three scenarios in separate tabs or with a scenario switcher: base case, downside (receipts drop 20%, which matches typical recession-era receivables deterioration), and upside. The downside case tells you whether your current cash covers a bad month — if not, arrange a credit line now, while your numbers still look acceptable to a lender.

Common Mistakes That Break Weekly Forecasts

The most frequent error is confusing profit with cash. A business can show a $10,000 monthly profit while its bank balance falls, because profit recognizes revenue at invoice date while cash arrives 30–60 days later. Your forecast must track actual settlement dates. The second mistake is ignoring seasonality: copying last month's receipts forward misses holiday spikes, summer slowdowns, and annual insurance renewals. Pull at least 12 months of history before setting baselines.

Third, many owners forecast only inflows optimistically and forget that outflows are lumpy. Quarterly VAT or sales tax payments, annual software renewals, and payroll tax deposits can consume two full weeks of receipts in a single week. Fourth, stale data kills credibility: a forecast updated once a quarter is fiction. Commit to a 30-minute update every Monday morning, reconciling last week's projection against actuals and rolling the window forward one week. Track your forecast accuracy — if Week 1 projections miss by more than 10% consistently, your receipt assumptions need recalibration, not your spreadsheet.

Fifth, single-scenario thinking. Owners who model only their expected case get blindsided because reality never matches the average. Always keep a downside tab showing what happens if your largest customer pays 30 days late.

Excel Templates vs. Dedicated Tools

Excel remains the right starting point for most businesses under roughly $2 million in revenue, but it has real limits:

Tools that connect directly to your bank and accounting system eliminate the manual export-and-categorize cycle, and newer AI-driven platforms can flag anomalies — a supplier invoice 40% above trend, a customer whose payment behavior shifted — automatically. The trade-off is transparency: with Excel you can trace every number to a cell; with some automated tools you cannot. Whichever route you take, insist on being able to explain every figure in your forecast to a lender or investor without hand-waving.

When to Act and How Often to Update

Build your first 13-week forecast immediately if any of these apply: your cash covers less than eight weeks of fixed costs, more than 20% of your revenue comes from one customer, you are hiring or taking on debt in the next quarter, or you have ever been surprised by a tax bill. Those conditions describe most small businesses at some point, which is why weekly forecasting should be a standing habit rather than a crisis response.

Update cadence matters more than sophistication. A rough forecast refreshed every Monday beats a beautiful one refreshed monthly. Each Monday: reconcile last week's actuals against projections, roll the window forward, adjust receipt assumptions based on new invoice data, and check whether any week in the next 13 dips below your minimum cash threshold. If it does, you have 13 weeks to fix it — chase receivables, delay discretionary spend, draw on a credit line, or accelerate collections with early-payment discounts of 1–2%. Acting with 13 weeks of warning is routine; acting with 13 days of warning is expensive panic.

Making the Numbers Trustworthy

A forecast earns trust through calibration, not complexity. Keep a simple accuracy log: projected versus actual closing balance each week. If your Week 1 error stays under 5% and Week 13 under 15–20%, your model is good enough to make decisions with. If errors are larger, the problem is almost always receipt timing — revisit your collection-period assumptions against real bank data rather than adding more spreadsheet structure. Rebuild the template once a year as your cost structure changes, and keep the version history so you can see how your expectations evolved. The discipline of comparing prediction to reality every single week is what turns a static Excel file into a genuine financial early-warning system.