Debt Capacity - Income Statement Adjustments
- 06:58
Update the model to include new assumptions and adjustments for the project, adjusting revenue and EBITDA calculations, and ensuring accurate depreciation and amortization for the new facilities.
Download a file of the data from the free downloads section, or access the live industry data in Felix.
Access the live industry data for Charles River Laboratories International here: https://felix.fe.training/company-analytics/?ticker=CRL&cik=0001100682
Transcript
Once the new assumptions have been added to the model, we've then got to be really careful to make sure that we don't make any adjustments to the model before we turn off iterations and our circular switch. So let's go back to the information tab, turn off the circular switch, and also turn off iterations as well within our Excel options menu so that if we do create any circular references by mistake, we get told about them. What we now need to do is to adjust the income statement to take account of our new project. What we have at the moment is one line for revenues, but what I need to do is to add in two more rows and to make it clear that the revenues from the existing model were for the core business, not for the new facility. I'm then going to add another revenue line for those new facilities, to give us total revenue. Let's go and grab the numbers for this. The revenue for the new facilities was just one of our underlying assumptions, but the revenues don't kick in until we get to the third year. So I've just grabbed that assumption and pulled it down. I'm going to also put zero in for the historical years as well and then calculate my total in the historical years. Adjust the formatting to show that it's a total, and then copy that across to the right as well. So we've now got a new total revenue figure that in the third year, in 2027 includes the additional revenue for that year from the new facility. We're going to do exactly the same thing for EBITDA. Insert two new rows. Make it clear that the existing EBITDA is for the core facilities and also that there is additional EBITDA for the new facilities, to give us our total EBITDA. Same approach as before, zero for the historical year. Add them up, make sure that we change this to a appropriate color coding. The EBITDA for the new facilities is going to be our revenue for the new facilities. But then given the assumption here that the new facilities margins are going to be the same as the existing sales, we can just go back and grab the existing EBITDA margin assumption and multiply this by our new facilities revenues. But here's what we've got to be careful. It's really easy to forget that we also said that the project setup costs are going to be expensed and EBITDA as well. So we've got to make sure that we deduct from this the project set up costs as well. And I'm deducting them because they're a positive number up there in the assumptions. If we copy this across for the first two years, you'll notice that this gives us a expense only in the second year for 2026 when we have no revenue, but just those project set up costs being incurred. And in the third year when we do Have have revenue coming through, we're still making a loss because the setup costs outweigh the EBITDA that we make from this new project. Once we've done that, we can then calculate our total EBITDA. And then we've got one final set of adjustments to have a look at, which is to calculate our amortization and depreciation. Just like we had for the other steps so far for revenue and EBITDA. We have the core business as well as the depreciation and amortization for the new facilities. Now, this is zero in the historical year because the project wasn't there, but to calculate amortization and depreciation, what we're going to need to do for our calculation of our depreciation amortization, it's just go down a little bit and see that there are some calculations for our PP&E numbers for the existing business, which has the core business depreciation and amortization. And I'm going to set up a similar calculation for the new project. So we've got our beginning project assets to which we're going to add the new project CapEx, and then we've got our depreciation and amortization. Well really just depreciation deduct from this to give us our ending project assets. We've got zero to begin with, which becomes the opening balance for the first year. Our new project CapEx is an addition and we've got that in our assumptions 250 in the first year. Now for depreciation, it's typical that we don't start depreciating assets until they're fully completed. And since this asset has been built during the first and second year, 2025 and 2026, I'm just going to hard code a zero for the first year. And this will be the case for the first two years. In the third year, 2027 is when we start seeing depreciation, when the asset is fully constructed and has the opening value of 500. There's no CapEx in 2027, but our depreciation is going to be based on all of the assets that have been built so far. That's the sum of our two CapEx numbers, and I'm going to lock onto those with F4 because they're never going to change. And then we need to divide this by the assumed economic life of the project of the facilities, which is going to be 20 years. We could add a minimum function here to make sure that we never depreciate this asset down to below zero for the ending value at the end of the period. But I'm going to skip over that for simplicity. But we do need to make sure that we add a negative sign at the beginning because depreciation reduces the opening balance of this asset. We can then copy that out to the end of the six years of the forecast period, the remaining four years to get a 400 value for the asset at the end of the 2030 year. The reason we're doing this was to get our depreciation number and now that we've got it, we can add this back into our income statement. So the depreciation for the new facilities, we're just going to grab that from our most recent calculation. But also don't forget that we need to make it a negative sign, because when we get to the third year, when we start our depreciation, it's negative on row 53 in the base calculation for our project assets. But we want it to be positive like row 34 for the depreciation in the income statement. And that's what we get from the 2027 year onwards. We then need to make sure that our EBIT calculation is working correctly, and at the moment, the EBIT calculation only includes EBITDA for the core facilities. So I need to adjust that to the total EBITDA on row 33. And also it's only deducting the depreciation for the core facilities. So I'm also going to need to deduct the depreciation from the new facilities as well. And then copy that across to the right to apply it to all of the six years of the forecast period. We should end up with just over 1,000, 1,033 as our adjusted EBITDA now at the end of 2030.