Welcome, welcome. My name's Gerard Kelly and I'm gonna be running this session with you today.

What we're gonna be going through, we're going to be looking at an advanced LBO model, and we're gonna be focusing on preference shares or shareholder loans.

These are insanely important to an Excel model involving an LBO, but a lot of people go, oh yeah, big deal.

Who cares? They're actually hugely important to private equity.

So we're gonna be spending about 30 to 45 minutes looking at this, showing how preference shares and shareholder loans work their way through an LBO model.

I can see a couple of people more just joining, which is great.

So if you want to download the file, go to resources, which is at the bottom of your zoom, click on resources, there's a link, and then you need to go and download this file here.

So this is the file on my screen right now. It's in green, the part complete file.

Okay? Now before we get into that, I really wanna explain to you guys why preference shares are so important.

So let me go some notes here.

Why would we have preference shares in an LBO model? Well, here I've got a scenario.

At the entry phase, we're about to buy an LBO targets, and we've got one group of investors pe, right equity, they're going to contribute $290 million.

That will give them a 90% ownership stake.

Good for them. But we've also got some management.

And the management who will be running the LBO, they're going to contribute $10 million.

But here a weird thing happens.

They're only contributing 10 million, but we're going to give them 10% ownership.

So that's more than they should be getting.

There's 300 million being invested in total.

We're giving them a higher stake that they should get.

So why would we do that? It's to incentivize management, make them work really hard.

They're about to have the worst five years of their life.

It's gonna be nonstop work. But why would they do it? Because at the end of the five years, when we exit Caribbean islands for everybody, okay, so we've got our management heavily incentivized.

So why do we need preference shares? Well, preference shares come in because things go wrong.

We've invested 300 million and unfortunately we're now about to exit, but a rather lowly, we're going to exit for a low $250 million.

Sad times. Okay, well, you know, I still don't see why we need preference shares.

Well, let's look at the outcome for private equity.

Private equity, they had a 90% stake.

So they get 90% of the 250, they're going to walk away with 225.

They invested 290, it goes down to 225.

Oh dear. Sad faces all round.

But for management, it's very different.

For management, they've got a 10% stake of the 250, which means they're gonna walk away with 25 million.

They initially invested 10, it's gone up to 25.

Wow. Management are over the moon, management are having a party.

Champagne pops everywhere.

And we realize a weird thing has happened.

We've incentivized management to drive the company into the ground as quickly as possible.

They get to walk away with 25 million.

So the interests of shareholders are not aligned at all.

Their interests are miles apart from each other.

Reference shares or a shareholder loan. How we can fix that? So here's the same scenario again, but we're gonna change things on the blue section, on the right, in the blue section, I'm still gonna have private equity investing 290, but we're gonna split it into two different parts.

They're going to invest 200 as preference shares and they're going to invest 90 as equity.

Just regular bog standard equity management are still going to invest $10 million.

We've still got the 300 that we invested last time.

The ownership is the same, the ownership, private equity get 90%.

Management gets 10 cents.

The only thing that's different is that are those preference shares.

So we're gonna exit now, gonna exit now. And unfortunately we're going to exit for 250, exactly the same as in the red column on the left.

But the preference shares are where things are different.

The preference shares mean that private equity walk away with their full 200 million of preference shares before any other money is dished out.

So of the 250, 200 is already now gone to private equity, how much is left over? Well, there's still another $50 million that could be dished out and they're going to get 90% of that.

So private equity will get 245.

How do they feel about this? Well, they initially invested, oh dear, it was quite a lot of money, wasn't it? They initially invested 290.

It's now gone down to 245. Private equity, feeling sad.

Oh dear. Management, it's all different now.

Management of that $50 million left over, they're going to get 10%, they walk away with five.

So management are also feeling sad because management puts in 10 million.

Now they've only got 5.

So preference shares and shareholder loan safeguard private equity against this downside risk.

Okay, safeguards their investment.

So I want to show you guys how preference shares or shareholder loans blow through and LBO model.

So we're going to download the file to do this.

Now I can notice there's loads more people who've just joined in.

So guys, for those of you just joining, welcome, welcome.

Um, I've just been explaining why preference shares or shareholder loans are really important in LBO models.

