Accumulated Depreciation using Mixed References

Last time we had discussed the use of SumProduct() to ease your life for calculation of consolidated revenues and depreciation. This time we would be using the sum function! Yes you heard it right – The Sum function.

But we would use the Sum function with a small trick! We would use it to calculate running cumulative sum! And believe me, you would need this function so many times – to calculate accumulated depreciation, cumulative debt, Profits to Retained Earnings and almost all the accounts that would consolidate into the balance sheet.

Using Sum function to calculate cumulative values

Most of the accounts in the Balance sheet are cumulative accounts (For example, cumulative debt, accumulated depreciation, etc.). In models, we need to have these as running numbers

You would encounter this problem of calculating running cumulative values often and Sum formula (Used with slight intelligence) does come in very handy for the purpose.

Using Sum() function for calculating running cumulative values

Sum function uses an array as an input. If used intelligently – starting element of the array with fixed reference and the second part of the array as moving, it can help you get a running cumulative value. [read more: Relative & Absolute References in Excel Formulas]

The basic trick is very simple. In the first cell in the sum function, I fix the first array argument using “$” and sum till the same cell.

Now when I copy this function to the right, the first reference remains constant, whereas the second one keeps moving (as it is relative). This results in a growing array and hence a cumulative sum for accumulated depreciation.

Where else can this function be useful in Finance?

As I had pointed our earlier, most of the balance sheet numbers are accumulated numbers (Balance sheet is like a bucket, which accumulates values) and hence you can find the application of this running sum in almost all such accounts. I have used it (Can’t remember how many times!!) for converting Profits to Retained Earnings (when there are no dividends paid out), Debt Raised to accumulated debt (when there are no repayments), Debt for Interest During Construction, etc.

How do you accumulate numbers?

There are obviously multiple ways of doing the same thing in excel. I have shown you one way to get accumulated values. Conceptually accumulation is very simple – New Accumulated value = Old Accumulated Value + New Value. You can use this concept (maybe I will demonstrate in my next tutorial) to get the accumulation.

How do you accumulate numbers in excel. I would encourage you to share your experience!

Templates to download

I have created a template for you, where the subheadings are given and you have use the functions to get the right values for you! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Also you can download this filled template and check, if the information you recorded, matches mine or not!

Join our Financial Modeling Classes

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.

Please click here to learn more about the program & sign-up.

The article is written by Paramdeep from Pristine.

Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here.