Sensitivity Analysis in DCF Models: Testing Key Assumptions with Excel Data Tables

The SFC’s December 2024 consultation on the proposed Code of Conduct for ESG Ratings and Data Products Providers (the “Consultation Paper”) has placed renewed scrutiny on the quantitative assumptions underpinning long-duration asset valuations. With the HKMA’s Supervisory Policy Manual module SA-2 (Interest Rate Risk Management) requiring banks to stress-test key valuation parameters, and the HKEX’s 2023 amendments to the Listing Rules mandating enhanced climate-related disclosures under Appendix 27, the margin for error in a Discounted Cash Flow (DCF) model has narrowed considerably. For CFOs and financial advisors, the days of presenting a single-point terminal value as a definitive answer are over. The market now demands a probabilistic range, and the most accessible tool for delivering this is the Excel Data Table. This article examines the mechanics of building a two-variable sensitivity analysis in a DCF model, the regulatory context that makes it a necessity, and the pitfalls that can render the output misleading if not properly structured.

The Regulatory Imperative for Sensitivity Analysis

The HKMA’s Supervisory Policy Manual module IC-1 (Risk Management Framework) requires authorised institutions to identify, measure, and monitor material risks, including valuation risk arising from model assumptions. While this applies directly to banks, the principle cascades to listed issuers through the HKEX’s Listing Rules, particularly Main Board Rule 2.13, which mandates that information in a prospectus or annual report must be sufficiently detailed to enable investors to make an informed assessment of the issuer’s financial position.

The SFC’s September 2023 circular on “Management of Climate-Related Risks by Fund Managers” explicitly references the use of scenario analysis and sensitivity testing as part of a robust risk management framework. For a CFO presenting a DCF-based valuation to a board or an investment committee, failing to show how the valuation changes under different revenue growth rates or discount rates is no longer a matter of analytical preference—it is a compliance risk.

Building the Two-Variable Data Table

A DCF model’s output is most sensitive to two inputs: the weighted average cost of capital (WACC) and the terminal growth rate. The Excel Data Table function (Data > What-If Analysis > Data Table) allows the user to generate a matrix of valuations across a range of values for these two variables. The mechanics are straightforward, but the execution requires precision.

Structuring the Input Grid

The Data Table requires a formula cell that references the DCF output (e.g., the total enterprise value). This formula must be placed at the top-left corner of the grid. The row input cell corresponds to the variable you want to vary across the columns (typically the WACC), and the column input cell corresponds to the variable you want to vary across the rows (typically the terminal growth rate). The model must be structured so that these two input cells are the only variables being changed—any other assumption that shifts during the calculation will corrupt the output.

Common Errors in Grid Setup

A frequent error in practice is placing the formula cell in the wrong location. The Data Table function reads the formula from the cell directly above the first column of results and to the left of the first row of results. If the formula cell is not positioned correctly, the table will return incorrect values or a circular reference error. Another common mistake is failing to anchor the reference to the DCF output cell. If the output cell is not an absolute reference (using $ signs), the Data Table will recalculate the formula for each cell in the grid, potentially breaking the model’s logic.

Interpreting the Output Matrix

Once the Data Table is populated, the user is presented with a matrix of enterprise values. For a Hong Kong-listed issuer with a market capitalisation of HKD 5 billion, a WACC range of 8% to 12% and a terminal growth rate range of 2% to 4% might produce values ranging from HKD 4.2 billion to HKD 6.8 billion. The interpretation requires more than a simple colour scale—it demands an understanding of the probability distribution of the inputs.

Identifying the Breakeven Point

The matrix allows the user to identify the combination of WACC and terminal growth rate at which the DCF valuation equals the current market capitalisation. This breakeven line is the most actionable output for an investment committee. If the current market price implies a WACC of 9.5% at a 3% terminal growth rate, the board can assess whether that combination is reasonable given the issuer’s cost of debt (based on recent bond issuances) and its long-term growth prospects (based on industry reports from the Hong Kong Trade Development Council).

