Relative Valuation and Corporate Valuation with MS Excel

Name: Relative Valuation and Corporate Valuation with MS Excel
Rating: 4.3 (23 reviews)

Master the art of Relative and Corporate Valuation with hands-on Excel models and real-world strategies

Created byEDUCBA Bridging the Gap

Last updated 10/2024

English

English [Auto],

What you'll learn

Introduction to Relative Valuation – Understanding its importance and use in financial analysis.
Different Types of Valuation Ratios – Exploring Price-to-Earnings (PE), Price-to-Book (PBV), Price-to-Sales (PS), and PEG Ratios.
Advantages and Disadvantages of Valuation Ratios – When to apply each and their limitations.
Practical Application of Valuation Ratios – Hands-on exercises with real-world company examples.
Comparable Company Analysis – Identifying and evaluating public companies for benchmarking.
Equity Value vs. Enterprise Value – Calculating and understanding the differences between these key metrics.
Building Financial Models – Constructing advanced models like Dividend Discount Model (DDM) and Discounted Cash Flow (DCF).
Forecasting Financial Statements – Projecting income statements, balance sheets, and cash flows.
Working Capital and Cash Flow Analysis – Learning the intricacies of working capital and how it affects valuations.
Excel Skills for Corporate Valuation – Using Microsoft Excel to implement financial models, calculations, and sensitivity analysis.

Course content

4 sections • 80 lectures • 7h 41m total length

