Either you are managing your own business or managing someone else’s business, you should have a “Cash Flow Forecast“. Determining how cash flows within the business may best be envisioned as an actual flow of dollars for each transaction. In this post, we review a typical cash forecasting model that uses a series of assumptions to arrive at a monthly prediction of cash inflow and outflow. The model begins with assumptions regarding sales levels, collection periods, and debt interest rates in the sections entitled: “Revenue” and “Assumptions”. These assumptions are then used to arrive at predicted cash receipts and cash disbursements by month, as noted in the sections entitled “Cash Receipts Detail” and “Cash Disbursements Detail”. We bring this information together in “Analysis of Cash Requirements” to arrive at a net cash change per month. The final section, “Balances in Key Accounts”, notes the amount of cash the company expects to invest in its working capital and other key accounts over the course of the year. This format is short and easily readable, so managers can quickly grasp the reasons for changes in cash flows.

We will note the reasons for using each line item in the cash forecast, as well as how the information is derived. This line-by-line explanation gives you a thorough understanding of the model, allowing you to duplicate it easily.

Review the following cash flow example in detail, consulting the explanations section to clarify any points of uncertainty, for as long as it takes to obtain a thorough understanding of how a cash flow forecast works. We highly recommend that every company create a cash flow forecast and update and consult it regularly, because cash flow is the lifeblood of a business and can rapidly lead to a cash flow coronary that results in a business heart attack.

Cash Flow Forecast Sheet Example

Before going to the explanation, let’s have a look at the following cash budget sheet below (a First Quarter Cash Flow Forecast Report). Please do not be overwhelmed with the long spreadsheet; I am going to bring you through line-by-line later on the explanation section. So here is the Cash Flow Forecast Sheet Example:

Cash Flow Forecast Sheet Explanation

The Cash Budget Sheet consisted of five main section as shown above and each section consisted of lines explained below:

Revenue

• Total dollar sales. This information comes from the sales department’s forecast and is extremely important; the sales figures are used later in the cash forecast to determine the timing of cash receipts and the amount of likely cash expenditures. Because it affects so much of the cash forecast, a company must be sure to enter the most accurate information possible into this line.
• Collections, cash sales. This is a percentage and is multiplied by the total dollar sales figure in the preceding line to derive the “Cash sales” figure that is listed under the “Cash Receipts Detail” section. This figure represents the cash inflow that has no timing delay, since customers pay at the time of product receipt.
• Collections, collect in 30 days. This is a percentage and is multiplied by the total dollar sales figure in the first line of this section to derive a portion of the “Collections of Receivables” figure that is listed under the “Cash Receipts Detail” section. This figure represents the proportion of cash inflow that has a delay of approximately 30 days in arriving and represents that portion of accounts receivable that arrives on time. Those businesses using different payment terms on their billings should use their stated number of payment days instead of the 30 days used in this example.
• Collections, collect in 60 days. This is a percentage and is identical to the preceding one in its usage, except that it represents the proportion of accounts receivable that are collected later than normal. This figure tends to fluctuate with the looseness of a company’s credit granting policy and in inverse proportion to the aggressiveness of its overdue accounts receivable collection efforts.
• Collections on November sales. The sample cash forecast we are reviewing begins with December, so any late cash receipts from preceding months must be entered in this line.
• Average gross margin percentage. This is a percentage and represents the average cost of sales in each month. In this model, it is used to derive the “Total Purchases on Credit”, which is the first line in the Assumptions section. For example: by multiplying the February sales figure of $140,000 by 70 percent, we arrive at total purchases for the month of $98,000, to which we add an inventory buildup for the month of $94,000 (as noted in the “Inventory” line in the “Balances in Key Accounts” section). When added together, this equals total purchases of $192,000, which is the number listed under February in the Total Purchases on Credit line in the Assumptions section.

Assumptions

