Advanced LBO Tutorial (Real $3.3BN Transaction)!

what's up everyone we are going to do an advanced lbo stream and this one I'm pretty excited to go through this lbo with all of you because I spent a lot of

the past few days kind of remembering how to like do a bunch of the stuff we're going to go through today and I wanted to start off by showing you exactly what you are going to end up

building um you're going to be able to download this Excel for free later on but I wanted to show you just like a an overview of what we're going to be

building so you kind of know uh what your what's in store I guess so starting off from the top we're going to be actually finding all of these

assumptions ourselves so whereas in previous I guess the one lbo stream I did and my DCF videos where I have a lot of the information downloaded for this

stream we're going to be finding every single piece of information here from filings and uh from filings and so it's going to entail a lot more work just

Gathering all the figures and that's going to be a little bit new we're going to go into filings that normally a lot of people don't know about which um basically is like the proxy to find a

lot of the key specific information for the actual transaction and I forgot to mention so we're going to be going over a real lbo that happened back in 2010

when a private Equity firmed name named 3G acquired Burger King and I wanted to choose Burger King because everyone knows that company and so I thought it' just be a little bit more interesting

and because it was a real deal we can actually look go back and find a lot of the figures that we need and then um we're going to build sources and uses very standard for an lbo then we're

going to build out these assumptions here um going to actually track down all the historical financials and then build out um some

cash flow items then lever free cash flow that schedule and then interest rates and then our irr so I kind of showed you guys I kind of gave away I guess um what we're doing before we

actually kind of do it but uh I thought it would be a good way to for you to kind of see what exactly we're going to be

doing so um to give you an an intro to everything here previously we I did an lbo a simple lbo mod model stream which

where we went over where I gave you the assumptions we built out sources and uses worked on a basic operating model basic that schedule IR calculations in this video we're going to as I said find

all the assumptions based on real transaction figures we're going to build outsources and uses table operating model with two scenarios and then a debt schedule was a circuit breaker and irr

calculations so this is part one because already I don't even know how long this video is going to take could take two to three hours and I'm not trying to sit on

in front of the screen for like five hours because in the next two lbo modeling videos that I stream they're going to be continuations of what we

build on today so in Parts two and three there's going to be a lot more that we build out so including Revenue cases separate historicals tab forecasting cash flow statement and balance sheet so

full three statement model additional debt tranches such as adding a revolver mandatory debt repayments paid in kind interest cash sweep liore amortization

amortization of financing fees sensitivity tables adding a DCF so there's a lot more that we're going to be adding on and building off of what we what we do today so um and I there's

going to be a lot of actually just a few new like uh Excel modeling type of shortcuts or no um formulas that we're going to use actually basically one called a sticky

if statement which is not like super hard but you're not really going to find it um in many what I mean is that it's kind of

hard for anyone for for you to really know what that is and to be taught it uh anywhere else besides actually if you're in banking or watching the stream I

guess no one else really uses it um notes so this model will be available for free to download after the stream ends you can check out the description for that um new Discord Channel I'm

going I've been super super slow about creating this but it will be uh available soon so it'll be in my description at some point hopefully in the next few days I also am building out

a how to get into Investment Banking course which you can sign up for early if you'd like and then there's a bunch of videos that I've listed in the description which you can uh check out

if you'd like um also I guess yeah me I guess the guy who created this model so I have a bunch of links in this model later if you want to check out or in description

I actually during uh before I actually stream I'm going to try to mention on my Instagram stories when I'm going to be streaming I have uploaded that right now

so feel free to check that out if you like and with that said um I'm going to add a tab move

this over to the end and then this is going to be our lbo now before I go into this though wanted to let you guys know about today's sponsor for the Wall Street prep who has created a private

Equity certificate program with Warton which is where I got my MBA and if you're interested in breaking into private Equity this is certainly a program you can consider it's an online

course it's not cheap 4,800 um for the entire course but you really get a lot out of it you can use my code rare liquid to get 300 bucks off prob early by this deadline to get 200 bucks off

and this program runs three times a year so but depending on when you watch this video video this date might be a little different but you can see here that a

lot of prominent private Equity firms work with Wall Street prep and Wharton like KKR and then there's like Blackstone here um car I know part of it

as well and then you a lot of people who attend this program are people who are actually kind of already working in the finance industry so a lot of time there's Bankers or investment professionals and just want to like

sharpen their skills meet people ET you can see where all of these um the applicants and people come from and then if you go down you can kind of see it's an 8 week program 8 to 10 hours weekly

time commitment but you kind of learn at your own pace which can be a plus if you are busy and don't want to necessarily kind of go to school to learn this stuff and you'll learn things about an intro

to the private Equity class the PE investment framework private Equity deal process deal structure modeling and how to think like

a private Equity professional um the faculty and speakers include Wharton professors um keynote speakers David Rubenstein Legend uh who co-founded Carlile and then there's Martin brand

the head of North America for private Equity Blackstone a lot of like cool speakers um there's actually a ton of them and then another cool thing is that

you act W Street prep and Wharton actually kind of create this if you go join this program then you're able to meet a lot of the peers I know they're having inperson events they actually

invited me to one but I wasn't able to make it in New York Los Angeles bunch of other places and then the other thing is like uh there's exclusive recruiting events with the top private Equity Head

Hunters so feel free to check this out I'll leave a link to all of this in the description if you're interested and with that said let's go into Burger King

so I'm going to also be asking some answering questions in the chat if you ever have them so feel free to ask away as I kind of build all of this out and

as I kind of mentioned this is probably going to take a while uh so let's start off with the basics so Burger King

acquired by 3G Capital $24 per share transaction valued at $4 billion so this is when the transaction was announced you can see here September 2nd

2010 and what end ends up happening though after an announcement and this is true with almost actually just I I I'll

just say it's very true a lot for private Equity transactions where the transaction value that's listed and a lot of the information and like the

amount of debt take taken on and all of that it kind of changes once the transaction actually needs to close and then details are kind of ironed out so what we are going the numbers we're

going to see are going to be a little bit different than some of some of these except like this $24 per share um but this is really like when you are starting off with any transaction you always want to start off with the press

release because that it will always there will always be a press release so let's um I'm going to kind of I have a screen up here so I'm going to be

looking up here quite a bit um so yeah I built everything from scratch but then uh I can't I want to make basically the model that we built pretty much the exact same thing as I showed you at the

beginning of this video and so to do that I'm going to be kind of looking up at the model that I built here and there a little bit so starting off we're going to

build our different sections so we're going to have assumptions here oh no this is going to be for switches although I probably I don't

know if I would start with switches when I am building out a model uh three or five let's do five okay so the

two switches that we're going to need are a circuit breaker and then a case and I'll explain what those mean

later I'm also going to make these columns bigger size I'm 15 and then assumptions

here oh this also might might be too small let me zoom in a little bit okay so let's think about the

assumptions that we need um let me see we'll call this transaction background and then over

here we're going to call this uh evaluation at entry and so for transaction background let's get our company

name ticker last latest clothing share price L latest

clothing share price date and then I'm going to add some borders here all right company name uh Burger

King the ticker before it was before it was acquired bkc so oh I forgot actually it should be obvious based on the fact that there's like a

dollar per share but the uh Burger King was a public company and then it got taken private through this transaction so ticker

bkc um I'm going to make these blue there's a certain blue that I want though it might be this one yeah okay

latest closing share price so the transaction was announced on September 2nd so that means that the

latest closing share price the last price when it was public was the day before Oh share price oops so that's the date

and then to find the latest closing share price we can we could uh let's go let's go to Burger

bkc um share price history might be a little bit hard to find all right [Music]

historical that this makes sense because the transaction closed at $24 okay so we're going to have to go way back go back to

2010 and let's go to September transaction closed on the 1 so this should work okay closing price September 1st 2010

was $18.86 and sometimes this isn't you'll see why we need need this later but sometimes this number is not even what you actually want because

sometimes the deals get leaked and so before the announcement the stock price will already have run up a lot of times when that happens sometimes actually people even

say that like the really like old school Bankers even though everything should be super super confidential they actually leak the information somehow through

their ways to uh news outlets and then they add pressure to the deal I've heard um I've heard about that before but all you know we're not sure if that's

actually true or not true or not um let me ask there are some questions so is this Burger King India or restaurant Brands this is Burger King like the entire company I'm not sure what Burger King India is is good website for free

Street estimates honestly I don't I I think I would yeah I would just Google it around but I I don't really know um would you

be able to show your assignment at Wharton uh not in actually I don't so I I can't um I can't share that because there's

just too much information in there so no I can't sorry because I for like a real

transaction okay so um what we want to do here is kind of figure out our valuation at entry and so we're going to need to get the all offer value p uh

offer value per share we're going to see what the premium or discount is then we can get the offer value which is basically your Equity value

and then diluted shares outstanding so that's another thing that we have not done before or I haven't done on my streams of how to actually calculate your diluted shares so that's something

we're going to do then we're going to need our debt and cash figures I'm going to indent this a little bit and then and that gets us to our Enterprise Value

um I'm going to just yeah okay capitalize this and then LTM

EA which we're going to calculate way later and then that we're going to back into our entry multiple so we're not going to be able

to calculate everything right now but there are certain things we can so this offer value per share as we we have this

scene here it's $24 per share and sometimes even after an announcement there can be another buyer that swoops in or something and there could be

additional things that happen that caused this to increase but in our case that's not going to happen so it's just 24 and then the premium is our offer

value divided by our previous price and that gets us to 27% so basically when Burger King was bought

the investors received a 27% premium um which is not bad okay and then diluted shares outstanding this is

something that we are going to calculate later um debt cash we also need later so everything else here we're going to actually calculate in a little bit I

want to work on another section first so transaction financials and assumptions um so things that we're

going to need are our LTM IA Deb cash minimum cash this is also something new and then exit multiple um and I see a question is this going to be available yes these streams

are always going to be available Okay so LTM IA so these are all going to be numbers here that we are going to uh

need let me change the formatting a little bit okay this is very OCD of me but I just want there to be like uh an extra space

a little bit here um because all the numbers when I'm formatting I usually do it that way so I'm actually going to steal from one of my

