Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

Comparable Analysis Valuation Model (Equity Valuation)

Written by MarketXLS Team on 
Wed May 05 2021
 about Fundamental analysisPortfolioStock ComparisonsStock investmentsStocksTechnical analysisValuation Models
Comparable Analysis Valuation Model (Equity Valuation) - MarketXLS
Boost your market advantage with MarketXLS. Real-time, enterprise-grade data. Book a demo or subscribe now!
Comparable Analysis Valuation Model (Equity Valuation) - MarketXLS

A comparable analysis valuation is a relative equity valuation technique used to compare the value of a company with metrics of other companies in the same industry. Equity valuation involves all the tools and techniques that allow investors to estimate the value of a company. It is calculated by multiplying the share price by the number of shares outstanding. The market equity value is different from the book equity value. The book value is the shareholders’ equity calculated by finding the difference between assets and liabilities. 

Equity valuation has two broad models, which include submodels within them. The two models are absolute and relative. Absolute valuation is estimated by calculating the present intrinsic value of a business by forecasting the future cash flows. The absolute valuation only calculates the values for one company; it does not compare the company with its competitors. Discounted cash flow and the discounted dividend model are examples of absolute valuation. Templates for both of these models are available through MarketXLS: 

Absolute vs Relative Valuation

Relative valuation models are the opposite of absolute valuation models because they calculate the value of a company by comparing it to other businesses. It explains that the company is a reflection of the worth of the companies it is compared to. So if the companies are worth five times their earnings, then the valued company will be worth five times too. Comparable analysis and precedent analysis valuation are examples of relative valuation models. Relative valuation is used more than absolute valuation because it is quick and easy to calculate. It also gives investors a starting point on whether they want to investigate more into the company. We will be focusing on the comparable analysis valuation model.

Comparable Analysis Valuation Model 

The first step of using a comparable analysis model is to choose one industry and then a group of companies within that industry. Creating a comparable analysis model is simple and using the MarketXLS template makes it easier. A valuation ratio is used to determine if the stock is overvalued or undervalued.

Comparable Analysis ValuationA comparable analysis allows you to use multiple metrics of your choice to make the best comparison between companies.

Metrics that the MarketXLS template uses are: 

  1. Annual Revenue: the total amount of money a company makes from all of its 12-month sales of goods or services.
  2. Enterprise Value (EV): measures the total value of a company and includes the market capitalization, the company’s cash on the balance sheet, along with the short and long-term debt. EV calculates how much a company’s takeover cost would be if an organization decided to buy every single share of the company. The calculation of EV = Market Cap +Debt +Preferred stock – Cash.
  3. Market Capitalization: refers to the total value of a company’s outstanding shares of stock. It is calculated by multiplying total outstanding shares with the current share price value of one stock. Like EV, a market cap helps us understand the relative size of a company compared to others. There are three main types of market cap companies: large-cap, medium-cap, and small-cap. Large-cap companies have a value of $10billion or more. Medium-cap companies value between $2billion and $10billion. Small-cap companies value between $300million and $2billion.The difference between enterprise value and market cap is that cash and debt are omitted when calculating market cap. This means that the market cap only gives an outer picture but not a detailed estimate. The company is undervalued and has more cash than debt if the EV is less than the market cap. If EV is more, then the company is overvalued and has more debt.
  4. Earnings before interest, taxes, depreciation, and amortization (EBITDA): are used to evaluate a company’s operating performance. The calculation of EBITDA = Net Income + Interest +Taxes +Depreciation + Amortization. GAAP or IFRS does not recognize EBITDA because EBITDA is said to be unreliable and misleading if used alone. EBITDA does not take into account the changes in working capital.
  5. Price-to-Earnings ratio – calculates the ratio of a company’s current share price to its earnings-per-share(EPS). There are two types of P/E ratios:
    1. Trailing P/E: calculates the ratio of current market value divided by the earnings-per-share of the previous 12 months. The Trailing PE ratio is more reliable than the forward PE because it is based on previous performances. The Forward ratio is an estimate, so the EPS would not be completely accurate. MarketXLS calculates this as trailing twelve months (TTM). 
    2. Forward P/E: uses future earnings to estimate the earnings-per-share for the next 12 months. The Forward P/E ratio is mostly used to make investment decisions on which stocks are likely to perform better in the coming year.

Using MarketXLS

MarketXLS offers a comparable analysis valuation template which calculates many metrics. All the investor has to do is input the symbols of the stocks they wish to compare. Link to the template: https://marketxls.com/template/comparable-analysis-valutation-model-r/    

Comparable Analysis Valuation Model (Equity Valuation)

 In conclusion, comparable analysis valuation is a great financial tool for investors to evaluate their target companies with their competitors. Along with the comparable analysis model, investors can use the other equity valuation models to analyze better and assess a firm’s worth.   

References

https://einvestingforbeginners.com/relative-valuation-daah/ 

https://www.thebalance.com/calculating-the-enterprise-value-of-a-company-357495 

https://www.fidelity.com/learning-center/trading-investing/fundamental-analysis/understanding-market-capitalization

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
I agree to the MarketXLS Terms and Conditions
Call: 1-877-778-8358
Ankur Mohan MarketXLS
Welcome! I'm Ankur, the founder and CEO of MarketXLS. With more than ten years of experience, I have assisted over 2,500 customers in developing personalized investment research strategies and monitoring systems using Excel.

I invite you to book a demo with me or my team to save time, enhance your investment research, and streamline your workflows.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and templates.

We respect your privacy and will never share your email.
MarketXLS is a complete Excel stock solution

Kevin Hsu

StockKevin.com

I have used lots of stock and option information services. This is the only one which gives me what I need inside Excel

Lloyd Lenase

Option Day Trader

MarketXLS is a data junkie’s dream. It gives me the flexibility to mine for hidden treasures.

Dave

Swing trader since 2011

I like to access historical closing prices on a particular date. That makes tracking performance easy.

Patrick Cusatis, Ph.D., CFA

Associate Professor of Finance - Penn State University

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

🎉 Exciting news! 🎉

You are invited to join our Discord Channel.

Interact, learn, and grow with experts in the markets!

Join our Discord