Asset Schedule (Part 2)

8.1 – Hello depreciation

We closed the previous chapter with our first balance sheet projection, i.e. the gross block. This chapter will complete the asset schedule and plug the numbers back to the P&L and Balance sheet.

At this stage, here is how the asset schedule looks  –

The gross block looks tidy. We will now have to work our way through depreciation. The biggest challenge with forecasting the accumulated depreciation is getting the current year depreciation number, which, as you realise, flows from the P&L statement.

Here is the snapshot of current year depreciation as stated in the P&L –

Investors are often confused about these two depreciation numbers, i.e. the accumulated depreciation stated in the balance sheet and the current year depreciation number displayed in the P&L (on the expense side).

The current year depreciation stated in the P&L is the depreciation value (in Rupee terms) applicable only for the financial year under consideration. The company’s finance team calculates the current year depreciation by factoring in all the assets (gross block) on its books. The current year depreciation stated in the P&L changes for each year based on how the gross block changes.

On the other hand, the accumulated depreciation in the balance sheet is on a cumulative basis. The depreciation number gets rolled over on year on year basis. In other words, the current year depreciation (from P&L) gets added over to the next year depreciation, thus forming the accumulated depreciation for this year.

Don’t worry if you find this confusing; you will understand this better shortly, but for now, I want to you think about the direction we are heading in.

We have already projected the Gross block number. If we can project the current year depreciation number in the P&L, we can apply the base rule again in the asset schedule and forecast the accumulated depreciation number.

After we forecast the accumulated depreciation, we can also calculate the netblock of the company. Finally, the net block number from the asset schedule flows back to the balance sheet.

By carrying out the above steps, we achieve two things –

• • Project the current year depreciation number in P&L
  • Get the projected netblock number in the asset schedule, which gets plugged back to the balance sheet

Everything depends on techniques to forecast the current year depreciation.

Before we proceed, a slight but relevant digression ????

I want you to think about the following situation. Assume you are a freelance photographer with a variable monthly income. Your income depends on the work that comes your way.  You are also responsible for managing your household expenses.

Your monthly income for October is 25K, of which you spend 3K on entertainment.

The next month you earn 30K. How much do you think you should spend on entertainment for the month of November?

The easiest way to do this is to spend in the same proportion as you spent in October.

In October –

3000/25000

= 12%

Hence for September,

12% * 30000

= 3600

We have used the method of proportions here. I want to extend this thought and project depreciation for the current year.

For Year 5, Depreciation and Amortization stated in P&L is 41.71Cr. The gross block, as stated in the balance sheet for Year 5, is 538.76Cr. The projected Gross Block for Year 6 is 588.77 Crs. Given this, what do you think will be depreciation for Year 6?

Let us apply the proportion technique.

For gross block worth 538.76Cr, the company reported depreciation of 41.71Cr, which means –

41.71/538.76

= 7.74%

For Year 6, the gross block value is 588.77 Crs, so what is the depreciation given the same proportion?

7.74% * 588.77

= 45.58Cr

With this, we can estimate that the depreciation for the next year would be 45.58Crs. Remember, this number flows into the P&L.

The depreciation value will remain the same at 7.74%.

You can extend this a bit more. Instead of taking the previous year’s proportion and assuming the same proportion will hold for the next year, you can calculate the depreciation to gross block ratio for all the historical years and then take the five years rolling average for the future years.

It will look something like this –

Y6 = Average of Y1 to Y5

Y7 = Average of Y2 to Y6

Y8 = Average of Y3 to Y7

So on and so forth.

You can choose either technique; I’ll stick to the first technique for simplicity.

Of course, the 3^rd alternative is to dig deep into gross block and get into the accountant shoe to figure the exact depreciation value; you can do that if you have a strong accounting background.

Going back to the P&L with the 1^st technique, I can directly input the formula –

As you can see, in cell J16, which points to the Depreciation expense for Year 6, I’ve divided the depreciation amount stated in P&L for year five over the gross block stated in the balance sheet for year 5. The result of this division is the depreciation proportion, which I then multiply by the gross block projected for Year 6.

The resulting value is the depreciation amount for Year 6. Note, this is a projection that we are making. I can extend the same math to all the future years and get the depreciation expense for the year.

By the way, we also made our first P&L projection, so it’s a tiny baby step in our financial modelling journey ????

At this point, my P&L looks like this –

Alright, with this in place, let’s go back to the asset schedule.

8.2 – Accumulated depreciation

Some of you may have guessed the next few steps already.  It is pretty simple, we go back to the asset schedule sheet and plug in the current year depreciation number from P&l. Once we have that, we use the base rule to complete the accumulated depreciation.

Here is how I’ve plugged the number from P&L –

We have the closing balance for Y5, i.e. 223.68Cr, which becomes the opening balance for Y6. Add to this the current year depreciation, and we should get the closing balance for Y6. Of course, we won’t get into the depreciation non-expense bit as there is no visibility on this line item. Hence will keep it at zero for all the future years.

I’ve applied the base rule to get the closing balance of accumulated depreciation. The netblock is the gross block – accumulated depreciation, which I’ve projected for future years.

Of course, you take the accumulated depreciation number from the asset schedule and complete the netblock calculation in the balance sheet.

With this, we have projected the Fixed assets section in the balance sheet.

In the next chapter, we will discuss the debt schedule.

Key takeaways from this chapter

• • Accumulated depreciation stated in the balance sheet is on a cumulative basis.
  • The current year depreciation stated in the P&L is for the year only
  • You can use the method of proportions to forecast the depreciation for the year
  • You can apply the base rule to forecast the accumulated depreciation
  • Netblock = Gross block – accumulated depreciation