Excel
All Top 50 Excel & Business Modeling Questions#47
Building Two-Variable Sensitivity Matrices with Excel Data Tables
HardGoldman SachsInterview Question #47
Asked at Goldman SachsIn 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.
Input Table: ModelInputs
3 rows preview| Driver | Baseline Value | Cell Reference |
|---|---|---|
| WACC | 10.0% | B2 |
| Terminal Growth Rate | 2.5% | B3 |
| Enterprise Value (Output) | $150.0M | C10 |
Expected Output Structure3 rows
| 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 |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 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 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.