other models okay let's use this formatting all right let's check this [Music] too should be fine okay um also another

thing that I'm going to do throughout as I build this model is ADD comments which I normally don't do but what's really good practice when you're building out models when you're in banking in

particular or actually any model that you built in any Finance industry is adding comments so that people know where you found your information so if you press shift F2 you can add a comment

and then I'm going to add here that you know you can find it at this link that way someone ever has this model they can see where you got your information so LTM IA we're going to get later debt and

cache these are figures that we need to pull up and I'm going to grab this in you can get this information from a variety of different sources but so all these links that I

have up here and we're going to go through today at least that these uh six are going to be are are already in the video description so when you're

thinking about okay so this company was acquired in 2010 September 1st and for whatever reason Burger King

kept reporting um more filings even after acquisition you that's very odd usually the company stops there is a good reason as to why this company in

particular did um I but I didn't really look into it too much but basically if I were trying to figure out okay what are

the latest debt and cash figures at the time of acquisition you have to think like okay it's September you have to go back and think to yourself okay it's September 1st 2010 right now and I am

traveling back in time the information that would be available to me is only starting September 1st 2010 and before that right all the numbers here or all the filings here

after that the figures are not available so if we didn't have all this information then we would use the 10K

debt filing or 10K filing here to grab our financial figures and so this is the 10K if you go to financial

statements and go down you can see that there is this um balance sheet here right

what I'm going to do though is later on use some of the filings that came afterwards and you'll see

why but let's start here so okay we need to get our um cash and debt figures so

let's let me go here so that starting off with that current portion of long-term debt and capital leases so Capital leases are always a

point point of contention in terms of whether or not you should add it to your debt and the way I was taught it at JP Morgan was pretty much never add Capital

leases to your debt although it can be argued that you should and also could depend on the industry I was in healthcare and so maybe that was one reason but Capital leases are basically

assets that you need to pay like a monthly or maybe yeah usually monthly um kind of payment for so it kind of is like that where you have like an interest rate where you're you're

constantly needing to pay but you end up eventually probably like owning the asset it really depends on the contract but because it's kind of like an you eventually own it as an asset

it's treated a little bit differently just then versus debt where where like for debt if you pay it off it's just gone right versus the capital lease you might end up owning it or something like

that so basically all that's to say that I'm going to not include Capital leases and I'm going to show you also why I think that makes sense for this model a

little bit later on like my justification so 93.3 you can see this includes Capital leases right so I'm going to have to take out Capital the

short the current portion of capital lease here later on so debt equals 93.3

plus our total or term debt net of current portion 666

7.7 so there's that I'm going to also add a note here 10K page

72 for now and then our cash is a lot easier it's just cash and cash equivalents sometimes there's like other stuff but in our case here it's pretty simple

and then I'm going to copy this comment oops so copy and comment alt es C enter if you have a

PC okay um so let's find the current portion of capital leases that we need to take out so to do that maybe I'll

look up 93.3 nope capital leas we're looking for the current portion of capital

leases okay so the remaining balance blah blah blah blah blah blah blah which 5.6 and 4.8 are classified as current portion of

long-term Deb and capital leases as of June 30th 2010 Etc so this 5.6 is what's included in the current portion of long-term debt and capital leases so that's what we want to

subtract okay so we're going to take this out and then add another note so 10K for this is for the debt and then 10K page

101 this is for current portion of capital leases and can make this a little bigger I will I'm going to

add the I I always just add the link because basically what as an analyst if you add add this the associate is always going to check over your work right and so as they're

checking this over if they're like okay I see it's like in the 10K whatever it's just an extra step for them to have to like find everything so if you add the link they can just copy

paste it right so I'll just add that here too after while if you're like always referencing it you don't you don't probably need to do it for every single

one okay so let me go a little bit into why I think again like Capital leases taking it out also makes sense so we're going to go into this proxy statement um

this is called the Prem actually I don't really know how they call it but usually it's like I think def M 14a this is preliminary 14a the way you find this also like

normally if you have bam SEC then you can see what all all of this says but I didn't want to pay for it so and you can still access everything you just don't see what the titles are but you can

guess where they are right so proxies here whenever a transaction occurs whether it's an lbo or just a strategic acquisition there's always going to be

proxies uh that are available and honestly I don't know what all of these are but the one you're looking for is one that says that has an m in it for like merger and usually there's one

that's not a preliminary one there's like I think def m14a but for whatever reason I couldn't find it so this one is

like good enough we'll use this and when we go here if we go into I'm G to have to find what we're looking for

but uh the merger okay so if you go to the merger there's a lot of good information you can like go to town on this stuff with like all the background and whatever you want a lot of really interesting

background for every single transaction that happens and all of it needs to be public because of the SEC so you can see here when the proposal was made so it was made like

April 6 and then all this stuff happened um thing I'm looking for oh actually this is not what I'm looking for I'm looking

for the merger [Music] consideration financing it's either one of those two yeah I think it's

financing all right so yeah this is what I'm looking for you can see here when so this kind of talks it kind of alludes a little bit to sources and uses but you see here we we anticipate

the total funds needed to complete the merger including the funds needed to pay our shoulder shareholders which will be about 3.3 billion so this is where they're paying the equity holders $24

per share and then you're paying that Ness of the company approximately 729 million right so if we were to include the capital leases I think we get

further away from that 729 million and so um that's why like I think there's like a a good uh for the private Equity investors

like I imagine they just kept the capital leases maybe like refinanced them later who knows what what what happened but I think it's it makes sense to get closer to this figure

and then you can also see here funded through combination of 1.5 billion in equity financing 1.9 in these are all the debt tranches and so we're going to

kind of reference this information later but use information from elsewhere okay so um some other key pieces of information that we do need to get

though is what is the minimum cash balance required so I'm going to basically try to figure out all this

information um in the filings and let's go through that so this 10K is from 2011 so I if you remember earlier I mentioned okay normally you want to only

look at the filings or you'll only have the filings from when the transaction was announced and then the company once it's private are not going to be publishing 10ks 10 Q's but Burger King

did for whatever reason I and um so what's really helpful in our case is that we can go to to the 10K a year later and they're going to list out all

of the important information we need for what actually happened um for the transaction so uh let me figure out what page that

was on the thing we need is on okay page 87 so I guess how you would find this otherwise is um minimum

cash cash Reserve [Music] um 3G that's too many okay I'll know when I

get to the page so I guess I should also explain what I'm looking for um so I guess first if you go into

the uh this is like the notes D the Consolidated financial statements you can see here that on September 2nd 2010 the company entered into a merger

with uh 3G which is a private Equity Firm and so um if you go down this has a lot of information of

what we need so as you can see here require total cash of 4.3 billion and as I talked about this is different from the 4 billion here right so as I

mentioned earlier like what you see here it just can be a little bit different and we want to get closer to the actual figures of what really happened in order to get to the most accurate irr that we

eventually can right so that's why since we have this information we're going to use this other times if companies aren't like filing these 10ks or whatever after they're taken private then there will be

a lot of news articles and sources where you can find this kind of information so the thing that I'm looking for here

is this 64 9.4 million of cash on hand so um there's this concept that you should know of minimum

cash and this it basically whenever a transaction happens you're going to need some amount of cash in the bank to run the business right just because you're acquiring the company doesn't mean you start off with

a zero cash balance and so there is like this minimum cash number that we're going to need um we're going to back into it a little bit later but uh this is something that we're

going to be needing a little bit later and then or actually we could we could do it right now so M minimum cash 69.4

million basically it's your cash minus this minimum or let's see oh 69.5 million sorry 69.4 million

cash on hand that's what they used so the minimum cash balance is uh that they're going to need is the amount of calance on the balance sheet minus the

cash that they actually used um sorry sorry that was confusing so let me start from the beginning so you understand so this here talks about how the entire transaction was financed

so 4.3 billion was financed with 1.56 in equity 1.5 in debt another 250 million euros and 8800 million in senior notes

and 69.4 million cash on hand so 69.4 million of the cash on the balance sheet was used to finance the transaction meaning that the minimum

cash that the company needs is 11 18.2 so that's what we're going to have and still make a little bit more sense as we build out our sources and uses

table too exit multiple we're going to need to get a little bit later um there so let's now start building out

our capital structure and I know a lot of this is taking a long time to get through and that's just the nature of

this okay so I'm going to move this down by one oops what's happening okay all right so we're as you can see we're

kind of building things out in peace meal like not everything can be just done section by section cleanly so um

let me just also add a note here this is 2011 10K page 87 and then contrl L you can get the link

it's Chrome shortcut not an Excel one okay so capital structure let's look at what we have here which is um the

financing right so a lot of this information is a little bit different than what you get in the I actually actually don't think it's even listed here in the

10 uh oh in the proxy right in the proxy it says here we're going to finance the transaction with roughly 1.5 billion in equity 1.9 in senior credit facility

blah blah blah but in the 2011 10K you can see that it was 4.3 billion 1.51 250 so they added like a Euro tranch 800 in

senior notes so we're going to use the numbers that the private Equity from actually dealt with so um to build this out we're going

to need to write down what these are so secur Term Loan there's a USD trch and then the secure ter Lo Term Loan the

EUR Euro tranch and then there's senior notes right that's what they have here and then 1.56 billion Equity we need that

later that this number we're going to try to back into basically one 1.51

billion so let's have amount and then we're going to need oops times IA the interest rate

fees fee the actual dollar value and then the term like how many years and then amortization so we're not going to actually use this information in this video for how to amortize fees and stuff

like that but we are going to little bit later um so let's start off with the amount so so the first trunch was 1.51

billion and we want to make this [Music] blue so I'm going to copy this formatting and then I want to add a note

of where I got this right so 2011 10K page 87 and I'm not going to add the link because at this point I've added it mult multiple

times um 250 million trunch but this is in Euros so I want to get this somewhere else this disussed note 11 um and then $800 million in senior

notes so let's go to note 11 which is going to be way down here okay so what I'm looking for here is the Euro was €250 Million EUR but I

want the US dollar figure since we're doing everything in USD and that'll be just more accurate so this is 334.12

