Sensitivity Analysis in Financial Modeling: How to Build Data Tables

Why Sensitivity Analysis Is Essential

Every financial model is built on assumptions. Revenue growth rates, margin projections, discount rates, terminal multiples -- these inputs drive the outputs that inform billion-dollar decisions. The problem is that no assumption is certain. Sensitivity analysis addresses this uncertainty by systematically testing how changes in key inputs affect the model's conclusions.

In investment banking, private equity, and equity research, sensitivity analysis is not optional -- it is expected on every deliverable. When you present a discounted cash flow valuation to a managing director, the first question will be "what happens to the implied share price if revenue growth is 200 basis points lower?" If you cannot answer that instantly with a well-constructed sensitivity table, you have not done your job.

This guide covers everything you need to know about sensitivity analysis in financial modeling, from the conceptual framework to hands-on Excel implementation.

---

Sensitivity Analysis vs. Scenario Analysis

These terms are often used interchangeably, but they describe different approaches to stress-testing a model. Understanding the distinction is important for both interviews and real-world work.

Sensitivity Analysis

Sensitivity analysis changes one or two variables at a time while holding everything else constant. The goal is to isolate the impact of specific assumptions on the output. For example, you might vary WACC from 8% to 12% in 50bp increments and observe how the implied enterprise value changes at each level.

This approach is mechanical and systematic. It produces clean tables and charts that clearly show which inputs have the greatest influence on value.

Scenario Analysis

Scenario analysis changes multiple variables simultaneously to reflect a coherent narrative about the future. A "bear case" might combine lower revenue growth, compressed margins, higher interest rates, and tighter credit markets. A "bull case" layers in accelerating growth, margin expansion, and favorable market conditions.

Scenarios are more realistic than isolated sensitivity shifts because real-world changes rarely happen in isolation. However, they are also more subjective -- the analyst decides which combinations of assumptions constitute each scenario.

When to Use Each

In practice, you use both. Sensitivity tables show the mechanical impact of individual assumptions, while scenario analysis communicates strategic narratives to clients and investment committees. A typical model presentation includes base, upside, and downside scenarios supported by sensitivity tables on the most critical variables.

---

One-Way Sensitivity Tables

A one-way sensitivity table (also called a one-way data table) varies a single input and shows how one output changes across a range of values.

Common One-Way Sensitivities

• Implied share price as a function of WACC (8.0%, 8.5%, 9.0%, 9.5%, 10.0%, 10.5%, 11.0%)
• Implied enterprise value as a function of terminal value exit multiple (6.0x, 7.0x, 8.0x, 9.0x, 10.0x)
• IRR as a function of exit year (Year 3, Year 4, Year 5, Year 6, Year 7)
• Leverage ratio as a function of EBITDA growth rate

Building a One-Way Table in Excel

Step 1: Set up your table layout. Place the range of input values in a single row (for a row-oriented table) or a single column (for a column-oriented table).

Step 2: In the cell at the intersection of the row header and column of input values, place a formula that references your output cell (for example, the implied share price cell in your DCF).

Step 3: Select the entire table range including the input values and the formula cell.

Step 4: Navigate to Data > What-If Analysis > Data Table.

Step 5: For a column-oriented table, enter the cell reference of the input variable in the "Column input cell" field. For a row-oriented table, use the "Row input cell" field.

Step 6: Click OK. Excel will populate the table by substituting each input value into the specified cell and recording the resulting output.

Formatting Best Practices

• Shade the input cells so they are visually distinct from the calculated outputs
• Include clear headers labeling the input variable and output metric
• Round outputs appropriately -- do not show eight decimal places for an implied share price
• Highlight the base case value (the current model assumption) within the sensitivity range

---

Two-Way Sensitivity Tables

Two-way sensitivity tables vary two inputs simultaneously and display a single output in a matrix format. These are the most common sensitivity outputs in banking and PE.

Classic Two-Way Sensitivities

• Implied share price as a function of WACC (rows) and terminal growth rate (columns) -- the standard DCF sensitivity
• IRR as a function of entry multiple (rows) and exit multiple (columns)
• Accretion/dilution as a function of purchase price premium (rows) and synergies achieved (columns)
• Implied equity value per share as a function of revenue growth (rows) and EBITDA margin (columns)

Building a Two-Way Table in Excel

Step 1: Set up a matrix. Place one set of input values across the top row and another set down the left column.

Step 2: In the top-left corner cell (the intersection), place a formula referencing your output cell.

Step 3: Select the entire table range.

Step 4: Go to Data > What-If Analysis > Data Table.

Step 5: Enter the cell reference for the row input variable in the "Row input cell" field and the column input variable in the "Column input cell" field.

Step 6: Click OK. Excel fills in the matrix with the output value for every combination of the two inputs.

Example: DCF Sensitivity

