Reference

DCF Analysis

Overview

Build a DCF as a clean extension of an existing forecast model, not as a disconnected valuation page. The model should start from forecast operating data, calculate NOPAT, derive Unlevered Free Cash Flow, build a transparent WACC, calculate Terminal Value, discount to Enterprise Value, bridge to Equity Value, and end with Implied Share Price plus sensitivity tables.

The output should be compact, auditable, and presentation-ready. Default to a banker-style Excel layout with disciplined color usage, explicit hardcode vs. formula distinction, minimal borders, and clearly labeled sections.

The DCF must also show real market judgment and business sense. The forecast linkage, WACC, terminal assumptions, valuation bridge, and sensitivities should reflect a defendable understanding of the market, the company, the business model, and how investors would underwrite the valuation. Do not choose assumptions mechanically; show business understanding through the numbers.

If the user does not provide a fixed layout requirement and does not upload a template workbook to mimic, default to the embedded # DCF Format Reference section in this file.

Workflow

1. Define the valuation frame. Confirm valuation date, currency, units, forecast horizon, tax convention, discounting convention, terminal value method, diluted share-count convention, and bridge-year balance sheet date. If the user does not specify, default to 5-year explicit forecast, mid-year convention, perpetuity growth as the primary terminal method, and exit multiple as a cross-check.

2. Link forecast data from the operating model. Pull forecast lines directly from the existing model rather than retyping values. At minimum, link Revenue, EBIT, Depreciation, Amortization, Capex, Operating Working Capital or its components, tax rate, cash, debt, lease liabilities when relevant, and diluted shares.

3. Build the NOPAT -> UFCF schedule. Use operating income as the starting point. The default structure is:

   • EBIT
   • Less: Taxes on EBIT
   • NOPAT
   • + D&A
   • - Capex
   • - Increase in Operating Working Capital
   • +/- Other recurring operating adjustments only when clearly justified
   • = Unlevered Free Cash Flow

4. Build a transparent WACC. Show Cost of Equity, Cost of Debt, capital structure weights, tax shield, and any premiums or adjustments. WACC must be explainable from visible assumptions. Do not hardcode a final rate without showing how it was built.

5. Calculate Terminal Value. Use Perpetuity Growth Method as the default primary method. Use Exit Multiple Method as a cross-check when the business and market context support it. Make sure the terminal base and the terminal multiple are consistent.

6. Discount the forecast and terminal value. Discount explicit-period UFCF and Terminal Value separately. Use mid-year convention by default unless the user requests year-end discounting or the workbook structure makes year-end discounting more appropriate.

7. Bridge EV to Equity Value and Implied Share Price. Start from Enterprise Value, subtract net debt and other senior claims, add non-operating assets when justified, then divide by diluted shares. Keep the bridge explicit and traceable.

8. Build sensitivity analysis. At minimum, include one two-way sensitivity table. Default sensitivity tables:

   • WACC x Terminal Growth
   • WACC x Exit Multiple when an exit multiple is shown
   • Sensitivity table values must be formula-driven in Excel. Do not hardcode the sensitivity body with pre-calculated numbers from Python or manual paste.

9. Review before delivery. Check signs, units, years, tax treatment, terminal assumptions, discount factors, net debt bridge, and diluted share count. Confirm the sensitivity tables move in the right direction: higher WACC lowers value; higher g or exit multiple raises value.

Output Rules

• Default to one dedicated DCF tab unless the user asks for a different workbook structure.
• Keep the page compact. Do not create large presentation-style gaps that hide the math.
• Keep hardcodes visibly distinct from formulas at all times.
• Use one year-label style across the full sheet. Do not mix 2028E, '28, and LTM without explicit reason.
• Show one clear base case and use cross-checks only as secondary support.
• Display -- or NM instead of visible Excel errors or broken formulas.

Decision Rules