10 2011 10K page 105 and I'm going to actually make that same comment so control copy alt

ESC because we can get that information here as well and then 800 million in senior your notes EIT figure we can I

guess build this out for now the number is going to be it's going to be it's going to have an error for a while oops I need to Anchor

this yeah I don't love seeing that as I build on build out a model but oh well okay um so and then you can see the interest rates here as

well right 6.82% 7.11 10.19 and let's get okay 6 point 8% make this blue um blue color

coding means it's means it's a hard code by the way 6. 82%

6. 82% 7.11 1.11 and then 10.19% you can see the maturity dates uh

well actually fees let's see fees this is something that I'm going to need to back into as well so we're going to

let's do that after I add one thing which is the term so uh okay so term this transaction

happened at 2010 so you can kind of just like think about this and think okay so maturity is 2016 so it's like six years eight years so six or the other

ones six six and eight I'm going to format this so that it says Years also make this

[Music] blue okay um so fees all of this we're going to should we do it now actually yeah we

need to do it now so in this filing if we go back to where we were looking at the actual transaction information whenever a transaction

happens there's a ton of [Music] fees well actually it seems like a lot but I guess it's just like a small percentage of what actually happens but

fees and expenses related trans to the transaction total 91.2 million including 43.2 of investment banking and legal fees which we will add later

compensation related fees of 34.5 million and then commitment fees of 13.5 million associated with the bridge loan

which uh blah blah blah debt issuance cost capitalization total to 69.2 million so essentially the fees for the

debt issuance need to be 69.2 million and for me what I like you you're not actually

given the real percentage it may be possible to find somewhere but I think it's kind of hard to find that information so I played around with

these figures so this is kind of something where it's not going to be something super quick for people to for you to figure out in like a real uh if you're building this like without me

doing this already but if you're just joining basically I built out this model in advance of this stream and so I already know a lot of this information

and basically what we're doing here is just I figure I figure okay we'll say the fee for the first trunch is 2.7% the next one's 2.5% next one's

2.5% and then if we sum this up this gets us to 69.1 so pretty close to the 69.2 that we need maybe I make

this like 2.7 2.71 2.51 2.51 okay so we're at

69.2 so now we have our fees and then amortization of fees this is not something we're going to build into our model but we are in the next part two and three of this elbio that we're going

to be building out more and more over time but essentially you're going to need to model this in at some point uh

later on and so these fees are something that you can kind of amortize and so basically what this means is you divide it by the your the actual fee and

then you can kind of like uh take it out of I think um I have to double check this but I believe that you can I forget actually if it comes out of

the income statement but I'll know this by the next video basically uh don't worry about it but I wanted to build it out so we have it ready for the next video and then we're

going to have the total here um so total equals the sum of this and

then that we need later but we do want okay I'll fix that formatting when I need it amortization okay uh make this

gray all right so we have a lot of good information here now um I think what I want to do next is build out the a lot

of like what's up here and to do that we're going to need to build out our diluted share count so something again that we haven't

done before U we're going to call this shares and I'm actually going to take from the model that I built instead of recreating all this and

just also it kind of explain what everything is and then technically you can since you'll have this model you can build everything out on

your own as well if you'd like okay so and and the reason I also didn't want

to build all of this out too much is because even when I was at JP Morgan all of the um like I never

calculated this from scratch we always had something like this to c calculate our fully diluted shares so let's start off with the offer

price which we have over here I I'll I'll explain what all this means step by step as we go through it I'm going to make this green and green means that it comes from

a different sheet basic Shar is outstanding so no we can't get it here think about where I got this last

time okay page 88 all right so you could go to the 10K cover which is actually what I initially did but I decided after digging around

more that I wanted to use a different number so the number of shares as of August 19th was 135 milon 882 but if you look here so in the notes Here in the

2011 10K there's a section that talks about how much cash was paid to equity holders and then for all everything they

purchased and then this represents cash paid $24 per share or 136 million outstanding shares so there are some more shares issued I thought this was

just a more upto-date real figure so that's what I want to use so 136.6 million and then I'll add a note

this is from the 10 2011 10K page 88 um I'll explain what all these mean later and all of this stuff but Bas uh

when I was at JP M we also had this kind of like table and in our case here as we let's look up uh

options there's always like an options table or options information in the stock-based compensation note that uh gives you information about

how many options they have and everything um as of October 18th T10

10.41 yeah okay so this is out of October 18 2010 which is again like a little bit post the transaction but I think this is best

number to use versus this one which is as of June 30th okay so 7.4 oops 7.4 212 million exercise price this is

$743 and normally a lot of 10ks will break this down into a lot of different tranches and have a different exercise price for

each because this is a weighted figure but for whatever reason Burger King doesn't and so um we're not going to be I'm not going to be able to show you how to like just

enter in all of this information but uh maybe in the next like I plan to may maybe like build out this lbo for Burger King and then another lbo transa real transaction in the future and so maybe

like I'll show you this sometime in the future um but all of this is kind of basically working towards figuring out

which Shares are in in the money so what that means is if the share price offer price is above the exercise price then it's in the money because the exercise

price is the price at which the owner of the option if it's vested can pay to purchase a share so if it's 1740 $17.43

to purchase a share every anyone in their right mind um would exercise that option because you can buy it for

1743 and sell it for 24.3 right you can make this profit

$657 so this basically just you can think about it as okay if that's true then we can uh assume that the dilutive share count it's not going to increase

exactly by 7.4 to but that's kind of roughly like the thinking here but you need to use uh the treasury stock method to actually figure out how many shares would actually be

issued um so this so to go through like what all these numbers mean so in the money exercisable options so that's like 7.42 and then total proceeds are the

exercise price times the number of shares so that's some product of D21 like this column like it's just multiplying each of these but all of these are zero so basically it's just

multiplying 17.43 * 7.42 total shares repurchased are total proceeds divided by our offer price and

so the net dilutive options are actually this 5 7.4 minus 5.4 these are the actual options that are newly

issued um but uh and then dilute a share from other security so there are not only stock options are also things called um like restricted stock units

performance stock units I think sorry let me move my chair but these are shares where the exercise price is actually zero these

are like more special I guess and given to I don't actually know like executiv and stuff but these are basically hey if you get if you fulfill your contract or

whatever you can actually earn shares and not have to pay this kind of exercise price or anything so there's

1.7 million for that and then the uh so this let's add a note here that we got it

from um page [Music] 99 or this one is Page 100 so 20 11 10K

page 100 this is we got from 2011 10K page 99 all right so let's link to so we're going to do something a

little bit different I'm going to show you why but diluted Shar is outstanding let's link to

this um make this [Music] green and then for now we can calculate our offer value or Equity value or

whatever times our fully diluted share count right but um the reason that I'm going to do something so normally

oops normally what you should do here is ADD uh wait let's see okay normally what you should do here is get your basic shares

outstanding plus dilutive shares net dilutive options plus your dilutive impact of shares from other Securities so that means it's 14.3 so this adds Z

shares outstanding Plus plus your stock options plus the rsus and all that stuff gets us to 14.3 and that gets us to 3. 368

billion um but the reason that I didn't want to do that let's see where I'm gonna have to remember where let me

so go to page 87 I think all right so you can see here the number that I really want to

get to is this figure here because again this is a this is the actual amount that 3G ended up paying for the shares right

322 32 77.3 um in total 332 5.4 so they talk here when they actually

made the transaction they settled um 48.1 million so instead of for whatever

reason instead of paying for these stock options and this uh these like rsus or psus or

whatever they paid like a different amount instead which if we look here is like the 48.1 million right

so let's see what I did um yeah okay so 48.1 million in order to get the number of shares that they actually paid for the

stock uh okay so 48.1 divided by our offer price basically this one this basically

where I'm doing here you pretty much would never do anywhere but I'm just doing this to back into a number uh to back into a number so this

is in the uh 2011 10K 2011 10K page

88 this gets us to 138.5 shares and this gets us to an equity value of 3325 which is exactly what we want here right

so that's why I am doing it this way to get to the exact figures that the company actually paid um in order for us to just get to

the most exact like oops exact irr as possible okay so we have this um debt we

can go here cach we can go here and then make these purple and then we can back um back into our or

get to our Enterprise Value which is our offer Value Plus debt minus cash 3893 and then LTM EIT we'll get a little

bit later um so we'll we'll enter this a little bit later exit m multiple we're going to make well I'm going to do it later

okay so let's let me think about what we need to do next here but I also want to let's see capital structure add a row

here okay so we did a bunch let me um see if there's any you their questions here in the chat I know I've been just ignoring it for a while

but uh in a compan comparative company analysis do you use industry average multiples or regression based multiples I haven't used regression based multiples before maybe it some

people use it but I've never would you be able to make up the case for us on your online course similar to your assignment um for my banking course I don't think we you need to build out

something like the banking course I'm building is going to be aimed to uh help you know exactly what you need to know for banking recruiting not necessarily like building out intense

Financial models maybe like I could build like a modeling one later later down the line uh could you explain the rational behind minimum cash I could I'm going to do that a little bit more in a

bit when you say you're making it from scratch what software from scratch just means that I we started from an empty Excel

spreadsheet okay so let's see capital structure next thing we need are sources and uses actually I'm going to make this so this is going to be our s oops

actually sources and uses we're going to make our sources table here and then our uses all right

so sources and uses um if you're unfamiliar with sources is basically the [Music] new capital that you're going to be

using to make a TR to fund a transaction whereas uses are everything you're going to be using that money for

essentially so when you are thinking about okay I'm going to fund this transaction what a private Equity Firm does is they use um

a lot of debt to fund the transaction that's like the whole thing behind what they do and so these three tranches that we built out a career Term Loan for USD

for Euro for their Euro one and then Senior notes and then we also can see from oh man where was it I think oh here

okay the acquisition uh of approximately 4.3 billion we're financed with 1.56 billion in equity so that's what we want to make

sure that we have for sponsored for sponsor for the equity figure that the sponsor pays and then these bet tranches and

69.4 million of cash on hand right so they funded it with the three things that we added here plus cash on hand

and then sponsor Equity right so those are the sources that the company has said they