Corporate Valuations - Overview1:43
Explore corporate valuations through the dividend discount model, discounted cash flows, and relative valuation, covering free cash flow to the firm, WACC, options and treasury stock methods, and Excel skills.
Ddm - Dividend Discount Model- Intrinsic Value5:47
Use the dividend discount model to find intrinsic value by discounting year one and year two dividends plus the sale price at 15%, and note the NPV shortcut.
Ddm - Dividend Discount Model- Required Rate Of Return6:13
Apply the dividend discount model in MS Excel to derive the implied required return from next-year dividends, price, and growth, and analyze sensitivity of returns using data tables.
Ddm - Dividend Discount Model- Compare Intrinsic And Market Price5:07
Compare intrinsic value and market price using the dividend discount model, determining overvalued or undervalued stock and exploring how expected returns affect recommendations via data tables.
Ddm - Intrinsic Value Of Growth Companies4:07
Analyze non-constant dividend growth in the dividend discount model, computing explicit cash flows and a terminal value with a moderated long-term growth rate and cost of equity.
Ddm - Dividend Discount Model- Present Value8:12
Apply the dividend discount model to value a stock from explicit cash flows and terminal value, correct NPV with zero cash flows, and analyze sensitivity to cost of equity.
Introduction To Dcf4:53
Perform a basic discounted cash flow exercise with a mechanical view. Explore interlinked sheets for fcff projection, capital structure, cost of capital, and growth assumptions.
Forecasting Income Statement Ebitda6:26
Forecast revenue using growth rates, project costs as a percentage of sales, and compute EBITDA as revenue minus COGS minus G&A.
Understanding The Working Capital8:10
Explore how banks differ from manufacturing firms in valuation, focusing on money-based assets, mark-to-market concepts, and why price-to-book value aligns with bank market value.
Completing The Working Capital Calculations2:42
Forecast depreciation as 13% of sales and derive ebit from ebitda. Apply a 33% tax rate and 13.3% of sales capex to determine free cash flow to the firm.
Linking The Free Cash Flow To Firm Fcff2:03
Apply the treasury stock method by using only exercisable options to estimate dilution, distinguishing options outstanding from exercisable and accounting for lock-in periods, with strike price and current market price.
Discounting The Explicit Period Cash Flows5:12
Assume an 8% wacc and calculate the present value of the explicit period for the free cash flow to the firm, then compare npv with xnpv using dates.
Calculation Of Terminal Values6:49
Learn how to compute terminal values in relative and corporate valuation using MS Excel, applying the perpetuity growth and exit multiple methods and reconciling them with discounted cash flows.
Dcf Valuation Summary6:22
Complete the dcf valuation summary by calculating the npv of the explicit period and terminal value to derive the total enterprise value, then derive fair equity value and share price.
Dcf Sensitivity Analysis7:18
Explore how sensitivity analysis using two-dimensional data tables in Excel shows how growth rate and WACC assumptions affect per-share value under perpetuity growth and EBITDA multiples.
Understanding The Capital Structure11:47
Analyze the capital structure to classify short-term and long-term debt, revolvers, bonds, and convertible securities, then compute the weighted average cost of capital for enterprise value.
Options Treasury Stock Method6:18
Explore how the treasury stock method handles employee stock options, distinguishing in the money and out of the money options, and their effects on diluted shares, EPS, and valuation.
Options Explained2:03
Explain the treasury stock method and why dilution analysis uses options exercisable rather than options outstanding, noting the lock-in period that reduces available options (0.15 vs 0.14, 0.01).
Calculation Of In The Money Convertibles4:28
Calculate in the money convertible securities by analyzing convertible preferred and convertible debt, using Excel logic to convert when market price exceeds conversion price and count shares accordingly.
Calculation Of In The Money Stock Options5:10
Compute in-the-money stock option proceeds using strike price and market price with an Excel if function, then determine buy back shares and the net dilution impact.
Calculation Of Debt Equity Ratio4:06
Calculate fully diluted equity value by linking stock price, shares outstanding, and dilution effects, then assemble debt components—short-term, straight long-term, and convertible—to derive the debt-to-equity ratio for capital structure analysis.
Cost Of Debt Calculations4:05
Calculate cost of debt using the synthetic rating method by linking EBIT and interest expense to interest coverage, selecting spreads from market-cap tables, and applying tax adjustments.
Cost Of Equity Calculation7:32
Compute cost of equity with CAPM for a new IPO using comparable analysis to estimate levered beta; unlever and re-lever to 0.79, then apply risk-free 5% and 5% market premium.
Enterprise Value Calculation Completing The Missing Links3:29
Link missing inputs in enterprise value calculation by connecting cost of capital, debt, cash, and fully converted shares. Assess terminal value and exit multiple scenarios to estimate final value.
Introduction To Relative Valuation7:49
Explore how relative valuation uses equity value and enterprise value multiples, like price-to-earnings and ev-to-ebitda, to build a comparable sheet and assess value drivers and financials.
Relative Valuations Enterprise Value And Equity Value5:18
Explore enterprise value multiples such as EV/sales, EV/ebitda, EV/ebit, EV/FCF, and capacity, alongside equity value multiples like pe, price to cash flow, and price to book, in relative valuation.
Relative Valuations Comparable Comp Sheet6:17
Build a professional comparable sheet for upstream oil and gas using enterprise and equity value multiples. Analyze EV to EBITDA, price to earnings, and price to cash flows.
Understanding Pe Ratio6:45
Learn how price to earnings ratios drive relative valuation and sector comparisons. Distinguish forward and trailing P/E to assess overvaluation.
Forward And Trailing Pe3:18
Compare forward P/E and trailing P/E to see how price links to expected EPS versus historical EPS. Prioritize forward P/E for near-term value, using trailing P/E to contextualize historical multiples.
Advantages And Limitations Of Pe4:26
Learn how price to earnings handles negative earnings and why p/e can be non meaningful, accounting policy differences, balance sheet risk, and capital structure, for more accurate relative valuation.
Understanding Pbv Ratio2:13
Explore the price to book value ratio, calculated as price per share divided by book value per share, and its use in banking sector valuations.
Why Pbv Is Used In Banks8:10
Banks use price to book value because their assets are money-based and marked to market, making book value a close proxy for market value, unlike manufacturing's hard assets.
Pbv And Roe Used For Energy Sector2:12
Explore how price to book value equals price to earnings multiplied by ROE, and see how government regulation in energy and utilities, like in India, makes P/BV align with P/E.
Understanding Pcf Ratio3:01
Treat price to cash flows as a supplement to price to earnings in relative valuations, using cash flow per share and cash flow statements to gauge true cash generation.
Why Pcf Used In Oil Gas Gold Real Estate3:03
Price to cash flow is used for oil, gas, gold, and real estate. Upstream oil and gas with known reserves have variable cash flows; low pcf signals takeover targets.