• Total purchases on credit. The derivation of the amounts in this line were described for the “Average Gross Margin Percentage” in the “Revenue” section. The total purchases number is later used in the “Payment for Purchases on Credit line” in the “Cash Disbursements Detail” section, with a delay of one month (since we assume supplier payment terms of 30 days). This is the chief component of the cash disbursements total.
• Line-of-credit interest rate. This is a percentage, and is used later in the “Cash Disbursements Detail” section to determine the interest payment on the line of credit, which is a cash disbursement.
• Line-of-credit balance in December. The last line of the cash forecast includes a calculation of the balance in the line of credit; however, this figure will be incorrect unless the model already contains the balance from the previous year. Therefore, we include this preliminary debt figure.
• Long-term debt interest rate. This is a percentage and is used later in the “Cash Disbursements Detail” section to determine the interest payment on the long-term debt, which is a cash disbursement. Unlike the interest rate for the line of credit, there is only a single entry for this amount, rather than an entry in every month of the year; the reason for the difference is that most long-term debt is fixed at the beginning of the debt agreement, so there is no need to adjust the rate over the course of the year.
• Long-term debt balance in December. The “Cash Disbursements Detail” section includes line items for the interest and principal payments on long-term debt. Those payments are derived from the December debt balance, since it reveals the total amount that the company still has left to pay on its debt.
• Long-term debt payment schedule. This line item lists the grand total payment to lenders each month that is required to fulfill debt payment obligations on the long-term debt total that was listed in the last line item. If there are debt balloon payments, they should be entered in the correct month in this line.
• Minimum acceptable cash balance. This figure is the minimum amount of cash that the management team has decided must be kept on hand at all times, perhaps to meet short-term cash needs. This figure is needed to calculate the “Cash Needs Comparison” line in the “Analysis of Cash Requirements” section. The figure also appears in the “Ending Cash Balance” line of the same section, where we have borrowed enough funds through the line of credit to ensure that the cash balance never drops below the minimum acceptable cash balance.

Cash Receipts Detail

• Cash sales. The numbers in this line denote the total amount of cash received from cash payments for sales. These cash receipts have no timing delay, since they come from customers as immediate payment for sales to them. The numbers are derived by multiplying the sales figure in the Total Dollar Sales line in “Revenue” section times the cash sales percentage in the same section, and for the same month.
• Collections of receivables. This line is a calculation that summarizes the delayed cash receipts from sales in the past two months. Specifically in this model, it is 50 percent of the sales from two months ago, plus 40 percent of the sales from the preceding month. (These collection percentages were listed in the “Revenue” section).
• Other. There are always miscellaneous cash receipts that can come in from a variety of sources, such as tax rebates or proceeds from asset sales. These figures are entered manually in this line.
• Total cash receipts. This line summarizes all the cash receipts previously noted in this section.

Cash Disbursements Detail

• Payment for purchases on credit. The numbers in this line are drawn directly from the “Total Purchases on Credit” line in the “Assumptions” section. However, their timing is moved forward one month, since we are assuming that purchases made in the preceding month have payment terms of 30 days and so must be paid in the following month. For example: purchases made in February of $192,000 do not appear in the cash forecast as payments until March.
• Operating expenses. The numbers in this line are entered from the annual budget, and contain the salaries, facility expenses, and other miscellaneous administrative costs associated with running the business.
• Long-term debt interest. This line item and the next one, Principal, are based on an electronic spreadsheet command. The command is derived from the debt payment amount listed in the “Long-Term Debt Payment Schedule” line and the “Long-Term Debt Interest Rate” line, both located in the “Assumptions” section. You can use the IPMT command in Microsoft Excel to determine the proportion of the monthly debt payment that is ascribed to interest expense, while you can subtract the interest expense from the “Total Debt Payment” to derive the principal payment that is listed in the next line. These two lines can be merged if management is not interested in the interest and principal components that comprise a debt payment.
• Principal. See the preceding line item.
• Interest payment on line of credit. This line item is based on the month-end line-of-credit balance from the preceding month, multiplied by the interest rate for the month, which results in the interest payment due to the lender during the current month. For example: the February interest payment is derived by multiplying the January debt total of $58,250 by the interest rate of 15% (reduced to one-twelfth, since this is a single month payment), which results in an interest expense of $728.
• Income taxes. This line contains the estimated income tax payments for each quarter of the year, and is usually inputted directly from the annual budget.
• Other. There are always additional cash payments that do not fall into the standard categories previously noted in this section.