used so we can write total here um we can have the amount we're going to need uh let's just copy this actually and then percent Capital this

should look familiar if you watch my previous lbo videos

um use uses so we can just link to what we have here which is the amount we'll make this

purple cash on hand so they talk about here 69.5 four million cash on hand right so that's simply the amount of cash the company has on the balance

sheet minus their their minimum cash balance so again kind of explaining what all this means when a company is acquired it

still needs cash to run the business right Burger King once it is acquired doesn't mean that the company can just have zero dollars in its bank account it's going to need like working capital

basically in order to keep funding the transaction uh or sorry keep business going and so the company has $888

million in cash on the balance sheet but 69.4 million of that was used to fund the transaction so they just took it from the bank accounts essentially and

then funded part of the transaction with it so that's the 69.4 million and this minimum cash balance is implied that if you take away the 69.4

million that was used this is the amount of cash Burger King needed in order to buy inventory let's say like buy burgers and patties and french fries and potatoes or whatever so that's what that

that means sponsor Equity we need to add later it's our plug all this information

we can add later but total here basically is the sum of this and there's a lot of stuff we can we need to like kind of add in a little

bit later so let's get to uses though there's equity payment um which is like paying off all of the equity holders and then there's um debt

refinancing transaction fees financing fees and then other which I will explain later so for this as

well we are going to or let's let's figure out what all of this is so for the equity payment this is up here this 3325 right this is what you need to

pay to for to all the equity holders and again um something we're trying to back into is this like $4.3 billion doll number this should be what this

eventually becomes so Equity payment as I said is up here this is what you pay your Equity holders debt

refinancing so this this is the debt that you need to refinance so what happens in a an lbo is that a um private Equity Firm will get all of the

company's debt that it previously had and then get new debt right which is everything you see here and the reason it does that is because the private Equity Firm typically wants to take on more debt new debt and will refinance

everything usually with better interest rates because they have connections with the bank and do this all the time um okay and then transaction [Music]

fees you can see here fees and expenses related to transactions total 91.2 million and this is what they paid investment bankers lawyers compensation related expenses I guess here commitment

fees for bridge loan so 91.2 million right for a 3.3 or four $4.3 billion deal they paid

around 91.2 million in transaction fees which I don't know kind of it's kind of a lot not saying that they paid more than they should have or whatever I'm just like always astounded by like how

much money is moved around in these kinds of things okay so this is linked to the same sheet so it's purple this is a hard code so it's blue and I'm going to link

this to the 2011 10K page 87 we got that here financing fees are here

and then we need to get to a total of uh 4.3 billion or so um let me remember what I did

here so we want to eventually the key number that we want to get this [Music]

to or this two is 1.56 billion doll in equity right so I'm going to back into this

number a little bit so 4.24 billion the sponsor Equity is always the plug this is always how you do it your total sources should always equal your total

uses and it shouldn't be a penny less or a penny more it always needs to equal each other so this is actually going to equal this and then your sponsor Equity is a plug so

whatever remaining Capital that the private Equity Firm needs in order to fund the transaction is going to come from the private Equity firm's fund right so the

sponsor Equity that needs to be paid is the total total here minus the sum of the rest of the

sources 1528 right but it says here 1.56 so that means there's in order to get to this number there is like something else that is is not talked

about it's a really small number but it is like 40ish million or 30 some 32 million or something like that right so in order to back into this number basically what I what I what I did here

or I'm going to do is I'm going to get 15560 minus this and I'm just going to I'm just going to say okay there was 32 million somewhere else that needed to be

um accounted for but is not talked about in the filings or whatever so I'm just going to say that this 32 million there's another there's another

like line item we need here so I'm going to add a note here um to get to 1.56 billion in

sponsor Equity adding this extra line item and you can refer to this in the 10

you can go to 2011 10K page 87 to refer to how I got this 1.56 billion so this way if like a an associate ever came up to me and was

like Hey like why' you add this 3032 or whatever I'd be like okay like look at this note it says that the sponsor paid 1.56

billion all right so um we have a lot of the key figures that we need here for sources and uses again we need to get to our eitaa figure this will really help us basically plug

get a lot fill in a lot a lot of this information that we are missing right now and that is actually the next step

so what we're going to do next is get our financials and this is also actually going to take a while because

we're going to literally copy paste all the figures so bear with me here um um okay

so let's go to the 10K financial statements we have our figures here before we do this since I'm at like a natural stopping point let's see if

there's any questions on in the chat uh what was the gender ratio male to female at JPM so I would say actually

for analysts and Associates it's pretty even and during my class in New York like I was in SF but in New York there are actually more girls like a lot more

girls than guys as you go up the ranks i' okay so let's call it 5050 for analysts and Associates and then once you get to like VPS VPS and directors

and above usually it I guess VPS start to get like 65 35

and then the directors the lower level directors is more like 60 30 uh 70 7525 and then once get higher and higher up

it's like 909 to1 or something like that things made may have changed them and these are all just super um random watching this while eating a Double Whopper I love it um how does Burger

King use this cash on hand to fund the transaction if it sponsor Equity how does BK fund it then so BK is not the one funding it it's just that the

private Equity Firm can just use the cash in order to fund the transaction all right so financials um the company fiscal year

end is on June 30th for whatever reason I honestly really hate it when companies have a weird fiscal year end but can't control that okay so we're

going to have an assumptions table here but not worry about this until later and then we're going to need the operating

model so um let's figure out what we need here so Revenue percent growth I'm going to indent

this okay and then we're going to need our cogs this percent of sales gross

profit also percent of sales Opex percent of sales again ebit uh and then we're going to build

out two cases and I'm going to show you why this is like actually so important and why I decided to do it you'll see but we're going to have a conservative case and then we're going to have a management case

and then we're going to need interest expense and then interest income earnings before taxes and then taxes percent tax

rate and then we get to our net income okay so a lot of figures here and we are going to need to um get all of them by

hand and I'm only I'm doing this because I get a lot of requests of for the DCFS at least of like oh what if you don't have fact set how would you get your

figures and while we're not doing it for a DCF it should be kind of helpful to do do this on your

own um or you can build this out without any any software or anything basically is why I'm doing this okay so as

I'm doing something a little boring and basically copy pasting a lot of figures uh let me know in the chat where you guys

are from like what country or city always curious to know you know where you guys are from um okay but to look at the as you guys are doing that the

numbers here are in the financial statements um this is the 10K best practice is always to copy

paste it's very very tedious but it will save you from making a ton of Errors so

2502 okay okay cogs so let's see total Company restaurant expenses okay so the company just

basically um treats a lot of these basically as their cost good solds so other other companies may think okay when I pay an employee these are sgna

expenses but for a for a food company part of the actual like cost of the food and delivering it I guess they this they treat as what they pay or they include

what they pay as employees not just the food and paper and product costs so actually I think maybe a faster way to do this I can copy paste it this is from

2010 though this is from 2012 I'm going to change all the formatting later gross profit Revenue minus your cogs

Opex other operating income expenses to Total oper so this actually adds everything so we're going to need to back into

this I'm using my mouse should not do that okay so OPC since they annoyingly like combine everything we're going to

need to back into this I copied it so what I did here was oops what the

heck I just there was like some way I changed the color which would save me a lot of time if I knew how to do

that okay whatever uh uh so what I did was I copy pasted the uh total number here and then I

subtract I can't control Z back into it okay I'll do it again so this is for 20 or this is 2010 wait am I doing this wrong oh no

this is right the 2169 other okay in uh total operating costs and expenses 2169 is 2010 and then 20 this is 20 2009 and

this is 2008 so in order to get to this figure what I'm doing is getting the total minus the cost of goods sold right the these are the right figures but if I

copy paste this here this is going to just get to or it's just GNA copy the wrong cells or whatever right so what I'm doing here is control shift

right arrow key control copy and then altv and then that paste the values and I can delete all

this ebit is our gross profit minus our Opex and then interest ex interest

expense have these figures here so this is 2010 this is 20 2009 that's

2008 then here is the interest income whoa okay uh and it's negative here because they subtra they're subtracting it out and interest income

is positive right you're in you're receiving money since the formatting is a little weird I'm just going to actually hardcode this one so 5.9

2.7 and 1.0 and I personally like to keep every single number in the model positive even

if it's a an expense something you should subtract out um but other people like to make everything negative if you're negative if you're subtracting and then sub

subtract it out so that's just totally personal preference okay so all of these are hard codes so I'm going

to get this alt for the formatting and then you can just press F4 which will basically repeat what you

just previously did um okay and then ebit minus interest expense plus interest income gets you to your ebit

and then taxes income tax expense here and later on when you download this model like you don't need to do all this stuff I'm doing this is very tedious

stuff um but I guess good maybe to see like how to do it okay so this was a hard code

and then let's get the percent of well first we need growth so growth is the current year minus previous year minus

one I like to have the percentages in parentheses which is not [Music] here I don't want to build this out so

I'm going to take it from one of my other models oh actually so I'm going to do something that a little bit different than the DCF models that I

used to build or I I streamed and build out the assumptions over here so Revenue we're going to have Revenue

growth um gross margin EIT margin tax rate oh and I see a lot of you guys are from all

over actually I'm going to have to okay so we're going to have all of the assumptions here so it's the reason I

put it on the top I think sometimes a lot of like different modeling Excel I don't know sheets and stuff that I come across a lot of them like to have

these on the bottom below but I always like to have assumptions at the top that drive everything like as much as possible because then you can like change these things it's like more easy

it's more quick you get quicker to like seeing where the assumptions are like up here and stuff um in an Ideal World all your assumptions are in one section at the very top but I think like sometimes

it's it's better to have in elsewhere and the reason you want to have your assumptions kind of all grouped together ideally at the top is because then someone can easily change some things and like kind of see if you have an IR

reference number here which we're going to C we're going to have later um you can easily just change some assumptions and kind of just see what

what happens to everything that'll make sense later okay so Revenue so we're going to have basically all the percentages here so Revenue growth we're not going to have for this first one

this minus this minus one so since these are historical these are just calcs gross margin um and if you put an anchor to

the row for the revenue then you can use the same formula actually no you can't because you need a different line here so but anyway okay so that we have our ebit