Consider a discounted cash flow model where the implied share price depends heavily on WACC and the terminal growth rate used in the terminal value calculation.

Your two-way table might look like this:

| WACC \ Terminal Growth | 1.5% | 2.0% | 2.5% | 3.0% | 3.5% | |------------------------|-------|-------|-------|-------|-------| | 8.0% | $48 | $52 | $57 | $63 | $71 | | 8.5% | $44 | $47 | $51 | $56 | $62 | | 9.0% | $40 | $43 | $46 | $50 | $55 | | 9.5% | $37 | $39 | $42 | $45 | $49 | | 10.0% | $34 | $36 | $38 | $41 | $44 |

This single table communicates an enormous amount of information. A managing director or client can immediately see how the valuation range shifts under different rate and growth assumptions.

---

Tornado Charts: Identifying Key Drivers

A tornado chart is a horizontal bar chart that ranks input variables by their impact on a single output. It answers the question: which assumptions matter most?

How to Build a Tornado Chart

Step 1: Identify the key input variables in your model (revenue growth, EBITDA margin, capex as a percentage of revenue, WACC, terminal multiple, tax rate, etc.).

Step 2: For each input, define a reasonable range -- for example, plus or minus one standard deviation from the base case or a defined upside/downside value.

Step 3: Run your model with each input at its high and low values while holding all other inputs at base case. Record the output for each scenario.

Step 4: Calculate the range (high output minus low output) for each input variable.

Step 5: Plot the ranges as horizontal bars, sorted from largest to smallest. The resulting chart looks like a tornado -- wide at the top (most impactful variables) and narrow at the bottom (least impactful).

Interpreting the Results

The tornado chart tells you where to focus your analytical effort. If the implied share price swings by $15 when you vary the terminal growth rate but only $2 when you vary the tax rate, you should spend your time refining your terminal growth assumption, not debating tax rates.

In presentations, tornado charts are powerful communication tools. They help clients and investment committees quickly understand which assumptions drive the valuation and where the risk lies.

---

Advanced Techniques: Monte Carlo Simulation

For analysts who want to go beyond static sensitivity tables, Monte Carlo simulation offers a probabilistic approach to uncertainty analysis.

Instead of testing discrete values, Monte Carlo simulation assigns probability distributions to each input variable and runs thousands of iterations, producing a probability distribution of the output. This gives you not just a range of outcomes but the likelihood of each outcome.

While Monte Carlo is less common in standard banking work, it appears in:

• Project finance models where cash flow uncertainty is high
• Risk management applications
• Sophisticated LBO models at PE firms
• Insurance and structured product analysis

Excel supports Monte Carlo through VBA macros or add-ins like @RISK and Crystal Ball. Python with NumPy and pandas is increasingly used for this type of analysis.

---

Common Mistakes to Avoid

Using unrealistic ranges: Your sensitivity range should reflect plausible outcomes, not extreme hypotheticals. If WACC is realistically between 8% and 11%, do not build a table that goes from 5% to 20%.

Ignoring correlations: In two-way tables, the two input variables should ideally be independent. If you sensitize revenue growth and EBITDA margin, remember that in reality, higher revenue growth might come with lower margins (investment-driven growth) or higher margins (operating leverage). Call out this limitation.

Failing to label clearly: Every sensitivity table should clearly state which output is being shown, what the input variables are, and where the base case sits within the range. Unlabeled tables create confusion and undermine credibility.

Over-relying on sensitivity analysis: Sensitivity tables are a tool, not a substitute for judgment. A table might show that a stock is worth $30-50, but it takes analytical thinking to determine which scenario is most likely and what the investment conclusion should be.

---

Sensitivity Analysis in Interviews

Interviewers may ask questions like:

• "What are the key sensitivities in a DCF model?" Answer: WACC, terminal growth rate (or terminal value exit multiple), and revenue growth assumptions.
• "How do you build a data table in Excel?" Walk through the steps outlined above.
• "What is the difference between sensitivity analysis and scenario analysis?" Explain the distinction between varying one or two inputs mechanically versus changing multiple inputs to reflect coherent narratives.
• "Which DCF input has the biggest impact on valuation?" Generally the terminal value assumptions -- either the terminal growth rate in a Gordon Growth approach or the exit multiple. This is because terminal value typically represents 60-80% of total enterprise value in a DCF.

---

Build Your Sensitivity Analysis Skills

Sensitivity analysis is a fundamental skill for anyone working in finance. Whether you are building a DCF, an LBO model, or a merger model, the ability to stress-test assumptions and communicate ranges of outcomes is what separates competent analysts from exceptional ones.

Practice building one-way and two-way data tables in Excel until the process is second nature. Study how WACC, terminal value, and growth assumptions interact in valuation models. Use the IB Flash platform to drill technical questions on sensitivity analysis and financial modeling. The more you practice, the more confidently you will handle these topics in interviews and on the job.