This line item is used for manual entries of these extra cash outflows.

Total cash disbursements. This line summarizes all of the cash disbursements previously noted in this section.

Analysis of Cash Requirements

• Net cash generated this period. The numbers in this line are calculated by subtracting the amounts in the “Total Cash Disbursements” line in the preceding section from the amounts in the “Total Cash Receipts” line in the “Cash Receipts Detail” section.
• Beginning cash balance. This figure comes from the “Ending Cash Balance” line at the end of this section, but for the preceding month. It is netted against the “Net Cash Generated This Period” line to arrive at the “Cash Balance Before Borrowings” line, which follows.
• Cash balance before borrowings. As just noted, this line is derived by netting the “Net Cash Generated This Period” line against the “Cash Balance Before Borrowings” line. The resulting numbers show the cash inflow or outflow resulting from operations.
• Cash needs comparison. This line compares the “Cash Balance before Borrowings” line to the “Minimum Acceptable Cash Balance” in the “Assumptions” section to arrive at a total amount of borrowings needed or cash available for an additional debt payment. For example: in the month of March, we have a preliminary cash need of $54,248, but then increase it by $20,000, since we require an internal cash balance of $20,000, resulting in a total cash need of $74,248.
• Current period short-term borrowings. This line is a calculation that is essentially the inverse of the preceding line. It itemizes a borrowing requirement that exactly matches the cash need we have just calculated in the “Cash Needs Comparison” line.
• Total short-term borrowings. The numbers in this line are cumulative from month to month. For example: the total short-term borrowings at the end of January are $58,250 but are increased by $47,478 in February (see the Current Period Short-Term Borrowings line), resulting in a total borrowings figure of $105,728.
• Ending cash balance. The numbers in this line are based on a minimum cash balance of $20,000 (as noted earlier in the “Minimum Acceptable Cash Balance” line in the “Assumptions” section), or a higher cash balance, if the line of credit has been paid off.

Balances in Key Accounts

• Cash. The numbers in this line are drawn directly from the “Ending Cash Balance” line in the preceding section. Its purpose in this section is to be part of the summary of key accounts that most affect monthly cash flows.
• Accounts receivable. The numbers in this line are derived from the sales and collection figures at the top of the “Revenue” section. For example: the March accounts receivable figure is composed of two calculations. The first is 90 percent of the current month’s sales, which is derived by assuming that only 10 percent of sales are paid for in cash (as noted in the Cash Sales line in the “Revenue” section). The remaining amount comes from previous month sales, which in this example are 40 percent of the February sales. After adding the two calculations together, we arrive at an estimated accounts receivable balance of $218,000.
• Inventory. The numbers in this line are derived manually and are normally input from the production or inventory budget page in the annual budget. Many manufacturing companies will build inventory levels prior to the commencement of their main selling seasons, and so the inventory level will not necessarily bear a direct relationship to sales levels each month. This line item is part of the calculation for the “Payment for Purchases on Credit” line in the “Cash Disbursements Detail” section, as explained earlier in the line.
• Accounts payable. The numbers in this line are drawn directly from the “Total Purchases on Credit” line in the “Assumptions” section and represent the total source of funds from suppliers that will offset cash used by the other line items in this section (e.g., accounts receivable and inventory).
• Line of credit. The numbers in this line are drawn directly from the “Total Short-Term Borrowings” line in the preceding section. Its purpose in this section is to be part of the summary of key accounts that most affect monthly cash flows.