margin actually do I want to do it this way yeah I will okay and then tax rate is the taxes divided by our earnings before taxes oh

oops okay so these are all links or these are all calculations so they're going to keep I'm going to keep them black and

then let's calculate all these other ones so or let's um get all these other ones okay so there we have the one we have

Revenue growth this is growth margin ebit margin then tax rate size

and then there are some that we're going to just calculate here so this is cost of good sold divided by our Revenue growth and the reason that we're not doing it for every single one is because

these four are the ones that we just need to to make assumptions for all these other ones we don't really need to so this is for our cost of good sold and

then since I anchored the revenue row I could just copy paste for all the ones that are percentage of sales this

one and that one okay so we have our historicals now and then the question is okay how what kind of projections do we need and this is

where a lot of the time people are uh when they're asking about like projections from like oh W if I if you don't have a good access if you don't have good access to fact set or whatever

and projections like how would you actually get your numbers so that's what's actually really helpful about the proxy that I

mentioned here way I guess like earlier on I referenced this if you're like joining more recently but um the really

interesting thing I think personally of trans actions is that they actually gave you a lot of information of what the company was thinking at the time so

if you look here there's this line that says certain company projections you go here you

can go down a bit and you can actually see these are a summary of The Five-Year projections that the company actually thought that it

would hit right so and normally like getting projections like management projections for any company is like impossible unless you get unless you're like an equity

research analyst or hedge fund analyst or something like that right so what's cool here is that we actually have this case of what management told 3G like hey

we think that we can hit these numbers and these are numbers that you can probably plug into your model in order for you

to um build out your lbo and all of that so we can use these numbers

basically to get to a uh get to a or to build out our projections essentially um or actually we can

actually just use these numbers so we're going to use the numbers for Revenue formatting is all weird but so in a in a future video uh when I build out like part threes and like make

this more complicated and whatever um we're going to build out Revenue cases but for this video we are going

[Music] to just use what management gave us so I'm going to add a note

here that this is from uh Prem 14a page 60 okay and so for here we're actually

going to back into this as well so this is and it's this is interesting right even management I guess like has told the company like

we're going to decelerate we're going to probably get rid of a ton of Burger Kings for the first years and then kind of regrow it as they become they figure out their

operations in a sense so that's what we're going to do here for our Revenue growth and then

cost if get sold and all of this we're going to back into but uh and I I'll explain why in a bit

but let's also get our management figure here which is here right so this is the management

figure I'm going to add the comment here as well and then we can also do this as a percentage of sales

okay okay let me just remind myself like what I did okay yeah so the reason that I want to have um two cases is that it'll just make the model

more Dynamic and then also you don't want to just believe what management is going to tell you right like the company thinks here that or management is saying

we can get from 13.6% evit margins to 29.1% in 5 years and that's honestly pretty aggressive um and so what I want to do

is have like a more conservative case as well in order to uh see like okay if we don't believe management maybe we have our own case and see like what our IR

would be so what we're going to do here is build out our own projections a little bit so in our first year what we're going to do like you can see here

um previous year with 13.3% margins and then they say it's going to be around 13.6 so what they're basically saying is we're not going to see much improvement in year one we're going to see a lot more in the following years so to be a

little bit more conservative what I'm just going to do for our gross margin and ebit margin here is just make it the same as the previous

year and I'm going to indicate that I'm doing something like different which is what red color coding means and then for these following years we're going to um

build out a separate or we're going to project these out basically so I'm going to add something called um thissection for projections that's very common called

Step I don't actually don't know why it's called Step well it's called Step probably because it's like a you like step up a little bit but I don't know they could have called it anything

so I'm going to here I'm going to say that it's U because I pre I've done this I've done this in advance so what I do here is

1.75% 2.25% I believe or no 2.2% and then 0.5 0.5% so what I'm going to do is basically just add to our gross margin

and ebit margin here you'll see why I chose this number these numbers in a bit so I'm anchoring here so each basically this is the our gross margins

are going to improve by 1.75% each year and then our ebit margin is going to increase or sorry this should be Opex

margin our Opex margin so this should actually be this oops that's wrong this should be

this so these are going adding adding these two together will get us to our EV margin that we're projecting right so um okay I also did this wrong our gross

margin should be increasing over time while our opics margin basically should be decreasing over time because it should be improving so our Opex margin is

decreasing over time to 13.4% and then our uh gross margin is improving over time okay

so what we want to do in our conservative case we're going to need a switch here oops and what we're going to we're going to use the circuit breaker thing a little

bit later um but what we want to do here is ADD uh go to data so alt a a alt avv

we're going to add a list and then we're going to call this on or off so B this basically lets us go

either on or off oops I think I did this wrong um I don't need parentheses let's alt avv okay

wait list oh um and we're going to use this later but since we're building out our switches now we'll we'll just have it

and then for our cases we also only want we want a list here and we only want one or two and then what I'm going to do here

is I'm going to say I'm going to have ADD if statement so if this case equals one then we're going to have uh conservative case this is just

for us to know what is being used otherwise we're going to have it say management so this will always help us know what case we're on essentially so

you can see here Stitch to one or two and then I'm going to name this cell uh

I'll name it case and I'm going to name this one Circ okay so let's start building out all of this

stuff first so um okay let me remember how I did this part actually okay yeah

all right so let me start off with like the basic stuff and then we'll build it may well I guess we'll make it a little bit more uh complicated I'll add all the other stuff a little bit in a a little

bit later so um gross margin basically if we were to not worry about anything else then basically what we would do is just copy paste this across right

oh I also did this wrong so top so this should be up here ebit we calculate

okay so we copy oops copy this okay so then if we weren't worried about anything else we would basically back into all these

figures [Music] um okay and this is going to be so that's going to be that times

Revenue this is going to be Opex times Revenue as well and then we can back into our cogs figure right gross profit minus our oops

Revenue minus our gross profit and then you can see here okay okay so it's important to always like after you build out your projections look at the trend so it's around 62.7

64.5 and this goes down to 57.5% for cogs and you want that to be decreasing over time means you're becoming more efficient gross profit you want to see it increasing over time so

okay we're assuming it grows up to 42.5% when it was historically around 35 to 37% Opex you want it you're seeing it decrease to 13.4% which is really

aggressive and I'm going to explain soon why I decided to make two cases as I was like prepping this model so if you see here management

assumes that we're getting to this 29.1% right so in our conservative case that we built out here this is our revenue or

sorry gross profit minus our Opex and so this is copy paste this so this kind of tracks to our

management case right so 13.3 a little bit more conservative year 2 17.3 year 3 21. 21% 25.2 and basically the reason

21. 21% 25.2 and basically the reason that I earlier chose these numbers here is because I wanted to eventually end up to this

29.1% which I thought was pretty Prett aggressive for an event margin for like this food company but like in these in these four years before it's lower than

management right so we're clearly being more conservative and when you're modeling you always want to be more conservative generally speaking because usually tons of things go wrong and so

you want to make sure that you're giving yourself cushion and I thought that I'd be very surprised if Burger King actually was able to reach 29% evit margins

and that's because um that's like that's really really high and so um I mean if any company could do it maybe a private Equity Firm could but as I was building

this out I thought okay these management figures are too optimistic I want to have a conservative case and so that's why I I built

this so I'm going to use a choose function so if if um case equals wait no choose

case and then either this one or this one so what I you can you probably saw earlier I named like by you can name any cell by clicking on the top left here

and then uh naming it something so this just makes it so that I don't have to scroll all the way back up here and then like link to this I can just use that naming instead

so this this choose function basically is like okay if you if the case is one choose this first one if if it's at two then choose this second one so right now it's at

342 but if I switch this to management case it's 351 right and then percent of sales we can copy paste

here but there's a little bit of a complication right so if so we right now everything is on the management

case right everything's on the management case but the Opex and gross profit cogs these are still on

the conservative case because if we this this is being linked here it's not so if we were to actually look at the figures here so um gross profit minus our Opex

that gets us to 342 so this is very very misleading and it's not what you want in your model

right you want whenever a case is turned on or off for all of the relevant figures to be to be uh to be updated and so if someone if you were to Output this

or something and and send it in like a PowerPoint deck then your md or Ed or VP when they're looking at it and reviewing they're going to check to see if the numbers make sense and they're going to

be like hey like why did you output this output this like Financial table and the numbers like don't actually add up to

what ebit is right so you want to kind of uh build like build like a something that's like um will prevent something like that from happening right so

Revenue we don't need we don't need to worry about but basically what I'm going to do here is if the case is set at two then

I'm just going to have this you know placeholder thing to really say like hey don't like these are these cells aren't being used what the heck

oh and then we're also going to do the same thing here so if Case equals 2 then don't use this basically is what I'm

saying all right so right now since the case is at two we don't want any of these to be showing and we're going to do that basically that the same thing for all of

these so if if Case equals two then show nothing I can copy this um in this one

if if Case equals 2 show nothing and I'm alt ESF formula copy pasting it across same with this one if

case if the case equals two then show nothing who oh yeah a little bit tedious

but not much else you can do um show nothing all right all right so now this is good in that when the case is at two these should show nothing right this

basically indicates okay we are not using these figures here but there's this issue here where okay if it cases at two you don't want to see a bunch of Val value um errors in your model right you

don't want that either so right now what this is doing is pulling from the second option here because case is set at two but when we switch to our conservative

case then you want everything to repopulate right and as you just saw it when I switch from case to case there's

like this shows as an error and you never want that to be showing in a model so what we are going to do here is add something called sticky ifs and so these

are what I call like sticky if statements and um I remember when I was learning this uh I thought it was like I feel like it's not it's actually not that

complicated at all really nothing in Excel is but it is a little bit more of an advanced kind of like formula or like whatever

um and you'll see what it is so basically what we want to do is ultimately what we're doing is with sticki ifs is we want these numbers to

stay here even if we change the case so that there is no uh value error right and so you'll

see how this works so basically it's if if Case equals one right so if the case is on uh conservative case then we want

what we have here right simple as that but if not then we're adding an another if statement in here in here so if Case

equals 2 then what you want is uh the actual cell here and then if