I need you guys to download a file.

If you go to the bottom of the zoom, the bottom of your zoom screen, there's a little bar and one of the things on it is resources.

If you click on those resources, there's a link.

And in the link you'll get taken to this website and on the websites and need to just download this file.

Okay? So I'll give you a quick five seconds to do that.

Oh, we've got loads of people today.

Okay, so let's open up the Excel file.

There's a lot of tabs here and what the model is, it's mostly filled in, it's mostly done already.

I'll talk about bits, don't worry.

But the preference shares still need to be done from start to finish.

So to give you an idea of what we need to do, I've got a to-do list, okay? So here's our to-do list how preference shares affect an LBO.

So we start off with the sources and uses of funds.

Preference shares are source of funds and we're going to maximize those preference shares.

Then the next line down the balance sheets on acquisition day, we'll have to have those preference shares coming in as a source of funding and being added to the balance sheets.

After that, we model a normal three statement model.

You start with your income statement, then your balance sheet, and then your cash flow.

But on the income statement, preference shares don't appear on the balance sheet.

We do need to put them in as a liability.

So our preference shares will be there as a liability, but we're going to have to do some calculations for that liability and they'll come from their debt schedule.

We'll forecast the preference dividends, the preference shares and their dividends.

There's then nothing to do on the cashflow.

And then we'll calculate the IRR.

So this is our order of operation.

Now guys, if you have any questions for me, then please feel free to ask questions.

You can ask them in the chat.

You can ask them in the Q&A.

I've got everything open in front of me, so please feel free to ask.

I will obviously add some extra time at the end if you want to add any questions there.

So let's have a look at the model and let's go through this little to-do list here.

And we're gonna start at the top sources and uses of funds.

So here's our file. I want to start on the LBO tab and oh, there's a lot of stuff here.

Feels very complex.

Oh, Gerard, am I really gonna go through this? I'm gonna hold your hand, don't worry.

So what we've got is lots of assumptions and stuff, but what we really want to look at are the sources and uses of funds.

And I've just selected those sales there.

We've already calculated the acquisition equity value.

That's our major use of funds to buy the company.

The company we're gonna be buying is called Debenhams, okay? Debenhams, is a department store. It still exists, still exists in in a smaller format than when this LBO happened.

So we're going to buy Debenhams, we're going to refinance their net debts and then we need to pay their fees.

So there's an awful lot of money that we need to get hold of 1,800 or 1.8 billion pounds.

This is a British company.

So I now need to look at my total sources of funds.

Where am I going to get 1.8 billion pounds from? Well, we've already filled in lots of the numbers for you.

We're going to have a first lien debt, a second lien debt.

We could have junior notes and mezzanine debts and some common equity.

Look how small the common equity is.

Private equity, put as little as they can in there.

They're going to put the rest in their preference shares.

So we need to calculate, excuse me, we need to calculate this yellow cell.

Your preference shares are gonna be the plug in your sources and uses of funds.

So how am I gonna calculate it? Well, I'll ask myself, how much money am I going to use? How much money am I going to use? I'm going to use 1,800, okay? And then I'll subtract any money I've already got.

So I need to go and source lots of money.

How much have I already got? Well, I've already got, and I need to sum this, I've got a revolver first lien, second lien, junior and mez comma, and then the common equity underneath.

So I've already sourced most of that 1,800.

The rest will come from these yellow preference shares.

I presenter. Ah, cool.

My preference shares are 622.

Again, just have a look at the numbers.

The common equity is absolutely tiny.

Most of my private equity money has gone into preference shares.

The vast majority in fact.

So we're pretty much finished on sources and uses. There's just one extra thing I want to point at just while we're here.

It's the rate of return, the rate of return on the pres.

And we've got that, just sneaking its way onto the screen. On the right hand side, we've got the rate of return being a 12% pick note for all of the other items.

These would be interest rates, interest rate on the debt, interest rate on the debt.

Our preference shares, it's a dividend.

We're going to pay a 12% dividend on the 622, but it's called a pick note and that means it's paid in kind.

And that means my dividends are not going to be paid in cash every year.

They're not paid in cash annually.

