Cash Flow Forecast Spreadsheet: Spot a Cash Gap Before It Happens

The four inputs

• Opening balance — what is in the bank today.
• Receipts — when customers will actually pay, not when you invoice.
• Payments — payroll, suppliers, rent, loan repayments, tax instalments, owner drawings, in the week they leave the account.
• A minimum buffer — the lowest balance you are comfortable holding.

Weekly or monthly?

Use a 13-week (roughly one quarter) weekly forecast when cash is tight or payments are lumpy: it shows which week a problem lands in. Use a 12-month forecast for planning hires, purchases and seasonality. Many owners keep both and roll the weekly one forward every Monday, replacing last week's forecast with actuals.

When a week goes low

A low week is a prompt to act early, while there are still options.

• Chase or bring forward specific receipts (deposits, progress invoices).
• Move a discretionary payment to a later week.
• Talk to a supplier about terms before the due date.
• Arrange any financing you need before you need it, not during the low week.

Common forecasting mistakes

• Using invoice dates instead of the dates customers actually pay — check how late your slowest customers usually are.
• Forgetting irregular payments: annual insurance, quarterly tax instalments, equipment replacements, holiday pay.
• Leaving out owner drawings, so the forecast looks healthier than the account will be.
• Never replacing forecasts with actuals, so errors compound week after week.
• Treating the forecast as fact: add a note of the assumptions behind each large receipt so you can see which ones changed.

Worked example: six weeks for a small service business

Opening balance $12,500; minimum buffer $5,000. Receipts are when clients are expected to pay; payments include payroll in weeks 1 and 3.

Week 3 closes at $4,700 — $300 under the buffer — because a large client payment lands in week 4. Seeing this in week 1 leaves time to ask for that payment a week early or move a supplier payment, instead of finding out from the bank.

Weekly forecast routine

• Update the opening balance from the bank.
• Replace last week's forecast figures with actuals.
• Re-date receipts that are now late.
• Add any new payments you have committed to.
• Roll the forecast forward one week.
• Act on any week below your buffer.

Brainfy files for this task

Checked against the files customers download. Prices are current catalogue prices.

• Cash Flow Forecasting System (Paid — $49.00)

  XLSX · 1 file · sheets: Start Here, 13-Week Forecast, 12-Month Forecast

  A 13-week and a 12-month forecast with opening and closing balances and a low-cash warning against your minimum buffer.

• Cash Flow Organizer (Paid — $14.00)

  XLSX · 1 file · sheets: Start Here, Cash Flow, Lists

  Not a forecast: a dated ledger of money in and out with a running balance and lowest-balance figure, useful for irregular income.

Sources and method

The forecast logic (closing = opening + money in − money out, flagged when below the buffer) is the same as the free Cash Flow Planner; the example's balances are recomputed by an automated test with that code.

Keep going

• Client Onboarding Checklist: The First Two Weeks of a New Project
• Markup vs Margin for Contractors: Price a Job for the Profit You Want
• Browse Small Business