Financial Forecast Modeling

Date: February 22, 2011

Written By: Jeff Gillesse

One of the significant foundational tools we use as turnaround professionals in most distressed business situations falls under the general description of financial forecast modeling. Typically, this is accomplished by creating a dynamic model using Microsoft Excel, as this software has the greatest acceptance in the marketplace as well as being fairly easy to use yet powerful enough to perform most of the complex functions needed.

While most accountants, analysts, and third party users of a company’s financial information rely upon historical financial presentations based upon Generally Accepted Accounting Principles (GAAP) for assessing financial position and performance, our primary focus as consultants instead is on the projection of future cash flows. The reason is simple – cash is always king. Cash is the desired commodity that represents wealth accumulation and is the unit of exchange to acquire goods and services that are the driving force for our economy. If an enterprise encounters a cash shortage other than a temporary one, a crisis usually closely follows.

Financial forecasting models serve multiple purposes. Often, they are developed and provided to a company’s senior secured lender as a prerequisite to the procurement of a credit facility, from which specific covenants may be derived for inclusion in the loan agreements. In our experience, these are far too often viewed by borrowers as mandated compliance steps and not regarded as valuable management tools. However, we view them as one of the most significant tools management can utilize, for purposes that can include:

• Effective forecasting of cash resources given a set of assumptions and variables;
• Validation of short and long term viability or sustainability as a going concern;
• Validation of certain variables emanating from proposed management decisions, such as a strategic acquisition, capital expenditures, infrastructure changes, or corporate restructuring;
• Systematic linking of collateral value, debt levels, and cash flows – ascertaining that positive cash flows are not occurring to the detriment of collateral assets.

It is common in these situations for us to develop a comprehensive dynamic financial model that
presents some or all the following:

• Cash flows by week for the upcoming 13-week period;
• Cash flows by week for a 52-week period;
• Cash flows by month for 2 additional years;
• Forecasted collateral borrowing base by week for 13 and 52 weeks;
• Summary forecasted balance sheet and statement of operations monthly for 3 years;
• Supporting worksheet tabs that drill down to base data such as inventory utilization, workforce expenditure by individual, sales by product line or business segment, and all other detailed underlying assumptions for values contained in the presentation tabs;
• User-defined variable fields and tables that can be modified as necessary to create comparative scenarios in connection with dynamic decision-making;
• Data collection tabs containing ongoing actual financial metrics and cash flows;
• Report tabs that show comparisons of actual performance compared to the forecasted amounts;
• Charts or graphs that provide illustrative metrics and comparisons, sometimes referred to as a “dashboard”.

The forecasts, particularly the cash flows, allow a user to evaluate future liquidity while identifying high and low levels. The forecasted cash flow statement contains sufficient line item detail so that a user can evaluate the significant drivers of cash intake and outflows, with the source of each sufficiently supported in a supplemental workbook tab or other source. It is extremely important that the creator of the model be completely familiar all of the entity’s operational dynamics, so that the presentation is accurate, meaningful, and credible.

Whether we are called upon to create or validate forecasts prepared internally or by a third party, we apply high levels of critical scrutiny so that the numbers presented are dependable for their intended purpose. Users should not, however, lose sight of the fact that forecasts are essentially as their name implies, “forecasts”, meaning that they are projections of specific financial metrics based upon events and circumstances that can be reasonably foreseen; this is where the experience of the developer is most valuable. Despite the extreme care that may be undertaken in developing models and the related forecasts, actual results cannot be guaranteed. A parallel can be made to weather forecasting, where sophisticated computer models are deployed to assist human judgment in predicting weather conditions; we are all aware that while they are generally accurate, the results are sometimes surprisingly different than expected.

If you would like more information about financial forecast modeling, please contact us at
info@leveltengroup.com or call Jeff Gillesse at 616-893-0366.