Lesson Objective: To build dynamic one-way and two-way sensitivity tables (Data Tables) that analyze the impact of changes in individual assumptions on model outputs, and to construct the professional “Tornado Chart” that visually ranks these assumptions by their impact on value.

In-Depth Notes:

1. The Mechanics of Sensitivity Analysis:
Sensitivity analysis is performed using Excel’s Data Table functionality (a “What-If Analysis” tool). The analyst defines a “driven cell” (the assumption to be changed) and a “target cell” (the output to be measured, e.g., Enterprise Value, IRR, EBITDA).

  • One-Way Data Table: Varies a single input (e.g., Revenue Growth from 1% to 10%) and shows the impact on a single output (e.g., Enterprise Value). The table is constructed by placing the varying inputs down a column and the formula for the target cell in the top-right cell. The Data Table command uses the TABLE(column_input, row_input) function.

  • Two-Way Data Table: Varies two inputs simultaneously (e.g., Revenue Growth across the top and EBITDA Margin down the side) and maps the output (e.g., Valuation Multiple) in a matrix. This is the standard format for “sensitivity matrices” presented in investment committee meetings.

  • Global Best Practice: The Data Table function is volatile (it recalculates every time the workbook recalculates). To prevent processing lag in large models, the “Calculation Options” must be set to “Manual” (except for the Data Table sheet), and the analyst must use the F9 key to recalculate only when necessary. The input cells must be “hard-locked” to prevent accidental alteration of the table’s structural layout.

2. Constructing the “Tornado Chart” (The Value Driver Ranking):
The Tornado Chart is the most common professional presentation format for sensitivity analysis. It is a horizontal bar chart that ranks each assumption by the magnitude of its impact on the target cell.

  • The Mechanics:

    • Step 1: The analyst selects 5 to 7 key assumptions (e.g., Revenue Growth, EBITDA Margin, WACC, Terminal Growth Rate, CAPEX as % of Revenue).

    • Step 2: For each assumption, the analyst defines a low and high range (e.g., Revenue Growth: 2% to 6%).

    • Step 3: The model calculates the Enterprise Value using the low assumption (holding everything else constant) and the Enterprise Value using the high assumption.

    • Step 4: The difference between the high and low outputs is calculated. This is the “range of impact.”

    • Step 5: The assumptions are sorted in descending order of impact (largest impact at the top of the chart).

  • The Visual Output: The chart features horizontal bars. The bar represents the range of Enterprise Values resulting from varying that single assumption. The wider the bar, the more sensitive the valuation is to that assumption. In the US (SEC regulations), the Tornado Chart is a mandatory element of Fairness Opinions to demonstrate that the valuation is not overly reliant on a single, unverifiable assumption. In Europe (ESMA), the Tornado Chart is required for disclosures on “Alternative Performance Measures” to illustrate the sensitivity of key financial metrics to underlying assumptions.

3. The “Break-Even” Analysis (The Critical Threshold Calculation):
Break-even analysis is a specialized form of sensitivity analysis that answers the question: “How low/high would a specific assumption need to be for the project/valuation to become unviable?” This is a critical tool for investment committees evaluating high-risk projects.

  • The Mechanics:

    • The analyst uses Excel’s Goal Seek function to reverse-engineer the break-even point.

    • For example, in an M&A transaction, the modeler asks: “At what point does the Net Present Value (NPV) of the deal become zero?” This is the “break-even acquisition price.”

    • The model uses Goal Seek to set the NPV Target Cell to zero by changing the Acquisition Price Assumption.

  • Global Regulatory Application: Under the UK Corporate Governance Code, boards of directors are required to conduct a “going concern” assessment. Break-even analysis is used to determine the maximum allowable decline in revenue that the company can absorb before it violates its debt covenants. This is a critical risk management tool for corporate treasuries.

 
PreviousNext