IRR/MOIC Sensitivity Tables

Assuming you know how data tables work, here's the best way to do this:

• EBITDA margin: set up an empty driver cell. Then, you create an if statement in your model's EBITDA calculation: if(driver cell = "", standard EBITDA calc (gross profit - costs), driver cell input * Revenue). Then, your "row reference" or "column reference" should refer to this empty driver cell. This will not fuck up your model, but the sensitivity will input whatever you're sensitizing when it runs its calculations. This obviously gets more complicated depending on how the other cashflow items are calculated (if your NWC and capex items are built off of complicated other drivers, use the method under the subbullet for revenue CAGR, and put implied EBITDA margin as a side output)
• Revenue CAGR: Same as above except your if statement will be reflected in the revenue calc in your model: if(driver cell =" ", standard revenue calc, prior rev * target revenue CAGR). Keep in mind, this only works if your cost drivers are very simple and are based off of % revenue; it won't be accurate if your cost drivers are based on actual operating metrics.
  • If you have an actual operating model that's driven off of specific metrics, then do this: sensitize off of the biggest revenue driver (e.g., volume, bookings, pricing, etc), and then add a row/column to the side to show "implied revenue CAGR," which is a 1-sided sensitivity that outputs revenue CAGR with the same revenue driver as the sensitivity. This will result in uneven revenue CAGR differences between the sensitivities, however it will accomplish the same purpose in viewing how returns and revenue CAGR are correlated.
• Entry and exit multiples should be standard, and you simply reference the input cells that you already have for entry and exit multiple

Personally I hate sticky if's, and I don't get the comments above saying to use sticky if's vs sensitivity tables - it's a pain in the ass to have to keep inputting that shit when the sensitivity calculates it automatically. If it's really a problem, just delete the figures in the sensitivity table until you need to run it again (which takes like 2 seconds). For what it's worth, it should be well-known that rev CAGR and margin sensitivities are highly illustrative and most people should understand that if you have a complicated operating model it makes no practical sense to make investment decisions off of these (unless it's a model test, or sometimes the Partner wants to "just see what it looks like")