Click main image to view in full screen.
BENEFITS OF THIS EXCEL DOCUMENT
- Excel XLSX File
VALUATION MODEL EXAMPLE EXCEL DESCRIPTION
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.
Got a question about the product? Email us at support@flevy.com or ask the author directly by using the "Ask the Author a Question" form. If you cannot view the preview above this document description, go here to view the large preview instead.
Source: Best Practices in Valuation Model Example Excel: Discounted Cash Flow Excel (XLSX) Spreadsheet, Improve Your Excel
ABOUT THE AUTHOR
Introducing our Excel Premium Templates – your solution to simplifying your tasks. Our collection offers meticulously designed templates that are crafted to streamline your work processes and enhance efficiency. Whether you're managing budgets, creating reports, or organizing data, our premium templates are tailored to cater to your specific needs. With user-friendly interfaces and ... [read more]
Ask the Author a Question
You must be logged in to contact the author.