Types of Relative Valuation3:39
Learn to value assets on a relative basis and compare firms using multiples such as price to earnings, enterprise value to ebitda, price to book, and price to sales.
Advantages and Disadvantages7:50
Understand the advantages and disadvantages of relative valuation, which uses multiples from comparables and aligns with market mood, but may yield inconsistent, biased, or nontransparent results compared with DCF.
Earning and Book Multiples6:48
Calculate enterprise value by adding market cap, debt, and preferred shares, then subtract cash and cash equivalents to reflect the cost to acquire a company.

Understand EV Ratio8:18
Practice calculating and interpreting enterprise value ratios such as EV/EBIT, EV/EBITDA, EV to cash flow, and EV to sales with excel, and assess undervalued opportunities.
Understand EV Ratio Continues7:55
Calculate enterprise value from market cap, debt, cash; compute cash free flow using operating income, depreciation, taxes, and working capital changes. Apply ev/ebitda and ev to sales to compare peers.
Finding List of Public Company5:59
Mine data to estimate a private toy company AB’s enterprise value using employees and revenues, then compare with three public firms via industry comparables and a summary matrix.
PE Ratio6:46
Understand the price to earnings ratio, a metric for valuing stocks by dividing market value or market cap by earnings per share, with trailing P, rolling P, and forward P.
Advantages and Disadvantages of PE Ratio4:58
Understand the advantages and disadvantages of the price-to-earnings ratio, especially its ease of use, benchmarking against industry, and how missing alignment between price and earnings can lead to undervaluation.
Advantages and Disadvantages Continues8:18
Evaluate the disadvantages of the price to earnings ratio, such as subjectivity, market sentiment sensitivity, and inflation or currency effects that can distort earnings and valuations.
Equity Value6:17
Compare equity value and enterprise value, noting debt and cash effects, and learn equity multiples such as price to earnings, price to cash flow, and price to book value.
PBV Ratio9:51
Understand the price-to-book value ratio, calculated as stock price divided by book value per share, linking market value to tangible assets and ROE for financial services and no earnings firms.
PBV Ratio Continues4:39
Analyze the price to book value ratio and its advantages when earnings are negative. Identify limitations, such as intangibles not captured and accounting differences that hinder comparability.
Price to Sales Ratio4:54
Use the price to sales ratio for distressed or high-growth firms with negative or volatile earnings, since it relies on sales; beware potential sales manipulation and avoid profitability assumptions.
Advantages and Disadvantages of PS Ratio7:39
Use the price to sales ratio for distressed firms or startups with volatile earnings. It is less manipulable than EPS and less volatile than P/E, though it ignores revenue recognition.
PEG Ratio2:26
Explore the PEG ratio, which divides price to earnings by annual EPS growth to compare growth-adjusted stock value, and examine equity versus enterprise value with practical Excel examples.
Equity Value Vs Enterprise Value3:26
Differentiate equity value from enterprise value: equity value equals enterprise value minus debt plus cash and investments, while enterprise value includes all capital and indicates the acquisition cost.
Understand Comparable Company Analysis4:42
Learn comparable company analysis as a relative valuation technique that values a firm by comparing its multiples with peers using metrics like enterprise value and earnings per share.
Use of Comparable Company Analysis6:19
Explore the use of comparable company analysis, highlighting data availability, ease of use, benchmark multiples, and its advantages and disadvantages under market conditions.
Method of Comparable Company Analysis9:31
Select the universe of comparable companies, using listed peers or external effects, then narrow by region, size, sector, products, customers, and geography; identify look-alikes with Bloomberg and analyst reports.
Get Financial Information3:46
Identify and gather peer group financial information from databases or public sources, and apply key multiples—ev/sales, ev/ebitda, p/e, p/b, and levered cash flow per share—in a comparable company analysis.
List Of Comparable Company List10:48
Explore how to perform a comparable company valuation in Excel, using 10–15 firms, to derive equity value and enterprise value, with benchmarking and base data setup.
Bench Marking Analysis5:02
Explore a practical comparable analysis framework using benchmarking tabs and base sheets to evaluate a target company against peers on market value, financials, margins, growth, and leverage and coverage ratios.
Working on Target Sheet4:06
Explore the target sheet with hypothetical data to understand ratios, multiples, and comparable analysis, including equity value from fully diluted shares and current price, plus debt, cash, and beta.
Reported Income Statement8:27
Analyze the reported income statement and estimate full-year results from partial-year data in Excel, covering sales, cost of goods sold, interest, taxes, EBIT and diluted EPS.
Adjusted Income Statement3:35
Calculate the adjusted income statement by incorporating non-recurring items into gross profit and net income, apply tax adjustments, and derive adjusted EPS using average diluted shares.
Cash Flow Statement Data7:22
Analyze a cash flow statement with straight-line depreciation, capital expenditure, and percentage of sales, then assemble the balance sheet and compute diluted shares outstanding and EPS, plus comparable analysis.
Equity Value and Enterprise Value4:26
Explore equity value and enterprise value through ratios like EV to sales, EV to EBITDA, and price-to-earnings ratio, using balance sheet data and sales projections.
Trading Multiples9:49
Explore relative and corporate valuation with Excel by applying trading multiples, calculating EV to EBITDA and price-to-earnings, and deriving ROIC, ROE, ROA, and dividend metrics through growth-based projections.
Working on Company A Sheet5:23
Build and compare a company A sheet in Excel, calculating sales, COGS, SG&A, EBIT, and ratios, while linking tax rate to mirror a target company for relative valuation.
Calculating Gross Profit3:55
Learn to build an adjusted income statement by removing non-recurring items, showing zero non-recurring items in 2014–2015, and reviewing adjusted gross profit, EBIT margins, net income, with straight-line depreciation and no capex.
Working on Balance Sheet Data10:32
Construct and adjust balance sheet inputs in a valuation model, including assets, liabilities, shares, and dividends, then compute enterprise value and key multiples.
Working on Company B Sheet9:27
Build and analyze a Company B valuation in Excel using sales, margins, depreciation, and capital structure assumptions. Derive equity value, enterprise value, and key multiples for benchmarking against peers.
Select Market Data5:26
Build a hypothetical income statement and balance sheet in Excel, using market data and assumptions on sales, costs, depreciation, interest expense, taxes, and non-recurring items to derive ratios.
Working on Sheet C4:53
Explore constructing a balance sheet, calculating enterprise value, and analyzing ratios like EV-to-sales and price-to-earnings using MS Excel, including diluted shares and penny stock scenarios.
Understand Bench Marking Summary10:15
Explore benchmarking in Excel by comparing equity value, enterprise value, and sales across large, mid, and small caps, linking sheets and computing mean, median, and growth rates.
Understand Bench Marking Summary Continue4:55
Compare target companies against industry mean and median using Excel to assess margins and growth, and benchmark ROIC, ROE, ROA, and valuations to derive equity and enterprise value.
Working on Output Sheet6:23
Use the output sheet to display enterprise value, ebitda, ebit, and price to eps, then perform a comparable analysis with ev/ebitda and industry benchmarks to judge investability.
Working on Industry Average Sheet2:36
Explore relative valuation and discounted cash flow valuation using an industry-based Excel sheet, including operating leases, R&D capitalization, and industry multiples for US apparel.
Input Sheet7:29
Build an input sheet in Excel for relative and corporate valuation, converting data from income statements and balance sheets into assumptions, beta, growth, and capex rules.
Relative Valuation Sheet1:49
Apply the relative valuation sheet with the input's value-to-sales ratio of 1.5 on revenue to derive equity value and per-share value.
Working on DCF Valuation Sheet4:08
Learn to build a dcf valuation sheet in Excel, calculating revenue projections using the compounded annual growth rate and deriving terminal value for ten years, alongside relative valuation.
Operating Leases5:24
Convert operating leases into debt and capitalize R&D and expenses to adjust Ebit. Apply present value and depreciation concepts to refine operating margins in a DCF framework, including terminal value.
Total Capital Invested8:19
Learn to build an Excel valuation model by calculating free cash flow from EBIT after taxes, depreciation, capital expenditures, and changes in working capital, then compute cost of capital.
Calculating Reinvestment Rate8:01
Compute reinvestment rate using ebit, capital expenditures, depreciation, and free cash flow to assess return on capital; apply a ten-year dcf with terminal value to value the firm.

