Increasing User Buy-in of Financial Forecasts

Business HandshakeLet me begin with two assumptions: first, your primary modeling tool is Microsoft Excel; second, you share model projections with others. If both these assumptions are correct, I have two secrets of success for those new to financial forecasting.

The first is that everyone who sees your forecast assumes they know more about what the modeled results should be and better understand the impact of changes than you do. That you spent countless hours constructing the model, studying company and industry trends, back-testing formulas and validating every assumption will be quickly lost in their rush to point out what to them appears to be “obvious errors”.

I frequently develop complex models generating quarterly projections of full financial statements for a three to five year horizon. Models usually involve the consolidation of multiple entities and detailed ratio analysis. A typical model has 35 to 60 variables. Every variable is contained in named cells on an assumptions tab (immediately behind the title tab). All formulas utilize the appropriate variable name rather than a cell reference or hard coding.

For those not familiar with the use of named cells in Excel, go to the File Manager icon on the Formulas tab. Additional guidance is available online. One source is http://bit.ly/18tl7OP.

Typically, a client will zero in on one or two variables, insisting (as an example) that sales growth projected in year 3 is clearly wrong! He or she is so confident of their belief that the model has likely lost significant credibility with them.

Invariably, the impact of the user’s change is not as significant as they suppose. Sticking with the sales example, changing the growth rate has no impact on earlier years. Furthermore, the effect on future income is reduced by resultant increases in the cost of goods sold, inventory carrying costs, variable expenses such as commissions and shipping, borrowing costs and so on. Finally, income taxes further reduce the bottom line impact by another 35% to 40%.

Rational discussion and logic serve no purpose in this situation. You cannot change human nature! Your goal is merely to channel it in a productive direction.

I do this by simply asking what they think the number should be. I then take them to the assumptions tab and change the offending variable to their number. The model then recalculates, eliminating any guesswork on the impact of the proposed “correction”.

Seeing is believing.

The second “secret” complements the first. Without exception, even the most complex models come down to a mere handful of key variables. Since your goal is to redirect rather than change behavior, help users focus on those that drive projected results, rather than getting bogged down in immaterial detail.

You can accomplish this by highlighting which variables have an individually material impact on the cumulative results of your forecast. Begin by deciding what the appropriate base or dependant result is. I find it is most often one of three things depending on the primary use of the model: net income, stockholders equity or the internal rate of return.

I then test every variable in isolation with a 10% unfavorable change. For example, a 20% sales increase is reduced to 18%. I note the impact of each variable on the cumulative base result. I then typically use a materiality threshold of 2% for disclosure. The less attention drawn to non-critical variables the better!

Rarely will a variable have a high correlation to the measured result. A typical scenario might be that a 10% change in each of my 35 to 60 variables produces four to six with an impact greater than 2%, with none exceeding 8%.

This sensitivity analysis is the third tab, immediately behind the variables. By quantifying and clearly presenting the impact of changes in this manner, you are inviting needed input (and therefore user buy-in), without having to debate or justify the majority of variables that will have minimal or no impact on your forecast. Users can then concentrate on achieving a comfort level with a relative handful of model inputs, saving everyone time.

As a closing note, while the focus is on the cumulative impact of variable changes, there are times and circumstances when individual period results are also important, regardless of the dollar impact. For example, loan covenant compliance is a constant requirement. If a change in an otherwise insignificant variable creates an incidence of non-compliance, the change cannot be ignored.

How I handle that situation is the subject of a future article. Here is a hint: conditional formatting!

  • RSS
  • Newsletter
  • Twitter
  • Facebook
  • LinkedIn