Financial Modeling: Build a Complete DCF Valuation Model

Start from nopat and add back depreciation and amortization, then adjust for changes in working capital, capex, and other assets and liabilities to obtain unlevered free cash flow.

Apply the time value of money to discount future cash flows and compute present value with a discount factor, using WACC to reflect debt and equity costs.

Calculate the cost of debt by summing all interest bearing financial liabilities, then divide interest expense by that total to feed the WACC.

Apply the capital asset pricing model to estimate a company's cost of equity by adding the risk-free rate to beta multiplied by the market risk premium.

Decide on a ten-year explicit forecast to project cash flows, revenues, and balance sheet items, then verify that assets equal liabilities for a robust DCF valuation.

Calculate terminal value in a DCF model with the Gordon growth approach, representing perpetual cash flows after the explicit forecast, discounted by WACC and growth rate G.

Apply the discounted cash flow approach by discounting explicit forecast cash flows and the terminal value using WACC, via the present value formula, to derive enterprise and equity value.

Compute enterprise value by summing present value of cash flows in the explicit forecast period and the terminal value; determine equity value using enterprise value minus financial liabilities plus cash plus non operating assets minus non operating liabilities, divided by shares outstanding.

Learn discounted cash flow valuation via a case study introduction, covering DCF preparation stages and simplified assumptions for a valuation exercise used by practitioners.

Explore industry analysis, company analysis, and product positioning as you gather financial information and build hypotheses and scenarios for a complete DCF valuation.

Build a dcf valuation model in excel by outlining p&l assumptions, balance sheet inputs, and cash flows, and compare optimistic, base, and pessimistic scenarios across five years.

Assess Chico’s cheese producer with its distribution network, as rising demand, new entrants, and operational efficiency shape revenue, raw material pricing, and DCF model scenarios.

Model the top line by building three revenue scenarios—optimistic (10% annual growth), base (8%), and worst (4%)—using a P&L framework and the choose function for scenario selection.

Build flexible financial models in Excel with Tewes to incorporate three scenarios and dynamically forecast revenues, while selecting scenarios updates growth and the P&L figures.

model other revenues and cogs within a complete dcf valuation, using flat growth for other revenues and cogs as a percentage of revenues with historical 76–80% range across scenario hypotheses.

Forecast operating expenses and D&A as a percentage of revenues, using historical percentages to set optimistic, base, and pessimistic cases across five forecast years.

Model interest expenses as flat, forecast via the revenue formula, set extraordinary items to zero as a percentage of revenues, and assign 18–22% taxes by scenario, pending EBT.

Forecast balance sheet items by projecting trade receivables, inventory, and trade payables with the day's technique, while property, plant and equipment, other assets, and other liabilities grow as revenues scale.

Explore the days methodology used to represent balance sheet items like days sales outstanding (DSO), days payable outstanding (DPO), and days inventory outstanding (DIO) to forecast receivables, payables, and inventory.

Learn to calculate days metrics (DSO, DPO, DIO) using days = trade receivables over revenues times 360, with COGS references for adjustments in 2016, and negate negative results.

Learn how to project trade receivables, trade payables, and inventory using days, selecting the latest figures over averages, and apply a 360-day basis for forecasted balances.

Forecast property, plant & equipment, and other assets and liabilities as a percentage of revenues using 2016–2018 ratios, average them, and apply fixed references to the forecast period.

Create a clean, good-looking output sheet for the P&L, displaying revenues, COGS, gross margin, operating expenses, EBITA, interest, taxes, and net income across years, ready for printing and cash-flow calculations.

Apply practical skills to populate the P&L sheet by linking input assumptions, copying formulas with fixed references, and calculating taxes and net income for historical and forecast periods.

Populate the balance sheet output sheet from the assumptions sheet using sumifs. Copy across periods with paste special formulas to project inventory, trade receivables, other assets, trade payables, and liabilities.

Link blank balance sheet items to historical values, fill intangible assets, financial assets, cash and equivalents, and shareholders equity, and verify that total assets equal total liabilities.

Build unlevered cash flow models from EBIT, apply operating taxes, add depreciation and amortization, and adjust for working capital and investments to derive unlevered free cash flow for enterprise value.

Bridge unlevered free cash flow to net cash flow by including interest expenses, the tax shield from debt, changes in financial liabilities, and changes in equity including dividends.

compute cash flows for a dcf valuation by linking the panel sheet, selecting operating taxes, and calculating net operating profit after taxes, gross cash flow, and working capital movements.

Calculate net cash flow by reconciling unleveraged free cash flow with actual cash changes, using interest expenses, taxes, delta liabilities, and delta equity, then verify with opening and closing cash.

Copy and paste cash flow formulas across periods, fix column references with find and replace, and balance the balance sheet to prepare with closing cash for the upcoming DCF valuation.

Value a firm with a dcf using the weighted average cost of capital and perpetuity growth, discounting explicit forecasts to present value. See typical ranges and a sensitivity analysis.

Calculate the present value of unleveled free cash flows by applying discount factors to each forecast period, linking to the cash flow sheet and preparing for continuing value.

Apply continuing value formula to year five unleveraged free cash flow with a perpetuity growth rate, discount to present value, and combine with forecast cash flows to determine enterprise value.

Calculate the equity value from enterprise value by subtracting financial liabilities and debt-like items and adding cash, using 2018 balance sheet values.

Explore how sensitivity analysis using data tables in Excel reveals how enterprise value responds to changes in WACC and perpetuity growth within a complete DCF model.

Use goal seek to find the exact WACC that yields an enterprise value of 100 million in a DCF model.

Explore how the DCW model uses charts to compare historical and forecast revenues and EBITA margins across optimistic and base scenarios, and evaluate cash flows, working capital, and enterprise value.

Master the essentials of dcf valuation by forecasting p&l and balance sheet items, building a cash flow statement, applying scenarios and sensitivity analysis, discounting cash flows, and calculating continuing value.