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.
10 Responses to “Accumulated Depreciation using Mixed References”
This is a useful skill to know. It all revolves around the ability to "freeze ranges". I think the true benefits of freezing ranges (using the F4 key) need fully explaining, but this is a good example of where it is useful.
well in the example above you cna always use "F15 = E15 + F14" using all relatove referencing 🙂
@Stephen: Right! It is a useful skill. In fact you can use this technique in multiple instances. For example, if you want to calculate NPV of the project till various years, it can be used.
@Nilay: Yes, that is also a mechanism and a nice one too. I use both the techniques....
Hey; Good to know, it helps to add another additional skill
This reminded me of a recent post on the Fast Excel blog:
http://fastexcel.wordpress.com/2011/07/07/making-the-most-of-your-xips-part-1-counting-xips-and-reducing-them-to-make-excel-calculate-faster/
Worth reading when you're doing these (and other) calculations on large data sets.
Dear M-B,
This is interesting! I would say that if the calculations are very large in number (in the sum formula atleast), then it would make sense to take care in terms of excel operations! Usually in financial models (in equity side) the number of calculations is not so large. You work with data that would be in the range of 5-10 years. So I never thought about optimizing my code!
Your post reminds me of coding and optimizing code at the assembly level. It is quite interesting! 🙂
Using the F4 key to effective anchor the base point of a calculation, such as producting moving cumulatives is acutally a fairliy basic thing that everyone doing any kind of financial modelling needs to be aware of.
I am still trying to find the cumulative sum as this one applied to a table and could grow, but still could not find a correct formula.
Hello, Sir,
Actually, I have applied to the table and could grow, but still facing the same problem please help me out.
Great info and straight to the point. I am not sure if this is in fact the best place to ask but do you guys have any thoughts on where to hire some professional writers? Thank you 🙂