Instead, it's going to roll up, it's going to accumulate the dividends, accumulate onto the preference shares.

I'm just going to call them prefs.

Excuse me. I've got a question in the Q&A, fantastic.

Will private equity firms only do deals with prefs? Do sellers accept this? Usually, okay, they are going to to want to see some equity financing as well, but it's absolutely fine to have it in as prefs because the only person that's going to be worse off, that's gonna be worse off than you are going to be the equity holders, which is typically management who've rolled over their shares.

Do sellers accept, usually we're not paying the sellers in preference shares. They're, they're not becoming shareholders in the company.

Preference shareholders in the company, they're getting paid in cash and they're, they're walking away.

It's only us that's putting in, money as preference shares.

Okay, great question. Thank you very much for that.

So we've got our prefs and we've got the rate of return on them, 12% pick note.

And that means the dividends will be added, added, added, added onto the prefs.

We've done the first thing in our list. That's awesome.

So I now move on to the acquisition day and we need to do a balance sheet here.

Now, I'm well aware many private equity models don't bother with a balance sheet.

It's an awful lot of work. You've got to get it to balance.

There's loads of items that private equity don't care about.

So I completely understand that.

Don't worry, we're not gonna be focusing, focusing on balance sheets here today.

Let's just go have a look at the balance sheet. There's only one thing that's missing from it.

Let's go to the balance sheet tab.

What we've got down the left hand side here, classic balance sheet labeled, and then we've got some historical balance sheet numbers.

That's great. Our deal is going to happen on the 31st of December 15.

And what happens on that day is you then have a whole load of adjustments, adjustments, adjustments, adjustments happen in columns G and H, which then gets you to your new balance sheet in this combo column.

Okay? So that is the post deal balance sheets in the combo column.

Lots of adjustments need to happen.

I'm not gonna walk you through all of them, but it's things like we pay off their old debts.

So the old revolver is gone, the old debt is gone.

We're not gonna go through all those adjustments, but a new thing has popped up.

It's this pref of 622.2.

We did that in the sources and uses and my uses have now been linked in here and all of the debt items are come in the first lien, the second lien, the mez, but the prefs was import.

So let's just check what's going on.

I've got myself a lovely balance, balance sheet, which is great going forward where I'm balanced, but that's okay, come back that in a second.

So back to the to-do list.

All we need to do now I say all that's to diminish it enormously.

What I need to do now is a three statement model, and that's always going to be income statement, then balance sheet in cashflow, income balance, cashflow, the income statements, there's nothing to do.

It's n/a.

If we have a quick look at the income statements, there's no ba, excuse me, there's no preference share line.

Now some people will argue that they say, hang on, Gerard, hang on.

I've seen lots and lots of income statements and they often have at the bottom dividends, they have net income and then they have dividends.

Surely the preference dividends could be here.

So to be fair, that could be the case, absolutely could be here.

Dividends aren't actually an income statement line item, but some companies put them at the bottom to show, ah, out of my 10 of net income, four were paid as a dividend.

It's just quite handy things to have, but it's not necessary.

So no dividends preference, dividends needed here, no preference shares.

We can avoid it. So the income statement is a normal three statement model.

So back to our to-do list.

The next thing we need to do is the balance sheet liabilities.

And we need to forecast those preference share as a liability into the future.

So let's go back to the balance sheets.

So here's the balance sheets.

We've got these yellow cells completely empty at the moment.

We need to find, or we need to calculate the preference shares in the future.

And I've already hinted at what it is.

We're going to take last year's preference shares and we'll add to the dividends, add to the dividends, add the dividends because they just accumulate or roll up.

Where are we going to do that? Well, back to the to-do list.

We're going to do it on the debt schedule.

These two will be linked together.

So let's go to the debt schedule.

We'll forecast the preference shares and their dividends.

So here's my debt schedule. It's quite big.

So if you haven't seen this kind of debt schedule before, it's called a cash sweep or a debt waterfall.

So what a cash sweep means is you put a big pile of cash at like the top of a waterfall and then as the cash goes down the waterfall, it gradually pays off the debt pays off the debt pays off the debt.

And that's what's happening here.

We're gradually paying off the revolver, then paying off the first lien and the second lien, et cetera.