• If the user does not specify otherwise, use 5 years of explicit forecast.
• Use NOPAT = EBIT x (1 - tax rate) as the default operating after-tax profit measure.
• UFCF must remain unlevered and pre-debt. Do not include interest expense, debt issuance, debt repayment, or dividends.
• Use market-value capital structure weights for WACC unless the user explicitly requests another method.
• Default Cost of Equity to CAPM.
• Add size premium, country risk premium, or company-specific premium only when there is a visible rationale.
• Use a normalized operating tax rate where possible instead of a distorted one-off effective tax rate.
• Keep Terminal Growth Rate < WACC.
• Make sure terminal multiples match the terminal operating base.
• If terminal value drives an unusually high share of EV, surface that sensitivity explicitly.
• Default to diluted shares for per-share value.

Modeling Rules

Forecast Linkage

• Link forecast operating data directly from the model.
• Avoid hardcoding values in formulas. Do not write formulas such as =36726*(1+C12); instead, reference the appropriate source cells, including across worksheets when needed.
• Do not rebuild a second operating forecast inside the DCF page.
• If operating working capital is not already shown clearly, add a support block that defines what is included in operating current assets and operating current liabilities.

NOPAT -> UFCF

Default formulas:

• Taxes on EBIT = EBIT x tax rate
• NOPAT = EBIT - Taxes on EBIT
• UFCF = NOPAT + D&A - Capex - Change in Operating Working Capital

Treatment rules:

• Use sustainable operating tax assumptions.
• Add back D&A because it is non-cash, but avoid double counting with other adjustments.
• Use cash capex rather than a depreciation proxy unless the user asks for a steady-state simplification.
• Include only operating working capital items in Change in Operating Working Capital.
• Do not mechanically add back SBC unless share dilution treatment remains consistent.

WACC

Build WACC step by step:

1. Cost of Equity Default:

   • Cost of Equity = Risk-Free Rate + Levered Beta x Equity Risk Premium Optional:
   • + Size Premium
   • + Country Risk Premium
   • + Company-Specific Premium

2. Beta When a peer beta build is needed:

   • Gather peer levered beta
   • Calculate D / E and tax rate
   • Unlevered Beta = Levered Beta / (1 + (1 - tax rate) x D / E)
   • Use selected-peer median or a justified average
   • Relever to the target capital structure: Relevered Beta = Unlevered Beta x (1 + (1 - target tax rate) x target D / E)

3. Cost of Debt Preferred sources:

   • Current borrowing rate or bond yield
   • Credit spread / rating-based estimate
   • Interest Expense / Average Debt as fallback

4. Capital Structure Weights Default:

   • E / V = Market Value of Equity / (Market Value of Equity + Market Value of Debt)
   • D / V = Market Value of Debt / (Market Value of Equity + Market Value of Debt)

5. Tax Shield

   • After-Tax Cost of Debt = Pre-Tax Cost of Debt x (1 - tax rate)

6. Final WACC

   • WACC = (E / V x Cost of Equity) + (D / V x After-Tax Cost of Debt)

Terminal Value

Primary method:

• TV = Final Year UFCF x (1 + g) / (WACC - g)

Rules:

• g must be below WACC
• g must be realistic for mature long-term nominal growth
• The final-year UFCF should be close to a sustainable level

Cross-check method:

• TV = Terminal Metric x Exit Multiple

Rules:

• Match metric and multiple correctly, for example EBITDA with EV / EBITDA
• Use an exit multiple that can be explained by trading context or peer evidence

Discounting

• Use mid-year convention by default
• Show discount periods and discount factors explicitly
• PV of UFCF = UFCF / Discount Factor
• PV of TV = TV / Discount Factor
• Enterprise Value = Sum of PV of UFCFs + PV of TV

EV -> Equity Value -> Price

Default bridge:

• Enterprise Value
• Less: Net Debt
• Less: Preferred Equity
• Less: Minority Interest
• Add: Non-operating Cash / Investments / Associates when appropriate
• = Equity Value
• Equity Value / Diluted Shares = Implied Share Price

Rules:

