What is a Cash Flow Forecast? Why you need one … and a simple guide to creating one for your business.

What is a cash flow forecast?

In simple terms, a cash flow forecast is a document where you estimate the amount of money that will move in and out of your business, including your projected income and expenses.

It is vital when running a business to understand your cash flow, to help you plan how much cash you will need in the future. Typically, your cash flow forecast will cover the next 12 months but can also be broken down into shorter time periods of a week or a month, if appropriate.

Cash flow forecasts can be straightforward to prepare, the key is to keep them up-to-date and relevant.

What can a cash flow forecast do for you?

Is your business meeting expectations? Compare your actual income and expenses against your forecast — you will see which areas of your business are over- or under-performing and can then react accordingly … and more proactively.

Do you need help with budgeting? Your cash flow forecast can help you to identify the need for a business loan or to allocate funds for equipment or other business purchases. This can be very useful for your tax preparation.

What If? You can adapt your forecast to see the potential effects of any future business changes. For example, if you plan to hire a new employee in the next 12 months, you can add the potential salary and related costs to the forecast to see how it will 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 better-informed business decisions.

You can also run best- and worst-case scenarios to see how your business will cope in difficult times, or what you’d be able to afford to do if trading turns out better than projected.

If your 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 need to get to that stage.

What should I include in my 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.

  1. Likely sales — 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 ran in those months
  • the state of the market now
  1. Projected payment timings — Once your estimated sales are in place, you need to add in when you expect the ensuing payments to be received.
  1. Projected costs — Your business will likely have fixed and variable costs, and both will need to be included.
  • Fixed costs such as rent and salaries. These figures will stay the same regardless of how much you earn. Add their 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, for the likely sales, there will be a timing difference between paying for stock and raw materials and receiving the payments for the sales.

The Technical Bit — Steps to Create a Cash Flow Forecast

Don’t be put off by all the diagrams and instructions, they are here to help! Follow these steps at your own pace to create a Cash Flow Forecast for your business.

  1. Login to your business bank account and export 13 weeks’ transactions (starting from 1st January 2020 in the illustrations) as a CSV file.
  1. Make a note of the bank balance on 1st January, the opening balance. (NB: The following examples show 10 weeks of 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:

  1. Add a Category to each of the transaction rows, for example:
  • Fixed Cost
  • Variable Cost
  • Regular Customer Receipt
  • New Business Receipt
  • Loan
  • Wages

  1. Add a Week Number column and a week number to each transaction by typing =WEEKNUM(A2) in cell E2

  1. Double-click the bottom right-hand corner of Cell E2 to fill all the rows with the appropriate week number.

  1. Click the Insert Tab and select all of the data in the Worksheet.

  1. Click Pivot Table and you will see the data range you have selected and. decide where you want the Pivot Table to be displayed, in this example 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.

 

  1. Click OK and the PivotTable Fields dialogue box will be displayed.

Do the following actions:

  1. Drag Amount to Values
  2. Drag Week no. to Columns
  3. Drag Category to Rows

The Pivot Table will be created where you specified, and you have now created a Cash Summary by week.

  1. 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 that 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 your 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 % of sales revenue in the actuals, you can forecast supplier payments as the same % of forecast sales for your cash flow forecast, just remember the timing differences: eg the supplier payments in week 11 are that % of the customer receipts in week 15.

In this example, the cash flow forecast highlights the cash shortage in weeks 14, 17 and 18.

And is that it?

Nearly! A few last things:

  • Extend the forecast to 12 months
  • Update it weekly with actuals
  • Extend the forecast by one week

This way you will always have a rolling 12-month cash flow forecast to help you manage your business … with no surprises. 

Why is a cash flow forecast so important?

Accurate and timely cash flow forecasting is important for so many reasons:

  • By forecasting your income and budgeting accordingly, you can ensure suppliers and/or employees are paid on time. This will help you avoid situations like losing a supplier or your 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 other parties involved 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.

If this is all still a little mind blowing and you could do with some extra help, give me a call! Or click here to arrange a free coaching session where I can further guide you through the process. https://brendahector.actioncoach.co.uk/programmes/