We're not doing cash week today, but what I want to do is scroll down to the bottom and find these preference shares.

Now some people will say, Hmm, Gerard, why are preference shares particularly a pik note? Why are they in a cash sweep? Because the cash at the top of the waterfall pays off debt one, debt two, debt three in a pick note preference share pik notes.

That cash will not pay off the preference shares. Okay? These preference shares will only be paid off when private equity exits from the company.

That's gonna happen in year seven in this model.

So it's a good argument.

The preference shares, they shouldn't really be here in a cash suite.

That's why we've placed them at the bottom.

So we need to calculate our preference shares and my beginning preference shares will be last year's ending figure.

I then need to calculate accrued dividends.

Now, the accrued dividends, that means they're being added to last year's figure.

I need some kind of dividend payout figure or interest rates.

And we had that on the LBO tab. Let's go to the LBO tab. Let's have a look. And on my screen, I've got it in K18, it's in orange, that 12%, I'm gonna lock onto that proce four, and then I'm gonna multiply that by the debt tab.

So let's go back to the debt tab, but I've got a bit of a question mark.

If this is debt, I would calculate the interest on the average of last year's debt and this year's debt, or beginning debt and ending debt.

When it's a pik note, you don't need to do that. Okay? So we'll just take it on the beginning balance instead.

So multiply that up and I get 74.7.

So I start the year with 622 of preference shares.

I add the dividends, I can now sum them both to find the ending preference shares.

Great. So I've got 696.8, that's awesome.

Now I'll leave that on screen for a second, but what I'm going to do is I'm going to select those three and I'll copy them all to the right.

So I could press control R, copy them to the right command R on a Mac, or if you're not a fan of shortcuts, then instead I could just select those three, grab my mouse and I'm going to grab the handle in the bottom right hand corner.

Just pull that to the right. There we go. Cool.

Okay, back to the to-do list.

The debt schedule is done.

We have forecasted the preference share and the dividends.

Awesome. What do I need to do with that? I need to go and put that those mu, those numbers back into the balance sheet in the liability section.

So let's go do that. Back to the balance sheet.

Here's the little liability section. It's here yellow and I am in column J.

I'm gonna press equals, but just for a moment, just take a quick note.

Last year's preference shares 622, right? Let's go to the debt tab.

I'm gonna find ending figure in column J, J on the balance sheet.

J hits and I notice R last year, 622 has gone up by the dividends rates.

So I need to copy that to the right.

You guys can drag that to the right or you can select the right control R, whichever you prefer.

So I'm gonna do that, I'm gonna copy that to the right now if that's come from debt J 60 and my balance sheet is pretty close to balancing.

There's still two reasons why it hasn't.

One of them I care about a lot.

The other one I don't care about so much.

So why is it not balancing at the moment? What? What are these extra things that are happening because of preference shares that I need to know about if I'm building this model? Well, let me take you to another one.

It's the preference dividends that are causing a bit of trouble when you've got a balance sheet.

When anytime you have a transaction, it always needs a balance before the transaction and after the transaction.

So you might have something go up here and you might have something go up here that would balance, or you'd have something go up here and maybe down here that would balance.

When it comes to preference dividends, what people always assume with dividends, they think ah, dividends, right? Dividends mean we paid something out.

This is wrong by the way.

People always assume cash goes down.

Ah that's why it's not working.

But with the pick notes, there's no cash flow, okay? The preference dividends, they're just rolling up and rolling up.

So what's happening? Well firstly, my prefs, my pref shares, they're going up, the dividends are being added, added, added. They're going up. So what's the other half? Why is this not balancing? And even if you don't have a balance sheet, you still need to have to have this in your model.

The other half is it's coming out of equity, it's coming out of the normal equity.

So we'll have a decrease in our equity because the money that is kind of owed to your regular equity goes down because you've paid it out to your other equity holders, your preference shares and the two balance out.

So I need to reduce my equity.

I'm gonna go back to the model and I'm gonna do a little calculation on the calcs tab. There's a calc tab, special tab just for this.

And let's go have a look at it. Here's the calcs tab and in rows 12 to 16, excuse me, I've got an equity base calculation.

Base stands for beginning and then onto that you add the net income.

