Comparable Company Analysis Excel Walkthrough | Valuation Multiples - YouTube

in this video we'll go over the comparable  company's valuation from learning the basic  

theory to applying the concepts on excel and  eventually reaching a valuation range for a  

company and even though the name sounds very  fancy this is actually a valuation method that  

we use all the time as it's simply valuing an  asset by comparing it to other similar assets  

suppose we found a house that we like that  costs 300 000 and it's got 2 000 square feet  

but because this is our first house we're  not really sure if it's worth the price  

and so we look around the neighborhood to see if  there's other houses that have been sold recently  

in the process we find out the house in front was  sold for 600 000 but it's bigger at 4 000 square  

feet so it's not quite fair to compare the price  difference as there's obviously a difference in  

size as well instead we can use a ratio like the  price per square foot and this way we can find  

out that they're both actually selling for 150  per square foot based on this metric it seems  

like the house that we want is actually selling  at a fair price that's the basic idea with this  

valuation method now that we have an understanding  of this let's take a look at the key steps in a  

comparable companies valuation also known as a  multiples valuation firstly you need to identify  

comparable companies the theory is that similar  companies provide a good reference point to  

value the company that you're targeting and i say  companies here but this is really for any asset it  

could be back to our housing example where you  looked at neighboring houses to be able to tell  

what your house is worth as well secondly we need  to find the relevant numbers which will allow us  

to calculate the multiples or the ratios for  the housing example it was gathering the price  

as well as the size to then be able to calculate  the multiple which was the price per square foot  

once we gather all the data we can benchmark these  multiples so calculating the averages the medians  

etc of all of the comparable companies and lastly  we can determine the valuation range for the  

company that we're interested in and don't worry  if this wasn't all that clear we'll get into all  

of these steps in detail so let's get into it step  one is selecting the comparable companies and this  

step is quite important if you don't select the  right companies then your whole valuation is going  

to be flawed there's really two types of things  you need to consider here business and financial  

when it comes to business it's making sure they're  in the same industry that they operate in similar  

market regions and they have a similar target  customer after all it wouldn't be fair to compare  

a luxury fashion brand like say dolce gabbana to  a fast fashion brand like h m even though they're  

both selling clothes right on the financial side  it's about their numbers are they of similar sizes  

it wouldn't be all that accurate to compare  a startup with a thousand dollars in revenue  

to a company that's mature and has 10 billion in  revenue there's also profitability so seeing if  

they have similar margins and the credit profile  which is essentially how risky they're perceived  

to be by investors with these points in mind  how do we go about actually finding comparable  

companies a simple and free way to go about it  is looking at the company you're interested in  

and looking at their annual report suppose we're  looking to value nike just going to nike's annual  

report there's a section called competitors  in which you can see it's stating all of the  

competitors that they have and what they do  alternatively 4k options include bloomberg or  

capital iq depending on where you work or study  this might be available to you for free though  

in my case for example when i signed up cornell  these were available to us students this whole  

process should end up with you finding about  five to ten comparable companies the next step  

is gathering the relevant data and calculating  the multiples for this we first need to know what  

multiples we'll be using and generally these are  split into enterprise value multiples or equity  

value multiples the enterprise value ones here are  in dark blue while the equity value ones are in  

the lighter blue the difference is that enterprise  value refers to the entire business of both debt  

and equity holders while equity value is only  referring to the equity holders when looking  

at the multiples ebitda and revenue both flow  to debt and equity holders while earnings is  

only for equity holders as its net of interest  expense unfortunately these multiples aren't  

always applicable for example if a company has  no earnings or very little earnings you probably  

can't use a price to earnings ratio similarly you  might not even be able to have ebitda in there  

and so in that kind of a scenario probably your  ev over revenue is going to be your best resource  

these are some of the more popular ratios but  there's also some industry specific ones like ev  

over subscribers for a media company like netflix  or ev over square footage for a real estate firm  

once we know the metrics that we want we can go  ahead and start gathering the data that can either  

be done in a company's annual report in bloomberg  in cup iq etc to apply this theory let's suppose  

that we're a lemonade business called enjoy lemon  over here and we're trying to assess our values so  

how much we're worth and for that we've gathered  these comparable companies which are some of our  

competitors and we've got their different sets  of data that we're gonna need to then be able to  

calculate the three different multiples that we've  got over here to calculate these multiples we're  

gonna need both the enterprise value as well as  the equity value so let's go ahead and find out  

how to calculate them for the equity value it's  just gonna be the share price times the shares  

outstanding press enter there and from there  you can just drag it down by just going ctrl c  

then we'll go alt h v f that's just going to  paste the formulas for us press the f2 key if  

you want to verify that then for the enterprise  value that's going to be the equity value plus  

the net debt which is just that minus cash so go  equals equity value plus the net debt over here  

and again just drag that down onto hvf nice now  we can go ahead and start calculating these as  

we have the relevant data so for the ev over  revenue it's just gonna be the enterprise value  

let's make sure to lock that one so we can move  it across so press the f4 key three times there  

such that the dollar sign is only on the age then  we're gonna divide that by the revenue over here  

and press enter then we can just drag that across  so we'll just select it and then we'll go to  

alt hvf and there you go so we have it both for  the revenue as well as for the ebitda for the  

pe this is going to be slightly different as this  is the equity value and not the enterprise value  

so for this one it's going to be equals to the  equity value divided by the net income which is  

essentially the same thing as the price per  share over the earnings per share presenter  

then we're just going to drag that down then alt  h we have nice that's the end of the second step  

and speaking of valuation if you're liking this  video you can also check out our complete finance  

evaluation course where an investment  marker a financial analyst and myself  

teach everything we know about finance  valuation and financial modelling  