not then this [Music] same oh this same formula all

right so beforehand you saw that if I changed the case it added a a value error right so if I change the

management now this number actually stays at 342 right where even though all these numbers are now gone and what this is

actually doing right now if you look at it it's actually taking from two blank numbers and but it's keeping it as the numbers we had previously so this is to kind of go over

this what this is basically saying like how first you have to understand how if statements work which is that it's very simple it's if this scenario is happening then give me this if not then

give me this right it's a lot simpler when you look at like this one if cases two if it's a management case then give me this blank if not then give me the uh

cogs margin in this scenario when it comes to the nest I think it's actually also call this nested if statement but then I just we also I'll just always just call it sticky ifs so if Case

equals 1 so if it's on conservative case then just give me um G62 minus 65 which is just give me ebit um give me gross

profit minus op Opex if not then when case is two when it's at management give me what this number was

originally which is at this 342 basically if not just give me like this number so so it's you know there's kind of layers to this in a sense but

essentially what it does is just it keeps this number the same even if you change the um you change the case so hopefully that

makes sense like it takes a little bit of time to kind of get used to but at the end of the day it's just like a simple formula and um but you know when I first learned it I thought it was kind of cool

even though it's kind of just a formula so that's um that's your ebit and then now we need to calculate our the other stuff we have here I also

realize it's a little bit OCD but didn't italicize these margins part our interest expense and income you know we're building out an lbo so

obviously this comes later and um I'll highlight it for now this is a huge part of the whole lbo earnings before taxes this

is going to be our ebit minus our interest expense plus our interest income percent of sales here and then taxes

Let's uh let's assume that the company is able to so I have like minus 0.5% here so I'm just going to assume that it's able to find some ways to decrease his taxes

a little bit over time basically and I did also double check this to see and I remember reading that Burger King actually was able to decrease his tax weight by quite a lot even more than

this but I'm just being conservative so um I'm going to add that there and then

we can refer to the taxes and then we can calculate our net income so earnings before taxes minus

taxes this is our earnings before taxes times our tax rate and then now we can copy paste this across okay so you can also see you know

as like a quick sense check you want to be seeing like okay what is our what did the margins look like over time so 9% increasing to

19.8% net income so clearly the company hopefully or hopes to become much more profitable over

time so this rounds out our financials I mean there's more that we have to do but uh this is a bulk of the financial

section so let me pause here and now see if there are any questions in the chat um can you build a three Financial three I guessing I'm guessing you're me asking three statement fin financial

model and that's going to be actually um as I talked about some of you guys might have joined later but this is part one of the advanced lbo later on we're going to forecast cash flow statement balance

sheet and do all this extra stuff probably going to take me two videos so I'm going to split it up into Parts two and three but you know later on probably

since if you joined in the middle of all of this you may want to um start from the beginning or like use this and we're going to build off what we built here in

those future models basically work at Middle Market Bank do or do a master's in finance at LSC to get into bulge

bracket uh really hard to say just blanket statement but I think if your goal is banking and

at LSC it's a very good pipeline then maybe do that if on the other hand you can join a middle Market Bank and Lal then you know you get actual experience

and you're able to lateral into bank so that could be an option like honestly it's it's really tough to say what's better or worse um if you want to just keep making

money though you should do the M you could do you do the lateral thing best degree to get into IB just anything business related it could be

economics math is fine even CS is fine um why do you use a Che if in a sticky if formula couldn't work just on if

condition so there's no space to not add another if like if I if I were to so you see these commas here this

basically just adds uh another if statement into all of this and you can add this extra scenario yeah if you were like I guess what

you're might be asking is like okay if case one equals and do this if not then take this yeah it turns to zero yeah I don't

think this works conservative and then management oh did that work oh you know actually you're right

yeah my bad so this makes things simpler um so so actually I guess what you can do is just I add I I basically made it more

complicated you can just refer back to this own cell and then you don't need to add all that other stuff um so actually bibs thanks for calling that

out it does work if you just refer back to the cell that you're in so right now we're in case one and even if we change a case two the

number should stay the same which it does so versus this you always want to make things simpler if you want if you can especially as your models get like super super

big um having sticky if statements actually slows down your model if you have like tons of tabs and like all that stuff so uh yeah good good catch on that one all right I'll answer other

questions later because there is actually a ton left to do and um yeah there's just a ton left to do okay so net income I'm going to

make this basically you always want to make the like the kind of important parts um kind of stand

out um so we got to our net income another thing I want to show you guys is something called uh Etha

reconciliation Okay so let's go [Music] to let's go to Burger King and then

let's go to it's going to be kind of hard to find oh maybe it was no I'm going to the time when basically

the way I found this was okay we we're using this 10K which was announced on March 23rd and then so they probably use

announced a press release at one of these times which seems to be the second one oh double check your ebit you copy paste it

incorrectly oh oh actually I guess you can't use that yeah okay so you do have to do the thing I

said um why is it doing that I guess or maybe I need to oh maybe I just need to refresh

it so I mean it is here now okay so now it works yeah so it is a little bit buggy right like you do run into these kinds

of issues so you just want to I guess basically what happened is sticky ifs what they do is like they'll calculate it once beforehand it was at zero because I changed the formula and

then it was basically subtracting 0 minus 0 so it was like stuck on zero but then once I changed the cases it recalculated it and then became sticky if that makes sense so that's what

happened here thanks for uh the Zacharia thanks for calling that out okay so Etha reconciliation what is this so every

company okay this is not what I'm looking for what the heck where is that 10K filing

um this is why it's usually great to have all these showing what all this is saying March 23rd

2011 should be one of these okay we'll go back to 2010 oh I think I looked at 2010 one okay so August

26 should be one of these ah here it is okay so every company um IA is what people use all the time but it's actually a non-gaap figure

it doesn't show up on a financial statement or shouldn't maybe it's referred to but companies always talk about like

adjusted ethon and whatnot because they make changes to there are always like little things that happen that the company says he you should be adding this back to IIT

basically and um let's so and then when when you go to a company's a uh press release for their earnings they always kind of give you what they believe IAS

should B so let's find that yeah just here all right

so thought they had it for three years but I guess not um either way I just want to show you what it is and we're not going to worry about in sluse it too much anyway so what we're going to do

here is get our ebit DNA um

adjustments that gets us to our iita so we're still in 2008 here so we're gonna go [Music] to

354 DNA um something we're going to need to grab from the cash flow statement DNA here copy

these so 20 okay yeah and then adjustments this is what I'm just basically trying to show you guys usually it's a lot more more there's a lot more but

[Music] for for Burger King for whatever reason they only have like these two adjustments a lot of other companies will have like stock based comp and then like all these acquisition stuff and

it'll be like a lot of numbers and it'll make your Etha a lot bigger which is when it's kind of useful but in this scenario it's like they're just adding 1.5 and two so in 2009 they're saying

add 1.5 plus two back for our other figures here we're just going to have zero um and I'm going to add a note here this is [Music] in filing

here um and then this gets us to our IA right so you add these back so normally if you were just looking at Gap all I sorry it's not Gap e ibit because there

is there is no such thing but if you're just normal EIT then uh it'd be four less right and then as I said usually these adjustments are

a lot more and you want to account for them as you are building out your uh your IA for the next for your

projections right and that that's why it's really important but for this specific lbo it's not important um let's also add here

percentage of sales which we can add here and the great thing about doing this now is now we have our IA our last 12 months IA so we

can go back up here if you forgot we do still have a lot more to like a little bit more to like add and change

here so LTM IA this is not our Pure LTM iita right this is

2010 as of June 30th 20 uh 2010 and so ideally you actually want the last 12 months like literally the last 12 months of financials which a private Equity

Firm may have something closer to but in our scenario we only have the public information that's available right so the closest we can get to is the latest

filing that is provided so we we are going to treat this as our last 12 months EA and a real private Equity Firm will would actually just like the once you're

actually in a transaction the company will give you all the numbers you need it's in their best interest to do so so LTM ubit is here as well so now we can calculate what the entry multiple was

which is our Enterprise Value divided my our LTM ebit and I'm going to Cal uh format this yeah I like you see here like this

extra little spacing I like having everything just the same um okay so 8.8x you know it's pretty interesting to see okay this is

what the company entered the um transaction at that multiple and then you can we can assume that the company

is going to just enter at the same multiple you know obviously that's something you might want to change later but normally a good conservative assumption is that

you're going to e exit at the same multiple and the reason for that is because you don't want to assume multiples always change depending on the market and so you don't want to make a

assumption that it's going to get higher right like you can't that's out of your control okay so now we have all this information filled out here we can

also finish out our sources and uses table too all right so percent of EOD this is this div divided by our LTM EA just a bunch of helpful information

to know and then percent Capital we didn't fill this out either which is the amount and then divided by the um whatever you're looking at

okay we want the same [Music] thing here and then percent Capital we want to move this

there yes this will be posted all my all the streams are always posted okay so you always want your percent

Capital to equal 100% because that's 100% of your capital and then you can see the EA figures here um it's

interesting for the debt um you can see what um how how many turns of EA basically that the transaction was

funded with and I was always instructed at JPM that you never want to go above six um I don't know if that depends too much it might be it might be different

now SL so dependent on the industry but interestingly here you know they're right at around 6X IA um although the Ia figure that they use might be a little bit different but we're in the right

ballpark is basically what it seems like to me all right so now that we have everything up there figured

out what we have left to do is figure out what what our cash flow items are and then also our well we have to figure out our we have to calculate

our ebit projections and stuff but then we have to figure out our cash flow items then we can build out our debt schedule and then uh that's pretty much the the whole

thing but it looks like um maybe another like hour or so that we have left here so EA conservative so and this is percent of

sales management of sales all right so this again is going to be dependent on the case that's being

used um okay so for the first for historicals we don't really have to worry about it it's just going to be you know what's up here

all right um oh this also needs to be purple purple by the way is when you're

referring to a cell in the same sheet okay and then so for conservative um let's see what I did here

here all right so uh as we went through earlier on there is IA that's provided by the

company so I wanted to basically when the company when we're on our management case we want to show these

figures or use these figures whereas if it's um the other the conservative case we we're not using using these figures so this is