But here it is. This is the one we care about.

You then subtract the preference shares preference dividends.

So that's where what we need to do, we need to go and put that in here.

And that will mostly sort out our issues, with the balance balance sheet.

So I'm gonna press equals and I'm then going to put a minus sign because I'm subtracting I need the minus sign.

Now I need to find the dividends.

So I go to the debt app, I then go to the dividends, 74.7.

I'll then have that coming off.

I'll subtract that, subtract that from the equity.

My balance sheet is almost balanced now, so I'll copy that to the right or you can drag it if you're on a Mac or you just don't like shortcuts, just copy that to the right, no problem.

Drag it to the right. My equity now adjusted downwards.

Let's just check out the balance sheets. It's almost there.

It's almost there.

The only reason it's not balancing now is because of interest.

Okay? There's all that debt has circular interest on it.

We need that to flow through the model.

So let's just make two changes to our model really quickly.

Guys, to get interest, circular interest flowing, I need to turn a switch on.

I need to turn a zero to a one to turn interest on.

That's on the input tab.

Here's the circular switch in row five.

I'll turn that to a one. If you got an error warning saying whoa, circular reference, all you need to do is go to file options aisle and then options down the bottom left hand corner here, go to formulas and you need to tick this box here.

Enable iterative calculation.

If you want a Mac, that's okay, we've got you there as well.

In the top left hand corner of your screen, you'll see the word Excel and you need to press Excel references.

Then go to the calculation section and they'll, it'll say iterations or iterative calculations, you need to take it.

So I'll say that again. If you're on a Mac, you need to go to Excel preferences calculation section and then find something like that.

I think it says iterations. Great.

Just wanna check my balance sheet. Now really hope it balances and it does great, but I completely understand an awful lot of private equity models. They say, who cares about balance sheet? I don't need a balance balance sheet.

So completely understand if that's you.

Cool, we're almost done. So back to our to-do list.

We've done the debt schedule.

Next up would normally be the cash flow statements, but there's nothing to do on a cashflow statement.

Preference shares aren't paid preference dividends aren't paid, no cashflow.

That's an easy one.

So the final thing we need to do, so we need to calculate the IRR, the internal rates of return.

We need to calculate the IRR and include those preference shares.

So we've got that in the model.

We're going to find it in the LBO tab.

So here's the LBO tab, click on that.

I'll then scroll down and we've got just a couple of yellow items we need to fill in.

Let's just explain what's going on because you may not have seen this kind of thing before.

We're going to exit.

We're going to exit in year seven and exit in year seven.

When we exit and we are private equity, we're gonna receive a cash flow.

So exit in year seven, we're going to receive a cash flow.

We're gonna try and calculate that cash flow to do it.

I firstly calculate the enterprise value. That's great.

Got, we've got that. Your enterprise value, you then add on cash, subtract any debt, subtract any mezzanine debt, and we also subtract the preference shares to work out the equity value.

Okay, so we need our preference shares.

I'm gonna press equals and I'm going to press a minor sign because preference shares are subtracted when going from enterprise value to equity value.

I'm going to find my preference shares figure on the balance sheet.

It's in yellow, let's go find it.

So I'm in column J on the balance sheet now and my preference shares 696.8.

And make sure you've got that minus sign at the beginning.

Cool. Ah, got the first one in there. That's great.

Now again, I need to copy that to the right or drag that to the right.

The first year doesn't look very sensible.

The equity's negative. So let's copy this to the right.

Drag, drag, drag, drag, drag.

Oh, let's look at year seven.

Hey, year seven, got lots of lovely equity there.

Going to get rid of these percentages, but I need them.

So we've got lots of lovely equity there.

351.8, that's great.

So now I need to dish that equity out to the various shareholders and we're gonna be focusing on the PE institutions.

Now they're gonna get two different returns.

The first return they get is the equity.

And if we just remember quickly how much they invested, we go up to sources and uses of funds.

Private equity, they invested most of this 10 million pounds.

They invested most of this 10. Let's just have a quick look.

Their ownership was 90%.

So at entry they invested 90% of that 10, that 10 has now exploded in size.

Let's go have a quick look Again, the equity value in year seven.

Wow, that ten nine has grown to 300 good for private equity.

