Cash Flow Forecasting & How to Create a Cash Flow Forecast with Microsoft Excel

Cash flow forecasting -

How to Create a Cash Flow Forecast using Microsoft Excel - Basic Cash flow Forecasting.

This video takes you how to create simple a 12 month cash flow forecast using Microsoft Excel.

Which is for a start-up small business with stock.

In the video the cash flow forecast includes how to enter the company name, which in this case is Small Business 2U, the heading which is Cash Flow Forecast 2018.

Plus how to centre these headings across columns using ‘Merge and Center.’

The basic cash flow forecast is broken down between the main headings: Cash in-flows; cash out-flows; net in-flows and out-flows; the opening balance; and the closing balance.

Within the cash in-flows section of the cash flow forecast this includes sales, bank loan and owner advances.

The sales uses a formula based upon stock in hand.

The cash out-flows include a number of overhead expenses, like advertising, merchant fees, postage and stationery. Plus cost of goods for resale, equipment purchases and loan repayments.

The goods for resale entries are linked to a formula like sales. In the basic cash flow forecast, each time an entry is made for goods for resale the stock numbers are updated also.

Within the formula for sales and goods for resale I also show you how to ‘fix’ a part of the Excel formula for copying this across to other cells.

Two scenarios are demonstrated, one with positive cash flows for the period, and a second scenarios where an overdraft is required. In the second scenario, another piece of equipment is purchased, which creates an overdraft for two of the months.

This requires for an entry in the overdraft costs too.

One further heading included in the cash out-flows in our basic cash flow forecast is owner drawings.

Also, with cash flow forecasting it’s a good idea to include comments on certain of your entries. For example, a comment is added to the equipment purchase entry to explain what the item is. In this case the equipment purchase is a laptop computer.

Cash flow forecasting is straight forward if you have a basic cash flow forecast like the one demonstrated. However, if you are looking for a more sophisticated cash flow forecast, please take a look at our cash flow forecasting software here:

Cash flow forecasting isn’t only about the cash flow forecast, but is also about forecasting the profit and loss account too. It is also important to incorporate a projected balance sheet as well with your cash flow forecasts, as well as a cash flow statement too if you are a limited company.

If you have any questions on cash flow forecasting or on this basic cash flow forecast, please ask them below in the comments section. I will be happy to answer these free of charge.