Cash Flow Forecasting
Forecasting the Viability of a Financial Decision
Cash Flow forecasts help you to build a model of the way in which cash moves within a project or organization. They help you to predict whether the sales or income you forecast will cover the costs of operation. They also allow you to analyze whether a project will be sufficiently profitable to justify the effort put into it.
Cash flow forecasts can also be useful for analyzing your own personal finances. This is useful when you are about to make difficult financial decisions.
By carrying out a Cash Flow forecast on a spreadsheet package you can investigate the impact of changing factors within the forecast. If you have structured the spreadsheet correctly then you will be able to see, more or less instantly, the effect that changes will have.
Normally we structure Cash Flow Forecasts in a standard way. This is explained below. Other sorts of forecasting can be carried out with spreadsheets. A good way of structuring these is to firstly analyze the system being forecasted with a system diagram. This system diagram will show the relationships between factors. You can then quantify these relationships, and build a model based on them. The structure of the model will depend on the system being modeled.
How to Use the Tool:
We structure the Cash Flow Forecast as a table. On the table we have columns for each period (normally a month) within the forecast. Rows show individual cash movements such as sales of a product, sales costs, and particular expenses.
We create the table for the forecast in three stages. Refer to the example in Figure 1 (below) as we run through the stages:
1. Set Up Column Headings:
Decide the period of time over which you want to run your forecast, and the length of the periods within it. Typically the forecast will run over 1-2 years, with the periods as months.
Decide the period of time over which you want to run your forecast, and the length of the periods within it. Typically the forecast will run over 1-2 years, with the periods as months.
Head up one column with the title 'Cash Movement'. Then enter the periods of the forecast as the next column headings.
This will give you column headings of, for example, Cash Movement, January, February, March, April.. etc.
2. Set Up Row Titles:
We organize rows into three main groups:
We organize rows into three main groups:
- Income:
These rows show income expected during the period. Set up a separate row for each source of income. Examples might be:
- Sales of ABC product
- Sales of BCD service
- Investment income
- Etc.
Where costs of operation are directly dependent on the amount sold, you may decide to deduct the direct cost of the sales made within this group of rows. Put in a subtotal at the bottom of the group.
- Outgoings:
These rows show all of your costs, itemized by the type of cost. Examples might be:
- Staff salaries
- Payroll taxes
- Stationery
- Telephones
- Etc.
Set up a subtotal at the bottom of this group.
- Totals:
The next row shows the total of the income rows minus the total of the out-going rows for the month. This shows you your profit or loss for the month.
Underneath this, put in a running total. In this row add your profit or loss for the period to the previous running total. This shows your financial position at the end of the period.
3. Estimate values:
By now you should have a table marked out with column headings and row titles. Now fill in the values of the cells on your table. An easy way of doing this is to fill in the first column, and then use the spreadsheet 'Fill… Right' function to copy values across. Then adjust values in the other columns appropriately.
When you are entering projections for sales for a new business, bear in mind you will not sell much until your customers have seen mention of your business several times (often 6 or 7 times). Your estimates for sales will be much more reliable if you base them either on previous years' revenues, on trial marketing, or on good quality market research.
When you are entering values for costs, try, where possible, to base projections on costs from previous years. If this is not possible, base your estimates on real prices quoted. This keeps your estimates as realistic as possible.
4. Calculate!
On most modern spreadsheet packages this will happen automatically, providing you have set up totals correctly as described in section 2. As you enter and change the values of cells within the spreadsheet, you should see that the period totals and running totals change appropriately.
Cash Flow Forecasting is a relatively simple technique for checking the viability of a project. Cost/Benefit Analysis is another. These are good techniques for decisions involving relatively small amounts of money. Where large sums are involved (for example, in financial market transactions), project evaluation can become a complex and sophisticated art, which uses more formal techniques. The fundamentals of this are explained in Principles of Corporate Finance by Richard Brealey and Stewart Myers – this is something of an authority on the subject. The book is reviewed at the top of our right hand side bar.
Example:
A motor home enthusiast has decided that he wants to set up a motor home hire company. He has researched the costs of set up, and estimated the number of weeks of hire he can sell during the year.
Note that he has been quite optimistic in hoping to sell all the weeks of holiday available during the high season of July and August. He will charge the same price as his competitors for a holiday.
He works out the cash flow forecast below:
Click here to see the forecast
Looking at these figures, the enthusiast is very worried - although the business scheme he wants to set up will return a small profit of just under $10,000 at the end of the year, he will be nearly $18,000 in the red at the end of March. Unless he has this amount of spare cash of his own and is prepared to tie it up in the venture, or he is willing to arrange a loan or overdraft facility (which will incur further costs), his proposed business model is not viable.
He may just have saved a lot of time, money and stress by using a Cash Flow Forecast.
Key points:
Cash Flow Forecasts are important tools for investigating whether a project or business is viable. They allow you to experiment with changing factors, to see the impact that this will have. Spreadsheet packages are invaluable for cash flow forecasting.
We set up Cash Flow Forecasts in the following stages:
- Setting out column headings for periods (normally months) during the forecast.
- Setting out three main groups of rows:
- Income rows, with a subtotal
- Expenditure rows, with a subtotal
- Period total and running total rows
- Entering values within cells: Ideally you should do this from real data, or from formal market research information. If this is not possible, then you will have to use the best estimates you can make.
- Calculation
0 comments:
Post a Comment