Data Tables for Valuation Sensitivity Analysis

1. The "Why": Moving from a Point Estimate to a Range

A DCF valuation is highly sensitive to certain inputs. As you've seen, small changes to the Weighted Average Cost of Capital (WACC) or the terminal perpetuity growth rate can lead to substantial differences in the final share price. Relying on a single set of assumptions is risky.

Sensitivity analysis provides a solution by showing how the output (Implied Share Price) changes in response to changes in key inputs. In your civil engineering background, you would perform similar analyses, testing how a structure responds to varying load conditions or material strengths to understand its limits and safety factors. In finance, we test our valuation against different economic and company-specific scenarios.

As the article notes, the WACC and terminal growth rate are two of the most powerful levers in a DCF model. Therefore, we will focus on building a two-variable data table to analyze their combined impact on the Implied Share Price.

2. The "How": Building a Two-Variable Data Table in Excel

Excel's "Data Table" feature is the perfect tool for this task. It automatically recalculates your model's output for every combination of two specified input variables. Let's walk through the process step-by-step.

For this process to work, you must have a fully integrated financial model where your key inputs (the drivers) are clearly defined and linked to the final output. Our work over the past few lessons has already established this.

Let's summarize and crystallize the key steps from the article:

Step 1: Structure the Table

• Choose a blank area on the same spreadsheet as your key inputs and outputs.
• In the top row, enter the range of values for your first variable (e.g., WACC).
• In the first column, enter the range of values for your second variable (e.g., Terminal Growth Rate).
• Crucially, these values must be hard-coded or calculated from a hard-coded starting point (e.g., 8.0% in the first cell, =PREVIOUS_CELL + 0.5% in the next). Do not link them to other parts of your model, as this can cause errors.

Step 2: Link the Output

• In the top-left corner cell of your table (at the intersection of the row and column headers), create a direct link to your final output cell. In our case, this would be the cell containing the Implied Share Price.
• This is the only cell in the table structure that should be linked to your model.

Step 3: Generate the Data Table

1. Select the entire range of your table, including the row headers, column headers, and the linked output cell in the corner.
2. Go to the Data tab in Excel's ribbon, click on What-If Analysis, and select Data Table.... (Keyboard shortcut: Alt + A + W + T).
3. A dialog box will appear with two fields:
   • Row input cell: Click into this field, then click on the original input cell in your model that corresponds to the variable in your table's top row (e.g., the cell where you input the WACC for your main DCF calculation).
   • Column input cell: Click into this field, then click on the original input cell in your model that corresponds to the variable in your table's left column (e.g., the cell for the Terminal Growth Rate).
4. Click OK. Excel will populate the table with the calculated share prices for each combination of inputs.

3. Interpreting the Results and Common Pitfalls

Once the table is populated, you need to check and interpret it.

Verification:
The resource from Breaking Into Wall Street (LINK) provides a good checklist.

Does the logic hold? As WACC increases, the implied share price should decrease. As the terminal growth rate increases, the implied share price should increase. If you see a whole row or column with the same value, something is wrong with your setup.

Common Pitfalls:
Building data tables can sometimes be frustrating. The resource from Financial Edge Training (LINK) outlines several common problems and their solutions.

Here are the most critical pitfalls to remember:

• Calculation Settings: Many financial models are set to "Automatic except for data tables" to prevent the workbook from slowing down. If your table doesn't calculate, check your settings under File > Options > Formulas. You can manually force a recalculation by pressing F9.
• Inputs on Different Sheets: The Data Table function requires the "Row input cell" and "Column input cell" to be on the same worksheet as the data table itself.
• Rounding Errors: Sometimes, the value in the center of your table (which should correspond to your base case) doesn't perfectly match your model's output. This is often because the hard-coded value in your table header (e.g., 8.5%) has fewer decimal places than the calculated value in your model (e.g., 8.5214%). To fix this, you can copy and "Paste as Value" the exact input from your model into the table header.

Conclusion

You have now reached the end of the DCF Valuation module and have a complete, robust valuation tool at your disposal. You can not only calculate an intrinsic value per share but also understand and present the sensitivity of that valuation to its most critical underlying assumptions.

Key Takeaways:

• Sensitivity analysis is essential for understanding the risk and uncertainty inherent in a DCF valuation.
• A two-variable data table is the standard method for testing the combined impact of key drivers like WACC and terminal growth rate.
• The setup is methodical: structure the table with hard-coded headers, link the output in the corner, and correctly assign the original model inputs in the Data Table dialog box.
• Being aware of common pitfalls—like calculation settings, input locations, and rounding errors—is crucial for efficient and accurate modeling.

This powerful technique of modeling a range of outcomes is a cornerstone of professional financial analysis.

Preview of the Next Module:
So far, we have focused entirely on intrinsic valuation—valuing a company based on its own cash-generating ability. In our next module, "Relative Valuation and Market Comparables," we will pivot to a different approach. We will learn how to value a company by comparing it to its peers in the public markets and to similar companies that have recently been acquired. This will add another critical layer to your valuation skill set, allowing you to see a company's value from multiple perspectives.