Technical question - Revenue sensitivity in LBO Model
Hi all,
I have a question re return sensitivities in LBO Models.
My understanding of how to sensitize IRR/MOIC with entry/exit multiple is straightforward. However, I sometimes see questions about IRR sensitivities to revenue growth/EBITDA margin.
Usually my DT are off because my P&L is not driven by revenue growth or EBITDA margin figures but rather by my operating assumptions so I don't really know how to do this.
Does somebody have a corrected example? Or would be willing to explain how to do this?
Very much appreciated
Thanks
It is quite a pain to do as you will indeed have to drive your P&L assumptions on the back of % increase. On top of this what i usually do is to put a cell that has the trigger for the sensitivity for Revenue growth % where you will put by how much your revenue will increase or decrease in % (e.g. Let's assume revenue fall by 5% every year vs base case). In other words, your year 2 revenue in P&L will be a formula like (Revenue Year 1)*(1+Growth in the base scenario + Toggle of the sensitivity), and so on for all the years. Then you have to basically run the sensitivity linking the trigger and then the Entry / Exit or whatever other metric you need to use.
Careful tho that to fully make the change flow you also have to act on the other lines of the P&L so that those are based on the % of the "new" sensitized revenue, by adding again the trigger cell reference to the other variables (e.g. D&A, EBITDA margin etc.). Alternatively you can drive these as fix % of the new revenue, if that is easier. (I.e. either you say ebitda is 15% of revenue, and that is linked to the new revenue so will increase accordingly, or "ebitda is x millions and on top of this we need y millions due to revenue growing by 5%).
Capex, is tbd on this side as it depends on the business and the assumptions (are the revenue growth sensitised for price or for quantity, for instance).
Please note, likely you will have a tab for P&L and another tab for Sensitivities. Given Excel does not allow DT to run on different sheets i usually put the "master" trigger next to the Data Table and then just create a copy linked in the P&L for ease of use.
Hope it helps, and yes if you have structured your model without the %s to act it takes some time to make this change :)
Super helpful, many thanks. I thought it was much more straightforward as I've surprisingly seen questions on revenue/ebitda margin sensitivities in quite a few timed model tests
I mean if in the test you start with everything as % it's just a matter of adding a trigger throughout the revenue growth formula and everything else should subsequently flow given you calculate it all as % of your revenue (that are already adjusted). Thing is long if you have to effectively flip the mechanics of your model
Ipsum consequatur qui sit adipisci quam aut. Odit et natus quas quo reprehenderit. Perferendis illum sit corporis impedit alias aut. Voluptas perferendis enim cumque et eos eos. Sit laboriosam quisquam neque suscipit.
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...