If there is no chief financial officer (CFO) or treasurer, then the job of determining the projected flow of cash falls on the shoulders of the controller or even an accountant for small-medium enterprises. This means that the controller or the accountant must maintain a worksheet that estimates the cash inflows and outflows, perhaps as frequently as on a weekly or even daily basis, so that management can tell when there will be a need for either extra financing to obtain cash or extra investing for excess cash.
This post DOES NOT ONLY provides guideline on how to create cash forecast, but it reveals some facts that a controller found during the process of the cash forecast construction, potential issues and how to deal with the issues to keep your cash forecast the most valuable tools for financial decision-making. Consequently, this post become longer than any other cash forecast construction guideline ever published in the internet. It is dedicated to the people who have been engaged in the real financial world, but financial observers and students should find it useful too.
The basic concept on which the cash forecast is built is that this is not accrual accounting—we do not care about accrued revenues or expenses, just actual receipts or payments, from and to all possible sources. For example, if the accrual system is gradually recording an annual property tax payment of $120,000 in monthly $10,000 installments, these installments will not appear in the cash budget, because there is no actual monthly payment. Instead, there will be a single annual cash expenditure of $120,000 that will appear in the forecast when the full amount is due for payment.
The cash forecast format constructed in this post is split into three pieces: one for assumptions, another for cash inflows, and the final piece for cash outflows. In the first section of the budget, as shown in below figure, the underlying data and assumptions needed to compile the remainder of the report are listed. Here is an example of Cash Forecast:
Cash Flow Line Item January February March
Sales dollars per period $2,000,000 $2,500,000 $3,000,000
Production costs as a-
percentage of sales 50% 55% 55%
Days needed to collect-
accounts receivable 45 45 45
Days needed to pay-
accounts payable 30 30 30
Days of inventory on hand 60 60 60
Sales tax percentage 6% 6% 6%
Sales per employee $100,000 $100,000 $100,000
Annual average pay per employee $40,000 $41,000 $41,500
Section II—Cash Inflows
accounts receivable* $2,000,000 $2,000,000 $2,250,000
Collections on notes receivable 5,000 5,000 2,500
Collections from asset sales 0 15,000 0
Collections from equity sales 0 0 100,000
Total Cash Inflows $2,005,000 $2,020,000 $2,352,500
Section III—Cash Outflows
Payments for production costs* $1,000,000 $1,100,000 $1,375,000
Payments for salaries and wages 66,667 85,417 103,750
Payments for general and
administrative costs 175,000 175,000 175,000
Payments for capital expenditures 0 150,000 0
Payments for notes payable 25,000 25,000 25,000
Payments for sales taxes 120,000 150,000 180,000
Payments for income taxes 0 0 75,000
Payments for dividends 0 200,000
Incremental inventory change 0 750,000 550,000
Total Cash Outflows $1,386,667 $2,435,417 $2,683,750
Net Cash Flows +618,333 -415,417 -331,250
Cumulative Net Cash Flows +$618,333 $202,916 -$128,334
* Sales for each of the two preceding months are assumed to be $2,000,000.
Section I — Assumptions
- Sales dollars per period. The key driver of the entire cash forecast is the amount of sales volume to be expected in each of the measurement periods. This is because the sales figure interacts with the amount of purchases and days of inventory and accounts receivable that are expected to be on hand; and all three of these items are major components of the cash outflow portion of the cash forecast.
- Production costs as a percentage of sales. The amount of production costs needed in each period is derived directly from the anticipated sales figure for each reporting period. The controller usually uses historical actual results from the financial statements to determine the percentage of purchases that occur for each dollar of sales. This is typically a reliably steady and easily predicted figure.
- Days needed to collect accounts receivable. This assumption interacts with the sales figure from previous months to arrive at the amount of collections from accounts receivable expected in the current month. For example, if an average of 30 days are needed to collect funds, then the entire sales figure from the immediately preceding month can be listed as cash received in the current month. However, if the collection period is 60 days, then the entire sales figure from two months ago can be predicted as cash received in the current month.
- Days needed to pay accounts payable. When determining cash outflows, the primary determinant is the average number of days during which a company holds onto its accounts payable before issuing payment to suppliers. This figure can vary over time as the mix of suppliers (who may have differing payment terms) changes, or is based on decisions by management to lengthen payment terms if there will be anticipated cash shortages.
- Days of inventory on hand. A major element of working capital that has a strong impact on cash flows is the days of inventory that management chooses to keep on hand. This is not a steady turnover figure, especially for companies with a wide array of new products, because new inventory must be added to support new products. This item in particular is deserving of careful attention to ensure that the correct turnover figure is used.
- Sales tax percentage. This is the percentage that the company owes the government on sales that occurred in the prior period. This percentage is then multiplied by the sales projection for the preceding period to derive the total sales tax payment for the current month.
- Sales per employee. A component of the cash outflow section at the bottom of the cash forecast is the payroll cost, which is partially derived by this line item, which assumes a certain headcount based on the sales volume. This figure must be used with care, because some employees in the overhead category will be on the payroll even if there are no sales. Consequently, a more detailed cash forecast might also include the number of overhead employees based on the budget, rather than tying all personnel to sales volume.
- Average pay per employee. The preceding measure determines the number of employees who will be paid in the payroll expense that is noted under the cash outflows section of the cash forecast report, while this measure multiplies the total headcount by the average pay per person to determine the actual cash outflow.
Section II — Cash Inflows
- In the second section of the cash forecast shown, all possible sources of cash inflows are included. The primary one by far is collections on accounts receivable, though there are three additional line items for other sorts of discretionary cash inflows. Because these last three are not based on continuing operations and are not driven by formulas from the first section of the cash forecast, they are highlighted with italics, which denotes manual entries in the model. Descriptions of the line items are:
- Collections on accounts receivable. This is the primary source of cash inflows, and the only one from continuing operations. It is created by a formula that determines the amount of cash receipts that can be expected from the sales in previous months. The inputs are the “sales dollars per period” and “days needed to collect accounts receivable” line items in the first section of the cash forecast.
- Collections on notes receivable. A relatively minor item in most cases is the receipt of cash in payment for funds that the company has lent out to employees or other organizations. This is usually a manual entry in the cash forecast.
- Collections from asset sales. A company will sell assets from time to time, which results in a cash inflow. Because the exact period or amount of sale is rarely predictable, this cannot be included in the assumptions section and is a manual entry in the cash forecast.
- Collections from equity sales. Another source of funds is the sale of equity. This is done at the company’s discretion and is usually timed to take advantage of the highest possible market price of company stock. Due to the timing problems associated with market conditions, this is not a predictable item that can be included in the assumptions section and is thus a manual entry in the cash forecast.
Section III — Cash Outflows
In the final section in the figure, all possible sources of cash outflows are included. There are a number of major contributors to this category [as opposed to the single one—collections from accounts receivable—that was the case for cash inflows]. The most important ones (in descending order) are payments for production costs, payroll, general and administrative payments, capital expenditures, and notes payable. These final two items may be switched in priority for highly leveraged companies that make large debt payments. Several items in this section are not based on continuing operations or derived from formulas in the first section of the cash forecast, so they are highlighted with italics, which denotes manual entries in the forecasting model. Descriptions of the line items are:
- Payments for production costs. These are the costs for materials and associated production supplies that are needed to create products. In essence, this is the cost of goods sold, not including direct labor costs. This is typically the largest cash outflow for most companies, with the exception of service industries.
- Payments for salaries and wages. This is usually the second largest cash outflow, save for companies located in services industries, in which it is the largest. In Exhibit 5.2, this cash outflow is derived with a formula that is based on the sales per employee and the average pay per person. However, this formulation assumes that headcount varies directly with the sales level, which may not be the case. An alternative approach is to use the payroll listed in the budget.
- Payments for general and administrative costs. These costs are associated with corporate overhead, such as sales, audits, insurance, and rent. The best source of this information is the budget, because it rarely varies with the estimated sales level.
- Payments for capital expenditures. Payments for capital expenditures are highly predictable, because they are already listed in the budget and can be determined with some additional precision, since the expenditure authorizations normally come through the accounting department well in advance of any purchase.
- Payments for notes payable. Debt payments can be derived from a separate schedule of loan payment dates and amounts. There is no need to separate the interest expense and principal into two separate line items, because the concern here is only with the amount of the cash outflow, and not the exact nature of the outflow.
- Payments for sales taxes. Sales taxes can be predicted based on a percentage of the estimated sales. Accordingly, the formula for this is a combination of the estimated sales from the preceding month and the sales tax percentage.
- Payments for income taxes. It is difficult to reevaluate the entire budget in order to arrive at an estimate of the net income for the next few periods, so an easier approach is to use the estimated net income tax already located in the budget, adjust for any obvious expected changes in income, and record this expense in the cash forecast as a quarterly payment.
- Payments for dividends. Dividends are very predictable, because the board of directors specifies the amount to be paid, usually well in advance of the actual payment, and also because most companies maintain such a consistent level of dividend payments that the amount to be paid is probably very consistent with previous payment amounts.
- Incremental inventory change. Projected changes in inventory can be an important reason for both cash inflows and outflows. This is the only line item in the cash outflows section that can show either an inflow or an outflow, because inventory levels may rise or fall. This item is derived from a formula that alters the inventory level based on the assumed number of days of inventory shown in the assumptions section of the cash forecast.
Having described the components of the cash forecast in some detail, the information can now be assembled into a coherent forecasting model, as shown in the above figure. In the example, the forecast is limited to three monthly reporting periods. The model could include the previous two periods, because the collections and payments information is based on sales information from previous periods. However, adding historical information can be confusing to the reader, so it will be assumed that this information is located in hidden columns that are not included in the final report. Next, all relevant assumptions are included. Sales are expected to increase through the reported periods, with an increase in the cost of production as a proportion of sales; these are the first two items in the cash forecast, because they have the largest impact on the forecast result.
Then the two cash delaying factors are included, which are the standard number of days that will pass before a company can expect to receive cash in payment for previous sales, as well as to make payments for previous purchases. The next item, the days of inventory on hand, is used later in the cash outflows section to determine the amount of inventory that must be maintained to support the current sales level. A sophisticated cash forecasting model might include a provision for inventory to be built up in advance of sales, so that there would be an associated cash outflow some time in advance of sales being generated, because customers will not purchase products unless there is first a stock of inventory on hand from which to purchase. The next two line items, the sales per employee and the average pay per employee, are used later in the cash outflows section of the model to arrive at the payroll cost.
In the second section of the forecast, all cash inflows are shown. The collections on accounts receivable are based on a collection period of 45 days, as was noted under the assumptions in the first section of the forecast. For the months of January and February, collections are drawn in part from preceding months that are not shown in this forecast; it will be assumed that the amount of sales in all previous months is $2 million [as is also stated in the note at the bottom of the figure]. Because the sales in previous months are all the same, the collections will also be $2 million per month for the first two months. However, the collections figure for March shows a different amount. The collections total of $2,250,000 is based on a 45-day collection period for months that have different sales totals; in this case, one half of the sales are drawn from January sales and one half from February. Going forward, the same calculation will apply in April, which will include one half of the sales from February and one half from March. The remaining items in the cash outflows section are manually entered, because they are not based on continuing operations and therefore cannot be automatically forecasted.
In the third and final part of the cash forecast, the amount of payments for production costs is calculated by taking the total sales figure for the preceding month [since one of the assumptions in the first section is a time lag on payments of 30 days] and multiplying it by the percentage of production costs, which was also noted in the assumptions section. The salaries and wages payment is calculated by multiplying the sales level for the month by the sales per person (which assumes that headcount varies directly with sales volume), and is then multiplied by the average pay rate, which must then be divided by 12 to determine the monthly salary total. The general and administrative expense is taken from the budget, as are the cash flows shown in the following line items for capital expenditures, notes payable, and income taxes. The payment for sales taxes is derived by multiplying the projected sales for each month by the sales tax per dollar of sales. A sophisticated model could include a time-lag feature for the sales tax payment, since remittances to the various governments require a varying number of days delay before payment is due.
Finally, the incremental inventory change is calculated by multiplying the percentage of production costs [as noted in the assumptions section] by the sales dollars for the period, which gives us the materials portion of the cost of goods sold. Then, the days of inventory on hand is taken into account, which is 60 days for all three forecasted periods; this translates into having enough inventory on hand to cover the materials portion of the cost of goods sold for two months. Sales are projected to increase substantially for the second two months of the report, so the inventory will correspondingly increase. This turns out to be a very substantial cash amount, because the inventory level required to support sales is considerable.
The last steps are to summarize the cash outflows sections and subtract them from the totals for the cash inflows sections. The last row of the report shows the cumulative cash flow for all reported periods. The cumulative figure is of most use for determining any borrowing or investing needs in the near term. In the example, the increase in sales has sparked a significant increase in the amount of cash needed for inventory, which offsets cash inflows from the increased sales. As a result of the revenue surge, there is less cash than at the beginning of the reporting period.
A generally minor issue in a cash forecast is cash inflows and outflows that are not based on credit. For example, customers may pay for their purchases immediately in cash, as may the company to its suppliers.
Most organizations conduct their business almost entirely on credit, which leaves cash transactions as a vanishingly small portion of the total of all cash inflows and outflows. Thus, it is generally safe to exclude cash transactions from the cash forecasting model. The main exception to this rule is those industries, such as retail food distribution, in which nearly all sales transactions are paid in cash. In these situations, the cash inflows must be divided into cash from sales, with a lag of zero days, and all other sales (probably in the form of credit cards or checks), for which there is a short delay of perhaps 7 to 10 days. This arrangement should be sufficient for those situations in which cash inflows are the norm.
A key issue in the preparation and distribution of the cash forecast is that it must be as accurate as possible—the controller should not just mechanically prepare it based on old assumptions from months before. On the contrary, many of the underlying assumptions that are listed in the first part of the cash budget will change over time and must be updated regularly to ensure the highest possible degree of accuracy in the forecast. If this updating function is not performed, then the cash forecasts will not be as accurate, which will lead to a loss of confidence by users in the report; eventually, it may even fall into disuse if the information it imparts is sufficiently inaccurate.
To determine the level of accuracy, it may be useful to retain the last few reporting periods on the report and, for those periods, report the actual cash balance next to the projected balance, with a percentage difference.
This extra information reveals the level of accuracy of the reported information. If the cash forecast is continually inaccurate, one should investigate the underlying causes. For example, there may be a small group of customers who habitually pay later than all other ones. If so, it may be necessary to divide projected sales into two categories, one for those customers and one for all others, and assume different collection periods for each group. Similar levels of detailed refinement may be necessary in other areas as well. If this results in an excessively lengthy cash forecast that is difficult to update, then a controller must weigh the utility of having more accurate information against the work required to obtain it. There is usually some median level of reporting accuracy that is sufficient for a company’s practical needs, and this is the point at which no further cash forecast revision work is necessary.