Sensitivity Analysis: Stress-Testing the Model

Sensitivity analysis is the process of "stress-testing" your valuation to see how the final price changes when you alter your most important assumptions. In the world of finance, we often call this "What-If" analysis. Because a DCF model is built on a foundation of estimates—revenue growth, profit margins, and discount rates—even a tiny change in one of these numbers can lead to a massive swing in the final valuation [1] . For example, changing a terminal growth rate from 2% to 3% might add millions, or even billions, to a company's perceived value. Sensitivity analysis helps you identify which variables are the "value drivers" and how much "margin of safety" you have if your forecasts are slightly off.

Variables: Identifying the Critical Drivers

Not every number in your spreadsheet is equally important. Sensitivity analysis focuses on the "High-Impact" variables. In a standard DCF, the two most sensitive inputs are usually the Weighted Average Cost of Capital (WACC) and the Terminal Growth Rate [1] .

1. The WACC (Discount Rate): This represents the riskiness of the investment. If interest rates in the economy rise, the WACC typically rises as well. A higher WACC means that future cash flows are worth less today, which drags down the NPV [1] .
2. Terminal Growth Rate: This is the rate at which you assume the company will grow forever after the initial forecast period. Because this number affects the "Terminal Value" (which often makes up 60-80% of a DCF's total value), it is incredibly powerful.
3. Operating Margins: If a company’s costs are higher than expected, its Free Cash Flow (FCF) will drop. Testing different margin scenarios helps you see if the company remains a good investment even if it faces inflation or increased competition.

Step-by-Step: Creating a Sensitivity Table

Most analysts use a "Data Table" in Excel to perform a two-variable sensitivity analysis. This creates a grid that shows the stock price at various intersections of WACC and Growth.

• Step 1: Set up the Grid. Place your WACC assumptions across the top row (e.g., 7%, 8%, 9%, 10%, 11%) and your Terminal Growth assumptions down the left column (e.g., 1%, 1.5%, 2%, 2.5%, 3%).
• Step 2: Link the Formula. In the top-left corner of the grid, link to your final "Implied Share Price" cell from your DCF model.
• Step 3: Run the Data Table. Highlight the entire grid, go to the "Data" tab, select "What-If Analysis," and then "Data Table." Link the "Row Input" to your WACC cell and the "Column Input" to your Growth Rate cell.
• Step 4: Analyze the Results. Excel will automatically fill the grid with different share prices. You can now see, for instance, that if WACC stays at 9% but growth drops to 1%, the stock is worth $50, but if growth hits 3%, it’s worth $120.

Risk: Incorporating Beta and Interest Rates

Sensitivity analysis also allows you to account for market-wide risks. One such risk is Rho, which measures how an option's (or a company's) price changes in response to shifts in the risk-free interest rate [2] . While Rho is often used in options trading, the principle applies to valuation: as interest rates rise, the "hurdle rate" for investments increases, making future cash flows less valuable [1] .

Another factor to stress-test is Beta, the measure of a company's sensitivity to market swings [3] . If you are valuing a private company, you might "unlever" the betas of public peers to find the industry risk and then "relever" it based on your target's specific debt levels [3] . By testing a range of Betas (e.g., a "Low Beta" scenario for stability and a "High Beta" scenario for volatility), you can see how sensitive your valuation is to the overall mood of the stock market.

Simulations: The Monte Carlo Approach

For a more advanced "stress test," some analysts use a Monte Carlo Simulation. This method was invented in the 1940s to solve complex problems using random and probabilistic methods [4] . Instead of just changing one or two variables, a Monte Carlo simulation runs thousands of versions of your model, randomly changing all your inputs within a specified range.

• Why use it? It helps provide solutions for situations that are highly uncertain or have too many random variables in play [4] .
• The Output: Instead of a single "Fair Value," a Monte Carlo simulation gives you a "Probability Distribution." It might tell you there is a 70% chance the stock is worth between $80 and $100, a 15% chance it's worth more, and a 15% chance it's worth less.
• Practicality: While advanced, you can create a basic version in Excel using the "RANDBETWEEN" function to simulate different outcomes, such as a game of dice where you track wins and losses over 5,000 iterations [4] .

FAQ: Common Sensitivity Questions

Q: Why is my DCF so sensitive to the Terminal Growth Rate?
A: Because the Terminal Value represents all the cash a company will ever make from year 5 or 10 until the end of time. A 1% change in "forever" is a huge amount of money when discounted back to today [1] .

Q: What is a "Good" range to test for WACC?
A: Usually, analysts test +/- 1% or 2% from their base case. If your calculated WACC is 9%, you should look at the results from 7% to 11% to see how much "room for error" you have.

Q: Can sensitivity analysis help with private companies?
A: Absolutely. Private companies often have "Illiquidity Discounts" (typically 20% to 30%) because their shares are harder to sell [3] . You should stress-test this discount to see how it affects the final price.

Q: What if my sensitivity table shows a negative value?
A: This usually happens if your WACC is lower than your Terminal Growth Rate. Mathematically, the formula breaks because it assumes the company will grow faster than the rate at which you are discounting it, implying infinite value. In the real world, a company cannot grow faster than the overall economy forever [1] .

[1] , [4]