Why Your Sensitivity Table Mirrors Values: and How to Fix It

Are you grappling with mirrored values in your sensitivity table, where share prices aren’t reflecting the changes in WACC and growth rate as expected?

3 min readMicrosoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

There's a simple explanation for why your sensitivity table keeps mirroring values: you've built the data table's row and column inputs backward relative to how your formulas reference them. This isn't a flaw in your logic, it's a classic structural mix-up that trips up even experienced spreadsheet users. The mirrored output isn't a sign that your model is broken; it's a signal that the table's structure and your formula's input cells are out of sync.

In practical terms, what you're seeing is the difference between a one-variable and a two-variable data table. For a two-variable table, the formula in the top-left cell must reference both input cells, your WACC and growth rate, as part of the calculation that drives equity value per share. If your top-left cell only points to the equity value formula but doesn't explicitly link to both inputs, Excel will treat one axis as a series of values to replace and the other as a fixed reference. That's when you get duplicate or mirrored results: one input is being ignored, so the table repeats the same output across the row or column.

The fix is straightforward but requires precision. Make sure the top-left cell of your data table contains a formula that references both the WACC cell and the growth rate cell, not just the final equity value. For example, if your equity value formula is `=EquityValue(WACC, GrowthRate)`, then the top-left cell should be that exact formula, not a static number. Then, when you set up the data table, Excel will substitute each row value (the WACC percentages) and each column value (the growth rates) into the formula correctly. If you've done that and still see mirroring, double-check that your row input cell is set to the WACC cell and your column input cell is set to the growth rate cell, swapping those two is the most common cause of the exact symptom you described.

What this means for you is simple: your model is close, and the fix is mechanical, not conceptual. Take a few minutes to trace the references in your top-left cell and confirm the input cells in the data table dialog match the blue-linked cells you mentioned. Once those line up, the table will produce the asymmetric grid of values you expect, and you'll see how changes in WACC and growth rate genuinely interact. You don't need a new approach, just a tighter grip on how Excel maps the inputs to the formula. That's the difference between a table that mirrors and one that informs.

From Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

I'm trying to do a sensitivity table which changes the value of the share price based on changes in the WACC and growth rate but have been getting duplicate/mirrored? values.

Not sure if formula text is enough for anyone to get the picture, but feel free to ask for more details. Also the 10.06% on the left side of the data table is linked to the WACC input in blue, and the 2.00% at the top of the table is linked to the growth rate in blue.

Read the original at Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community