Requirements

Basic Knowledge of Finance – A foundational understanding of financial concepts like income statements, balance sheets, and cash flow is recommended.
Familiarity with Financial Ratios – Awareness of common financial ratios such as PE, PBV, and ROE will be helpful.
Basic Microsoft Excel Skills – Knowing how to navigate and use Excel for basic formulas and data entry is essential.
Interest in Corporate Valuation – A genuine interest in learning how to value companies and analyze financial statements.
Eagerness to Learn – Willingness to engage with practical examples and develop hands-on skills.

Description

Introduction:

In this comprehensive course, you'll dive deep into the art of Relative Valuation and Corporate Valuation techniques using practical examples and real-world applications. We’ll explore different valuation ratios, comparable company analysis, and advanced financial models, such as Discounted Cash Flow (DCF) and Dividend Discount Models (DDM). This course is designed to take you from a beginner to a pro in corporate valuation using Microsoft Excel, providing you with the essential tools to evaluate company performance and make informed financial decisions.

Section 1: Introduction to Relative Valuation

This section introduces the fundamental concepts of Relative Valuation. We begin by understanding its importance in financial analysis and comparing it with other valuation methods. Lecture 1 sets the stage for the rest of the course, giving you a clear framework for navigating through relative valuation strategies and tools. By the end of this section, you'll understand why relative valuation is a crucial tool for comparing companies in similar industries.

