What is a cash flow forecast?
Understanding your cash flow is vital for your business (and your stress levels) and forecasting plays an important role in your finances. Put simply, a cash flow forecast is a document that helps estimate the amount of money that’ll move in and out of your business. In addition it includes your projected income and expenses. Cash flow forecasts typically cover the next 12 months but can also be used for shorter periods of time – like a week, month or quarter.
Why use a cash flow forecast?
Cash flow forecasts are primarily used to help the business owners plan how much cash they’ll need in the future.
Cash flow forecasts can:
- Show you whether your business is meeting expectations. By comparing your actual income and expenses with your forecast, you can see which areas of your business are over or under performing and act accordingly.
- Help you budget for equipment purchases or identity the need for a business loan, which is very useful for your tax preparation.
- Be adapted to see the effects of planned business changes. If you’re planning on hiring, for example, you can add the salary and related costs to see how it’ll affect your business’s financial position.
Running hypothetical business changes through your cash flow forecast is a great way to predict their impact. If you can predict any cash surpluses or shortages on the horizon, you’ll be able to make more informed business decisions.
You can also run best and worst-case scenarios to see how your business will cope in difficult times, which is really important right now, or what you’d be able to afford to do if trading is better than projected.
If a business runs out of cash (and can’t get a loan or funding) it will become insolvent. This means that its liabilities exceed its assets, unless its ongoing revenue covers its debt obligations. With some effective cash flow forecasting, however, things shouldn’t get to that stage.
What should be included in a cash flow forecast?
There are three key elements to include in a cash flow forecast: your estimated likely sales, projected payment timings, and your projected costs.
Likely sales
Firstly, you need to estimate your likely sales for the weeks or months covered by your cash flow forecast. The easiest way to do this is to look at your sales history from the last few years. Take note of any seasonal patterns, the impact of promotions you have run in those months and the state of the market now.
Projected payment timings
Once your estimated sales are in place, you need to add in when you expect payments to be received.
Projected costs
So now your cash flow forecast shows you how much income you expect, and when you expect to receive that income you need to estimate your outgoings.
Your business will likely have fixed and variable costs, and both will need including.
- Fixed costs include rent and salaries and will stay the same regardless of how much you earn. Add these dates and projected amounts, including bills, fees, memberships and tax payments.
- Variable costs are the opposite – they’re usually dependent on the sales you make. For example, stock or raw materials. In this instance, you can use your likely sales to predict how much these costs will be. Remember that for the likely sales, there will be a timing difference between paying for stock and raw materials and receiving the payments for the sales
Cash flow forecasts are easy to prepare and the key is to keep them up-to-date and relevant.
Why are cash flow forecasts important?
Accurate and timely cash flow forecasting is important for a number of reasons:
- By forecasting your income and budgeting accordingly, you can ensure suppliers and employees are paid on time. This’ll help avoid situations like losing a supplier or employees becoming disengaged.
- By calculating how much cash the business will have at the start of the month, cash flow forecasts can act as an early warning for future issues. This can help identify the need for a loan or overdraft far in advance.
- Banks, investors and so on will usually want to examine a business’s cash flow forecast (among other documents before investing in them or providing a loan. A professional and thorough cash flow forecast is a great way to win over external stakeholders. The Importance of Financial Management.
Steps to Create a Cash Flow Forecast
Download the Transaction History from Your Bank Account
Login to your business bank account and export 13 week’s transactions, starting from say 1st January 2020 in this case, as a CSV file. Make a note of the bank balance on 1st January in this case, the opening balance. The following examples show 10 weeks transactions for readability however 13 weeks is a better sample as it will include any quarterly payments or receipts. Open the file in Excel and you will have a file that looks something like this:
Add a category to each of the transaction line for example: Fixed Cost; Variable Cost; Regular Customer Receipt; New Business Receipt; Loan; Wages etc.
Add a Week Number column and a week number to each transaction by typing =WEEKNUM(A2) in cell E2
Double click the square at the bottom right hand corner of cell E2 to fill all of the rows with the appropriate week number.
Click the Insert Tab and select all of the data in the Worksheet.
Click Pivot Table and you will see the data range you have selected and in this case, the Pivot Table will be displayed on a new Worksheet. You can display the Pivot Table on the same Worksheet as the data if you prefer by selecting Existing Worksheet and specifying the top left cell location.
Click OK and the PivotTable Fields dialog box will be displayed. Do the following actions:
- Drag Amount to Values
- Drag Week no. to Columns
- Drag Category to rows
The Pivot Table will be created where you specified and you have now created a Cash summary by week.
Now add the opening balance that you recorded when you downloaded the bank transactions to cell B28.
Cell B29 = B28- B25
Cell C28 = B29 and so on
Once you have done this for all columns, check the weekly opening balances tally with the values in your bank account.
Now you can create the forecast using the actuals as a guide by averaging and taking seasonality into consideration:
Remember that the forecast Customer Receipts are based on your sales forecast plus the payment terms so for example if you invoice terms are 30 days from date of invoice, your receipts will be one month after the sale whereas your supplier payments may need to be made before the sale. If you calculate the cost of supplier payments as a percentage of sales revenue in the actuals, you can forecast supplier payments as the same percentage of forecast sales for the cash flow forecast, just remember the timing differences e.g. the supplier payments in week 11 are that percentage of the customer receipts in week 15. In this example, the cash flow forecast highlights the cash shortage in weeks 18 and 19.
Extend the forecast to 12 months and update it weekly with actuals and then extend the forecast by one week, that way you will always have a rolling 12-month cash flow forecast to help you manage your business with no surprises.
Adapted from an original article by Jamie Goral, an amazing ActionCOACH from the South of England.
