Simple LBO Model - Case Study and Tutorial Video Lecture | Leveraged Buyouts and LBO Models : Startup Valuation - Finanace & Accounting

you
then welcome to another tutorial video
this time around we're going to go
through a quick LBO modeling test or is
it sometimes called a peeper LBO model
now these are very very common in
private equity interviews and really in
all types of finance interviews where
this topic could come up and what
they'll often do is instead of having
you sit down and create a full Excel
model with a lot of complicated details
that takes you several hours or week or
something like that they may give you a
very simple scenario and say here is our
scenario here's where the company's
Evita is here's how much debt we're
using here's what we expect for capital
expenditures here's what you should
assume for the debt repayment in the
cash here's the multiple that we're
aiming for and then based on this what
should we be paying for this company
upfront in the beginning so they could
easily give you a scenario like this and
it tests not only your understanding of
these modeling concepts and how an LBO
works but also if you can condense and
simplify information and if you can
figure out from the clues they give you
what different light em should be so
let's go to our excel file for this as
you can see it's going to be very very
simple because it is a very simple
scenario intended to test in a few
minutes whether or not you can figure
this out so what we're going to do here
is pretty much start at the top and
start filling out our IVA da and some of
the other metrics up here then go down
to the cash flow section and see if we
can use this to perhaps flesh out some
more of the items at the top then we are
going to look the exit at the end and
then based on some of the numbers here
we're going to calculate what the
initial price for this company should be
in this case the private equity firm ABC
capital is targeting a 3x multiple of
invested capital it plans to sell the
company after five years at an
enterprise value to keep it on multiple
of six X so we're going to go through
this exercise and see what's required to
do that now the first two parts here are
actually irrelevant the fact that the
companies had poor operating results of
revenue and epitaphs decline that
doesn't matter because with LBL models
you're only worried about future periods
you can sort of ignore these first two
paragraphs here the first relevant part
they tell you is that OpCo become
they're going to be acquiring his ebay
top 250 million and they expect eBay dot
to stay flat for the next five years
what does that mean in terms of our
model what it means is that in year zero
we've a bid of 250 million and each year
after that it stays flat notice how
we're not even bothering with formatting
or anything else like that to make this
look pretty we just want to get to the
correct answer because they speed test
here what else is relevant they're also
telling us that ABC Capital has obtained
debt financing 750 million and they
expect working capital be a source of
funds at six million dollars per year so
let's go in and enter these assumptions
the beginning debt balance is going to
be 750 million the interest rate is
going to be 10 percent and then working
capital
they said source of funds at six million
dollars per year that means that the
change in working capital I have
actually disabled working capital here I
should really call it change in working
capital it's going to be positive and
it's going to add to our cash flow each
year because they say source of funds
right here there's a lot of confusion
about this topic but in this case it's
very very simple so this might be a
company where for example they collect a
lot of deferred revenue upfront and so
they're getting more cash than what you
would expect from just looking at their
income statement it could be something
like that it could be that they're
taking a while to pay suppliers but
they're collecting cash from customers
very quickly so there are a number of
reasons why working capital could be a
source of funds but those are some
guesses in this case so I'm going to say
six in each year here your one through
your five and we have that assumption
entered right here let's keep going down
so OpCo requires capital expenditures of
35 million per year and as a tax rate of
40 percent so let's think about those
assumptions and going into them now the
capital expenditure assumptions are
going to be on the cash flow statement
or really this mini cash flow area that
we have really all we're doing is
starting with our net income and then
we're making some of the usual
adjustments to on that you see on the
cash flow statement like adding back
non-cash charges subtracting capex but
we're not going through a full cash flow
statement because it doesn't it's not
required by the question and it would
make it take way too long to do this so
for capex it's going to be a use of cash
because they are spending money on that
so we're going to take this copy this
cross that's negative 35 million per
year we also know that the tax rate as
they stated was 40 percent so we
have that information let's go back to
the case study and see what else we can
enter so we should assume no transaction
fees zero minimum cash required that
simplifies some of this treatment and
that PP&E on the balance sheet remains
constant for the next five years that is
very important because what that tells
you is that depreciation must equal
capital expenditures if the net PP&E
number is staying constant for the next
five years so they're not telling us
directly what depreciation here should
be but we know from the question prompt
that it has to be equal to the capex as
I say over here capex has to equal DNA
which equals 35 million because the PPE
stays constant for it to stay constant
depreciation will have to be a positive
of course capex will be a negative and
so they'll cancel each other out so for
the depreciation I'm going to link to
our capex and just flip the sign for
this one with that and then I'm also
going to go back to the income statement
and enter our depreciation figures right
here and remember this is going to be
negative on the income statement because
when you subtract DNA from eBay you get
to your EBIT your operating income then
when you subtract interest from that you
get to your pre-tax income or earnings
before taxes EBT down here so we have
that now let's go back and keep going
through the assumptions assume that
excess cash is not used to repay debt
and instead simply accumulates on the
balance sheet in real life you wouldn't
necessarily do this but I'm just trying
to give you some round numbers here that
you can work with with these numbers the
Venge is that these are so simple and
such clean numbers that you could
conceivably do this in your head or
write out on paper which is why it's
called a paper LBO model sometimes and
that's why we're making this assumption
here so let's think about this what does
this actually mean so to figure out how
much cash actually accumulates and what
our debt balance is over time we have to
do a couple things so first off for the
debt balance what we're going to do here
is linked to our beginning debt balance
up the top and then for the debt balance
each year after that if we actually had
cash that was generated that we can use
for debt repayment we could use it to
repay that debt but in this case we
don't
so this cash is just going to build up
on the balance sheet over time how do we
figure out what this cash flow is and
then what the debt balances well the
debt balance is easy because they're
telling us that nothing is repaid
so the 750 million is going to stay the
same through all five years here in this
model now
the cash generated to get this we have
to figure out what the company's net
income is and some of these other
metrics now we already have our DNA so
we can take that and our EBIT da and get
to our operating income or EBIT but we
need to get to the interest first to do
that we can take our interest up here
and I'm going to anchor this with f4 and
then we're going to multiply by the
beginning debt balance so in other words
the debt balance from the prior year
your zero here this is what's going to
determine our interest in your one if
that and let's copy this across we have
75 million in interest now we can get to
our pre-tax income so take our EBIT da
and our DNA and our interest those all
have negative signs so we can just add
these so our EBT or pre-tax income is
140 million each year what about the
taxes well for these we can take our tax
rate I'm using a negative sign here we
can take our tax rate Anker this with f4
x eb t and then copy this across then
for our net income just take our pre-tax
income and our taxes so we get to net
income of 84 million per year right here
now what about the cash that's generated
well remember how the cash flow
statement works we start at the top with
net income then we adjust for non-cash
charges working capital and then
subtract capital expenditures that gets
us to our free cash flow technically our
levered free cash flow or something like
that because we are including interest
expense here so for the cash generated
we're going to take our net income and
then we're going to add our depreciation
our working capital is going to add to
our cash flow here because they sends a
source of funds and then our capital
expenditures we're adding that because
it's already negative that of course is
going to reduce our cash flow so we have
this let's copy it across and you can
see that they generate 90 million of
cash each year here because we're
assuming no debt repayment what really
happens is that we have our net income
of 80 for the DNA and the capex cancel
each other out and so all that really
happens here is that our change in
working capital boosts our net income of
84 million and takes that up to 90
million instead so we have that and now
we can with this done we can sort of we
can move into the last step of this
process which is figuring out the
required purchase price to get a 3x
multiple of invested capital add an e V
to e Badal multiple of 6 X so let's work
backwards and think about how you do
this we know what our EBIT da at the end
of this
it is it's just the 250 million that we
had from the start we also know that
they're telling us to assume a 6x exit
multiple so we have that what is the
exit enterprise value then we take our
exit multiple of 6 X we multiply by our
y batata of 250 million now to figure
out how much actually goes to the
private equity firm you pretty much
always assume that they have to repay
any outstanding debt and then that any
excess cash generated can be put toward
that debt so it's going to increase how
much they get back at the end the cash
Turner is going to increase that the
debt remaining that they have to repay
is going to decrease that amount so
that's one we can think about it you can
also think about it like this that to go
from equity value to enterprise value
you have to subtract cash and add debt
if you're going from enterprise value to
equity value you do the opposite so you
would subtract debt and add cash instead
so there a couple ways to think about it
but that is what you do in this case now
for the debt we want to take the
remaining balance at the end so I'm
gonna use a negative sign and have this
negative for the 750 million right here
and then for the cash generated so what
we can say for this is we can just add
up the cash flow each year because
remember we assumed that this just stays
on the company's balance sheet we
assumed basically that they didn't have
any cash to begin with or that the cash
they had was the bare minimum they
needed so we're just going to have this
for our total cash generated comes out
to 450 million the equity proceeds let's
add up all these numbers so we have our
exit enterprise value minus our debt
plus our cash this gets us to 1.2
billion for the exit proceeds now to
figure out what the initial investment
was this part is a little bit tricky
remember / the case study instructions
that they are targeting a 3x multiple of
invested capital they plan to sell this
company after five years so let's enter
this 3x multiple right here and what we
can then do is take our equity proceeds
this is what goes to the private equity
firm at the end divided by our multiple
of invested capital and that'll give us
the initial equity investment that the
PE firm made now this is not the total
amount that they paid for the company
because remember they use the 750
million of debt in the beginning as well
but this is going to get us the equity
portion of what they contributed in the
beginning let's take this divided by the
multiple invested capital right there so
we have that and then for the initial
price what I'm going to say is
that we will take the 400 million that
they must have contributed in the
beginning and then we're going to add
the debt that gives us the total price
they paid their equity contribution plus
the debt they used to buy the company
1.15 billion
what is this as I multiple of evita we
can just take that price and divide by
the year zero e pizza here or the you're
wanting but that doesn't even matter
because they stay the same each year in
this case and so we get to a multiple of
four point six x and so that is really
it you can see some of the logic over
here I've just written it out in words
that the initial investment is four
hundred million to get to a 3x multiple
invest capital to use 750 million of
debt so the total price is 1.15 billion
representing a four point six x EBIT ah
purchase multiple so that's it this gets
you to the correct answer now and in
most cases that's it you're done one
other quick thing to point out is that
in this case one conclusion we might
draw is that on the surface this doesn't
necessarily seem like a great deal
because to get the multiple of invested
capital they're targeting we actually
need multiple expansion we need the exit
multiple to go up from the four point
six X in the beginning up to six X here
at the end which is expansion of about
thirty thirty three thirty five percent
something like that so that is one quick
conclusion we can draw based on this
very very simple math but that is really
it for our paper lvl model tutorial our
quick LBL model tutorial hope you
understand some more about this concept
now and are better prepared to do this
in case study interviews it is really
not that difficult you just need to work
quickly forget about formatting and get
to the math and the final answers as
quickly as possible
you