Cool. So that's their bog standard.

Boring, normal ordinary equity.

But what about the preference shares? Aha.

The preference shares is the second return they earn.

So they return the equity value rates and they get the pref, and they get the preference shares as well. They get two returns. So let's go and find their preference shares I press equals and I want to grab it from the yellow cells above, but it's negative.

So I'm going to use a negative sign to change it to positive again.

There we go. And I'm now going to copy that to the rights again, copy to the rights control R or commander. There we go. So my PE institutions in year seven, they get their preference shares back.

Six, two. Two has grown to 1375, oh my gosh, wow.

622 grew to 1375, 12% return every year and they get their equity value.

They invested nine, it's grown outta 300.

Pretty good. So their cashflow coming to them in year seven is 1676.

So now the last thing we need to do is calculate the IRR internal rates return for private equity.

We've got the formula already here.

It's linking to these cells.

But we just need to work out how much private equity invested and the preference shares come in again.

Ah, so we need to work out how much they invested.

I'm gonna press equals and open some brackets.

So how much was invested by private equity at the beginning? Well, they invested, oh, pardon me.

I need to make a small change. We need a negative sign at the beginning, because it was an investment, it was an outflow of cash to them.

So it has to be an, it has to be a negative.

So how much did private equity invest at the beginning? Well, they invested the prefs 622, great.

But they also invested some common equity and they invested most of that.

We're gonna multiply that by the 90, the 90% that they put in there.

So they invested 62 plus 9.

Okay. So that's how much they invested.

When I press enter, we'll see the IRR, and hopefully, hopefully, hopefully we'll get a lovely big return here.

So that's pressa. We're guessing 15%.

So 15% per annum, that's pretty good. That's pretty good.

But still, private equity are really aiming for 20% and above.

I know lots of deals at the moment are happening only returning 15, but the aim is really 20 and above.

So preference shares, they've affected a lot of things here, but they really protect private equity's.

Downside when it comes to upside doesn't really matter, but for the downside it's really very important.

Ah, so got a couple of questions come in. First one.

So does, does the IRR mean shareholders get an annual 15% return? Yes. Although they only got an actual cashflow after seven years and that cashflow after seven years means they've had the equivalent of 15%, 15%, 15%, 50%, et cetera.

But yeah, 15% per annum, that's what it means.

Another question. Why is the IRR formula linked until year eight? Yeah, really good question. Okay, so we've actually got the IRR here looking at years one and two and three and eight, et cetera.

The reason this is done is because what I could do is I could go to the top and I could change this.

Oops, sorry, wrong one. I could change this exit year.

At the moment it says year seven.

I could change that to an 8, enter.

Let's see what happens to the IRR. Oh comes down to 14.2.

Let's try year six and oh, okay, 15.9.

So maybe there's a better exit year and that's why we include all of them.

I'm gonna put it back to a 7.

Cool guys, are there any other questions while you guys are thinking of your questions and just writing them down, I'll just point out to you a couple of things.

First of all, we've got loads of more Felix lives running every single Friday.

Just have a quick look at all those that are coming up.

So next week we've got corporate bonds, M&A analysis, got AI, M&A consolidation.

Got loads of good stuff coming up here.

If I scroll to the bottom, you can watch recordings that have already happened, but a much better way if you, if you like these Felix lives and you want to look at some recordings, go up to topics at the top here, then go down to Felix live and here you've got the whole list. Look, this list is enormous.

Okay, there've been so many sessions.

Okay, so loads of Felix lives there.

The only last thing to point out, I know lots of you guys have either done some training with us and you're thinking about, oh, what do I do next? The pathways. The pathways are really good if you are already in an investment bank.

And if you've done the internship with us, maybe move on to analyst. If you've done analyst, pick and choose some advanced stuff.

If you are into, uh, asset management, the asset management pathways for you.

If you really want to look at private equity, you guys are here for private equity.

Again, maybe look at some of these advanced skills here.

Okay? Loads and loads of good stuff.

So lemme just go back and check if there are any more questions.

No more questions, guys. I hope you found that useful.

It's been awesome teaching you guys do come back again.

See you on another Felix live team. Goodbye. Bye guys.