Cash Flow Forecasting for Growing SMEs: A Practical Excel Framework

Introduction

Cash flow is the most common reason Australian SMEs fail. Not lack of sales, not bad products-running out of cash while waiting for money that's owed. A profitable business can still go under if its revenue arrives three months after its expenses are due.

The solution isn't complicated. It's a rolling cash flow forecast: a simple Excel model that projects cash in and cash out for the next 13 weeks, updated weekly. This article walks through how to build one, what to include, and how to use it to make better decisions.

Why 13 Weeks?

Most SME owners look at their bank balance and think they know their cash position. But the bank balance is a snapshot of the past. The question that matters is: what will my cash position be in four weeks, when the BAS is due and the quarterly rent hits?

A 13-week rolling forecast gives you visibility into the near-term future-long enough to take action on a looming shortfall, short enough that the assumptions are reasonably accurate.

Building the Forecast

Step 1: Set Up Your Timeline

Create a row for each week. Label them with the Monday date of each week. Use 13 columns, one per week, starting from the current week.

Leave a column to the left for category labels, and a "Notes" column on the far right.

Step 2: Capture Your Opening Balance

Row 1 is your current bank balance. This is the only hard number in the model-everything else is a forecast. Update it weekly.

Step 3: Forecast Cash Inflows

List every source of cash inflow with sufficient detail to be useful:

• Customer payments (by major customer if they're large enough)
• Recurring revenue (subscriptions, retainers)
• One-off sales or projects expected to close
• GST refunds (if applicable)
• Any other expected receipts (loan drawdowns, asset sales)

For each item, estimate the amount and the week it will arrive. Be conservative. If a customer typically pays in 30 days, assume 45. If a deal is 70% likely, discount it or flag it separately.

Step 4: Forecast Cash Outflows

List every expected payment:

• Payroll and superannuation
• Rent and utilities
• Supplier invoices (by major supplier)
• BAS and tax payments
• Loan repayments
• Marketing spend
• Software subscriptions
• Any one-off purchases or CAPEX

Be thorough. The most common forecasting error is missing irregular expenses-quarterly insurance premiums, annual software licences, Christmas party costs.

Step 5: Calculate Net Cash Flow and Closing Balance

For each week:

• Total inflows = sum of all expected receipts
• Total outflows = sum of all expected payments
• Net cash flow = inflows minus outflows
• Closing balance = opening balance + net cash flow (which becomes next week's opening balance)

Add conditional formatting: highlight any week where the closing balance drops below your minimum threshold (e.g., two weeks of operating expenses) in red.

What to Do With the Forecast

Scenario Planning

The forecast is most useful when you test scenarios. Create three versions:

1. Base case - what you expect to happen
2. Upside - customers pay on time, new deals close, no surprises
3. Downside - payments are 14 days late, one major customer delays, an unexpected expense appears

Compare them. If even the base case shows a red week, you need to act now-not when the money runs out.

Actions to Take on a Red Week

• Call slow-paying customers and negotiate earlier payment
• Ask suppliers for extended terms (30 days becomes 45)
• Delay non-essential spend (recruitment freeze, postpone CAPEX)
• Draw down a line of credit or overdraft facility
• Invoice earlier or more frequently

The earlier you spot the problem, the more options you have. A red week in week 11 gives you ten weeks to fix it. A red week you discover because the payment bounces gives you zero.

Common Mistakes

Optimistic timing. Everyone believes their customers will pay on time. Build your forecast on the assumption that they'll pay late. You can always revise upward.

Missing irregular items. BAS, insurance, annual subscriptions, staff bonuses, equipment maintenance-these aren't weekly expenses but they're real when they hit.

Not updating the forecast. A cash flow forecast is a living document. If you build it once and never touch it again, it becomes useless within three weeks. Set a recurring 30-minute appointment every Monday morning.

Confusing profit with cash. A big sale this month doesn't help if the customer pays in 60 days. Your forecast should be based on when money actually lands in your account, not when the invoice is issued.

A Simple Template Structure

Your workbook can have three sheets:

1. Dashboard - shows the 13-week view with a line chart of projected closing balance
2. Input - where you enter inflows and outflows by week
3. Reference - lists known dates (BAS quarters, insurance renewals, annual leave periods) so you don't forget

Keep it simple. A good cash flow forecast doesn't need macros, VLOOKUPs, or complex formulas. It needs accurate inputs and a weekly update habit.

Conclusion

Cash flow forecasting isn't complicated accounting-it's practical business management. A 13-week rolling forecast in Excel costs nothing to build and can save you from the most common cause of business failure. The discipline is in the weekly update, not the initial setup.

For more tools and templates to manage your business finances, visit ExcelWiz.com.au.