Understanding Your Cash-Flow Forecast
It is amazing how a crisis like COVID-19 can pressure-test all your business processes. Now, more than ever, understanding your business cash-flow forecast is critical. The best way to manage your business in order to have the right amount of cash at the right time is to create, maintain and utilize an effective rolling 13-week cash-flow forecast. If you don’t have one, use ours!
We believe understanding your cash position will support sound and confident decision-making. We cannot guarantee accuracy, as the formulas contained within the model may be inadvertently changed by the user. Also, recognize that inputs are unique to each user’s business and may require model alterations. We just want to help.
Please download the template here: https://focuscfo.com/13-week-cash-flow-template/ or email me for a copy.
Template Use Instructions
Tab 1 – Summary: This summarizes the various inputs pulled forward from the other tabs within the model. There is a self-checking formula on line 34 that ties ending cash position with the comparable number on the detail cash-flow forecast tab. If a number appears in any cell on line 35, the error arises from a formula change or improper insertion or deletion of a detail line on another tab.
Tab 2 – Detail Cash Flow Forecast: Input the company name in cell A1, the date in B5 that is the Friday of the first week of the forecast period, beginning cash balance in cell B7, any line of credit (LOC) draws/repayments along with interest and the LOC maximum limit and beginning amount available (all shaded areas). All other amounts are pulled forward from other tabs.
Tab 3 – Current A/R: This tab is colored red because it allows for an import of accounts receivable aging data from your accounting software. Once the information is imported, the amounts due to the company can be slotted into the appropriate column(s) in which a customer payment is expected. The total line is automatically pulled into the Detail Cash Flow Forecast tab.
Tab 4 – Receipts from Future Sales: This tab requires more manual work starting with the inputting of expected sales during the next 13 weeks. From there, sales are slotted into the appropriate week in the collections section where the collection occurs. This section also allows for input of one-off receipts like an insurance or lawsuit settlement, etc. The total line is automatically pulled into the Detail Cash Flow Forecast tab.
Tab 5 – Current A/P: This tab is also red because it allows for the import of accounts payable aging data from your accounting software. Once the information is imported, the amounts due to be paid can be slotted into the appropriate column(s) in which a payment is expected to be made. The total line is automatically pulled into the Detail Cash Flow Forecast tab.
Tab 6 – Expenses: This tab requires inputting the expected expenditures over the next 13 weeks. From there, the expenditures are slotted into the week in which it is expected to be paid. Many of the items listed here do not change from week to week, so there should be little change to the line descriptions once this section is built. Examples of line items include utility payments, insurance, contract payments, etc. The total line is automatically pulled into the Detail Cash Flow Forecast tab.
Tab 7 – Payroll: This tab also requires manual work starting with inputting total net wages into the correct column when payroll is paid over the next 13 weeks. Other payroll-related disbursements are also itemized here. The total line is automatically pulled into the Detail Cash Flow Forecast tab. Each week’s file should be saved as a different file name than the prior week’s file. Assuming you start with the prior week’s file and save it as a new file name, the changes occur in the gray shaded cells. Much of the information will need to be moved one column to the left since column B’s information relates to a prior week.