Predicting the future might seem like guesswork, but for Australian small businesses, it is a vital survival skill. A robust revenue forecast allows you to plan your hiring, manage your cash flow during seasonal dips, and invest in marketing with confidence rather than hope.
At Local Marketing Group, we see many business owners operating 'in the dark.' By following this guide, you will learn how to turn your historical sales data into a roadmap for future success.
Prerequisites: What You’ll Need
Before we dive into the spreadsheets, ensure you have the following ready:- Historical Sales Data: At least 12–24 months of monthly revenue data (from Xero, MYOB, or your CRM).
- Software: Microsoft Excel or Google Sheets.
- Marketing Context: A list of major past events (e.g., a massive EOFY sale or a month where your website was down).
- Patience: Data cleaning often takes longer than the actual modelling!
---
Step 1: Export and Clean Your Data
The first step is getting your data into a workable format. Export your monthly revenue from your accounting software. What you should see: A CSV or Excel file with two primary columns: 'Date' (Month/Year) and 'Revenue' (Total sales for that period). Action: Scan the list for any anomalies. If you had a one-off government grant or a massive asset sale that isn't part of your core business revenue, remove it. We want to forecast operational revenue.Step 2: Choose Your Forecasting Method
There are several ways to forecast, but for most Brisbane-based service or retail businesses, we recommend two starting points:- Straight-line Method: Assumes you will grow at the same constant rate as last year.
- Moving Average: Smooths out random fluctuations by looking at the average of the last 3 or 6 months.
For this guide, we will focus on the Linear Regression method, as it accounts for trends over time more accurately.
Step 3: Set Up Your Spreadsheet Structure
Open a new sheet. Create four columns:- Month Index: (1, 2, 3, etc. — this helps the formula understand time progression).
- Date: (e.g., July 2023, August 2023).
- Actual Revenue: (Your historical figures).
- Forecasted Revenue: (Where our formulas will live).
Step 4: Calculate the Monthly Growth Rate
Before jumping into complex formulas, understand your baseline. Subtract last month's revenue from this month's revenue, then divide by last month's revenue. Formula: =(B2-B1)/B1Do this for the last 12 months and find the average. This gives you a 'sanity check' figure to compare against your model later.
Step 5: Apply the FORECAST Function
In Google Sheets or Excel, there is a powerful built-in tool calledFORECAST.LINEAR or TREND.
Action: If your historical data ends at row 25 (Month 24), go to row 26 (Month 25). In the Forecasted Revenue column, enter:
=FORECAST.LINEAR(A26, C2:C25, A2:A25)
- A26: The new month index you are predicting.
- C2:C25: Your historical revenue figures (The 'Y' range).
- A2:A25: Your historical month indices (The 'X' range).
Step 6: Adjust for Seasonality
Australian businesses often face specific seasonal trends. Think about the 'January slump' or the 'EOFY rush' in June. If your linear forecast says you'll make $50k in January, but every January for three years you've made $30k, you need to apply a Seasonality Index. Action: Calculate the average revenue for each month across several years. Divide that monthly average by the overall monthly average for the year. Multiply your linear forecast by this index to get a 'seasonally adjusted' figure.Step 7: Factor in Your Marketing Pipeline
Data doesn't exist in a vacuum. If you’ve just engaged a Brisbane SEO agency or started a new Google Ads campaign, your historical data might under-predict future results. Action: Create a 'Marketing Multiplier' column. If you expect a 10% increase in leads due to a new campaign, multiply your forecast for those months by 1.1.Step 8: Create 'Best Case' and 'Worst Case' Scenarios
Never rely on a single number. Create three versions of your model:- Conservative: 10% lower than the forecast (for cash flow safety).
- Expected: The raw data forecast.
- Optimistic: 10-15% higher (in case a big project lands).
Step 9: Visualise the Data with a Chart
Numbers in cells are hard to digest. Highlight your 'Date', 'Actual Revenue', and 'Forecasted Revenue' columns and insert a Line Chart. What you should see: A solid line representing the past, and a dotted or different coloured line extending into the future. If the line takes a sudden, unexplained vertical jump, your formula likely has an error.Step 10: Review and Iterate Monthly
A revenue model is a living document. At the end of every month, enter your 'Actual' revenue next to your 'Forecast'. Action: Calculate the 'Variance' (Actual vs Forecast). If you are consistently off by more than 10%, you need to adjust your growth assumptions or your seasonality index.---
Pro Tips for Better Accuracy
- Clean your outliers: If a one-off $100,000 contract landed in March that will never happen again, 'normalise' that month down to a standard figure before running your forecast.
- The 80/20 Rule: Often, 80% of your revenue comes from 20% of your clients. If you lose a major client, manually adjust your forecast immediately rather than waiting for the data to catch up.
- Consider the Economy: Keep an eye on RBA interest rate announcements. For many Australian small businesses, consumer spending drops shortly after a rate hike.
Common Mistakes to Avoid
- Ignoring Churn: If you are a subscription or retainer-based business, you must subtract your average 'churn rate' (lost customers) from your growth rate.
- Over-optimism: It is tempting to project a 20% month-on-month growth because you feel positive. Always let the historical data be the 'anchor' for your expectations.
- Confusing Cash vs. Accrual: Ensure you are forecasting based on when the work is done (Accrual) or when the money hits the bank (Cash), depending on your tax setup. Mixing them will ruin your model.
Troubleshooting
- The formula returns an error (#N/A): Check that your 'X' and 'Y' ranges in the formula are the same length (e.g., both must span 24 rows).
- The forecast is a flat line: This happens if your historical data is too volatile or if you have used the wrong index. Re-check Step 1 to ensure your dates are formatted correctly.
- The numbers look 'Too Good to be True': Check if you have accidentally included GST in some months but not others. Always forecast using GST-exclusive figures to see true business performance.
Next Steps
Now that you have a basic revenue model, the next step is to align your marketing budget with these goals. If your forecast shows a gap between where you are and where you want to be, it’s time to look at your lead generation strategy.Need help making sense of your data or want to accelerate your growth to beat your forecast? Contact the team at Local Marketing Group for a strategy session tailored to your Brisbane business.