A DCF should hand you a matrix, and spreadsheet data tables get you one without copy-pasting the model 25 times.
Setup in Excel. Sheets differences come after.
- Make sure WACC and terminal g, the two inputs you want to shock, are single cells. Here they're C4 and C17. Excel requires a data table's input cells to be on the same worksheet as the table. If WACC is computed on the WACC tab, copy what we did. Make C4 on the DCF tab the officiating cell, which turns
=WACC!C12into an input mirror. Paste it as a value, or restructure so the DCF tab's C4 is the true input and the WACC tab reads from it. Cleanest of all is a single ⟦WACC override⟧ input cell on the DCF tab. - Pick an empty block, say B40:G45. In the corner cell, B40, link the output with
=C28, the value per share. That corner holds the output. It'll look like a stray number, so label it. - Across the top row, C40:G40, type the g values: 4.0%, 4.5%, 5.0%, 5.5%, 6.0%. Down the left column, B41:B45, type the WACC values from 11.0% to 13.0% in 0.5% steps. These are typed constants, and they're the one legitimate place hardcodes appear, because they're the question being asked.
- Select the whole block B40:G45, then go to Data → What-If Analysis → Data Table. Set Row input cell to
$C$17, because g runs across the row, and Column input cell to$C$4, because WACC runs down the column. Click OK. - Excel fills the block with
{=TABLE(C17,C4)}array formulas. Each cell is the model recomputed at that (WACC, g) pair.
Advertisement
What goes wrong, and what about Google Sheets?
This goes wrong in three ways. First up is a swap of the row and column inputs, which fills the table with a transposed nonsense grid. Test for it by checking the center cell against your base case. At 12% and 5% it should read 157.1. Second, the input cells are on another sheet, and Excel refuses or, worse, references the wrong cell without telling you. Third, someone "cleans up" the corner cell's formula into a typed number. From then on the table stops updating, and nobody notices for a month. Data tables also recalculate on every change and can crawl on big models, so set Formulas → Calculation Options → Automatic Except for Data Tables and press F9 when you need them fresh.
Google Sheets has no native data table. Build the matrix with a formula that re-derives value from each (WACC, g) pair, or use two one-way tables. The logic is the same in both.
Worked example: value per share by WACC and growth
Sundar's matrix shows value per share in ₹, with the reinvestment-consistent TV recomputed in every cell:
| WACC \ g | 4.0% | 4.5% | 5.0% | 5.5% | 6.0% |
|---|---|---|---|---|---|
| 11.0% | 169.9 | 175.9 | 182.7 | 190.8 | 200.4 |
| 11.5% | 158.6 | 163.5 | 169.0 | 175.3 | 182.8 |
| 12.0% | 148.8 | 152.7 | 157.1 | 162.2 | 168.2 |
| 12.5% | 140.0 | 143.3 | 146.9 | 151.0 | 155.7 |
| 13.0% | 132.3 | 135.0 | 137.9 | 141.3 | 145.1 |
Read it like an analyst, and resist the urge to pick a winning cell. Across the defensible neighborhood, WACC 11.5 to 12.5% and g 4.5 to 5.5%, value runs ₹143 to 175. At ₹185, the market price is outside that box. You'd need ~11% WACC and ~5.5% forever-growth to touch it. Notice the table's shape too. At this spread, value is far more sensitive to WACC than to g. A 0.5% WACC move is worth ≈ ₹10, and a 0.5% g move only ≈ ₹5. That's because the reinvestment-consistent numerator makes growth cost something. A table built on the naive TV would show fake g-sensitivity, since growth that comes free of charge always looks more valuable than it is.