Stress-Testing Your Business: A Free Tool to Help Forecast Cash-Flows

BY Tim Fisher
5/1/2020 - HVAC Market Intelligence , COVID-19
In the March edition of our monthly TRENDS report, Y/Y dollar-weighted sales were up 4.9 percent, bringing the Q1 average up to 2.4 percent (slightly off our Q1 forecast of 3.5 percent, which we outlined in greater detail here). However, given the pace and severity of the economic downturn, as well as the negative trends being reported in our Weekly Pulse Surveys, the odds of distributors experiencing Y/Y sales growth in the months ahead appear very low. To ensure that your business is prepared for the difficult months ahead, we encourage all our distributor members to begin stress-testing their businesses. Our friends at the Indian River Consulting Group (IRCG) recently created this simple, free stress-test tool to help distributors model the cash-flow implications of an economic downturn. For those of you interested in taking this tool for a spin, see how it works:
 

Mike Marks explains the importance of the tool

Screen Shot 2020-04-23 at 11.02.29 AM
  1. Data should be entered for each month from January 2019 through March 2020, but only in the yellow shaded cells.
  2. The model uses average daily sales to normalize month-to-month sales change.
  3. Sales values should include discounts given to customers and other standard adjustments.
  4. COGS should include discounts earned and inbound freight and other standard adjustments. Rebates can be applied here or in other income.
  5. All expenses should be entered among these six options. User has discretion on where expenses should be applied.
  6. Select cost type as described in footnote and also in the Instructions tab.
  7. Interest income and gain/loss on sales of assets should be included here. Rebates and interest paid can be entered here if not entered elsewhere.

Screen Shot 2020-04-23 at 11.04.49 AM

  1. Nothing from this tab is used in any model calculations. This tab is provided for use as a reference when considering headcount related adjustments relative to changes in revenues.
  2. Enter total company wages (all gross W2 amounts) and enter total benefit costs and payroll related taxes. This ratio will be used to estimate burden cost.
  3. Feel free to overwrite the functional areas listed and use only the rows you need.
  4. Enter number of employees for each function.
  5. Enter total annual wages (sum of annual wages) for each function.
  6. Total cost is the sum of Total Wages and Estimated Burden Cost. The sum of Total Cost should resemble the sum of the two payroll amounts entered at the top.

Screen Shot 2020-04-23 at 11.05.51 AM

  1. This tab provides a baseline on profit impacts, which will be used as part of the cash calculation when revenue projections are entered. Descriptions of the calculations used can be found on the Instructions tab.
  2. Enter revenue projections in the shaded cells relative to the same month from the prior year. Average daily sales are used for sales and expense calculations.
  3. Net profit before tax is considered cash, so if material amounts of depreciation are recorded it would make sense to exclude those.

Screen Shot 2020-04-23 at 11.06.41 AM

  1. Enter values from the February month-ending balance sheet accounts for Cash, Accounts Receivable (trade), Inventory, and Accounts Payable (to suppliers).
  2. If additional cash, such as paid in capital, is expected to be infused into the business, enter the amount in the appropriate month.
  3. If additional debt is expected to be acquired, enter the corresponding interest expense for each month upon which it will be payable.
  4. If cash is expected to be used outside of inventory, A/R, A/P, and operating expenses (including interest), enter in the month(s) upon which it is expected to occur.
  5. Cash balances are estimated through February 2021 based on net income and expected adjustments to working capital accounts (plus those mentioned in bullets B, C, and D above). Details on these calculations can be found on the Instructions tab.

Screen Shot 2020-04-23 at 11.07.54 AM

  1. The top four values are calculations displaying cash impacts from adjustments made to gross margin, expense and working capital adjustments made on this tab.
  2. Cash Adjustment shows the cumulative cash impacts from the changes made on this tab. Gross Margin and Expense adjustments carry-forward, while working capital adjustments are one-time occurrences (see Instruction tab for more details).
  3. Changes in gross margin should be entered as basis points (100 basis points = 1 percent). A positive value will increase margin.
  4. Changes in expense levels should be entered as dollars. A decrease in expenses should be preceded by a negative sign (“-“).

Screen Shot 2020-04-23 at 11.08.43 AM

  1. The top of this illustration is the same as the previous image. Rows 48 through 62 above are found below the margin and expense adjustments on the Modeling tab, which are described on the Modeling (1 of 2) outline. Please note: all adjustments to working capital accounts will show only in the month they occur; unlike the gross margin and expense adjustments they do not generate changes in subsequent months.
  2. If you expect accounts receivable days to increase due to customers not paying their bills promptly, enter a positive value. If you believe you will be able to shorten days sales outstanding, enter a negative value.
  3. If you want to estimate additional cash available from paying vendors in a longer period of time, enter the number of days you plan on extending as a positive number.
  4. Use days increase or decrease to estimate the impact of inventory changes. Note that days inventory will change as sales decline. The formula is calculated using days sales in the denominator.

The steps for using the IRCG cash flow model come directly from an informational webinar IRCG recently hosted to explain the tool. For distributors interested in accessing the cash flow model, you can find it easily on the IRCG website or download directly via this link.


For questions and more information reach out to Tim Fisher, or visit our COVID-19 resources page for more.