Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS
Fundamental analysisPortfolioStock ComparisonsStock investmentsStocksTechnical analysisValuation Models

Comparable Analysis Valuation Model (Equity Valuation)

Written by Meesha Ghedia
Wed May 05 2021
Comparable Analysis Valuation Model (Equity Valuation) - MarketXLS
See how MarketXLS helps you take advantage in the markets.
Download Option Template →
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/    

 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
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and starter sheets.
Get started today
Search for a stock

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

Get started today

Top MarketXLS Rank stocks

Rush Enterprises Inc. Class A Common Stock logo

Rush Enterprises Inc. Class A Common Stock

52.88
 
USD
 
1.12
 
(2.16%)
85
Rank
Optionable: Yes
Market Cap: 2,812 M
Industry: Auto & Truck Dealerships
52 week range    
42.72   
   55.06
Williams-Sonoma Inc. (DE) logo

Williams-Sonoma Inc. (DE)

126.10
 
USD
 
1.70
 
(1.37%)
84
Rank
Optionable: Yes
Market Cap: 8,021 M
Industry: Specialty Retail
52 week range    
101.58   
   176.89
Magnolia Oil & Gas Corporation Class A logo

Magnolia Oil & Gas Corporation Class A

23.81
 
USD
 
0.70
 
(3.03%)
83
Rank
Optionable: Yes
Market Cap: 4,657 M
Industry: Oil & Gas E&P
52 week range    
18.01   
   30.31
Foot Locker Inc. logo

Foot Locker Inc.

43.30
 
USD
 
2.59
 
(6.36%)
83
Rank
Optionable: Yes
Market Cap: 3,673 M
Industry: Footwear & Accessories
52 week range    
23.85   
   45.59
Shoe Carnival Inc. logo

Shoe Carnival Inc.

27.66
 
USD
 
-1.04
 
(-3.62%)
82
Rank
Optionable: Yes
Market Cap: 747 M
Industry: Apparel Stores
52 week range    
19.43   
   36.48
Group 1 Automotive Inc. logo

Group 1 Automotive Inc.

206.44
 
USD
 
9.36
 
(4.75%)
82
Rank
Optionable: Yes
Market Cap: 2,814 M
Industry: Auto & Truck Dealerships
52 week range    
136.16   
   206.49
Boot Barn Holdings Inc. logo

Boot Barn Holdings Inc.

87.42
 
USD
 
13.05
 
(17.55%)
81
Rank
Optionable: Yes
Market Cap: 2,188 M
Industry: Apparel Stores
52 week range    
50.20   
   105.66
Weis Markets Inc. logo

Weis Markets Inc.

87.46
 
USD
 
1.22
 
(1.41%)
80
Rank
Optionable: Yes
Market Cap: 2,298 M
Industry: Grocery Stores
52 week range    
58.40   
   95.57
PulteGroup Inc. logo

PulteGroup Inc.

52.37
 
USD
 
0.39
 
(0.75%)
80
Rank
Optionable: Yes
Market Cap: 11,760 M
Industry: Residential Construction
52 week range    
35.03   
   54.71
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

122.57
 
USD
 
2.42
 
(2.01%)
80
Rank
Optionable: Yes
Market Cap: 8,328 M
Industry: Auto & Truck Dealerships
52 week range    
88.58   
   131.55

More Features