The Risk of False Precision

A matrix with 100 cells (10 WACC values x 10 growth rates) gives the appearance of precision, but the underlying assumptions—the cash flow projections themselves—are point estimates. The Data Table does not incorporate the uncertainty in the cash flow projections; it only shows the sensitivity to the two selected variables. A prudent analyst will overlay a third dimension, such as a Monte Carlo simulation on the revenue growth rate, to generate a more complete picture. The HKMA’s Supervisory Policy Manual module TM-1 (Technology Risk Management) encourages the use of multiple models and stress scenarios to avoid over-reliance on a single analytical output.

Regulatory Disclosure and Presentation Standards

The HKEX’s Listing Rules do not prescribe a specific format for sensitivity analysis in financial statements, but the requirement for “fair presentation” under Hong Kong Financial Reporting Standards (HKFRS) implies that material assumptions must be disclosed. The HKICPA’s Practice Note 740 (Auditing Accounting Estimates and Related Disclosures) provides guidance on evaluating the reasonableness of management’s estimates, and a sensitivity analysis is one of the recognised methods.

Presenting to the Board

When presenting a Data Table output to a board, the focus should be on the range, not the point estimate. The SFC’s May 2022 circular on “Disclosure of Non-GAAP Financial Measures” cautions against presenting adjusted figures that exclude recurring items without a clear reconciliation. The same principle applies to DCF valuations: the board should see the base case valuation, the range of outcomes, and the key assumptions that drive the extremes. A waterfall chart showing the contribution of each assumption to the total valuation range is more informative than the raw matrix.

Cross-Border Considerations

For issuers with a dual listing on the HKEX and the Shanghai or Shenzhen Stock Exchanges, the sensitivity analysis must also comply with the China Securities Regulatory Commission’s (CSRC) disclosure requirements. The CSRC’s 2023 revisions to the “Administrative Measures for Information Disclosure of Listed Companies” require the disclosure of the key parameters used in valuation models, including the discount rate and the growth rate. The Excel Data Table output, when properly annotated, satisfies this requirement.

Practical Workflow for the Analyst

The analyst’s workflow should begin with a clean, auditable DCF model. The model should have a dedicated “Assumptions” sheet where the WACC and terminal growth rate are clearly labelled and referenced. The Data Table should be built on a separate “Sensitivity” sheet to avoid cluttering the main model.

Automating the Table Update

Excel’s Data Table recalculates every time the worksheet is recalculated, which can slow down a large model. The analyst should set the calculation option to “Manual” (Formulas > Calculation Options > Manual) while building the model, then switch to “Automatic” only when the final output is ready. For models with multiple Data Tables, the workbook can be structured so that each table references a different output cell (e.g., enterprise value, equity value, and implied share price).

Validating the Output

The final step is validation. The analyst should check the Data Table’s output against a manual calculation for at least three cells in the grid—one at the top-left, one at the centre, and one at the bottom-right. A discrepancy of even one decimal place indicates a structural error in the model. The HKMA’s Supervisory Policy Manual module SA-2 recommends independent validation of all quantitative models used for risk management, and the same standard should apply to valuation models used for board reporting.

Actionable Takeaways

1. Build the Data Table on a dedicated sensitivity sheet with the DCF output cell referenced as an absolute formula ($A$1) to prevent recalculation errors.
2. Set Excel calculation to manual while constructing the table, then validate the output against three manual calculations before presenting to the board.
3. Present the breakeven line—the combination of WACC and terminal growth rate that matches the current market price—as the primary analytical output, not the full matrix.
4. Disclose the range of WACC and terminal growth rate used in the sensitivity analysis in the annual report, referencing HKFRS requirements for material assumptions.
5. For dual-listed issuers, ensure the sensitivity analysis meets the CSRC’s 2023 disclosure requirements for key valuation parameters, including the discount rate and growth rate.