2 Excel Templates

1) Debt projection model

​Click here to get your Excel template​

The model can be adjusted very easily to any company. Keep you posted for the rest of the templates.

2) Opex model​​​​​​​​

​Click here to get your Excel template​

Here are 2 Infographics for Today:

1) 4 Types of business leverage

​Get this infographic on a high-resolution PDF​​​​​​​​​​​​​​​​​

2) DEBT vs EQUITY

​Get this infographic on a high-resolution PDF​​​​​

Here’s today’s “How to” guide:​​​​

How to apply DCF method in valuation businesses or projects [Step-by-step guide]

Step 1: Understand the Business and Gather Data

Objective: Gather all relevant financial data and understand the business operations.

Data Required:

  • Historical financial statements (income statement, balance sheet, and cash flow statement).
  • Industry and market trends.
  • Projections for revenue, expenses, capital expenditure, and working capital.

Step 2: Project Free Cash Flows (FCF)

Objective: Estimate future free cash flows for a projection period (usually 5–10 years).

Steps:

  1. Revenue Forecast: Project future revenues based on historical growth rates, market trends, and company-specific factors.
  2. Operating Expenses:Estimate future costs (e.g., cost of goods sold, SG&A, R&D) as a percentage of revenue or using trends.
  3. EBIT (Earnings Before Interest and Taxes):Subtract operating expenses from revenue.
  4. Taxes: Apply an effective tax rate to EBIT to calculate NOPAT (Net Operating Profit After Taxes).
  5. Depreciation and Amortization: Use historical patterns or projected fixed asset levels.
  6. Capital Expenditure (CapEx): Estimate investments in fixed assets needed to sustain growth.
  7. Changes in Net Working Capital (NWC): Project changes in working capital requirements (current assets – current liabilities).
  8. Calculate Free Cash Flow:

FCF= NOPAT + Depreciation & Amortization − CapEx −ΔNWC

Step 3: Determine the Discount Rate

Objective: Calculate the Weighted Average Cost of Capital (WACC) to discount the projected FCFs.

Formula:

Where:

E: Market value of equity.

D: Market value of debt.

Re: Cost of equity.

Rd: Cost of debt.

Tc: Corporate tax rate.

And:

Re = Rf + β⋅(Rm−Rf)

Re​ (Cost of Equity) represents the return that equity investors expect to earn on their investment in the company. It’s the rate of return required to compensate for the risk of holding the company’s stock.

Rf​ (Risk-Free Rate) is the theoretical return of an investment with zero risk. Commonly represented by government bond yields (e.g., U.S. Treasury bonds for U.S. companies).

β (Beta):

  • Measures the stock’s sensitivity to market movements.
  • β=1\beta = 1β=1: The stock moves in line with the market.
  • β>1\beta > 1β>1: The stock is more volatile than the market.
  • β<1\beta < 1β<1: The stock is less volatile than the market.

Rm​ (Market Return) is expected return of the overall market (e.g., S&P 500 index) and represents the return investors expect to earn from a diversified market portfolio.

(Rm​−Rf​) (Market Risk Premium) is the additional return that investors expect from the market over the risk-free rate and represents the compensation for taking on the risk of investing in the market instead of risk-free securities.

Step 4: Calculate the Terminal Value

Objective: Estimate the value of cash flows beyond the projection period using the Gordon Growth Model or Exit Multiple Method.

Gordon Growth Model:

Where:

FCFn+1: Free cash flow in the first year beyond the projection.

r: Discount rate (WACC).

g: Perpetual growth rate [the rate at which free cash flows are expected to grow indefinitely after the explicit forecast period. g should be realistic and typically aligned with the long-term growth prospects of the economy or industry. For mature companies in stable industries, g is often close to the expected inflation rate or GDP growth rate (e.g., 2%–3%).

Step 5: Discount Cash Flows to Present Value

Objective: Convert future FCFs and terminal value into present value.

Formula:

Step 6: Summarize the Valuation

Objective: Add the present value of projected cash flows and terminal value to derive the enterprise value (EV).

Adjust EV to get equity value:

Equity Value = Enterprise Value − Net Debt

Where:

Net Debt =Total (Financial) Debt − Cash

Example of DCF Application

Scenario

You are valuing a company with the following details:

  • Historical revenue: $50M (current year growth: 10%).
  • EBIT margin: 20%.
  • Tax rate: 25%.
  • Depreciation and amortization: $5M.
  • CapEx: $7M annually.
  • Working capital growth: $1M/year.
  • Perpetual growth rate: 3%.
  • WACC: 10%.
  • Net debt: $15M.

Step-by-Step Example

Revenue Forecast (5 years):

  1. Year 1: $50M × 1.10 = $55M.
  2. Year 2: $55M × 1.10 = $60.5M.
  3. Continue for 5 years.

EBIT:

  1. Year 1: $55M × 20% = $11M.
  2. Continue for each year.

NOPAT:

  1. Year 1: $11M × (1 – 25%) = $8.25M

FCF Calculation:

  1. Year 1: 8.25M + 5M − 7M − 1M = 5.25M
  2. Repeat for each year.

Terminal Value:

Year 6 FCF: Assume $8M.

Present Value:

Discount each year’s FCF and terminal value:

Equity Value:

EV: 95M − 15M = 80M

​​