So I am somewhat new to LBO modeling as we don't really do it at my firm but I am trying to learn because I find it interesting and eventually want to end up in PE. I work with data tables to perform sensitivities every day, but in the context of a LBO there are a few things I cannot figure out and would appreciate some assistance:
1.) Usually the returns tab shows IRR using an exit multiple and stripping net debt in the terminal year, so the variables you can sensitize are exit/entry multiple, and purchase price. All of these would be the "row and column" inputs with IRR's being the table output.
How would you make IRR one of the table inputs and show other variables (firm value, purchase price, leverage, exit multiple, etc) as the output? What I'm trying to do here is avoid having to use various row/column inputs that i have to keep plugging/chugging to get the IRR range i want and then back into the value after getting the proper IRR output, I want to make IRR the INPUT and have it spit out firm value or some other variable i want to see (that way i don't have to back into anything).
2.) How do you sensitize based on exit year, since putting the year as one of the table inputs essentially does nothing. I can create a timeline that shows IRR at each year, but if i want to sensitize say IRR based on exit year and exit multiple, not sure how to put it in data table format.
3.) How can you run sensitivities at different leverage, at first i thought you could just make the terminal debt equal to a certain multiple, but that doesn't change the leverage from the start to the finish, only in the final year. Since leverage needs to be run through the entire model from entry to exit to properly perform sensitivity, the only way i can think to do this is make leverage a function of a multiple from the start of the multiple. I.E. instead of typing in how much debt you will use in sources/uses, back into a multiple of EBITDA and make that the actual input to calculate debt in the sources/uses. Is there a more efficient way to do this?
4.) lastly how to sensitize based on margins. My intution tells me if the margins aren't static in your operating assumptions, it's not possible. If you assume say 30% EBITDA margins from start to finish in your assumptions tab, I would assume you could just link that cell that contains the margin to the data table and get your sensitivity. However if your EBITDA margin is 30% in year 1, 35% in year 2-5, and 40% after, then how can you sensitize it since there are technically 3 different variables?
If any of my questions are unclear please ask me to elaborate, and thank you for your time.
PM your email address and I'll send you an LBO model used to recently used on a proposed LBO.
I looked through your model and it does have sensitivities but they are no different than the ones i described above, maybe i communicated this ineffectively.
What I am trying to do is make a data table with the irr on the outside of the table as a row/column input (not on the inside as an output), and then use some other random variable (leverage, exit multiple, entry multiple, whatever) to show a VALUE (purchase price) as the output in the data table. The idea is to get a direct valuation at a given range of IRR's rather than to keep tweaking exit/entry multiples or other assumptions until i can get my desired IRR in the output of the table and then have to back into calculating firm value by finding what entry multiple corresponds with my 20% IRR.
Essentially I am trying to do the same thing but backwards...... i want to key in the IRR i want, and have it spit out the purchase price (or firm value).
Also as mentioned in the original post I am wondering how to run sensitivities in a data table on valuation (purchase price) given different leverage and margins. Given margins are often dynamic i can't simply link the margins from the assumptions page to my data table because it will only effect the year the margin is valid for.
As for leverage, unless i make the sources/uses table all from one leverage multiple and then back into different tranches by figuring out my total leverage required and then tediously making each tranche a % of the original leverage multiple, I can't figure out how to sensitize it shy of just making different financing cases in an assumption page (which can't be data tabled anyhow).
Sorry didn't read your first post closely enough. I see what you are trying to do. I think I have one of the things you mentioned covered in another model I use; I'll send it to you later - slightly different but it gives you a scenario analysis based on different required IRR in different years to give you purchase price. We only use it as a rough guide so there's 5% increments in between but you can adjust that fairly easily.
I don't use the excel data table function for these types of tables, I just use NPV and RATE functions to create my own sensitivity table.
For your leverage sensitivity:
Create a driver for your sources/uses that is total leverage as a multiple of LTM EBITDA. Assume that you can raise a certain amount on your more senior tranches and then backfill the most junior tranche based on that total leverage driver. For example, your total leverage input cell could be 4.0x and you assume there's 2.0x as a term loan and an additional 1.0x in senior notes. Then the remaining 1.0x has to be filled up with whatever else you're using (mezz/sub notes, whatever). On your sensitivity table you vary the 4.0x number.
For sensitivity of margins/revenue growth: Drive your model off of the base case %'s for revenue growth and EBITDA margins (i.e. don't type the $'s in and then figure out what growth/margin is). Create two input cells, one for incremental revenue growth and one for incremental EBITDA margin and type 0% in both. For every projected period, in the revenue growth cell add the incremental rev. growth driver (this doesn't change anything when it's 0% but changes all growth rates if you type something different in). Do the same thing for margins and then you can make a data table by varying these incremental growth cells.
For sensitivity based on IRR: IRR is the return from beginning equity to ending equity of the sponsor. If you make a simple data table varying leverage or your exit mult (and fixing the other) you can figure out a range of exit equity values. If you discount these equity values by your IRR's then you know what your sponsor's beginning equity contribution would have to be. From that point you can figure out EV, equity purchase price, etc.
Voluptas tempore inventore qui amet. Et illum iusto necessitatibus vel saepe quisquam culpa harum. Molestias magnam iure quae quas quia dolor praesentium ea. Ipsum facilis quia aspernatur iure ea.
Et repudiandae dicta ratione nihil officiis illo nihil non. Ipsum voluptatem soluta odio modi perspiciatis atque quidem. Odit quod qui praesentium ut.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...