A discounted cash flow model is a five-step assembly: forecast unlevered cash flows, choose a discount rate, compute the terminal value, discount the entire stream back to today, and bridge from enterprise value to per-share equity. Free cash flow to the firm is built as \(FCFF = EBIT \cdot (1 - t) + D\&A - \Delta WC - CapEx\); the equity version, FCFE, swaps net income for \(EBIT \cdot (1-t)\) and adds net borrowing. A weighted-average basis (for portfolio returns, blended COGS, or index membership weights) collapses to `=SUMPRODUCT(values, weights)/SUM(weights)`, and the same SUMPRODUCT pattern serves as a portable pre-SUMIFS conditional sum: `=SUMPRODUCT((region="EMEA")*(value))`.
To find an implied terminal value, anchor on \(TV = FCF_{n+1}/(WACC - g)\) from the Gordon growth model or \(TV = EBITDA_n \cdot \text{ExitMultiple}\) from a market-multiple approach, then discount the running stream and the terminal value together. Two refinements tend to matter in practice. First, a mid-year convention treats each period's cash flow as arriving at the midpoint rather than the end, dividing each discount exponent by an extra half-year; an XNPV using June 30 dates captures the same effect without rewriting the NPV formula. Second, the bridge from enterprise value to equity subtracts net debt and preferred stock before dividing by diluted shares: \(\text{Price} = (EV - ND - Pref)/\text{Shares}\). Net debt is computed as total interest-bearing debt minus cash and equivalents, deliberately removing operating cash to avoid inflating the equity claim. The sanity-check multiple for a comparable company is simply \(EV / LTM\_{\text{EBITDA}}\), which should be broadly consistent with the multiple used in the exit-multiple terminal value.
Because most DCF outputs depend nonlinearly on WACC, a sensitivity chart is often more useful than a single point estimate. Building one is mechanical: lay out WACC values from 7% to 12% in column A, place `=NPV(A2, FCFs) + InitialOutflow` in column B, and insert a line chart of B against A. The chart's slope reveals how brittle the value is to the cost-of-capital assumption. Solver can then be asked to find the WACC at which NPV equals the deal premium, or to back out the EBITDA growth path that justifies a given entry multiple. The implicit shape of the curve — steep at first, then flattening as compounding dominates — is itself a useful sanity check against a comparable transaction multiple or a deal sponsor's underwriting threshold.