1. Comparable Company Valuation Walkthrough
Okay. That was probably pretty challenging. There's a lot of stuff to do
in this. There's a lot of moving parts,
but let's go ahead and walk through it together. So first we can
start by calculating market cap. We have a share price
and then we have shares outstanding. We can just copy that down
and then do control D to fill down.
Enterprise value. Of course, that's market cap plus net debt.
Again, select down control D, and now we can calculate our multiples.
The first one is EV to 2022 EBITDA. What we'll do, we'll reference EV
and what we're going to do is anchor the column, so we'll F4 once,
F4 twice, F4 a third time so that we can
copy this formula. Once we complete it, we can copy it over and
it will automatically populate the enterprise value to the 2023 EBITDA.
So there's our EV to 2022 EBITDA and then we can copy it
to the right, we can double check it,
everything looks good and we can fill down.
We'll do the same thing with the PE ratio. In this case,
we're going to calculate it as market cap divided by net income.
And we will also anchor the market cap column and we'll reference net
income. Copy it to the right, just double check that the references are
correct. They are, and we'll copy it down.
So now that we have the multiples calculated, we can come down and
calculate the average, the median, the maximum, and the minimum. These are
all fairly standard outputs in a comps table.
Again though, we want to exclude the bottom four companies because they're
not great comps. We'll do average, and we'll start at the top and
again, we'll just go down six rows and we'll copy to the right,
same thing with median. Sometimes you want to use the median if there
are some outliers or if you have a very large compset. Median is
often preferable to the average, but again, it's the analyst's choice at
a certain point. So we have the MAX function and we'll just take
the maximum of the top six companies, copy that to the right,
and then we'll take the minimum using the MIN function, again, of the
top six companies. Okay. Now, let's go ahead and reference
the median multiples. And hopefully you saw this. We're going to value the
target company using the median multiples. So we'll pull in
the 2022 EV to EBITDA number multiple rather, median for the 2023 EBITDA,
the median PE, and the last median PE. Then what we need to
do is we need to pull in the 2022 EBITDA. Again,
we have the dates up here that tell us what metrics we need.
So we need the 2022 EBITDA. So we'll just do equals,
you do control page up to the DCF model.
So we wanna pull in the 2022 EBITDA, so H123 on the DCF
model, press enter. Wanna pull in the 2023
EBITDA as well. So equals control page up
2023 is in cell I123, same thing with net income, 2022 net income.
And then we'll pull in the 2023 net income as well.
Now, let's start by calculating the target company's enterprise value.
So since we have EBITDA and EBITDA multiple, we can directly calculate the
enterprise value like so, and we can do it for the 2023
metrics. Okay. We need to pull in net debt, of course,
we can reference that from the DCF model.
Go down to where we calculated equity value per share. Now,
net debt is negative on our DCF model.
We actually want it... We want it to be positive on our model.
So we're just going to switch the sign.
And one of the ways that I like to switch the sign is
multiply by negative one. So you can see
now we have a positive 14 million, and we can just copy that
like so. To get to market cap, again, we can take our enterprise
value minus our net debt, copy that down,
we'll pull over our shares outstanding also from the DCF model,
17,100. And we'll just go ahead and reference that like so.
And now that we have market cap and our shares outstanding,
we can calculate a hypothetical share price. So again, we started by calculating
enterprise value. We referenced net debt from our DCF model.
We calculated what we think the market cap of the company should be
based on those EV to EBITDA multiples, pulled over our shares outstanding.
We took our implied market cap divided by shares outstanding, now we have
an implied share price, again using enterprise value.
Next, we're going to calculate market cap directly.
So since we pulled over 2022 net income and 2023 net income,
plus we have the PE ratios for both of those years.
We can actually start at market cap, net income
times the multiple, again, net income times the
other multiple. We can copy down shares outstanding. We can copy this formula
down and we see we have share price already.
But we might also want to see what the implied enterprise value is
as well. So again, we can copy net debt down
and since we have a market cap and we have net debt,
we can add those together to get the implied enterprise value,
even though we started at market cap, that's very similar to our first
exercise when we looked at calculating enterprise value
from a public traded company. And then we calculated
equity value and share price given the enterprise value. So again,
the math is identical. So again, congratulations, you just completed the
Public Company Comparable Analysis. We're going to transition over and talk
about Precedent, Transaction Valuation a little bit, but then we'll come
back and we'll work on another Precedent Transaction Analysis table. It's
right below this one. See you there.
2. Let's practice!