Discounted Cash Flow (Excel XLSX)

BENEFITS OF THIS EXCEL DOCUMENT

1. Excel XLSX File

VALUATION MODEL EXAMPLE EXCEL DESCRIPTION

This product (Discounted Cash Flow) is an Excel template (XLSX), which you can download immediately upon purchase.

The Discounted Cash Flow (DCF) model determines the discount rate applied to all projected cash flows in order to estimate the intrinsic value of the company.

Weighted Average Cost of Capital (WACC)
This section calculates the Weighted Average Cost of Capital (WACC), which is subsequently used to discount future cash flows.

The template supports two methods for calculating the cost of equity:
1. Capital Asset Pricing Model (CAPM)
2. Actual Dividends Method

Key Assumptions and Inputs
Select the Equity Cost Calculation Method in the Assumptions table.
Define the income tax rate, which impacts both the WACC and free cash flow calculations.

CAPM Parameters (if selected)
If the CAPM method is used, the following inputs are required:

Risk-Free Rate: Represents the time value of money; typically based on the yield of 10-year Treasury securities.
Market Equity Risk Premium: The expected excess return of the market over the risk-free rate.
Beta: Measures the company's systematic risk relative to the overall market (a beta of 1.0 reflects market-average risk).
Size Risk Premium (optional): An additional risk premium applied to small-cap companies.

Additional Inputs
Enter historical data for Equity Value, Dividends, Market Value of Debt, and Interest Expense.
WACC is calculated as the weighted average of the cost of equity and the after-tax cost of debt, based on their respective market values.
Users may manually input interest rates in the User-Defined Rates column, if required.

> Note: White cells are designated for user inputs and selections. Colored cells contain formulas and should not be manually modified.

Discounted Cash Flow Template
Calculate the WACC prior to completing the DCF valuation.
Enter historical financial data from the income statement, along with forecasted figures for the current year. A minimum of three consecutive years of data is required, including the most recent forecast.
Any calculated parameter may be overridden with a manually entered value, if necessary.
The present value of projected cash flows over the next 10 years is calculated using WACC.
A terminal value captures the present value of cash flows beyond the explicit forecast period.
Enterprise value is adjusted for outstanding debt and cash balances to derive the company's market capitalization.
Dividing market capitalization by the number of outstanding shares yields the implied share price.

> Note: White cells are for inputs; colored cells are formula-driven and should not be altered.

Charts
This section provides visual analysis of the DCF results, including:
Net Sales Revenue
EBITDA
Free Cash Flow
Enterprise Value Composition

These charts are designed to support valuation interpretation and sensitivity analysis.

Source: Best Practices in Valuation Model Example Excel: Discounted Cash Flow Excel (XLSX) Spreadsheet, Improve Your Excel