Operating Model
- 06:56
LBO modeling balance sheet operating model
Glossary
LBO LBO modeling operating model Private EquityTranscript
We're now gonna build the operating model for Debenhams, and what we have here is, firstly, an input page with the assumptions that are gonna go into and drive the operating model. So here's where we actually have our circular switch, which we'll use to activate the circularity around the interest expense flowing through the operating model. Right now it's set to zero, and we want that off, and again, we wanna make sure that we're not modeling with obviously any existing circularity and that we have our iterations off so that we're alerted to any accidental circularity. What we have here is an operating model that has the ability to toggle back and forth between two different cases, a bank case and a management case. We're gonna inherit numbers from management and that management case is going to be probably more optimistic than the bank will be when it comes to assessing the ability of the company to generate cash flows and paid on the loan. So the bank is going to want to run its own scenario. So what we have here is a model where we've got assumptions for the income statement, and then the ability to change the assumption for SG&A as a percentage of sales. In LBO models, almost always, you're going to want to sensitize your EBITDA and see what happens with EBITDA growth. EBITDA, in most cases, is going to be driving the value added in the model, more so probably than anything else. Sensitizing EBITDA is almost the necessity in these models and we're gonna do that in this model by toggling back and forth between higher SG&A costs and lower SG&A costs. There's an already built choose function, and it's a choose function similar to the one that we were building on the LBO tab to toggle the capital structure. This one's slightly more sophisticated, and it works off of a label. We have a data validated cell here that's got a couple of different labels for bank case and management case that's linked to the base and management case that we've highlighted here. So we have a dropdown box that we can toggle back and forth on, and the choose function is essentially linked to that toggle box, and it's a match function embedded in a choose function so that we can match the name that's in the toggle box to the name that's in the row, and that'll pull in the assumptions from that same row, a slightly more sophisticated way of doing it. It's been done for us here, and then these assumptions that are here for the income statement and the balance sheet are going to feed into our income statement on the IS tab. We have to be careful of these three columns, these three columns, which exist on all of our pages, and they're there because we need to maintain matrix integrity so that column F is always 2015 and column J is always 2016, and there's no other way around that, unfortunately. On the income statement, our firm is gonna look a little bit weird in this first year but they will still work. We just have to be careful when copying, right? In the first year, our net sales are gonna be equal to one plus our assumption on the input page times all the way back in 2015 and cost of sales is going to be equal to our cost of sales assumption times sales. So that will be a copyable form. It's just really sales growth we're gonna have to be careful of. Gross profits, similarly to the balance sheet, we can take all of those previously calculated formulas and use them in the future. SG&A. We want to be careful here that we get the SG&A that's going to reflect the choose function toggle, and that's gonna be this J15 times net sales copy across our EBITDA, and now I have to go ahead and do a depreciation calculation. And if we look at our assumption for depreciation, it's based on our net PP&E, so we're gonna have to head over to our calcs page to do that calculation. On the calcs page, in order to make this copyable going forward, what we're gonna do here is similarly to what we did in the balance sheet. We're gonna just pretend for this balance sheet account that we're still doing the balance sheet adjustments and we're going to anchor our ending PP&E from 2015 into the combo column, and then that can feed the beginning of 2016. So that's exactly the way our balance sheet worked when we did the balance sheet adjustments. The capex assumption we can get from our input page. That is a percentage of sales, and the depreciation is 10.9% times the previous year's net PP&E or the beginning balance, and if we add these up, we get an ending net PP&E balance of 681.7. So we'll use that when we get to the balance sheet. In the meantime, the 73.6 is is what we need for the income statement, and I'm just gonna go ahead and link to that. Amortization, I'll go to my input page, and they don't have any other intangibles that we need to amortize, so that's gonna be zero. And now I can calculate my EBITDA. Obviously I'm gonna leave my interest expense 'til after I get my debt page done, so in the meantime, I can simply go ahead, and if I copy my previously done formulas, they're going to include a placeholder for the interest expense, and for the taxes, we'll go back to the input page and get our effective tax rate times our earnings before taxes, and then we'll get our net income formula from 2015 and that gets us to a net income of 126.1. Obviously not accurate yet. We can copy everything over except sales. So what I'm gonna do is I'm actually just gonna go ahead and copy this over one column. Now typically what I do when I'm modeling what we teach is that we get one year to balance, and then we'll go ahead and copy everything over, or else we end up just making mistakes, fixing them, and then forgetting to copy over, but on this income statement, because we have this very odd year one that skips over these three columns, I'm just gonna go ahead and do my 2017 column and get that done, so I'm going to take my assumption from my input page, which is this 0.4% plus one times on my income statement the previous year's sales, and then everything kind of falls into place for us. We can see our depreciation has not copied over because we don't have this done yet. If I go ahead and copy over one more year, it does fill in for us, and we see that we have our depreciation filling in, so now we have our model complete through 2017. I'm just gonna leave it right there for now.