Cash flow forecast made easy for small businesses
In our previous article we explained the concept of cash flow management, its importance in business, factors causing cash flow problems and some tips for effective cash flow management.
One of the top tips for effective cash flow management was “Cash Flow Forecasting – A must have”. As discussed in our previous article, though many tools are available in the market to assist businesses in cash flow forecasting, but due to the complexity of usage, lack of accounting knowledge and inability to hire services of professionals, small business owners tend to avoid forecasting cash flows.
In this article, we are providing guidance to small businessmen:
- about the importance of cash flow forecast, and
- how they can use our simple cash flow forecast template with some basic MS Excel skills.
Cash flow forecast and its use
A cash flow forecast is an estimate of your business’s cash inflows and outflows over a given period. It could be a month, quarter or a year but usually covers 12 months. Cash flow forecasting helps businesses predict periods of shortages and surpluses. It helps to determine whether they will be able to meet their financial and tax obligations when they fall due or whether they will have sufficient funds to implement their growth plans.
Components of a cash flow statement
Cash flow forecast can be set up the same way as a cash flow statement which will help you compare forecasts with actual performance. The resulting variance will help you identify the areas which are under performing.
For this very reason, we consider it important to take you through the concept of cash flow statement. Typically, a cash flow statement comprises of three broad activities of a business from which the cash is generated or used.
Operating activities: This represents cash generated from or used in operating cycle of a business for an accounting period. This generally includes cash sales & purchases, salaries, wages, and all other operating expenses of a business paid in cash. The movement in working capital is also taken into account in this section and includes items such as receipts from customers, payments to suppliers, payment of accrued expenses etc. One important thing to remember here is that non-cash items like depreciation, amortization and provisions are ignored while calculating cash from operations.
Investing activities: This represents cash invested in or realized from shares, bonds, or other similar activities. Amounts invested in fixed assets and realized from their sale also form part of investing activities.
Financing activities: Cash received from or paid to shareholders and lenders are accounted for under financing activities.
The above explanation is merely to educate business owners at the very basic level. However, for the sake of simplicity we have not classified cash inflows and outflows under these categories in our cash flow forecast template and simply listed down the items that results in cash inflows and outflows for a business.
How to use the cash flow forecast template
The rows (6 to 56) of the template represent various activities resulting in cash inflows and outflows whereas Columns (B-N) represent the period of those activities, which in this case are 12 months of a financial year.
The starting point for a cash flow forecast is the opening balance of cash & bank balance, which is infact the actual closing cash & bank balance of a previous financial year, month or period, and should be inserted at cell B6 & C6 of the template. Please note that the opening cash and bank balance for the financial year and for the month of July will remain the same. However, the closing balance for the month of July will form the opening balance for the month of August and so on.
The items from R9-R18 represent various Operating, Investing and Financing activities resulting in cash inflows for the business. The values for these activities will be inserted in respective months e.g. to forecast the cash inflows for the month of July, values for different activities should be inserted in cells C9 to C18.
The sum of cash inflows for respective months are calculated at row 19.
The items from row 22 to row 51 represent various Operating, Investing and Financing activities resulting in cash outflows for the business. The values for activities will be inserted in respective months e.g. to forecast the cash outflows for the month of July, values resulting from different activities should be inserted in cells C22 to C51.
The sum of cash outflows for respective months will be calculated at row 52.
Net cash flows at row 54 for each of the month forecasted is sum of row 19 and row 52. The positive value at row 54 indicates projected cash inflows are exceeding cash outflows, whereas, negative value (if any) indicates that outflows are exceeding inflows. The former is a healthy sign whereas the later raises a red flag for the business and requires deep dive to identify the reasons and accordingly remedial steps to overcome the situation.
The net cash flows calculated at row 54 when added to the opening cash and bank balance of row 6, gives us the closing cash and bank balance at row 56 of respective months.
This excel template can be used to forecast cash position of a business for any given period i.e. a month or a quarter or a year. Moreover, can be rolled over to get forecast for multiple years.
In our upcoming article we will briefly explain nature of each line item of cash flow forecast template. We will also help to classify each line item into Operating, Investing and Financing activity and will also provide guidelines how to forecast each of those activities under each line item.
Your feedback and comments will help us to further improve this template. Please download a copy of this excel template for free by subscribing to our mailing list.