Excellent tutorial, thanks a lot. I definitely need to dig a bit deeper into these mgt options' and optional repayments' formulas - these aren't easy to grasp when I try to do the case by myself :) Cheers from France
Your video is really helpful! A little question. In case study question 3, why should we implement a dividend recap and cash dividend distribution before Year 5 so as to boost returns? Equity value = Enterprise Value + Cash - Debt, so the two methods I mentioned will decrease equity value (and investor returns), I guess?
A dividend recap will result in an early cash distribution before the final exit in Year 5. The total amount received by the PE firm won't necessarily change by much, but the time value of money means that the IRR will increase due to the earlier distribution.
Why is the CapEx (factory maintenance expense) based on last year's number of factories and not the number of factories we have this year? Thank you very much
Maintenance CapEx relates to existing factories that have been operating for at least a year. New factories constructed in the current year do not yet need maintenance.
Why don't we integrate the 2% ($24m) advisory & management fees? I assumed that I should at least subtract that figure our return calculation (from the Equity Value). Thank you very much!
The Investor Equity is higher due to these fees, so it is reflected in the model. Delete the fees, and the Investor Equity in D20 goes down, so the IRR and multiple increase.
Your videos are awesome and I’m learning a lot. Also, I’m planning to do a quite short thesis on an LBO case. Do you have any suggestions on what could be/ has historically been a relatively simple LBO I could talk about?
Thanks. I don't really look at recent LBOs unless it's part of a new course or something similar, and we just re-did all our LBO models last year, so I don't have a specific recommendation. But try simple companies in standard industries (industrials, retail, consumer, etc.) first.
With this method the min cash can go below 5% of sales... how should it be handled? If free cash flow or Net income decreases the ending cash will go below the required 5%. How should it be handled
As mentioned in the other comment response, this would be handled in real life via a Revolver to draw on extra funds if the company falls below the Minimum Cash. There is no requirement to build one into this model, so it's not shown here.
Loved the content, though Im more just an excel user and not a financial analyst... I mean, I had to look up what LBO meant haha. I thought for sure you would mention it at some point. Also, it would be great if you showed the keyboard shortcuts on the screen as you record.... sometimes its hard to follow what you are doing since you are doing it on the keyboard and we cant see that. (and now I see you did it later in the video haha)
This tutorial assumes a certain level of prior knowledge because it's designed for people searching for the keyword "LBO modeling test." Anyone searching for that presumably knows what "LBO" means, what private equity is, etc. We tend not to focus on Excel shortcuts in financial modeling tutorials because pausing to state each shortcut slows down the pace. There are separate tutorials for Excel shortcuts and features here.
Hey, brilliant stuff. Could you explain why the "mandatory repayment" is min(term loan amortization, term loan)? Given that there is a cash sweep, should it not be If(fcf>min cash balance, term loan amortisation + 0.5*(fcf-min cash balance))?? Apologies if this is a stupid question but would appreciate any feedback on this
The cash sweep is the optional repayment part, so it's separate ("CF Used for Debt Repayment"). Mandatory repayments refer only to the fixed principal repayments that exist in each period as long as a sufficient loan balance exists. The MIN checks to make sure that the remaining balance is greater than the scheduled principal repayment.
Very helpful video, thank you. One question, for the sensitivity tables, I seem to always get 0 values or one repeating value. I checked the circular references, locked the reference cell properly, put on automatic calc mode.. Do you have suggestions what else could be an issue? I use mac, Excel 2016 version. Thanks!
It's either a problem with the table or a problem with your model links. Start by changing these assumptions in the model and seeing if the IRR changes. If it does not, it's an issue with how the assumptions are linked in your model. If it does, it's a problem with the table - try hard-coding each value in the left column and top row and delete and recreate the entire table with the correct row and column input values.
Hello, Brian. I noticed that you showed treasury method in videos when calculating dilution. Could you please make separate video about options and their implication in LBO/Valuation models? Thanks for this video!
There really isn't much to say about it. Options slightly affect a company's value in a transaction scenario, but in 99% of cases they are not a major driver. This would be like analyzing a book and focusing on the average amount of space between a period and the first letter of the next sentence...
Hi I've a doubt, while valuing the target for LBO using dcf , will v take post LBO values( where v use the new capital structure where new debt will be taken to fund the deal) or do we use the pre LBO values ( where v value the company with values as it is before the LBO is to be done)
A cash sweep does not create circular references. It's a simple linear calculation, and as long as we use the beginning cash and debt to calculate interest expense for use in the FCF calculation, no circular reference is created.
Hey Brian, absolutely love your videos. Is there any shortcut for making the indents in cells like you do, or do you just have to manually hit space. Also any chance you could explain how to move around the menu when I press CTRL 1 using only the keyboard.
Alt, H, 6 and Alt, H, 5 to insert and remove indents. We don't cover Excel shortcuts extensively in this channel (see the dedicated course), but there are several videos on how to set up the QAT in Excel and the more important shortcuts.
Hi, one thought, if the Free cash flow decreases year 1 to e.g., 10 (instead of 44) then we have Ending cash of 23 which is lower than the minimum cash. How can that be? From my understanding, we all the time need to have 5% min cash to sales in cash?
You normally handle this with a Revolver that allows for additional borrowing to meet the Min Cash, which is not included in this simple 60-minute example. You shouldn't really worry about edge cases and strange behavior in models in an extreme time-pressured case. This is more something to worry about if you have more time / need a more robust model that includes different scenarios.
Thanks for your video! When calculating the tax-deductible interest expense on the P&L, you included the entire interest expense (included the interest you didn't pay, like the PIK), not just the coupons you paid out to debt holders. Is this correct? Is the entire interest expense tax deductible?
For LBO model purposes, the entire interest expense, including both Cash and PIK Interest, is tax-deductible unless otherwise specified. There are some special cases where this is not true, and some regions limit the % of Interest that is tax-deductible, but in a 60-minute case study, you can't really think about these points. See the PIK Interest tutorial for more.
Debt principal repayments are never tax-deductible in any region or industry. They appear on the Cash Flow Statement and are not a tax deduction because they represent the simple repayment of borrowed capital, not the expenses of using that capital over time.
Hey, shouldn't you include the min-cash balance in the ending cash balance calculation? The way I see it, is that you need to keep it as a minimum for your work cap outflows/changes so it's always on the BS and should also earn cash interest? Thanks
The standard practice in LBO models is to include the entire remaining cash balance at the end, so we follow that here. If the company's cash balance is too low upon exit, the next acquirer will add funds to the deal to bring it up to the minimum level.
The Net Income that starts the FCF calculation already has a deduction for interest expense paid on the debt since it flows in from the Income Statement. So it is factored in, but in the line items above.
Hello guys I have a question concerning LBO analysis. Is it useful in an LBO to consider the degree of financial leverage to assess how much debt the acquirer can sustain?
There is no "acquirer" in an LBO, just a target company, because it continues to operate independently after acquisition by the PE firm. If you're asking about whether the target company's leverage pre-deal affects how much debt can be used in the deal, no, not really except in very minor ways (e.g., a company known to existing lenders might be able to raise debt from those lenders more easily).
Thank you. Two quick questions: Can you explain a little more why you do "Option % / (1 + Option %)"? Also, how would the layout of the returns analysis change if there was an equity roll? Would the % owned by the Mgmt. include the cash from their options? Or would it be multiplied against just the Exit Equity Value?
The additional shares issued create additional equity, but the initial option grant was based on the company's equity before the share count expanded, so the option holders receive a slightly lower percentage. We've covered equity rollovers in other examples if you look at some of the Sources & Uses videos.
@@financialmodeling Got it, that makes sense. Maybe to clarify my second question, let’s say the original Equity Ownership was 80% Sponsor / 20% Mgmt when accounting for the initial roll. When doing the returns analysis with a Mgmt. Option Pool, would you create another line, after normal exit equity value (row 94) called, for example, “Diluted Equity Value”, which includes the new cash from the exercise of the options and the subtracted equity to option holders, and then do the 80%/20% split from there? Or would the 80%/20% split be calculated on the normal exit equity value (row 94), and then we do all the options calculations? Essentially which comes first: options calculations or equity roll calculations? Apologies for the long-winded question thank you!
@@trebitda9443 So this is really beyond the scope of Q&A on this free TH-cam channel, but it depends on the priority/hierarchy as defined in the legal agreements. It could go either way, which is why the contracts would have to specify the order of operations/calculations.
@@financialmodeling Hello, so just to confirm I'm thinking of this correctly... So since assets increases due to managers exercising their options that caused the equity to increase. Since equity increased, we now have to slightly decrease the % of equity the managers will receive(options pool %) to in-order to take into account the increase in equity due to the increase in cash from excercising their options?
@@nyokabi123 Yes. But you could easily skip this step and still pass the case study. They mostly just want to see that you get it "roughly correct" in the allowed time.
Hello! Apologies if this has been asked already but is there a reason why the Ending Cash of the Term Loan is calculated with mandatory repayments instead of CF Used for Debt Repayment?
I'm not sure which line item you are referring to here. The "Ending Cash" in row 74 is always based on the Beginning Cash, Mandatory Repayments, FCF, and the CF used for optional repayments (row 73), as these represent the starting point plus the major changes in the cash balance for that year.
Nice video, many thanks! One question: why do you link the mandatory term loan repayments (5%, 10%, 10% etc.) to the initial debt amount of €300m (i.e. you anchor this €300m figure in row 67)? I always linked the mandatory repayments to the beginning debt balance of the term loan in each year - is that wrong?
That is incorrect in most cases. The principal repayment percentages are based on the initial debt balance - otherwise, you might get into a situation where the loan never amortizes completely (if the percentages add up to 100%, for example).
Hi i have another question at 42:09. I don't understand why we should use D71 when multiplying cash sweep % instead of free cash flow (e.g. D70)? Thank you
Because the company's entire FCF is not necessarily available for debt repayment. The available FCF is reduced by mandatory debt repayments and the minimum cash requirements.
"Cash Used for Debt Repayment" means "Cash the company uses to *repay* Term Loans." It's added here because it's already a negative in Excel, and adding a negative is the same as subtracting the number.
There are some older videos in the Excel section on this topic. It's a fairly boring topic that most people aren't really searching for, and it's well-covered in our actual Excel course already. So... maybe, but it's not exactly the highest priority topic here.
![DAX Filter Context Basics [Full Course]](/img/n.gif)
For the files and resources, please see:
www.mergersandinquisitions.com/lbo-modeling-test/
Hey man, This is great! thank you so much for this video! Will do it in full. Love your channel!
Thanks for watching!
Excellent tutorial, thanks a lot. I definitely need to dig a bit deeper into these mgt options' and optional repayments' formulas - these aren't easy to grasp when I try to do the case by myself :)
Cheers from France
Yup, it takes some time and effort to learn. But it gets easier once you have completed many examples.
Your video is really helpful! A little question. In case study question 3, why should we implement a dividend recap and cash dividend distribution before Year 5 so as to boost returns? Equity value = Enterprise Value + Cash - Debt, so the two methods I mentioned will decrease equity value (and investor returns), I guess?
A dividend recap will result in an early cash distribution before the final exit in Year 5. The total amount received by the PE firm won't necessarily change by much, but the time value of money means that the IRR will increase due to the earlier distribution.
@@financialmodeling That's helpful. Thanks a lot!
Keep it up👍
This is so valuable work. A big thanks
Thanks for watching!
Why is the CapEx (factory maintenance expense) based on last year's number of factories and not the number of factories we have this year? Thank you very much
Maintenance CapEx relates to existing factories that have been operating for at least a year. New factories constructed in the current year do not yet need maintenance.
@@financialmodeling That makes sense, thank you
Why don't we integrate the 2% ($24m) advisory & management fees? I assumed that I should at least subtract that figure our return calculation (from the Equity Value). Thank you very much!
The Investor Equity is higher due to these fees, so it is reflected in the model. Delete the fees, and the Investor Equity in D20 goes down, so the IRR and multiple increase.
If I go back to school, it will be to learn how to do this.
This looks really cool!
Thanks for watching!
Your videos are awesome and I’m learning a lot. Also, I’m planning to do a quite short thesis on an LBO case. Do you have any suggestions on what could be/ has historically been a relatively simple LBO I could talk about?
Thanks. I don't really look at recent LBOs unless it's part of a new course or something similar, and we just re-did all our LBO models last year, so I don't have a specific recommendation. But try simple companies in standard industries (industrials, retail, consumer, etc.) first.
With this method the min cash can go below 5% of sales... how should it be handled? If free cash flow or Net income decreases the ending cash will go below the required 5%. How should it be handled
As mentioned in the other comment response, this would be handled in real life via a Revolver to draw on extra funds if the company falls below the Minimum Cash. There is no requirement to build one into this model, so it's not shown here.
Loved the content, though Im more just an excel user and not a financial analyst... I mean, I had to look up what LBO meant haha. I thought for sure you would mention it at some point. Also, it would be great if you showed the keyboard shortcuts on the screen as you record.... sometimes its hard to follow what you are doing since you are doing it on the keyboard and we cant see that. (and now I see you did it later in the video haha)
This tutorial assumes a certain level of prior knowledge because it's designed for people searching for the keyword "LBO modeling test." Anyone searching for that presumably knows what "LBO" means, what private equity is, etc. We tend not to focus on Excel shortcuts in financial modeling tutorials because pausing to state each shortcut slows down the pace. There are separate tutorials for Excel shortcuts and features here.
Nice one!
Thanks for watching!
On one of these more simple tests, would you ever have to do something like limitations on interest expense (30% of EBIT)?
Probably not, especially since it's specific to certain regions and not a global rule.
Hey, brilliant stuff. Could you explain why the "mandatory repayment" is min(term loan amortization, term loan)? Given that there is a cash sweep, should it not be If(fcf>min cash balance, term loan amortisation + 0.5*(fcf-min cash balance))??
Apologies if this is a stupid question but would appreciate any feedback on this
The cash sweep is the optional repayment part, so it's separate ("CF Used for Debt Repayment"). Mandatory repayments refer only to the fixed principal repayments that exist in each period as long as a sufficient loan balance exists. The MIN checks to make sure that the remaining balance is greater than the scheduled principal repayment.
Very helpful video, thank you. One question, for the sensitivity tables, I seem to always get 0 values or one repeating value. I checked the circular references, locked the reference cell properly, put on automatic calc mode.. Do you have suggestions what else could be an issue? I use mac, Excel 2016 version. Thanks!
It's either a problem with the table or a problem with your model links. Start by changing these assumptions in the model and seeing if the IRR changes. If it does not, it's an issue with how the assumptions are linked in your model. If it does, it's a problem with the table - try hard-coding each value in the left column and top row and delete and recreate the entire table with the correct row and column input values.
Hello, Brian. I noticed that you showed treasury method in videos when calculating dilution. Could you please make separate video about options and their implication in LBO/Valuation models? Thanks for this video!
There really isn't much to say about it. Options slightly affect a company's value in a transaction scenario, but in 99% of cases they are not a major driver. This would be like analyzing a book and focusing on the average amount of space between a period and the first letter of the next sentence...
What program would I want to purchase on your website if I wanted to be an equity analyst? I assume the financial modeling one?
Yes, either Financial Modeling or Excel + Financial Modeling.
Hi I've a doubt, while valuing the target for LBO using dcf , will v take post LBO values( where v use the new capital structure where new debt will be taken to fund the deal) or do we use the pre LBO values ( where v value the company with values as it is before the LBO is to be done)
Pre-LBO values.
Thank you for this great video!
Why doesn't the excel have any circular reference with the cash sweep?
A cash sweep does not create circular references. It's a simple linear calculation, and as long as we use the beginning cash and debt to calculate interest expense for use in the FCF calculation, no circular reference is created.
Very Good video thks m8
Thanks for watching!
Hey Brian, absolutely love your videos. Is there any shortcut for making the indents in cells like you do, or do you just have to manually hit space. Also any chance you could explain how to move around the menu when I press CTRL 1 using only the keyboard.
Alt, H, 6 and Alt, H, 5 to insert and remove indents. We don't cover Excel shortcuts extensively in this channel (see the dedicated course), but there are several videos on how to set up the QAT in Excel and the more important shortcuts.
Hi, one thought, if the Free cash flow decreases year 1 to e.g., 10 (instead of 44) then we have Ending cash of 23 which is lower than the minimum cash. How can that be? From my understanding, we all the time need to have 5% min cash to sales in cash?
You normally handle this with a Revolver that allows for additional borrowing to meet the Min Cash, which is not included in this simple 60-minute example. You shouldn't really worry about edge cases and strange behavior in models in an extreme time-pressured case. This is more something to worry about if you have more time / need a more robust model that includes different scenarios.
Thanks for your video! When calculating the tax-deductible interest expense on the P&L, you included the entire interest expense (included the interest you didn't pay, like the PIK), not just the coupons you paid out to debt holders. Is this correct? Is the entire interest expense tax deductible?
Also, why not include mandatory principal repayments in the interest line in the P&L? Are they not treated the same?
For LBO model purposes, the entire interest expense, including both Cash and PIK Interest, is tax-deductible unless otherwise specified. There are some special cases where this is not true, and some regions limit the % of Interest that is tax-deductible, but in a 60-minute case study, you can't really think about these points. See the PIK Interest tutorial for more.
Debt principal repayments are never tax-deductible in any region or industry. They appear on the Cash Flow Statement and are not a tax deduction because they represent the simple repayment of borrowed capital, not the expenses of using that capital over time.
Hey, shouldn't you include the min-cash balance in the ending cash balance calculation? The way I see it, is that you need to keep it as a minimum for your work cap outflows/changes so it's always on the BS and should also earn cash interest? Thanks
The standard practice in LBO models is to include the entire remaining cash balance at the end, so we follow that here. If the company's cash balance is too low upon exit, the next acquirer will add funds to the deal to bring it up to the minimum level.
Thanks
Thanks for watching!
why doesn't ending cash balance factor in cash used to pay interest charges on debt and instead only principle pay down?
The Net Income that starts the FCF calculation already has a deduction for interest expense paid on the debt since it flows in from the Income Statement. So it is factored in, but in the line items above.
Thank you!
Thanks for watching!
Thanks you very much
Thanks for watching!
Hello guys I have a question concerning LBO analysis. Is it useful in an LBO to consider the degree of financial leverage to assess how much debt the acquirer can sustain?
There is no "acquirer" in an LBO, just a target company, because it continues to operate independently after acquisition by the PE firm. If you're asking about whether the target company's leverage pre-deal affects how much debt can be used in the deal, no, not really except in very minor ways (e.g., a company known to existing lenders might be able to raise debt from those lenders more easily).
Thank you. Two quick questions: Can you explain a little more why you do "Option % / (1 + Option %)"? Also, how would the layout of the returns analysis change if there was an equity roll? Would the % owned by the Mgmt. include the cash from their options? Or would it be multiplied against just the Exit Equity Value?
The additional shares issued create additional equity, but the initial option grant was based on the company's equity before the share count expanded, so the option holders receive a slightly lower percentage. We've covered equity rollovers in other examples if you look at some of the Sources & Uses videos.
@@financialmodeling Got it, that makes sense. Maybe to clarify my second question, let’s say the original Equity Ownership was 80% Sponsor / 20% Mgmt when accounting for the initial roll. When doing the returns analysis with a Mgmt. Option Pool, would you create another line, after normal exit equity value (row 94) called, for example, “Diluted Equity Value”, which includes the new cash from the exercise of the options and the subtracted equity to option holders, and then do the 80%/20% split from there? Or would the 80%/20% split be calculated on the normal exit equity value (row 94), and then we do all the options calculations? Essentially which comes first: options calculations or equity roll calculations? Apologies for the long-winded question thank you!
@@trebitda9443 So this is really beyond the scope of Q&A on this free TH-cam channel, but it depends on the priority/hierarchy as defined in the legal agreements. It could go either way, which is why the contracts would have to specify the order of operations/calculations.
@@financialmodeling Hello, so just to confirm I'm thinking of this correctly... So since assets increases due to managers exercising their options that caused the equity to increase. Since equity increased, we now have to slightly decrease the % of equity the managers will receive(options pool %) to in-order to take into account the increase in equity due to the increase in cash from excercising their options?
@@nyokabi123 Yes. But you could easily skip this step and still pass the case study. They mostly just want to see that you get it "roughly correct" in the allowed time.
Hello! Apologies if this has been asked already but is there a reason why the Ending Cash of the Term Loan is calculated with mandatory repayments instead of CF Used for Debt Repayment?
I'm not sure which line item you are referring to here. The "Ending Cash" in row 74 is always based on the Beginning Cash, Mandatory Repayments, FCF, and the CF used for optional repayments (row 73), as these represent the starting point plus the major changes in the cash balance for that year.
Nice video, many thanks! One question: why do you link the mandatory term loan repayments (5%, 10%, 10% etc.) to the initial debt amount of €300m (i.e. you anchor this €300m figure in row 67)? I always linked the mandatory repayments to the beginning debt balance of the term loan in each year - is that wrong?
That is incorrect in most cases. The principal repayment percentages are based on the initial debt balance - otherwise, you might get into a situation where the loan never amortizes completely (if the percentages add up to 100%, for example).
Doing this in '60 is for pros :)
Hey, it's not that hard when you also create the test...
Hi i have another question at 42:09. I don't understand why we should use D71 when multiplying cash sweep % instead of free cash flow (e.g. D70)? Thank you
Because the company's entire FCF is not necessarily available for debt repayment. The available FCF is reduced by mandatory debt repayments and the minimum cash requirements.
38:40 Hi I have one question. Why do we need to add the cash used for Debt Repayment when calculating Term Loans?
"Cash Used for Debt Repayment" means "Cash the company uses to *repay* Term Loans." It's added here because it's already a negative in Excel, and adding a negative is the same as subtracting the number.
@@financialmodeling Understood, thanks!
Can you make a video on formatting?
There are some older videos in the Excel section on this topic. It's a fairly boring topic that most people aren't really searching for, and it's well-covered in our actual Excel course already. So... maybe, but it's not exactly the highest priority topic here.
53:44 Hi do you have a relevant video for calculating of return on invested capital? Thank you
Yes: th-cam.com/video/0wTR2Up7RKA/w-d-xo.html
What's your keyboard and screen set-up?
Not sure what you mean. If you're asking about the QAT in Excel at the top, we cover that in an Excel video elsewhere in this channel.