Building Two-Variable Sensitivity Matrices with Excel Data Tables

Question

In a corporate valuation model, Enterprise Value is calculated in cell `C10` based on WACC in cell `B2` and Terminal Growth Rate in cell `B3`. Construct a 2-variable Data Table in range `E5:J10` where row headers in `F5:J5` represent WACCs (8% to 12%) and column headers in `E6:E10` represent Terminal Growth Rates (1.5% to 3.5%). Explain the exact setup and formula corner link.

Accepted Answer

Optimal Solution:
1. In the top-left corner cell (E5), link the output: =C10
2. Select the entire table matrix: E5:J10
3. Data tab > What-If Analysis > Data Table:
   Row input cell: B2 (WACC)
   Column input cell: B3 (Terminal Growth Rate)

Technical Explanation:
The formula corner (E5) anchors the calculation. Excel's TABLE() function iterates each row header into B2 and each column header into B3, evaluating the entire matrix into memory.

Driver	Baseline Value	Cell Reference
WACC	10.0%	B2
Terminal Growth Rate	2.5%	B3
Enterprise Value (Output)	$150.0M	C10

Sensitivity Grid	8.0% WACC	10.0% WACC	12.0% WACC
1.5% Growth	$180M	$140M	$115M
2.5% Growth	$200M	$150M (Base)	$122M
3.5% Growth	$230M	$165M	$132M

	A	B	C	E
1	Driver	Baseline Value	Cell Reference
2	WACC	10.0%	B2	Target [Enter Formula]
3	Terminal Growth Rate	2.5%	B3
4	Enterprise Value (Output)	$150.0M	C10
5
6
7