Sensitivity analysis or data tables allows us to look at many different scenarios within just one model. For instance, here we're looking at the current IRR of 12.9% that's linking up to this cell here, the current IRR. But we'd like to see what happens to that when we flex a couple of inputs. We're going to change the exit year, which is currently year five. Let's change that to three and four, and the current exit multiple, which is 17.1, let's flex that as well. At the moment, there's no circular interest in this model, so we need to turn that on. So I'm going to do two things. The first of those is I'll go to file options, then go down to the formula section and I'm going to tick enable iterative calculations. You might also notice that my workbook calculation is set to automatic so that my data tables update automatically. So I'll press okay. That was the first thing I wanted to change. The second thing is I now want to go to the info tab and just turn on the circular switch so that interest now flows through the model.

Great. I'm now ready to do my data table. I need to select all of my inputs. Then I'll go up to the data ribbon and then what if analysis data table.

Now Excel asks me, where's the row input cell? Well, my row inputs is the exit year, and then it will ask me, where's my column input cell? My column inputs are the exit multiples.

Both of these can be found in the top left hand corner of the model. So let's scroll up. Let's find them. So my row input cell was the exit year. That's 5 I then click into the column input cell section, and that was the exit EBITDA multiple. So that's the 17.1 in cell C25. I press okay, back down to the data table. Let's just check that we've got the right numbers in here. So my IRR changed to 12.5% when interest was turned on, and I can see that when we use the current exit multiple of 17.1 and the current year five exit, we have the same IRR there. So I know my data table is working, but what else is the data table showing me? Well, it's showing me that if I exit in year three and for an inflated exit multiple of 18.6, we get the highest IRR of 18.2%. Unfortunately, it's still a bit below that 20% IRR that we were after. And it's being a bit optimistic thinking that we're going to have an exit multiple of 18.6 when we're currently forecasting an exit multiple of 17.1.

But let's do a second data table changing a different variable this time we'll still change the exit multiple, but I'd also like to change the percentage premium being paid at the moment we're using a 20% premium. What if we were to flex that? So let's go select the table again, go to the data ribbon. What if analysis data table and it asks me for the row input cell, the row input. This time is the exit multiple and the column input cell or my column inputs is the premium.

So again, I'll scroll up to the top left hand corner. So my exit multiple, it's in row 25, 17 0.1, and my column input cell, that was the premium. That's just a little bit further up. It's in row 9 the premium paid. I press okay, go back down to the data table.

I just want to check that the current 12.5 in the top left hand corner is somewhere in my table. It should be where the 20% premium intersects with the 17.1 exit multiple and there it is. So I'm confident that my table's working. What else can I see? Well, I notice that the IRR does go above 20% in these four cells here, all we need is a very slightly higher exit multiple up to 17.6. That's great. However, we are being really a bit too optimistic maybe to assume that the current shareholders will sell out to private equity for a 0% premium.

So the data table's fantastic for showing us lots of different scenarios. In this case, perhaps a little optimistic that this company's going to be a good LBO target.