top of page
搜尋

Understanding Company Valuation Models: Using Excel or Python for Discounted Cash Flow (DCF) Valuation


Understanding how to value a company is a crucial skill for investors, financial analysts, and business owners. One of the most widely used methods for company valuation is the Discounted Cash Flow (DCF) model. The DCF model helps determine the present value of a company based on its future cash flows. At Lucky Mario, we strive to provide you with in-depth insights into financial valuation methods that empower informed decision-making. In this blog, we will introduce the DCF valuation model and demonstrate how to perform a DCF analysis using both Excel and Python.

1. What is Discounted Cash Flow (DCF) Valuation?

The Discounted Cash Flow (DCF) valuation is a method used to estimate the value of an investment based on the present value of its expected future cash flows. The DCF model is grounded in the idea that the value of a company is essentially the sum of all its future cash flows, discounted back to the present using an appropriate rate, typically called the discount rate.

The formula for DCF valuation is as follows:

DCF Value = CF1 / (1 + r)^1 + CF2 / (1 + r)^2 + … + CFn / (1 + r)^n + TV / (1 + r)^n

Where:

  • CF represents future cash flows.

  • r is the discount rate.

  • TV is the Terminal Value, which represents the value of the company beyond the forecasted period.

The main goal of the DCF analysis is to calculate the intrinsic value of a company, allowing investors to determine whether a stock is undervalued or overvalued compared to its current market price.

2. Key Components of the DCF Model

Before diving into the practical application of DCF using Excel and Python, it is essential to understand the key components involved in the DCF model:

  • Free Cash Flow (FCF): The cash available after a company has covered its operating expenses and capital expenditures. FCF is crucial for the valuation because it represents the cash that a company generates, which can be distributed to investors.

  • Discount Rate: The discount rate represents the risk associated with the investment. Typically, the Weighted Average Cost of Capital (WACC) is used as the discount rate, which accounts for the cost of equity and debt.

  • Terminal Value (TV): Since predicting cash flows indefinitely is challenging, the terminal value represents the company's value beyond the forecast period. The Gordon Growth Model is often used to calculate the terminal value.

  • Forecast Period: The period over which future cash flows are projected. Typically, this is 5 to 10 years.

3. Performing DCF Valuation in Excel

Excel is one of the most popular tools used by financial analysts for DCF valuation due to its flexibility and ease of use. At Lucky Mario, we often recommend Excel for users who prefer a hands-on approach to financial modeling.

3.1 Step-by-Step DCF Calculation in Excel

Step 1: Gather Financial Data

The first step in building a DCF model in Excel is to gather the relevant financial information about the company. This includes revenue, operating expenses, capital expenditures, and free cash flows. You can find this information from the company's financial statements, such as the Income Statement and Cash Flow Statement.

Step 2: Forecast Free Cash Flows

Once you have the historical data, use it to project Free Cash Flow (FCF) for the forecast period (typically 5 to 10 years). The FCF can be calculated as follows:

FCF = Operating Cash Flow - Capital Expenditures

In Excel, create a table that contains projected revenue growth, operating expenses, and capital expenditures for the forecast period. Calculate the FCF for each year using the above formula.

Step 3: Calculate the Discount Rate

The discount rate (WACC) can be calculated using the formula:

WACC = (E/V) * Re + (D/V) * Rd * (1 - Tc)

Where:

  • E = Market value of equity

  • D = Market value of debt

  • V = E + D (Total value)

  • Re = Cost of equity

  • Rd = Cost of debt

  • Tc = Corporate tax rate

Calculate the WACC in Excel by inputting the necessary data. This will be used as the discount rate in the DCF calculation.

Step 4: Calculate the Terminal Value (TV)

The Terminal Value represents the value of the company beyond the forecast period. You can use the Gordon Growth Model to calculate the terminal value:

TV = (FCF * (1 + g)) / (r - g)

Where g is the growth rate beyond the forecast period and r is the discount rate.

Step 5: Discount the Cash Flows

Next, calculate the present value of each projected cash flow and the terminal value using the discount rate. Use the following formula for each year's cash flow:

PV = CF / (1 + r)^n

Sum all the present values to calculate the Total Enterprise Value (TEV).

Step 6: Determine the Intrinsic Value per Share

To determine the intrinsic value per share, subtract net debt from the Total Enterprise Value and divide by the number of shares outstanding.

Intrinsic Value per Share = (TEV - Net Debt) / Shares Outstanding

4. Performing DCF Valuation in Python

For users who prefer a more automated approach, Python provides an efficient way to perform DCF valuation. Python's data manipulation and numerical libraries, such as Pandas and NumPy, make it possible to create a robust financial model with ease.

4.1 Step-by-Step DCF Calculation in Python

Step 1: Import Necessary Libraries

First, import the necessary libraries for financial calculations and data manipulation:

import numpy as np
import pandas as pd
Step 2: Gather Financial Data

Input historical financial data into a Pandas DataFrame. You can either manually input the data or fetch it using an API, such as Yahoo Finance or Alpha Vantage.

# Example data for Free Cash Flows (in millions)
data = {'Year': [2023, 2024, 2025, 2026, 2027],
        'FCF': [500, 550, 605, 670, 740]}

fcf_df = pd.DataFrame(data)
Step 3: Define Discount Rate and Terminal Value

Define the discount rate (WACC) and the growth rate for terminal value calculation:

discount_rate = 0.1  # 10% WACC
growth_rate = 0.03  # 3% terminal growth rate
Step 4: Calculate the Terminal Value

Calculate the terminal value using the Gordon Growth Model:

terminal_value = fcf_df['FCF'].iloc[-1] * (1 + growth_rate) / (discount_rate - growth_rate)
Step 5: Discount the Cash Flows

Calculate the present value of future cash flows and the terminal value:

fcf_df['Discounted FCF'] = fcf_df['FCF'] / (1 + discount_rate) ** fcf_df.index

tv_present_value = terminal_value / (1 + discount_rate) ** len(fcf_df)

total_value = fcf_df['Discounted FCF'].sum() + tv_present_value
Step 6: Calculate the Intrinsic Value per Share

Subtract net debt and divide by the number of shares outstanding to determine the intrinsic value per share:

net_debt = 200  # in millions
shares_outstanding = 50  # in millions

intrinsic_value_per_share = (total_value - net_debt) / shares_outstanding

print(f'Intrinsic Value per Share: ${intrinsic_value_per_share:.2f}')

5. Advantages and Limitations of DCF Valuation

The DCF valuation model has several advantages, including its focus on intrinsic value and its ability to account for future cash flow projections. However, it also has some limitations, such as its dependence on assumptions regarding growth rates and discount rates. Small changes in these inputs can lead to significant variations in the final valuation.

At Lucky Mario, we recommend using DCF valuation in conjunction with other valuation models, such as comparable company analysis (CCA) and precedent transactions, to get a comprehensive view of a company's value.

6. Conclusion

The Discounted Cash Flow (DCF) valuation model is a powerful tool for determining the intrinsic value of a company. Whether you prefer a manual approach using Excel or an automated approach with Python, the DCF model provides valuable insights into a company's future cash flows and growth potential.

At Lucky Mario, we strive to make financial concepts accessible and actionable for all investors. By understanding how to apply DCF valuation, you can make more informed decisions regarding investments and better understand the true value of a company. If you need more guidance or assistance in implementing DCF valuation, feel free to reach out to us at Lucky Mario. We are here to help you navigate the complexities of financial analysis and make better investment choices.

 
 
bottom of page