Course Content

Mastering Discounted Cash Flow Analysis with Excel

1. Introduction to Business Valuation

What You'll Learn About DCF and Business Valuation Why Valuing a Business Matters for Investors and Owners Overview of Business Valuation Methods

2. Understanding Discounted Cash Flow (DCF) Analysis

Introduction to the Discounted Cash Flow Valuation Method DCF Money Printing Machine Example DCF Formula Calculations

3. Cash Flow Forecasting and Discount Rate Fundamentals

How to Calculate Unlevered Free Cash Flow (UFCF)Understanding the Discount Rate in Valuation How to Calculate Cost of Debt Cost of Debt Calculation Example How to Calculate Cost of Equity (CAPM)How to Calculate Cost of Equity Using Build Up Method Cost of Equity Calculation Example

4. WACC, Terminal Value & Sensitivity Analysis

Weighted Average Cost of Capital (WACC) Calculation Terminal Value (TV) and Perpetuity Growth Rate (g)Terminal Value Calculation Example Sensitivity Analysis in DCF Valuation

5. Building a DCF Valuation Model in Excel

Company Overview and Valuation Objective Exploring the Excel-Based DCF Model Modeling the Income Statement Items Modeling the Balance Sheet Items Creating Clean Income Statement Output Creating Clean Balance Sheet Output Building the UFCF Model: From EBIT to Operating Cash Flow Accounting for CapEx, Assets & Reconciliation to Net Cash Flow Forecasting UFCF and Linking Cash Flow to the Balance Sheet Final DCF Calculation and Valuation Output Visualizing Results with Excel Charts Performing Sensitivity Analysis in Excel

6. Practical DCF Case Study – Company Valuation in Action

DCF Assignment Walkthrough: From Inputs to Enterprise Value

Performing Sensitivity Analysis in Excel

Once you've calculated a single enterprise value using your base assumptions, it's time to ask a key question: how sensitive is that value to change? This chapter addresses that by performing a sensitivity analysis on the two most impactful drivers of terminal value:

WACC (Weighted Average Cost of Capital) – reflects risk and opportunity cost;
Perpetuity Growth Rate (g) – reflects the expected long-term growth beyond forecast.

Both appear in the denominator of the terminal value formula:

\text{TV} = \frac{UFCF_{n+1}}{WACC - g}

Note

As WACC increases ↑, the discounting effect becomes stronger, reducing the present value of future cash flows—so enterprise value decreases ↓. As the Perpetuity Growth Rate (g) decreases ↓, the denominator in the terminal value formula becomes larger, which also lowers the terminal value ↓ and thus the overall enterprise value.

Small changes in either variable can lead to large swings in terminal value, and thus in enterprise value. Sensitivity analysis helps you visualize those effects.

In Excel, the process is straightforward:

You build a 2D table using WACC as the row input and growth rate as the column input;
Each cell in the table recalculates the resulting enterprise value using different combinations of those inputs;
You can use Excel's Data Table feature to automate the calculations across all scenarios.

This table doesn't just reveal upside or downside—it clarifies the risk. If your valuation varies wildly with small changes, your model may be too sensitive or reliant on long-term speculation.

A well-constructed sensitivity table empowers stakeholders to make decisions with open eyes:

Investors see risk and return trade-offs;
Operators see how strategy can reduce valuation volatility;
Analysts see which assumptions deserve the most scrutiny.

Everything was clear?

Thanks for your feedback!

Section 5. Chapter 12

Ask AI

Ask anything or try one of the suggested questions to begin our chat