EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
As a business owner, financial forecasting is an essential part of keeping on top of your finances. With Excel, monitoring key elements such as revenues, expenses, profits, and cash flows can be made really simple – even if you don’t have any prior experience.
By utilising a cashflow forecast template you can gain quick insights into the financial health of your business, helping you to make better decisions based on the actual money coming in and out.
In this article, I’ll walk you through how to implement an ACE, simple, yet effective business forecasting spreadsheet into your business to help you monitor, track, and plan your finances across the year.
A financial forecast is essential for businesses as it provides a proactive tool to anticipate and plan for future financial scenarios, helping to mitigate risks and capitalise on opportunities.
While profitability is a key indicator of a business’s success, it’s equally important to remember that the heart of a company’s ongoing operations lies in its ability to manage cash flow effectively. Without this, even a profitable business might struggle to maintain its operational viability.
Luckily for you, I’ve put together a FREE cashflow forecast template to help you get started.
Download the FREE Excel ACE Forecasting Template
Watch the video, or follow the instructions below to learn how to use the ACE Forecasting Template.
The purpose of this template is to give you a very simple way to do some forecasting of both profit AND cash flow.
It’s a very simplistic template – it’s not complex in taking into account things like VAT (if you need anything more complex, let me know and I’ll be happy to help), but the ACE Forecasting Template has been designed to help you with basic financial information.
When you open the template, you’ll see there are a few different sheets which are colour-coded (I am obsessed with using colours in spreadsheets!) – the green sheets are where you’ll input your data, and the red sheets are where you’ll see changes as a result of entering that data.
The blue sheet contains further information and instructions to help you with using the template. (I recommend printing this out to give you full guidance as you use the file.)
When filling in your forecast, you’ll always start with your expense sheet (this is the first of the green sheets).
The cells on the sheet are also colour-coded (and this is the same throughout the template). Here’s what the colours mean:
On the Expenses sheet, you’ll plan your monthly expenses. In the first column, list any expenses that your business will incur monthly.
In the second column you’ll need to choose the payment terms in days from the picklist (these are available to choose as 0, 30, 60 or 90 days).
These payment terms are from when the expense is incurred to when you will physically have to hand over the cash (i.e. if you are making an immediate payment, you would choose 0. If your purchase is payable in a month you will choose 30, and so on).
In the third column (the first dated column) you’ll need to input the value of each of the expenses. For example, if you’ve listed your expense as ‘Salaries’ in the column under January you will enter the sum of salaries to be paid in that month.
NOTE: If this is a recurring charge, you can just copy and paste the charge across all of the months in the row. If the charge changes month to month, you will need to update this across the row accordingly. If you have months where you don’t pay anything for a particular expense, then just leave the cell empty for that particular month.
As you input your expenses, you’ll see the formulas in the yellow column at the end are calculating the yearly totals for each of your expenses. You’ll also see in the top row of the sheet that the total value of expenses for each month is calculated and displayed.
Next, we’re going to move into the ‘Sales’ sheet. This sheet is laid out the same in terms of colour coding and operates in a similar way to the ‘Expenses’ sheets but this time should be used to track your INCOME across the year.
Use an individual row for each Product/Service/Customer/Area (as relevant) of your income.
Once you’ve filled in this sheet, you’ll now have populated both your total sales and expenses (income and outgoings). This is all your input complete, we can now move on to our red sheets (which automatically update) to see the outcome.
The P&L Report sheet shows your profit and loss across the year. The whole of this sheet is linked to the Expenses and Sales sheets by formula, and you can’t edit anything.
The sheet will display the following information for you:
This sheet will give you an overview of your profit and loss across the year.
The ‘Cashflow’ sheet is designed to help you track where money is throughout the business. Remember, you may have marked some expenses or payments to be made in 30, 60 or 90 days.
If you have a payment with terms of 30 days, this means that whilst you might have agreed to this in January, you won’t receive the money in to your account until February.
The ‘Cashflow’ sheet analyses your income and expenses in the same way as your P&L report but takes into account when the cash is ACTUALLY going to come through.
This helps you to keep a closer eye on the money you’ll have in the business during each month across the year.
There’s one green cell that you can edit on this sheet which you’ll find near the bottom in the ‘Opening Bank Balance’ row.
In this cell you’ll need to input your opening bank balance for the year (how much money you started the year with) for the sheet to accurately calculate your opening and closing balances for each month based on your actual cash flow throughout the year. Your opening bank balance at the start of January may want to include your overdraft limit.
Finally, in the ‘Mini Dashboard’ tab, you’ll find two charts that show your Operating Profit and your cash flow over the year.
These are simply to give you a visual representation of how each of these things differs across the year so that you can identify peaks and troughs in your finances throughout the year at a glance.
So there you have it – how to create a simple and effective 12-month business forecasting spreadsheet with help from the ACE Forecasting Template.
The simplicity of this cashflow forecast template means you can continuously go back to the Sales & Expenses sheets and make changes, then simply review the impact the changes have had on the Report until you’re happy with them.
EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