management these are hard codes this is in the pre m14a page AG

60 okay and then percent of sales you can again copy these are our eitaa margins right um conservative case what I think

we're going to need to do is actually build out our uh cash flow items first before actually being able to calculate what

this would be we need our we need our DNA right so what we're going to do is build out a new section now called Cash Flow

items and what we're going to need um are three three things which is our uh networking capital and

then our DNA and our capex right and we're going to do each of these a little bit differently um I know we are kind of

like right now doing IA but I'm going to start with our networking capital and then do our DNA and capex so if we go to we're going to need these historicals

again so if we go to our 10K they the company gives us our uh everything we need right

so um these are the current operating assets and then the current operating liabilities so we can actually

just copy paste these I don't know how it's going to look actually if I copy paste all everything at once I look might get

messy uh it's not too bad okay so this was oh the numbers are these are for

20110 these are for 20 2009 and these are for 2008 and we don't want any of this stuff

all right I see some of you guys are still here let me know if any of you guys have been here since the very beginning curious to know all right um because this is even

taking me longer than I thought it would take me and I can assume that this is kind of dry for the average person but if you're

interested in finance maybe it's not that dry right um okay so these are trade and uh these are like accounts receivable these are prepaid and current assets so these are current operating

assets and these are current operating liabilities and we need these to calculate our networking Capital so these figures here they're

also for me I find this stuff a little bit confusing so I actually have these notes that I wanted to um that I'm also going to add in this model when you download it you're going

to be able to see on the side here so and this could easily be like an interview question but negatives on the cash flow statement for assets mean that

it's an outflow of cash you've been spending money and that's why in here when you see negative 15.9 it means you're spending cash

right positive for assets means it's an inflow of cash meaning that you have gained money and then a negative on cash flow statement for

liabilities means an inflow of cash meaning like if you increase your accounts payable that means you got cash and need to pay it right or negative

sorry yeah negative means that and positive on cash flow for liabilities means an outflow of cash so what does this mean here so we have all the numbers as positives here

because I copy pasted it but this means that for the numbers here nine this negative outflow um I'm going to because we're

going to be doing our actually let me just like do this part first so the way we're going to be calculating this is equals the sign of

your current assets minus the sum of your current operating liabilities and then again percentage of

sales is what we're going to use to forecast okay so in terms of the

signs um when it's an outflow of when it as we let's refer to this negative actually actually basically

what you need to do is kind of um let's think here flip what we need to do eventually is flip the signs for the assets because

basically this implies like an an increase of current assets right because cash has gone out you're spending more on current assets so that's why the negatives need

to actually be positives in terms of the way I uh but this needs to be negative in terms of the way I'm calculating

things oh god um these are all for 14.9 this needs to be

negative and then for liabilities 20.8 this positive positive payable this is Nega negative and then for crude

advertising this is negative other acur liabilities these are all negative so this gets us

to negative 1.3% 2.3 3 and 2.2% and I'm just going to make a simplifying assumption and average these so in the future model that we build uh

for for Burger King when things get more complicated and stuff I'm going to be actually projecting out um

your all these like cash flow items but for this one we're actually going to be um just using the simplifying assumption

oops okay so multiply this by sales and now we have our first cash flow item that we need and we just need two

more all right so that's done okay now we need DNA um percentage of

sales uh DNA conservative DNA management all right so the reason that

we need this this is because we have we can back into our DNA here right and we'll do that here so

management um is our IA minus our management

ebit conservative case this is for for this we want to actually just project what r d would be and

so um I'm going to copy paste this so we already we already got it earlier so we don't need to do it again but I'm actually going to link down to

this just like preference um okay so then we can get percent of sales here so for our conservative case what

we want to do actually we can just make this part of [Music] historicals we don't really need that here um what we're going to do for our

conservative case is just use a threeyear average you know very simple and then hold it steady over time since it's not really changing that much

anyway all right and then so this is this time our sales figure all right so that's our DNA here and then what we want to do with

our DNA is basically just um use a choose function so if Case equals one

oops chose use case and then either this one or this one depending on what is needed or what what you what

you choose um okay so then this okay so we got our DNA now that we have our DNA and the reason I have conservative and

management is that for you know if you've been following along I guess management we are given IA we are given ebit in the proxy right given and so we have like a

management case of what ia and ebit should be in our conservative case we're calculating this on our own right we made our own projections and this is a conservative case we want to compare

against to see what our irrs are when we have our more conservative case and we have to calculate our own IA so that's

why we have both and we can get our DNA now here for the conservative case make this

purple and then this as a percentage of sales oh oops this needs to get our DNA plus our ebit all right so that's our conservative case

IA yep um looks good to me and then here again equals um choose case this one or this one

all right so we have our EIT projections super important formatting looks a little different here all

right so we have our I okay so now um I realize I've been looking at this computer screen for like two and a half hours straight I should probably

take water break all right s these um formatting looks a little off okay so we have our

DNA capex is going to be really easy we're just going to pull from our 10K which is our payments for property and

Equipment these are negative numbers but for me I like to keep everything positive as I

mentioned and then we're just going to use um historicals to project this one out so this is percentage of sales we're going to be doing

an average multiply this times our sales all right as I'm doing all this like kind of basic stuff um quick little

plug I announced my um streams on my Instagram so feel free to check that out and I've been posting a lot of Instagram reals and Tik toks

um which you can also check out all right so we have our cash flow items now what we can do is calculate

our leverage free cash flow and then get to to our debt schedule yeah so now I think we have about like 30 minutes left before we can finally get to our

IR so let's call this um lever free cash flow and then net income plus your

DNA uh minus your cap x minus your change in networking capital and then mandatory debt

repayments like this is something that we are going to add to our next model but for this one we're not going to so net income can scroll all the way

up here then plus oops we don't need to do that so net income is there DNA

here capex here changeing networking Capital here mandatory debt repayments I'm going to put a zero for

now so this is a hard code these are referencing a cell that we have and that's pretty simple right we

just that's all we needed to do to calculate our leverage free cash flow all that work leading to

this plus DNA minus capex minus change of network and capital minus on either minus any mandatory debt repayments which we were not are not going to do in

this model so that's it for Lever free cash flow pretty simple and then we're going to need to build out our debt schedule all right I'm going to add X's here also

so that's easier to navigate okay this is De schedule we're going to calculate our Debt Pay down first and then we're going

to calculate our interest all right so something that's a little bit different than the other lbo um stream that I did earlier last a

month ago or so was we didn't really calculate our cash at all um so that's something we're going to do in this model so we're going to need beginning

balance and then inflow versus outflow and then ending balance oops um we're also going to want to get our three tranches of

debt so we have the USD trunch Euro trch and Senior notes next model we build for this lbo we're going to also have a revolver but we're not going to do it for this

one getting balance pay down ending balance that's all we need here for these

okay so our let's build each of these out so that we're not going to have to worry about 2008 to 2009 and for 2010 all we need are the beginning balances

so for this cash that is the minimum cash so once the transaction is done um this minimum cash let's just say is what we need like you

can it's true that this cash balance that you're using like may not be able to get any interest the reason we're calculating this is to calculate our interest income because when a company

has cash they don't just have it in the bank they usually try to receive some income with it and so they'll get like 0.5% um of interest income so that's why

we we have this figure here um we're and the minimum cash balance You could argue like oh you're probably using it and you don't have all

of that money but for simplification perose is we're just going to assume that um that is the ending balance for the

year okay so now we have our ending balance our beginning balance of our first year of projections we can do this for

[Music] our three here and then um your interest in your cash um this is going

to if we were to have built out the three statements um then we could kind of add uh like the inflow outflow here and

everything but since we um since we don't have that for this model I'm just going to make this zero

all the cash is going to be used to pay down debt anyway for for this model and so the ending balance is just going to be

the uh in beginning balance um plus any inflows SL outflows so we can just copy paste this across and this is going to be pretty

simple we'll we'll use this later now this is uh where where we start paying down our debt right and so our lever free cash

flow um this is what we're using to pay down any debt and this is called this is often called a debt waterfall because

when you have the cash that you have generated you first pay off as much as you can of your first tranch and then what's left over your second and then what's that left over with your cash

your third our scenario like we don't have like that much cash so all of this is going to be really honestly paid to just this first trunch of that but you

still want to build the model as to be like pretty Dynamic so that um it's still using uh it can if you have like a a lot of cash or something it's it's

using for all of these uh different tranches and later on we're going to build out something called a cash sweep and basically what it's going this model is going to be able to do do later

on is choose like okay I want to pay like 50% of this debt tranch and then 50% of this one with my cash and Etc but in this scenario we're just going to start

um paying off this first one so what we want to do here um is basically choose let's see the

minimum of either our lever free cash flow or our beginning balance so that's 135 right so the ending Bal ending balance here is our

beginning balance minus our pay down right so that's how much cash we have left and then this and then the formula pretty much is the same

throughout right so you can see here 205 million in cash it's used to pay down then 257 then 294 then 333 we end up with 287

left and then we want to do something similar for our next tranches right so equals Min of either the of either your

leverage free cash flow or sorry either the minimum of um beginning balance

or I think it's your be oh man always trips me up a little bit 138 all

right so either this or the cash you have minus your pay down yeah this should be zero

because basically what this is saying is you want to pay down your debt either with your cash that you have or sorry you either want the

minimum of the what's in your uh debt tranch or the amount of cash you have left after you pay down your first tranch of debt right so that's zero in this case

because this minus the you you pay down everything so that's zero so that's smaller than this 334 so your ending balance is your

beginning balance minus your um ending balance or sorry your uh beginning balance minus your pay down I'm starting to get

tired okay uh but we're almost done and then Senior notes similar thing is either the Min

of you uh this beginning balance or your labatory cash flow minus your pay

down cool so basically this shows that you're not paying down debt for anything else besides this first tranch for the entire five years

and yeah that's just how it is here and then now we can calculate our total debt um getting balance these are just simply you just

need to add these three together for everything beginning balance pay down and then so this is this is our total

debt okay and then I guess we can add a check here so you want so this we added everything

