Sensitivity and Scenario Analyses in Financial Modeling

Microsoft Excel allows great flexibility when developing financial models, which is important given the level of subjectivity involved with determining variable inputs. Whenever a financial modeler prepares a set of projections or is doing an analysis that requires several inputs that are estimated, that value of the analysis becomes based on subjective values used to drive the model. In these situations, there are methods to provide ranges of possible outcomes. These ranges are crucial given the subjectivity of the inputs. The two main approaches are sensitivity analysis and scenario analysis.

Sensitivity analysis can be thought of as taking a particular variable, like price per pound, number of units sold or interest rate, and changing it to see the impact on the overall analysis. For example, assume that an analyst forecast assumes that there will be a 5% increase in the unit prices and a 5% increase in overall sales demand. One way to determine the impact of each assumption, one could keep unit prices constant while leaving the overall demand the same, or vice versa. Under this method, the analyst can see what impact changing one variable has on the overall assessment. This can be applied to any number of variables used in the model, and in general, this is referred to as “stress testing” the model. Under a worst case scenario analysis, one would take a variable like sales growth and assume 0% or negative, or take a cost assumption and increases it significantly. By taking this approach, one can see what might happen if the current product or service provided by a company was suddenly significantly altered one variable at a time and represents the ultimate stress impact.

Scenario analysis can be thought of as multiple sensitivities performed simultaneously. For example, an automobile manufacture has several things that affect the ability to sell cars and make a profit, like foreign competition, union wages and escalations, increasing costs of inputs and assumptions regarding postretirement benefits and pensions. Instead of assuming merely a 0% sales growth or increasing costs, managers may want to know what the combination of several effects might be and label different scenarios. One might assume 1) an increase in foreign car presence that would decrease domestic demand for cars; 2) higher union wages driven my contracts approaching expiration; and 3) recessionary impact on disposable incomes. Changes in these three variables might constitute on scenario, and the multiple variable changes can be assessed.

Normally, using data tables in Excel is an excellent method by which to assess stressing of variables. Of course, data tables at most can provide up to two variables simultaneously. Because of this, it is common to have three or four data tables in a row to get an overall assessment of changing more than two variables at a time. An example of this would be calculation of investor returns. One could change the exit multiples and year of exit in one, the exit multiple and sales growth in another, the amount of total debt and year of exit in another, and so on. The series of these analyses provides an overall picture of the changes in variables that may be very helpful in determining the factors that affect the outcome of the financial model the most.