Comparable Company Trading Analysis with FactSet

[Music]

hi in this video i will show you how to

create a comparable company trading

analysis or comps analysis

in excel using factset

what we'll be using is a template

you can find it on canvas

and then we will be coding formulas

from factset

so the ultimate goal is to build

a tool um

or a com set that could be easily

updated

so

most of it will be driven by formulas

so uh if you were to come back to it

you know weeks or months later you could

update refresh

factset links edgy and you could

have your most recent information

also

i will start with column b where i have

tickers

and i'll be working

first with hd which stands for home

depot

but then because

i will build it as formulas i will drag

the formulas and you could change

tickers

and come up with another comp set so

it's a very versatile

tool

so again you could

change stickers come up with your own

set of comps you could refresh it later

and you know this is the type of

analysis that a lot of junior investment

bankers do

uh once you and you know for them a lot

of this work

is done so they start with

more developed template but i will show

you how to set it up from scratch so

that you can customize it you know how

it works but again very useful and

practical experience

so let's get started

i will open up my

factset sidebar that's a very important

uh tool for today

my first

column is company name so i'll start

with the ticker and i simply

would like to find a company name

so for that i will start by searching

company

in data item

and by the way let me also

update the identifier

so here

i will

click on lookup

sorry not lookup but this

select cell reference i would like to

have it as a cell reference

and it populated

b8

where my ticker is

so click ok there

so that's good and now home depot showed

up as

uh identifier the company we'd like to

look for and again like i will do all

the formulas for home depot and then

we'll just

drag them for the other companies

so we'll only do it for one company

so for company name

in this list

we have a

field called company name

and the formula

factset formula is fg underscore company

underscore name

so that's the one i'll be using and

you'll need to pay attention to

um

you know the exact formulas because

sometimes you know they're similar

similar things but um

you know i need to be very precise here

so what i did on my in my comps i

actually put a cursor

under

company name hd so i kind of moved to

the right

so that my output

is to the right of the ticker and you

can see this green

shaded cell so like it's kind of

pre-populated it shows what it will look

like

home depot inc

works for me

i don't think there's anything else that

we need to populate for company name so

i'll click insert

and

company name populated

now it moved to the right by itself and

the column

that that is next is share price

so for that one identifier stays there

and then

um you could look for price but

particular

yeah let me try looking for price

let me see if you find it

so we've got different types of prices

here

i think the one that works is just this

you know

pretty simple one

let me see the one i'd like

let me pick this uh fg price

and then the options are

latest completed period currency local

adjustments i'll keep all that

as it is

and press insert

for number of shares

there is some options here so let me

look for

shares

under data item

there's a field for example common

shares outstanding

what i really need is fully diluted

shares outstanding

um i'll talk more about it but it's um

it's the metric that

the valuation should be based on it

includes

uh any derivative instruments uh things

like

employee

stock options

so

fully diluted share count assumes that

these derivative instruments that are in

the money that are exercised

and that's so we would like to run

um

share count and then

ultimately the enterprise value based on

fully diluted share account but it

doesn't really pop up here

and there is one here called common

shares used to calculate eps fully

diluted that's not the one i'm looking

for

that is the average for

quarter i need the latest

share account not average because eps is

average

um it's for a quarter notice of a

specific date

so let me try this

formula basically so i'll type f

c s

underscore shares

and what showed up here is

fact set

equity capital structure basic diluted

shares and market cap i'll click on that

and then under item

i will select total shares so which is

the sum of basic and fully diluted

and then

everything else looks fine to me

so the value that i'm getting is

uh i think it's billion because these

numbers are in millions so it's 1

billion and 83 million shares for home

depot i'll press insert that's my number

of shares

market cap is simply

share price times number of shares

you could pull up market cap directly

from factset

i wanted to split it a little bit

because i want you to have visibility

into

how market cap is calculated and also

enterprise value you could get

enterprise value directly from cap iq

but

you know quite often you want to show

how it's been

calculated so we'll need two components

for enterprise value this is column g

here ent stands for enterprise

we need cash and debt

okay so for cash

what i need to do

is go to

search for cash

and then

select cash and short term

investments

and there

under report basis you need to select

quarterly

