Performing Sensitivity Analysis in Excel

Once you've calculated a single enterprise value using your base assumptions, it's time to ask a key question: how sensitive is that value to change? This chapter addresses that by performing a sensitivity analysis on the two most impactful drivers of terminal value:

1. WACC (Weighted Average Cost of Capital) – reflects risk and opportunity cost;

2. Perpetuity Growth Rate (g) – reflects the expected long-term growth beyond forecast.

Both appear in the denominator of the terminal value formula:

Small changes in either variable can lead to large swings in terminal value, and thus in enterprise value. Sensitivity analysis helps you visualize those effects.

In Excel, the process is straightforward:

• You build a 2D table using WACC as the row input and growth rate as the column input;

• Each cell in the table recalculates the resulting enterprise value using different combinations of those inputs;

• You can use Excel's Data Table feature to automate the calculations across all scenarios.

This table doesn't just reveal upside or downside—it clarifies the risk. If your valuation varies wildly with small changes, your model may be too sensitive or reliant on long-term speculation.

A well-constructed sensitivity table empowers stakeholders to make decisions with open eyes:

• Investors see risk and return trade-offs;

• Operators see how strategy can reduce valuation volatility;

• Analysts see which assumptions deserve the most scrutiny.