• Use balance sheet values that match the valuation date or bridge year
• Avoid double counting items already reflected in EV or UFCF
• Let net cash situations flow through naturally
• If Equity Value or Implied Share Price is negative in the base case, do not pass through the result mechanically. Recheck the operating forecast, sign conventions, net debt bridge, non-operating adjustments, diluted share count, and any other core valuation logic, and only treat the output as deliverable after confirming the result is justified.

Validation

Before finishing, confirm:

1. Every DCF forecast line ties to the model.
2. NOPAT excludes financing effects.
3. UFCF treatment for D&A, Capex, and working capital is internally consistent.
4. WACC assumptions are visible and traceable.
5. g < WACC.
6. Terminal bases and terminal methods are matched correctly.
7. Discount factors increase with time.
8. The EV -> Equity Value bridge does not double count items.
9. Share count treatment is consistent with dilution assumptions.
10. Sensitivity tables move in the expected direction.
11. If the base-case Equity Value or Implied Share Price is negative, the model logic has been rechecked and the result has been confirmed as justified before delivery.

Deliverable Shape

For Excel delivery, include:

1. A title and context box
2. A NOPAT -> UFCF schedule
3. A detailed WACC build
4. A Terminal Value and discounting block
5. An Enterprise Value -> Equity Value -> Implied Share Price bridge
6. At least one two-way sensitivity table
7. Support blocks for beta, capital structure, or net debt when needed

For text delivery, summarize:

1. Base-case WACC
2. Base-case terminal assumption
3. Enterprise Value
4. Equity Value
5. Implied Share Price
6. Key sensitivity takeaways
7. Main valuation caveats

DCF Format Reference

This is the canonical DCF page layout reference for the agent to refer unless users query asked differently or provide templates. Use the same language as the user's query when referring to this format

Use one dedicated DCF tab by default. Keep the page compact. Do not introduce presentation-style blank space.

1. Sheet skeleton

Merged Title:                 B1:H1
Title Box Labels / Values:    B3:C8
Context / Notes Box:          E3:H8

Section Bar:                  B9:G9     NOPAT to Unlevered Free Cash Flow
Header Row:                   B10:G10
Data Rows:                    B11:G28

Section Bar:                  K12:P12   WACC Build and Key Assumptions
Header Row:                   K13:M13
Assumption Rows:              K14:P27

Section Bar:                  B30:G30   Terminal Value and Discounting
Header Row:                   B31:G31
Terminal Rows:                B32:G44
Model Notes Box:              K31:P37

Section Bar:                  B46:C46   Enterprise Value to Implied Share Price
Header Row:                   B47:C47
Bridge Rows:                  B48:C57

Section Bar:                  B61:P61   Sensitivity Analysis
Left Sensitivity Title:       B63:G63   Implied Share Price - WACC vs Terminal Growth
Right Sensitivity Title:      J63:O63   Implied Share Price - WACC vs Exit Multiple
Sensitivity Headers:          B64:G64 and J64:O64
Sensitivity Bodies:           C65:G69 and K65:O69

2. Title block

B1:H1   Target Name - DCF Valuation

B3      Ticker               C3      [yellow fill + dashed outer border]
B4      Date                 C4      yyyy/m/d
B5      Currency             C5      RMB / USD / reported currency
B6      Units                C6      RMB mn / USD mn
B7      Convention           C7      Mid-Year
B8      Terminal Method      C8      Perpetuity Growth

E3:H8   Context / valuation notes
        - bridge year
        - forecast years discounted
        - terminal method
        - net debt bridge notes

Rules:

• Only C3 uses yellow fill and dashed border by default. C4:C8 should not use yellow fill.
• Labels in column B are bold and left-aligned.
• The title sits in the upper left; do not center it across the sheet.

3. NOPAT to UFCF block

Section Bar: B9:G9

B10   Line Item     C10   2025E / 2026E / ...   through the final forecast year