not annual

um you know if you are in let's say a

situation where a company reported

quarter one quarter two you don't want

to

go back to the last available annual

balance you'd like to use the latest

quarterly information available to you

so that's why you select quarterly

and then press insert and you'll get

the cash balance

and then let's

search for that

so here we'll select

total debt

and it's also quarterly

late is completed

so i'll press insert that

from here i can calculate the enterprise

value

which is market cap

plus debt

minus cash

so i have 425

425 billion

for

home depot

you'll be getting slightly different

numbers and the share prices are updated

daily

um

but you know that's normal if you're

doing it

in 2021

you should be getting kind of similar

numbers but

with time you know your numbers will

deviate more and more

let's do the revenue estimates

so here's two types of um pools we'll

need to do uh we'll need to

differentiate between actual

and forward-looking

information in my template

i've got letter a next to 2019 and 2020

i'm in

october 2021

so

2019 and 2020 are actual for me

but 21 and 22 are

projections

um i have this kind of interesting

feature here where um

when you open up this template

a day

populates for today

and then it's used in

the year and then the year fits into the

headaches

so if your date is later so let's say

it's

10

21 2022

and then your headings change

but again we are not going to go there

for now it's just

what you see on the screen

but again there is distinction that 2019

and 20-80 are actual and 21 and 22 are

forecasted

so for actual let's start with just

simply searching for

uh well i'll call it sales

that'll give us the revenue so fact set

calls is sales but it's

the same as revenue

so click on sales

and the default option here for start

date is latest completed period

which would be 2020 for me

i'd like to pull data for 2019

so i will say

one year ago

and that pulls 110 billion

i'll click insert

and then for 2020 revenue

i will

repeat this process but instead of start

date one year ago

i will select latest available

that gives me 132 billion

now for

estimates we need to look for

research estimates

so

we need to go to this consensus estimate

um

it's a tab you know it's got a lot of

options here

and it actually

for item selected sales because i was on

sales

and then

for the year

i will leave

um current unreported year one

so it gives me 145 000 projection

from research analyst for 2021.

i'll keep that

and for the next year

i'll go to consensus estimate

and select

for the year

following year

fy2

that number is 147

billion

so we populated revenue estimates and

actuals

uh from here we'll just do it three more

times for gross profit ebitda and net

income

but you know the process will be very

similar

so in the data item i will search for

gross

let me see what it calls it

it's a gross profit

maybe it's gross margin

cross income margin

well

so this is

this is actually percentage so let me

try income gross income

let's see what we have under consensus

so under consensus we do have

gross income

so for year two i'll insert that it just

has to be in 2022.

okay here's gross income and we just add

the bottom

for the actual

so i can populate that too

so

i need to start with

one year ago

i'll press insert

so it gives me

35

583

because i kind of shifted the cell my

formatting has changed a little bit so

copy a format from the previous cell

let me just finish it up so i will go to

gross income for 2020

i will select under start date

latest available

and then for 2021

i will go to

consensus estimate

and then under year i will select

current

unreported year which is

49 billion

291

million

so we we're done with gross profit we

have two more to go

let's try ebitda

we'll start with

a big.tab

for actuals

for

start date

i will select one year ago

so 18 139

and then

same thing ebitda tab

just instead of start date

its latest available

gives me 20

797

and then i'll go to

consensus estimates

for 2021

we will need to have cardio

okay we've got that 20 24

298

insert

and then one more time

for the year i will select fy2

okay so the last one net income

i've picked the first one i had on my

list net income

start date should be

one year ago

if i want 2019

and then

for 2020

i will select

latest available

insert

some reason my formatting is a little

off here i'll copy

format

from prior cell

then

2021 will go to consensus estimate

year would be

current unreported year

15 448

and then the last one consensus

estimates

okay did the same thing

you can sense from here that uh it can

be fairly repetitive exercise and i'll

tell you when you do it many many times

in real life it becomes

a little tedious and but you get really

good at it

by doing it many many times

uh but that's that's what it is like you

just you know

you know we pull a lot of data

um i'll tell you one thing we are

actually saving a lot of time by setting

it up in fact set because uh when i did

it in my job kind of old-fashioned way