first we start with the basics of financial  accounting and ratio analysis where we learn  

to analyze a company's financial health using  apple's real annual report as an example once  

we're comfortable with the content we start with  financial modeling through a three statement model  

then we begin the valuation phase with the  discounted cash flow method starting with the  

theory of the time value of money all the way to  calculating the weighted average cost of capital  

after the theory we'll apply our learnings by  creating a full discounted cash flow on adobe  

looking at their real financial statements  to eventually derive a valuation range and  

an implied share price alongside the discounted  cash flow we'll learn two more common valuation  

methods in the comparables company's valuation  as well as the present transactions valuation  

once we have all the valuation ranges we'll  create visuals to showcase our findings  

and learn how we can present our  investment pieces in a stock pitch format  

so if you're interested we'll be running big  discounts for the first week since this video  

is published so go ahead and check that out in the  link in the description alright back to the video  

now the third step is to benchmark the multiples  because each lemonade company is going to  

be slightly different we want to try to find  something like an average or a median to be able  

to assess how much our company should be worth  based on what multiple so let's go ahead and do  

that we're going to get the highest the quartiles  the average and the medians all the way to the  

minimums so for the height that's just going to be  the max function versus the top key let's go ahead  

and select these press enter 75th percentile  for that we can use the quartile function  

press the tab key the array is going to be this  one comma and over here we want to find the the  

third quartile which is the 75th percentile so  put a three there press enter the average is  

just the average function then we'll select these  then the median is just the median function there  

press enter for a 25th percentile it's also  equals to the quartile function and from there  

we'll select this range over here press the comma  key and we want to find the 25th so put a 1 there  

press enter and lastly for the low it's just  the minimum press the top key and we'll select  

this range over here from there we can just  go ahead and select this and drag it across  

as you can see here both the average as well as  the median are the ones in bold because they're  

usually the most relevant ones um typically you  might want to prefer using the medium if there's a  

lot of outliers which the average accounts for so  for example let's say that this lemonco over here  

had say a hundred hundred times ev over revenue  then as you can see the average would be distorted  

but the median would actually stay the same  so that's why sometimes you might prefer to  

use the median let's now work on the final  step which is determining evaluation range  

and for this we'll be down over here where  we want to find out the enjoy lemon valuation  

starting off with the implied enterprise value for  the ev over revenue that's just going to be equals  

to we can select either the average or the median  over here let's say we go for the median then we  

multiply that by the revenue which is going to  be this figure over here for enjoy lemon press  

enter once we have the implied enterprise value  we already have the net debt which is going to be  

this figure up over here then make sure to  lock you press the f4 key once and press enter  

so the implied market value is just going  to be equals to the implied enterprise value  

minus any debt that they have and then press enter  lastly there for the shares outstanding that's  

going to be equals to the shares outstanding  that they've got over here so that's 125  

and then the implied value per share is just going  to be equals to the implied market value divided  

by the shares outstanding so that gives us 9.61  from there we can go ahead and drag this over so  

we'll go ahead and select it and just drag it  over here if we've locked everything correctly  

which i think we haven't because we forgot to  lock the shares out sounding press the f4 key  

there then just copy and paste that now  we've got for the ev over ebitda we've got a  

share price or an implied share price of 13.05  then lastly here we've got the pe ratio which  

is going to be slightly different because this is  an equity multiple and not a enterprise value one  

so it's actually going to get us the implied  market value for that we'll go to equals we'll  

select the median and then multiply that by the  net income over here for enjoy lemon press enter  

we can get the shares outstanding just copying  it from across and that should give us the share  

price which is going to be 9.77 if you want to go  ahead and also want the implied enterprise value  

you can go reverse that calculation so we will  actually copy the net debt that we've got equals  

this one plus the market value that should give  us the enterprise value as well so here we could  

conclude with something like hey the implied value  per share for enjoy lemon is around say 9 to 13  

which seems to be in line with the current share  price of 11.4 one thing to note is that the  

comparable companies valuation is not a perfect  method on the pro side it is very simple to use  

and it doesn't take too much time definitely a lot  easier than something like a discounted cash flow  

that being said on the coin side it's easily  influenced by the market which can also be a  

pro sometimes so for instance say if the entire  market is overvalued then obviously your company  

is going to be overvalued as well because it's  based on industry peers another con especially  

for big companies is that it's not very easy to  find comparables for example if for a company of  

the size of apple with all of their offerings  it's not that simple to find another competing  

company there for these reasons it's quite common  to use more than one method to value a company  

for example similar to the comparable companies  is precedent transactions which is also market  

based but on how much was paid for a similar  transaction in the past then there's discounted  

cash flow also known as dcf which looks  internally at the company's future cash flows  

to estimate value now in order to keep this  video concise we actually had to make a few  

simplifications so let's look over them for the  shares data which we found on the excel file as  

these shares outstanding over here usually it's  not all that simple to find this there's other  

types of shares that we need to account for like  stock options convertible bonds and other things  

like that that are going to change the number of  shares outstanding then there's calendarization  

and not all companies finish the fiscal year  at the same time in our case we didn't really  

consider the dates but generally speaking  maybe a company finishes their fiscal year  

say in may while another one finishes it in  september and so there's gonna be a discrepancy  

in the time period that you're comparing which  you'll need to adjust for lastly going from  

enterprise value to equity value usually has a few  more line items than just net debt these sometimes  

include preferred stock minority interest etc  now those are not all the simplifications there's  

other ones but we don't want to make this video  too long let me know down in the comments if you  

want a video on comparable companies for a real  company for more evaluation you can check out this  

link over here for our course this other video  over here for the discounted cash flow method  

or this video over here to look at valuation  methods more generally that's all for this  

video hit that like hit that subscribe if you  liked it and i'll catch you in the next one