Modeling Case Study - Income Statement from Depreciation
- 03:22
Forecast the income statement of a private company using a simple model. Linking the sales, cost of goods sold, operating expenses, depreciation, and tax assumptions to the income statement.
Transcript
When I come down to depreciation, I need to do a sidebar calculation. So I'm going to go to my calculation section and the first thing I'm going to do is I'm going to pull in the anchor from the historical balance sheet. It's really important to do this. So I'll go down to the balance sheet and I'm gonna pull in the net PP&E from the historical balance sheet as the ending balance in 2023. So that's what I call the anchor, and it anchors your forecast. Then what I'm going to do is on the beginning balance, I'm going to make it equal to the anchor that I've just put in, and I should have an assumption for capital expenditure, which is probably going to be linked to sales. And I can come down here, row 26, yes it is, it's linked to sales. So I'll go and pick up the total sales on the income statement in the same year to make sure those relationships are consistent. And then for depreciation, there'll be an assumption. Now you can either do depreciation as a percentage of the beginning property, plant and equipment, or you can do it as a percentage of CapEx. It's quite common to do it as a percentage of CapEx, but in this model we've got a percentage of beginning balance. Now, the ending balance, you need to be careful here that you just do a sum you don't control R because obviously that will take it from historical balance sheet. Double check the numbers, don't look too bad. And I'm gonna pull in that depreciation to the income statement. Now I'm gonna go down to where my depreciation line is and I'm gonna pull it from the base calculation that I've just calculated. I don't need to change the sign because it's a negative number on the income statement, calculate EBIT. I'm going to leave out interest income and interest expense because I haven't got a balance sheet yet, so I can't calculate interest, income, and expense. But other financial income, I probably do have an assumption for, usually I would keep it zero. So let me just see what the assumption is. Actually, there is some element that is being forecasted, so that's probably something like equity income from equity method investments, and then I'm going to calculate profit for tax. And then the tax expense, we will typically have an effective tax rate forecast. So I'm gonna go to the assumptions. And we've got a marginal tax rate and an effective tax rate. When we're forecasting a hundred percent of income, we want to use the effective tax rate. The marginal tax rate is only used for things like cleaning earnings or making marginal adjustments to reflect things Like the tax deductible of interest. So I'm gonna take the effective tax rate times, the profitable tax times minus 1, and then I can calculate net income. So this is a pretty nice, simple model. There's no share numbers. It's a private company that would not be relevant.