DCF Valuation Excel Modelling Tips: Building a Scalable Financial Model Structure

The 2025-2026 financial year has brought renewed scrutiny to valuation methodologies used in Hong Kong public filings, driven by the SFC’s updated Guidance Note on Valuation Practices (April 2025) and the HKEX’s ongoing review of sponsor work quality under Listing Rules Chapter 3A. For CFOs and corporate finance advisors, the DCF model remains the most frequently challenged component in IPO prospectuses, takeover circulars, and impairment tests under HKAS 36. A single structural error — such as a hard-coded terminal growth rate or a circular reference in the debt schedule — can trigger an SFC inquiry or a sponsor deficiency letter, delaying a transaction by weeks. The cost of a poorly built model is not merely computational; it is regulatory. This article provides a structural framework for building scalable, audit-ready DCF models in Excel, emphasising modular design, explicit assumption management, and error-proofing techniques that align with the documentation standards expected by Hong Kong regulators and the HKICPA.

The Architectural Principle: Modularity Over Monolithic Design

A scalable DCF model must be built as a set of independent, self-contained blocks. The most common failure in models reviewed by Hong Kong sponsors is the use of a single worksheet containing historical data, projections, and valuation calculations on the same grid. This creates two problems: it makes audit trails impossible to trace, and it forces the user to re-enter or copy-paste data across multiple sections, introducing manual error.

The recommended structure separates the model into four distinct workbooks or, at minimum, four clearly labelled worksheet groups: (1) Inputs and Assumptions, (2) Historical Financials, (3) Projected Financials and Free Cash Flow Build, and (4) Valuation and Sensitivity. Within each group, every cell containing a hard-coded number — as opposed to a formula — must be flagged with a consistent colour convention. The Hong Kong Institute of Certified Public Accountants (HKICPA) Practice Note 970.2, “Financial Modelling for Business Valuations” (2023 revision), specifically recommends that all input cells be shaded in light blue or yellow, and that no formula cell contain a hard-coded constant. This is not a stylistic preference; it is a control mechanism that allows a reviewer to identify, within seconds, whether a model contains embedded assumptions that are not documented in the assumptions sheet.

Assumption Management: The Single Source of Truth

The assumptions sheet should contain every driver that affects the cash flow projection: revenue growth rates, gross margin percentages, operating expense ratios, tax rates, depreciation as a percentage of capital expenditure, working capital turnover days, and the weighted average cost of capital (WACC) components. Each assumption must have a named cell reference (e.g., =Revenue_Growth_Y1) rather than a cell address like =B15. This practice, while requiring initial setup effort, eliminates the risk of broken links when rows are inserted or deleted during model updates.

For Hong Kong-listed companies, the WACC calculation must explicitly reflect the jurisdiction-specific risk parameters. The risk-free rate should be sourced from the Hong Kong Exchange Fund Notes yield curve for the relevant tenor (typically 10-year), not from a generic US Treasury rate unless the company’s cash flows are USD-denominated. The equity risk premium should reference the Duff & Phelps (now Kroll) HK Market Risk Premium, which as of December 2024 stood at 7.2% for the Hong Kong market, versus 5.8% for the US. The SFC’s April 2025 Guidance Note on Valuation Practices explicitly states that “the selection of the risk-free rate must correspond to the currency and duration of the projected cash flows,” citing the HKMA’s daily HKD yield curve data as the appropriate source for Hong Kong-dollar-denominated valuations.

The Free Cash Flow Build: Avoiding the Circular Reference Trap

The free cash flow to firm (FCFF) build is the section where most modelling errors occur, particularly in the interaction between interest expense, tax shields, and debt repayments. A common error is to calculate interest expense based on the average debt balance for the period, which in turn depends on the cash flow generated in that period, creating a circular reference. Excel’s iterative calculation feature can resolve this, but it introduces model instability and slows computation significantly when running sensitivity tables.

The correct approach is to build the FCFF using a “debt schedule out” methodology: project the cash flow first without any financing effects, then layer the debt schedule separately. The FCFF should be defined as NOPAT (net operating profit after tax) plus non-cash charges (depreciation and amortisation) minus capital expenditure minus the change in working capital. This definition excludes interest expense by design, because FCFF is the cash flow available to all capital providers before debt service. The circular reference only arises if one attempts to calculate FCFF after interest, which is a conceptual error.

For Hong Kong reporting purposes, the tax shield on interest should be calculated in the WACC, not in the cash flow. The SFC’s Code of Conduct for Persons Licensed by or Registered with the SFC (paragraph 16.5) requires that valuation reports “clearly state whether the tax shield is reflected in the discount rate or in the cash flows, and the rationale for the chosen approach.” The standard practice in Hong Kong sponsor reports is to reflect the tax shield in the WACC via the after-tax cost of debt, which avoids the circular reference entirely.

Terminal Value: The Single Most Dangerous Input in the Model

The terminal value in a DCF model for a Hong Kong-listed company typically accounts for 60% to 80% of the total enterprise value, depending on the stage of the company and the length of the explicit projection period. A one-basis-point change in the terminal growth rate can alter the valuation by 1.5% to 3.0% for a mature company with a 5-year projection period. The SFC has flagged terminal value assumptions as a recurring deficiency in sponsor valuation reports, noting in its 2024 Annual Report that “in 18% of reviewed valuation reports, the terminal growth rate exceeded the long-term nominal GDP growth rate of the relevant economy without justification.”

