Forecasting Sales and Cash Flow in Excel for Australian Small Businesses

Running a small business in Australia often means wearing every hat at once, from chasing invoices in Parramatta to fixing a faulty espresso machine in a Fitzroy café before the morning rush. With that kind of pace, planning tomorrow's revenue can feel like guesswork. A simple forecasting model built in Excel can replace that guesswork with a clear picture of where the business is heading, month by month.

Spreadsheets are still the most accessible forecasting tool available. They work on any laptop, they don't require a monthly subscription, and most operators in Brisbane, Adelaide, or Perth already have a working knowledge of them. The trick is knowing which numbers to enter, how to structure the layout, and how to keep the model honest as conditions change.

Laying Out a Forecasting Worksheet That Makes Sense

A forecasting sheet falls apart quickly if the columns and rows are scattered. The first job is to decide on a time axis. Most Australian operators find monthly columns work well, particularly because monthly figures line up neatly with the lodgement cycle for Business Activity Statements. A 12-month sheet, with one column per month and a totals column on the right, gives you a full financial year of outlook and slots straight into EOFY reporting on 30 June.

Once the time axis is set, build three stacked blocks. The top block is revenue. The middle block is cost of goods and direct costs. The bottom block is fixed overheads. Keeping these blocks separate makes the cash forecast easier to read and avoids mixing a one-off purchase with a recurring bill like rent.

Finally, add a small assumptions box near the top of the sheet. This is where the model's driving inputs live, such as average sale value, expected growth rate, and the supplier price rise you have been told about in writing. Linking the rest of the sheet back to this box means you can change one cell and the whole forecast responds automatically.

Starter Columns for a One-Page Forecast

• Month-by-month columns running from July through June to match the financial year.
• A column for actuals that can be filled in as real data arrives.
• A variance column comparing forecast to actuals in dollar terms.
• A short assumptions panel above the table for growth rates and price inputs.

Pulling Real Numbers from the Books

A forecast is only as good as the data feeding it. Before touching Excel, pull together the past six to twelve months of sales, the supplier invoices already paid, and the wage records required under the Fair Work Act. These documents are also what the ATO expects to see during a review, so working with them regularly is good discipline.

If the bookkeeping is done through a cloud platform, export the relevant reports as CSV files. From there, copy and paste the totals into the assumptions box of the new forecasting sheet. Melbourne retailers often find this easier at the end of a quiet Monday, when the till has been reconciled and the day's takings are settled.

A useful habit is to keep a small "actuals" tab alongside the forecast. Every month, drop the real numbers next to the predicted ones. The gap tells a story, whether that is a slow trading Tuesday in winter, a sudden rent rise, or the impact of a new competitor down the road in Surry Hills. Reviewing those gaps once a month is the single most powerful habit in small-business forecasting.

Building a Sales Forecast You Can Trust

Revenue forecasting comes down to two things: how many sales you expect, and at what price. A Brisbane bookkeeping firm might use billable hours per week multiplied by an hourly rate. A Perth fishing charter might use trips per week times the average ticket, adjusted for the closed season.

Apply a seasonal factor to each month rather than spreading revenue evenly across the year. Australian retail swings hard around Easter, Anzac Day promotions, and the lead-up to Christmas, while tradies in regional NSW often see a winter slowdown the spreadsheet should reflect. One simple way is to multiply the average monthly revenue by a percentage, such as 0.85 for a slow July and 1.20 for a busy December.

Round the figures, but not too aggressively. Forecasts presented to a bank or investor look more credible when they show tidy amounts, yet rounding inside the model can hide meaningful differences. Keep two decimal places in the working sheet and round only on the summary page. Over a twelve-month horizon, those small fractions add up to thousands of dollars.

Modelling Costs, Wages and Overheads

Costs deserve the same care as revenue. Direct costs, such as ingredients for a Sydney sandwich bar or stock for an Adelaide boutique, usually move in proportion to sales. Fixed overheads, like rent, insurance, software, and internet, do not. The Excel model should reflect that difference, with cost-of-goods rows calculated as a percentage of revenue and overheads entered as monthly dollar amounts.

Wages need particular attention in Australia. Award rates, superannuation contributions, and leave loading all sit on top of the base hourly figure. A modest café in Collingwood paying casual staff across weekend shifts can find labour running at 32 to 35 percent of revenue, earning it its own row in the spreadsheet. Bury those costs inside a generic "expenses" line and the figures stay hidden until the BAS is overdue.

It also pays to lift out one-off costs as separate lines. Think of the annual insurance renewal in March, the website rebuild every two years, or new point-of-sale hardware needed before EOFY next year. Giving them their own row stops them being quietly absorbed into the monthly average, where cash surprises often come from.

Habits That Keep the Forecast Honest

• Update assumptions whenever a supplier confirms a price change, rather than waiting until month-end.
• Compare forecast to actuals on the same day each month so the review becomes routine.
• Keep dated copies of every version of the spreadsheet, not just relying on autosave.
• Re-check the model when something major shifts, such as a new lease, a new staff member, or a new ATO rule.

Stress-Testing the Numbers with Scenarios

No forecast survives contact with reality unchanged, which is why a base case alone is not enough. Build three scenarios: a downside, a base, and an upside. The downside might assume sales drop 15 percent while a key supplier adds 8 percent. The base case rolls forward from current trends. The upside assumes a new contract lands or that a competitor on the same street in Newtown closes down.

Excel's Scenario Manager can run these switches automatically, but a plain approach works just as well: copy the assumptions tab into Downside and Upside tabs and adjust the inputs by hand. The difference between the three sets of outputs is the range the business should plan against.

Cash flow is where scenarios earn their value. A model predicting healthy profit can still run out of cash if debtors stretch their payment terms. Build a simple debtor-days assumption into each scenario, then watch how the bank balance shifts through February and March, when tourism slows on the coast and many small operators feel the squeeze.

Reviewing and Refreshing the Forecast Regularly

A forecasting sheet sitting on a desktop is just an artefact. It becomes a plan when reviewed regularly and updated with what actually happened. A practical rhythm is to set aside an hour each Monday to log the previous week's sales and update the relevant column. Doing it weekly, rather than monthly, catches errors early and keeps the figures fresh enough to guide decisions on hiring, ordering stock, or chasing overdue accounts.

Pair the spreadsheet with one written page of notes. What is the business trying to achieve over the next quarter? Which cost is climbing fastest? Which customer is overdue and by how much? Answering those questions on paper turns the figures into a story staff and family can follow. Owners after a guided introduction often attend the free workshops on offer through SCORE Nashville, where mentors walk through real forecasting case studies in plain English.

When the model has been running for two or three months, hand a printed copy to someone outside the business and ask them to spot the assumptions that look weak. A second pair of eyes will usually find the cost row that has crept up too quickly, or the seasonal factor that does not match the shop floor. Adjust the row, version the file, and keep moving.

For operators wanting a community to test ideas against, peer networks offer a useful next step. The small business chamber hosts forums where owners in Sydney, Brisbane, and beyond swap templates and review each other's assumptions. Sign up this week, share a draft of the spreadsheet with a peer group, and let the model earn its keep. Pair that input with the discipline outlined above, and the business will head into EOFY with a forecast that genuinely informs decisions rather than one that simply gathers dust. Good forecasting is not about predicting the future perfectly. It is about knowing early enough which way the wind is blowing so the business can adjust before it is too late.