How do I build a cash flow forecast from scratch?
![cash-flow-management.jpg](https://media.bethebusiness.com/original_images/cash-flow-management.jpg)
Cash flow refers to the amount of money going in and out of the business. A cash flow forecast allows businesses to track how cash will move around over a set period of time.
Cash flow forecasts are an invaluable part of business planning. A forecast gives you a better understanding of how your business works and where there are opportunities for either cost savings or growth.
This guide will help you improve the way you forecast your company’s cash flow.
How to structure a cash flow forecast
It's worth starting with a refresher on the key line items on a cash flow forecast, their meanings and the implications for forecasting.
The key line items include:
- Income: Payments made to your business. Forecasts need to be based on a robust understanding of sales performance
- Operating expenses: The money your business spends. Monitoring the biggest types of spending helps you understand what’s likely to happen in the future
- VAT movements: The amount of VAT you collect and pay. It’s important to have an accurate estimate of VAT owed because VAT is normally paid quarterly, creating a large liability
- Net cash movements: The balance of cash going in and out of the business in a given period (your income minus your outgoings)
- Summary: The opening and closing balance of your bank account
Forecasting income
It makes sense to forecast your company’s income first. There are two main approaches:
- Create a cash flow report and build your sales forecast from it
- Start with a separate profit and loss forecast and apply the assumptions to your cash flow forecast
Larger businesses tend to start with a profit and loss forecast, but there’s no right or wrong approach. The profit and loss forecast shows what the business is trying to achieve in the long term, potentially covering one to three years. Building a sales forecast within a cash flow report will likely lead to a shorter-term forecast.
Forecasting revenue
Think about what will drive changes in sales:
- Are your revenue lines likely to change? You may be planning to stop offering a service or launch a new product
- Are you investing in marketing or sales resources? What impact will this have and over what time frame?
- What external factors may have an impact? It can be difficult to include these in a forecast, but consider any big changes such as new legislation or a significant downturn in the economy
- What impact does seasonality have on your business?
It’s helpful to forecast sales by revenue line because each of the main business activities may behave differently. For example, an IT business might separate consultancy and hardware sales.
Add a year’s trading history to provide context and identify any seasonality in sales. Including a moving average will help you understand the underlying trend if your sales vary a lot month to month.
Creating a separate table that shows annual revenue will help you think about long-term trends.
Evaluating monthly and annual historic data and discussing what will impact performance will help you create assumptions for the coming months and years.
Adding the details
Your sales forecast needs to be added to your cash flow forecast. Formulas such as VLOOKUP and SUMIF can import the data automatically, saving time and avoiding transcription errors.
If your starting point is a profit and loss forecast, you need to add cash movements to show the differences between when invoices are raised and the payment is received. Monitoring payment terms is an important aspect of cash flow forecasting – reducing the amount of time you wait to get paid frees up cash to invest in the business.
Checking sales forecasts
Your sales forecasting process will improve over time. Make sure you’re doing retrospective analysis; how accurate were your previous forecasts and why did any significant differences occur?
Forecasting outgoings
The next step is to look at your outgoings.
The amount of detail about operating expenses contained in your cash flow forecast ranges from a simple outgoings line to including each type of expense. In a simplified version, it’s likely salary costs will be recorded separately as they don’t incur VAT.
Expenses are either fixed (incurred no matter how much you sell) or variable (based on sales volumes).
Forecasting a company’s fixed costs
Fixed costs include expenses like:
- Rent
- Salaries
- Accountancy fees
- Telephone costs
Project these lines forwards over the period you're forecasting, taking into account any change of circumstances, like switching suppliers or closing a branch.
If you’re working from a profit and loss forecast, payments will be represented in the period the cost was incurred, but this may not match the payment.
For example, you may pay for your insurance annually, so it appears in a single month of your cash flow forecast, but it’s divided over 12 months on your profit and loss report.
Forecasting a company’s variable costs
Variable costs change depending on sales volumes and include things like raw materials and parts. Examine the relationship between sales and costs; how might it change in the future? For example, a manufacturer might lower the per unit cost when orders reach a certain volume.
The level of detail that you put into forecasting different types of outgoings should be relative to the size of the expense; put more thought into larger items.
Keeping on top of VAT payments
VAT is normally paid quarterly and is a significant outgoing. This means you need a robust method for calculating how much you owe.
It’s helpful to record the gross and net amounts and VAT in your sales forecast, so that it can easily be imported to your main cash flow report (remember, VAT payments will be due at different points to payments).
If you’re working from a profit and loss forecast, you will need to adjust for the difference between when an invoice is raised and when it’s likely to be paid.
Develop assumptions for how long it takes customers to pay. For example, you could assume 60 per cent of customers will pay within the 30 day payment terms and 40 per cent within 60 days. These assumptions can be improved over time.
A summary of calculations and impact
The end of a cash flow forecast shows a summary of the calculations and impact on the amount of cash you have in the bank, including:
- Opening Balance: The money you have in the bank at the start of the month
- Net cash movement: Income minus operating expenses
- Closing Balance: The amount left in your bank account at the end of the month
Consider using Excel’s conditional formatting function to highlight any month in which the bank balance is expected to go below zero. It’s normal for a business to have a certain amount of runway – the amount of time they have before they run out of money – and the colour coding will help you visualise this.
It’s common to save money for corporation tax every month. It may make sense to put money aside for seasonal expenses too. You can add a line in the summary section to minus these amounts and add them back in later in the year, transferring the money to a savings account.
At the end of each forecasting period, you normally update key assumptions with the actual figures. The process helps you learn and improve; look out for any differences and think about how you can develop the process.
The aim of a cash flow forecast is to look forward, so updating the closing balance to the precise figure makes sure you stay on track. The closing balance doesn’t have to perfectly match what you have in the bank, but it should be reasonably close.