The terminal growth rate must be anchored to an observable benchmark. For a Hong Kong-listed company whose primary operations are in the PRC, the appropriate reference is the National Bureau of Statistics of China’s long-term nominal GDP growth forecast, which as of mid-2025 stands at approximately 4.0% to 4.5% for the 2026-2030 period. For a company operating solely in Hong Kong, the reference should be the HKMA’s potential growth estimate, which was 2.5% in the October 2024 Monetary Policy Statement. Any growth rate above these levels requires explicit justification in the assumptions sheet, referencing industry-specific growth drivers or market share expansion plans.

The Perpetuity Method vs. the Exit Multiple Method

The terminal value should be calculated using both the perpetuity growth method and the exit multiple method, with the final value being the average or a weighted average, provided the two methods produce results within a reasonable range (typically ±15%). The exit multiple should be based on the median forward EV/EBITDA multiple of the company’s peer group as defined in the prospectus or valuation report. The peer group selection must follow HKEX Listing Rules Chapter 11’s guidance on comparable company analysis, which requires that peers be in the same industry, of similar size (within one order of magnitude of market capitalisation), and listed on a recognised exchange.

A practical modelling tip: build the terminal value calculation in a separate worksheet, with the perpetuity growth formula =FCFF_Last_Year * (1 + Terminal_Growth_Rate) / (WACC - Terminal_Growth_Rate) and the exit multiple formula =EBITDA_Last_Year * Exit_Multiple side by side. Both formulas should reference the same terminal growth rate and WACC cells from the assumptions sheet. Then, add a cell that calculates the absolute percentage difference between the two values. If this difference exceeds 20%, the model should flag a warning, prompting the analyst to re-examine either the growth rate, the exit multiple, or the peer group selection.

Error-Proofing and Audit Trail Construction

A scalable model is one that can be audited by a third party — whether a sponsor, an SFC examiner, or an external valuer — without requiring the original modeller’s explanation. This requires three structural elements: (1) a consistent colour-coding convention, (2) a named range and formula audit log, and (3) a cross-check summary sheet.

Colour-Coding and Formula Transparency

The standard convention used in Hong Kong investment banking models is: blue font for hard-coded inputs, black font for formulas, and red font for references to other sheets or workbooks. Input cells should be shaded light yellow. All formula cells should be left unshaded. This convention allows a reviewer to visually scan a model and immediately identify which cells contain assumptions that could be changed. The HKICPA Practice Note 970.2 recommends that every model include a “legend” sheet that documents this colour convention, along with a list of all named ranges and their definitions.

The Cross-Check Summary Sheet

The final structural element is a cross-check summary sheet that reconciles the model’s output to the company’s reported financials for the historical period. For a DCF model to be credible, the projected financials for the most recent historical year must match the audited financial statements within a tolerance of 0.1% for revenue, EBITDA, and net income. The cross-check sheet should contain formulas that calculate the absolute difference between the model’s historical year values and the actual reported values, and flag any variance exceeding 0.1%.

This step is not merely a modelling nicety; it is a regulatory requirement. The SFC’s Code of Conduct (paragraph 16.3) requires that “all financial projections included in a valuation report must be traceable to the underlying assumptions and, where historical data is used, to the audited financial statements.” A model that fails this reconciliation will be returned by the SFC with a deficiency letter, causing delays in the transaction timetable.

Sensitivity Analysis: Beyond the Standard Two-Way Table

The standard two-way sensitivity table (varying WACC and terminal growth rate) is insufficient for a Hong Kong regulatory filing. The SFC’s April 2025 Guidance Note explicitly requires that sensitivity analysis “address the key value drivers specific to the company, not merely the discount rate and terminal growth rate.” For a property developer, this means varying the average selling price per square foot and the sales volume. For a retail company, it means varying same-store sales growth and gross margin. For a biotech company with no revenue, it means varying the probability of regulatory approval and the peak market share.

The model should include at least three sensitivity tables, each with a different pair of drivers. Each table should output enterprise value and equity value per share. The tables must be constructed using Excel’s Data Table feature (not manual copy-paste), and the WACC and terminal growth rate should be the first table because they are the most universally applied. The second and third tables should be specific to the company’s business model, as identified in the prospectus risk factors section.

Actionable Takeaways

1. Build the DCF model as four separate worksheet groups (Inputs, Historicals, Projections, Valuation) with a consistent colour-coding convention that distinguishes hard-coded inputs from formulas, following the HKICPA Practice Note 970.2 standard.

2. Source the risk-free rate from the HKMA’s HKD yield curve for Hong Kong-dollar-denominated cash flows, and reference the Kroll HK Market Risk Premium (7.2% as of December 2024) for the equity risk premium, as required by the SFC’s April 2025 Guidance Note.

3. Calculate FCFF using the NOPAT-based definition that excludes interest expense, and reflect the tax shield in the WACC via the after-tax cost of debt, to eliminate circular references in the debt schedule.

4. Anchor the terminal growth rate to the NBS China nominal GDP forecast (4.0%-4.5%) or the HKMA potential growth estimate (2.5%), and flag any deviation exceeding 50 bps in a dedicated justification note within the assumptions sheet.

5. Include a cross-check summary sheet that reconciles the model’s historical year projections to the audited financial statements within a 0.1% tolerance, and build at least three sensitivity tables that address company-specific value drivers, not just WACC and terminal growth.