B11   Revenue
B12   EBIT
B13   EBIT Margin
B14   Tax Rate
B15   Taxes on EBIT
B16   NOPAT
B17   Depreciation
B18   Amortization
B19   D&A
B20   Cash Capex
B21   Intangible Additions
B22   Total Reinvestment
B23   Operating Current Assets
B24   Operating Current Liabilities
B25   Operating Working Capital
B26   Change in Operating WC
B27   UFCF
B28   UFCF Margin

Rules:

• Keep the first column left-aligned and the year columns centered in the header.
• Link forecast rows directly from the operating model whenever possible.
• Important subtotal rows such as NOPAT and UFCF are bold.

4. WACC block

Section Bar: K12:P12

K13   Assumption    L13   Value    M13:P13   Rationale

K14   Risk-free rate
K15   Equity risk premium
K16   Levered beta
K17   Company-specific premium
K18   Cost of equity
K19   Pre-tax cost of debt
K20   Tax rate
K21   After-tax cost of debt
K22   Target debt weight
K23   Target equity weight
K24   WACC
K25   Terminal growth rate
K26   Exit EV / EBITDA
K27   Diluted shares

Rules:

• K13:M13 uses header fill and bold text.
• Hardcoded assumptions in column L use bright blue font.
• Formula outputs remain black.
• The rationale text runs across M:P and wraps.

5. Terminal value and discounting block

Section Bar: B30:G30

B31   Line Item      C31:... final forecast years
B32   UFCF
B33   Discount Period
B34   Discount Factor
B35   PV of UFCF
B36   Terminal UFCF
B37   TV @ Perpetuity Growth
B38   Terminal EBITDA
B39   TV @ Exit Multiple
B40   PV of Terminal Value
B41   PV of TV @ Exit Multiple
B42   PV of Interim UFCF
B43   Enterprise Value (Perpetuity)
B44   Enterprise Value (Exit Multiple)

K31:P37   compact model notes box

Rules:

• Use mid-year discounting by default.
• Enterprise Value output rows are bold.
• The section bar must stop at column G; do not extend it farther.

6. EV bridge

Section Bar: B46:C46

B47   Line Item                      C47   Value
B48   Enterprise Value (Perpetuity)
B49   Less: Debt & Lease Liabilities
B50   Less: Minority Interest / Other Senior Claims
B51   Add: Cash & Short-Term Investments
B52   Add: Non-operating Investments (when justified)
B53   Equity Value
B54   Diluted shares
B55   Implied Price
B56   TV / EV %
B57   EV / Terminal Metric

Rules:

• Keep this block narrow and compact.
• Equity Value and Implied Price are bold.
• Do not hide the bridge logic inside one net-debt line if more explicit rows improve auditability.

7. Sensitivity layout

Section Bar: B61:P61

Left Table
B63:G63   Implied Share Price - WACC vs Terminal Growth
B64       g / WACC
C64:G64   WACC headers
B65:B69   terminal growth headers
C65:G69   body

Right Table
J63:O63   Implied Share Price - WACC vs Exit Multiple
J64       Exit / WACC
K64:O64   WACC headers
J65:J69   exit multiple headers
K65:O69   body

Rules:

• Header rows use light blue-gray fill.
• Label the axes explicitly.
• Keep the tables compact and aligned on one baseline.
• A soft red-yellow-green scale is acceptable in the body, but keep it restrained.

8. Core formatting rules

• Font: Arial
• Title: Arial 14-16 bold
• Body: Arial 8-10
• Header fill: light blue-gray close to #D9E2F3
• Section bar fill: deep navy close to #0F243E, white bold text
• Input fill: pale yellow close to #FFF2CC
• Hardcode font: bright blue #0000FF
• Standard labels and formulas: black
• Do not over-border the page
• Do not make section bars longer than the tables they introduce
• Do not apply yellow fill to every input
• Do not introduce green summary-row styling from comps pages

9. What the agent should preserve

• the compact title block + note box structure
• the exact section order
• the narrower EV bridge block
• the two-table sensitivity finish at the bottom
• restrained banker-style spacing instead of slide-style spacing