Section 2: Types, Advantages, and Disadvantages of Valuation Ratios

In this section, we dive into different types of relative valuation methods, including Earnings and Book Multiples. You'll explore the advantages and limitations of each, giving you a balanced view of when and how to apply them. By the end of this section, you’ll be able to differentiate between Price-to-Earnings (PE), Price-to-Book (PBV), Price-to-Sales (PS), and PEG Ratios, understanding their strengths and weaknesses in analyzing company performance.

Section 3: Practical Applications of Valuation Ratios

This hands-on section focuses on applying the concepts from the previous section in practical scenarios. You'll work through real-world examples, such as calculating the Enterprise Value (EV) Ratio, identifying a list of comparable public companies, and analyzing Equity Value versus Enterprise Value. We'll guide you through detailed analyses of different companies using ratios like PE, PBV, and Price-to-Sales to assess financial health. You'll also conduct benchmarking analysis and learn how to build comparable company analysis sheets, which are vital tools in financial modeling.

Section 4: Corporate Valuation - From Beginner to Pro in Microsoft Excel

This section takes your valuation skills to the next level with a deep dive into Corporate Valuation techniques. You’ll start with a comprehensive overview and then progress through complex models like the Dividend Discount Model (DDM) and Discounted Cash Flow (DCF). You’ll learn how to forecast income statements, calculate terminal values, and link free cash flow to firm (FCFF). We’ll also cover how to analyze capital structures and perform DCF sensitivity analysis. By the end, you’ll have mastered the intricate details of both relative valuation and corporate valuation, becoming proficient in leveraging Excel for financial decision-making.

Conclusion:

By the end of this course, you will have a solid understanding of both Relative Valuation and Corporate Valuation methods. You’ll be equipped with practical skills in financial analysis, including how to apply valuation ratios, conduct comparable company analysis, and implement advanced financial models like DCF and DDM using Excel. This course prepares you to confidently analyze company performance and make informed decisions, whether you're working in finance, investment, or managing your own business.

Who this course is for:

Finance Professionals – Those working in investment banking, financial analysis, corporate finance, or related fields looking to enhance their valuation skills.
Business Students – MBA students or those studying finance who want practical knowledge in company valuation and financial modeling.
Investors & Traders – Individuals looking to evaluate companies more effectively through relative valuation and improve their investment decisions.
Accountants & Auditors – Professionals interested in understanding company valuations to complement their financial reporting or auditing practices.
Entrepreneurs – Business owners seeking to better understand the value of their company or potential investment opportunities.
Career Changers – Individuals transitioning into finance or corporate valuation roles who want to build foundational skills.
Anyone Interested in Corporate Valuation – Whether you're a beginner or have some experience, this course is for those eager to understand and apply valuation techniques.

Relative Valuation and Corporate Valuation with MS Excel

What you'll learn

Explore related topics

Course content

Corporate Valuation - Beginner to Pro in Microsoft Excel35 lectures • 3hr 3min

Intro to Relative Valuation1 lecture • 2min

Types and Advantages and Disadvantages3 lectures • 18min

Practical Example41 lectures • 4hr 18min

Requirements

Description

Who this course is for: