fbpx
Search
Close this search box.

Accumulated Depreciation using Mixed References

Share

Facebook
Twitter
LinkedIn

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

image

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]

image

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.

image

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.

image

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.

Financial Modeling using Excel - Online Classes by Chandoo.org & Pristine

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

10 Responses to “Accumulated Depreciation using Mixed References”

  1. Stephen says:

    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.

  2. Nilay says:

    well in the example above you cna always use "F15 = E15 + F14" using all relatove referencing 🙂

  3. @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....

  4. Anil says:

    Hey; Good to know, it helps to add another additional skill

  5. m-b says:

    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.

  6. paramdeep@gmail.com says:

    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! 🙂

  7. 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.

  8. Budana says:

    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.

  9. Aman Thakur says:

    Hello, Sir,
    Actually, I have applied to the table and could grow, but still facing the same problem please help me out.

  10. Private Proxies says:

    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 🙂

Leave a Reply