Cash Flow Forecasting in Excel: A Practical Guide for Australian SMEs

Introduction

Every business owner knows cash flow matters, but most cash flow forecasts fall into two traps: they're either too thin to be useful, or so detailed they're never updated after month one.

This guide walks through building a practical 13-week rolling cash flow forecast that takes 30 minutes a week to maintain and gives you real visibility - not just a number to show the bank.

Why 13 Weeks?

A 13-week rolling forecast hits the sweet spot. Monthly forecasts miss the short-term wrinkles that kill businesses (GST payments, quarterly BAS, supplier payment cycles). Daily forecasts are overkill for most SMEs. Weekly, rolling forward 13 weeks, gives you enough runway to see problems coming and enough detail to act.

Every week you drop the oldest week and add a new one. The forecast horizon stays constant. This is standard practice in corporate finance and works just as well for a $2M revenue business.

Setting Up the Model

What You Need

• Your last 3 months of bank statements (exported as CSV)
• A list of recurring payments by amount and date (rent, payroll, loan repayments, subscriptions)
• Your accounts receivable ageing (who owes you what and when they're due to pay)
• Any known one-off items coming up (quarterly BAS, annual insurance, equipment purchases)

The Structure

Create four sheets:

1. Inputs - All assumptions in one place: opening balance, revenue timing, expense timing, payment terms
2. Forecast - The 13-week grid. Each column is a week, each row is a line item
3. Dashboard - Key metrics: closing balance, minimum balance, days until cash-out
4. Actuals - As each week closes, paste in the real numbers to track variance

Key Formulas

Opening balance: Link from the previous week's closing balance.

Cash inflows: Most SMEs don't get paid the day they invoice. Build in your typical payment delay:

= IF(InvoiceDate + PaymentTerms <= WeekEnd, InvoiceAmount, 0)

If you invoice on 30-day terms and the customer pays in 35 days on average, use 35 days.

Cash outflows: The same logic - an expense incurred in week 1 might be paid in week 3.

Closing balance:

= OpeningBalance + TotalInflows - TotalOutflows

Minimum cash test:

= IF(ClosingBalance < MinimumRequired, "Action Required", "OK")

Adding a Sensitivity Layer

A single forecast is a guess dressed up as a plan. Build three scenarios:

If the worst case shows negative cash in any week, you have a real risk. That's your trigger to cut costs, negotiate better terms with suppliers, or arrange a facility before you need it.

Set up a data table in Excel to automate this - it takes 5 minutes and saves you from manually swapping assumptions.

What I've Found Works for Australian SMEs

After working through cash flow forecasting with dozens of businesses, a few patterns stand out:

The BAS cycle is the biggest single cash shock. Most SMEs treat BAS as an annual surprise rather than a quarterly certainty. Add a line item in your forecast for the month after each quarter end. Even a rough estimate ($10K per quarter for a typical service business) is better than missing it.

Payroll timing matters more than you think. If you pay weekly on Friday and your major client pays on 60-day terms, you can burn through cash fast even when the business is profitable. The forecast makes this visible.

Actuals vs forecast data is the most valuable output. The dashboard comparison tells you whether your assumptions are getting better or worse over time. If your revenue forecast is consistently 15% optimistic, adjust the base case.

Real Example: A Sydney Construction Subcontractor

A 12-person subcontracting business was turning over $2.8M but had zero cash flow visibility. The owner approved jobs based on margin without considering when the cash would arrive.

After implementing a 13-week rolling forecast, they discovered:

• Two major clients averaged 68-day payment terms (industry standard was 45)
• The next BAS quarter was going to hit during a 4-week revenue drought
• They had $47K sitting in retentions that could be billed immediately

The fix was straightforward - renegotiate terms with the two slow payers and start billing retentions monthly instead of at project completion. Within 8 weeks, the minimum cash balance went from negative to $32K.

Note: This case is illustrative. Actual results depend on your specific payment cycles and client relationships.

Frequently Asked Questions

How often should I update my cash flow forecast?

Weekly. Set a 30-minute recurring calendar slot. The first few weeks take longer while you build the model.

What's the minimum data I need to start?

Three months of bank statements and a list of your recurring payments. The forecast gets more accurate as you build history.

Can I connect this to my accounting software?

Yes. Use Power Query to pull data from Xero, MYOB, or QuickBooks exports. Set up a data connection and the forecast updates automatically.

What if my forecast shows a shortfall?

Act early. You have more options 8 weeks out than 2 weeks out. Supplier payment extensions, invoice factoring, or a temporary overdraft are all easier to arrange when you're not in crisis mode.

Conclusion

A rolling 13-week cash flow forecast is one of the highest-ROI financial tools you can build. It takes an hour to set up and 30 minutes a week to maintain. For that investment, you get visibility on cash shortfalls before they happen, better negotiation leverage with suppliers and customers, and a clear picture of whether your business is actually generating cash - not just revenue.