but we can also check to see if this minus this equals this and this should always say true and for whatever reason we always like to have our check be

red all right now almost there guys calculate our interest this is where

things um get a little bit interesting in my opinion so in for

interest there are few things we need to uh do here I'm just going to write all of this stuff out first okay so we have interest income rate

then interest [Music] rate okay so what's interesting here is um we have this thing called circuit

breaker if you remember way earlier I named this Circ and I'll explain what this does as I build out everything uh and we're going to need to

include it in our in the formula so interest income rate we can back into this actually by getting

our oh we need to calculate this actually I believe um let me see what I did right so what we our interest income

[Music] actually calculate this okay so this is a bit of a simplifying assumption um so actually we're going to

call this interest [Music] income wait interest on cash I guess is what we're going to call it

so we go all the way up to our model here we remember we had interest income so we're going to connect to that

this is might be a little bit confusing but this is this is cash but we're doing interest income of cash so that there's that and then if we

get this and divide what did I divide by okay in this case I guess we can just take it from our ending

balance in 2010 this is a little seems wrong oh okay I see what I did wrong so actually something I need to fix

here um our ending cash balance here is actually what we ended up with our the cash we ended with right um this is something that we calculated earlier

once the transaction happens this is actually not the ending balance we need we need to change this to our minimum cach so that's something a little bit

different now this makes sense to me yeah 0.5% is what we needed so usually interest income rate is around 0.5% of your cash and then

so make this purple um and then what we're going to so we're going

to introduce circularity in our model which basically um if you know anything about lbos is something very should be

very familiar but your interest expense and income up here affects your net income which affects your levered free

cash flow right because your net income's here which affect fects your debt pay down which affects your interest rate right because

the amount of debt you pay affects how much interest you have right so um normally speaking like what you would do here is you get your average of

your beginning balance and ending balance just because the the actual interest that you earn or pay differs month by month or whatever your term

like whatever it is is but as a simplifying assumption what you do when you're modeling is just take an average of the beginning and ending balance and then you multiply that by your rate and

that becomes the interest income or expense right but because of this circularity once we I'll show you how

how that unfolds later we want to add something called a circuit breaker so that [Music]

um that switch I have up there so if it equals off then let's see then you want it to just

equal this G 171 times the average so if it's off you want to get basically just multiply whatever if not then you want it to be

zero and you'll see why later here um right now so let's turn this on and off so it's off so this should calculate

to about one and don't worry too much right now about what it is I'll explain it in like 5 10 minutes let's just calculate everything else so far so let's get the interest rate

for the other two other three tranches of that all right so we have our three

tranches going to make these anchored and then we're going to do the same thing so equals if Circ equals

off then uh interest rate times the average of your beginning balance of this first

trunch of debt and your ending balance otherwise zero so this is your interest

expense see if I how's it fastest way I can do this um actually I don't think there's a

fastest way so again if Circ equals off then uh your interest rate times the average of your beginning and ending

balance otherwise give me zero last but not least okay if Circ equals

off give me interest rate times average of beginning and ending

balance otherwise zero okay so then we have our total we can calculate our total interest expense and then we can get our Blended

interest rate as well so the total interest expense you just add these actually a ton of Interest Blended interest rate is your total interest

expense divided by your uh ending balance I guess uh it's a little different maybe you should do it

an average of your beginning and ending actually same thing though pretty much so that is our interest and um this is

pretty much like the most important part in a sense um so what we now can do is go back all the way up to

our income statement here our operating model and now add in our interest expense

here and then also our interest income right and then that's pretty much our lbo um let's reformat this

though okay so let me explain the whole circuit breaker thing but let me also just check what our leverage fre cash flow is yeah so it's actually negative

in the first year and then goes up over time oh this actually also this is not good your pay down should never be a

negative figure um I need to change this formula so our cash flow is negative so

basically we should be paying zero I didn't expect cash flow to be negative and have this issue so I guess I'll just so

if cash flow is z is less than zero I'll just have it be zero otherwise do the Min function and I need to add this for all of them

I don't think this is the all right so this isn't a reason why you have the circuit breaker thing so this is actually a good example A lot of times because of the circularity in the

model if if you have if you don't have the circuit breaker thing then you will get this error and then you'll be like oh like what do I do right but since we have this circuit breaker

what's it what it does is it breaks the circularity and then um it turns it back on so now you can see all the numbers are working right uh I'll explain that a little bit

further after I fix this thing this last thing but equals if this is less than zero then use that Minun

function oops make zero otherwise use this oops okay so now we built out

everything uh let me just explain that circuit breaker thing one more time so you have all these like interest it line items and there's circularity in your model right I'll just write it down

because I know this is really confusing when you're first starting but your interest expense your interest in general including your interest expense in uh income this affects your net

income which affects your leverage free cash flow which affects your ability the amount of debt you pay down which

affects your interest rate or interest expense or income all right so interest affects your net income affects your lever fre cash flow affects your

day pay down Debt Pay down and if you have less debt then you pay less interest right so that affects your interest which affects your net income which affects blah blah blah so it's like a circular

reference like that and then um so one thing in your model that you oh I should save I haven't saved so one thing that you should need to turn on in your model

if you go to um for me alt ft then if you go down enable iterative calculation this allows for you to uh

have this circularity what what basically I don't really know how Excel does this kind of but basically what it will do is like when you have that circularity it'll calculate everything

literally a 100 times until it gets like to a asymptotic type of number where it's like very very close to that number or something I think that's kind of how it

works so um later right like as you're if you don't have this circuit breaker let's say I I break the model right and then I'm like doing stuff like then if I

try to it like oh no like I messed this up I need to change it it still gives me this error right but if I um turn this

on I turned on the circularity thing right it's like everything's fixed and then I just turn it back

off so that's that's what that is um but now let's get to our IR

calculation and then we're done all right so LTM EIT at exit exit

multiple Enterprise Value net debt sponsor Equity value sponsor

Equity at entry and then myc which is money over invested capital and

irr so this is just all references right so LTM EIT at Exit um we can go all the way to the top and

then get to our IA exit multiple also at the top right this is our assumption here H why is this so

high okay I did something wrong here oh is this right oh okay never mind this is right they just I guess entered at an 11 point I I guess I just thought it was lower but this

should be right 11.7 times uh is there entry multiple so that's also their exit multiple Enterprise Value is your LTM

ebit at Exit times your exit multiple net debt is your total debt ending balance minus your

cash um and I guess with the minimum cash balance you can argue maybe you don't take this the cash out but whatever um and Enterprise Value minus

your well let's see normally if you have a if you have if you project out your cash flow statement and your balance sheet then you will have a cash balance

to subtract from like for us we'll just we'll just make a simplifying assumption just take out that minimum cash um that gets us to our sponsor Equity value and then so our

sponsor Equity value at entry is down here in our sources and uses at 1.56 billion so money over invested Capital

um this seems Hold Up Wait I might have done something wrong yeah one sec oh yeah I knew this this this this is

going to our oh something's wrong with this model this should be 400 something okay I know what I did wrong so I

uh I did not add something correctly here but this DNA should flow to the historical DNA

yeah 445 so then this should be 445 and 8.8x okay that makes more sense because yeah m is about

2.6x um so that is the money over invested Capital so that's pretty much like okay so this is how much

the uh p uh PE Firm made they made 2.6 extra money and then the IR you get this to the

power of 1 / 5us 1 and that gets us to a 20 21% irrops ah so I mean this is what you're

really building towards right as an as a um private Equity Firm like associate or whatever as you're building out these models like it's really for you to

eventually get to your irr so always a great feeling to get to that final figure

and also to see it as something that is um kind of believable and makes sense right because we the whole point of this whole model like from start to finish

was to look at a real lbo and try to see okay what did the sponsor think they could achieve and what were their assumptions and all of that to get to this irr right and if

this irr was like 40 something perc or like 5% then you know that some of the things you were assuming were wrong but if it's around like 15 to 25% then it's like okay you built this pretty pretty

well so uh the only last thing I want to do here I like to have like this reference

here um so I like to have this because then as you change your different assumptions which is not too much in our scenario

but you can see okay let's if we change it to our conservative case Okay irr is 19% if uh we make it to our management case then it's

21% what if I change the Share value to $26 right like if I pay more for the company as a private Equity Firm as then you know they do all of this analysis

before they actually give the offer right and the company and up paying $24 per share but when they're building this model they'll probably like okay what if

I pay $30 a share oh my R is 17.4% okay what if I pay uh $26 per share 24 right so that's just helpful to have this

reference on the top right and then you can also like assume okay what if I change my exit multiple right normally you don't want to do that because that's you want to stay conservative but um yeah that's why I

like to have that up there and that you know wraps up this model and just to give you a reminder this was part one of this model that I'm going to keep building out videos coming later include

all this stuff and you're going to also be able to download this model for free um once the Link's ready I will add it to this video's description and in theend

comment and um there's other stuff you can check out my upcoming Investment Banking course my Discord Channel which I'll add a link to soon um other videos

blah blah blah check me out on Instagram and Tik Tok if you'd like and let me before I leave see if you have guys have any

questions how do you know which parts of the annual report to focus on for forecasting items it depends on what you're doing but if you are just doing like a DCF or

something or an lbo you just focus on the basic stuff like you know what we went through here is a lot of what you really need for an lbo but if you're going to forecast your

three financial statements then you're going to need to focus on like every line item kind of almost one question would you say that business degree is a good choice or CS

uh yeah I think that really depends on what you're interested in but CS I think would be like a great choice because it gives you the flexibility to pursue other things in the future and

you can still get into banking if you want you just have to like be able to get good grades because I know CS majors are pretty pretty tough or the classes can be but with that said you know

there's we're going to build upon what we worked on today in future uh videos and yeah things are going to get much more complicated and I

know a lot of you guys have been asking about like can we can you build out like other tutorials and other stuff and once we finish all of this the model is going

to be super Rob robust and if you know how to do everything that I went through today and then also these future um videos you're going to be if you are like you know interested in

finance you'll be very well prepared and I wish I learned all this stuff before I started banking but um I didn't have you

know this channel I guess I don't know anyway thanks guys for uh watching and um hope to catch you guys in the next stream