Let us utilize the proportion approach.
The company reported depreciation of 41.71Cr for the total asset worth of 538.76Cr.
What is the depreciation for Year 6 when its gross block value is 588.77 Cr?
7.74% * 588.77
This estimation suggests that depreciation for the next year will be 45.58Cr, which should be reflected in the Profit and Loss statement.
The depreciation value is fixed at 7.74%.
Rather than take the proportion used in the previous year and expect it to apply for the following one, try computing the depreciation to gross block ratio for all past years, then taking an average of those last five years to forecast future estimates.
It will look something like this –
Y6 = Average of Y1 to Y5
Y7 = Average of Y2 to Y6
Y8 = Average of Y3 to Y7
I’ll opt for the first approach since it seems to be the simplest.
If you possess a sound background in accounting, then exploring deeper into the net-block to get to the bottom of the depreciation figures may be an option.
By using the first method, I can quickly enter the formula in the P&L.
In cell J16, I divided the Depreciation expense stated in the P&L for Year 5 by the gross block stated in the Balance Sheet for the same year; thus generating the depreciation proportion. This figure is then used to calculate the Depreciation Expense for Year 6 by multiplying it with the projected gross block.
The resulting value is the depreciation amount for Year 6, which is projected. This same math can be applied to all future years in order to calculate the depreciation expense for each year.
We also took a small step forward with our financial modelling, since we created a P&L projection.
At this juncture, my profit and loss account appears like this –
Now that this is done, let’s review the asset list.
– Accumulated depreciation
Some of you may have already guessed what we will do next. It’s quite straightforward: we update the asset schedule sheet with the current year depreciation figure from the P&L and use the base rule to work out the total accumulated depreciation.
I have calculated the P&L numbers and used them as part of my computation.
The closing balance for Y5 is 223.68Cr and this serves as the opening balance for Y6. To work out the closing balance for Y6, add current year depreciation – although we don’t have any visibility on this line item so can only assume a value of zero for all future years.
I’ve employed the basic rule to work out the concluding balance of accrued depreciation. The net block is composed of the initial block minus the accrued depreciation, which I have approximated for coming years.
You can get the amount of accumulated depreciation from the asset schedule and then use that figure to do the net block calculation on the balance sheet.
We have now incorporated the Fixed assets section into the balance sheet.