you you manually repeated this every

single time but what you'll see like we

do it for one company

and then we

[Music]

you know just copy formula for the rest

of the company so we save a lot of

repetition and manual work

okay so let's calculate multiples now

i don't think i need

fact set here um so evita revenue

multiple it's actually a fun part and

it's fairly

um straightforward so i'll start with

enterprise value column column g

and i will freeze column g

so i'll put a dollar sign

in front of g8

so that i can copy across columns so

i'll divide

g8

by

ebitda for

sorry revenue

and here you'll see i skipped 2019

because that multiple isn't really

relevant i've pulled it more for growth

rate and analytical purposes but

i don't i don't need a multiple of 2019

revenue

so i've got 3.2

multiple and you know this cell has been

pre-formatted in the template

you know if you do it from scratch

you'll need to set up the formatting

that you like

because it

froze uh ev i can just copy to the right

and i'm getting

2.9 for 2021 and 2.9 again

for 2022 revenue multiple

i've skipped a year here so i'll have to

redo my formula i'll have to go to

enterprise value

freeze the column

and divide it by 2020 ebitda

so i've got

20.5 17.5 and 17.1

ebitda multiples which are

fairly high multiples

and then for price to earnings i'm not

going to use enterprise value i will use

market cap

freeze column f and divided by net

income for

2020. i'm getting 30.1 times

25.1 times and 24.8 times for 2022.

let's calculate annual revenue growth

rates

and that's when i need 2019 so i will

divide 2020 revenue

by 2019 revenue minus one

that gives me 20 growth rate

pretty high for

traditional company like home depot

and 15 in 2021

um

they've been beneficiaries of kind of

home improvement boom caused by

um

coved

and because their growth rates are high

their multiples are also high so you'll

kind of see

that relationship

for kagger which stands for

compounded annual growth rate

so we'll calculate average

growth rate from

[Music]

2020

from 2019 to 2022. so the formula is

2022 revenue

divided by 2019 revenue

so the growth happens over

three years

so

i will put it in brackets and

take it to the power

uh

one over three and three is three years

minus one

so the compounded

annual growth rate is 10

so over

three years

home depot

is expected to grow at 10

let's calculate gross margins

which would be

cross

profit

divided by

revenue

you can calculate ebitda margins and

profit margins this is just

one example

so you've got about 32 percent

gross margin

let me do one final pull from capital iq

a short description like if you're

changing tickers and want to look at

companies you're not familiar with you'd

like to understand what they do

and there's you know different types of

descriptions in fact set

i wanted to show you

a really

short one

that you can

put in your comps without kind of

manually doing a lot of work

so i'll type

description enter

and it's got equity business description

which is fairly long if i put it in

let me see yeah you see how it kind of

just stretches for

many columns um

so let me scroll down here

yeah i want this one actually third one

on my list

business description abbreviated in one

cell

and then if i press insert it gives me

just a few words

uh about this company and it's a good

check to have just to make sure

you're looking at the company that

that you really want

all right well so we've populated one

company

and from here

what i'll do i'll press ctrl c ctrl v

and

i've copied my numbers down

let me refresh it

some of the numbers are big here

so and you know it populated the rest of

the information um so it populated all

the

uh enterprise values all the estimates

and actuals

it gives me the multiples you know which

is the most important thing

i'll just do some sense check

to see if the multiples make sense you

know we've got pretty big

difference here like kohl's

i think is you know that has the lowest

multiple probably

because of

online delivery disruption costco on the

other hand still

is doing pretty well

and you can kind of correlate with

correlate the multiple with the growth

rate and again see if that makes sense

you can see that for calls

the kagger for revenue is negative so

lowest revenue

growth

means lowest multiple on evt beta bases

for costco on the other hand

their growth rate is 11

one of the highest growth rates and

their multiple is the highest

so that's how you kind of

interpret the multiples and get

comfortable with them

that really completes this exercise of

pulling data

from factset and if you save it again i

think it's a very

useful tool you can use it for different

companies different time periods

um and because you've done it from

scratch you can also

set up your own your own multiples your

own

you know things you'd like to use to

value

companies based on comparable company

trading analysis

thank you and i will see you in class

[Music]