Finance Friday: Year End Summary

Finance Friday: Year End Summary

I’m finally back from my second vacation of sorts. We’d been hosting some family from out of town for a couple weeks. It’s been tough balancing work, entertaining guests, and working on this blog so I took a brief respite. I should be back for a good steady stint from now on and shall be continuing this Finance Friday series on time!

You can download your own copy of the sheet here! And as always, the values are scrubbed to hide real data.

Year End Summary

Today we’ll be having a look at a sheet which helps me track my finances at a higher elevation.

Most of my sheets track on a month-by-month basis, so seeing things from a zoomed out point of view can be helpful. I have only been in the workforce and really tracking my spending for a few years now.

Because of that, this sheet has only a bit of data to analyze. That being said, the value in this sheet will grow much more as time goes on.

Assets

In essence, the Year End Summary is really just a consolidation of all the data from Cashflow Monthly. (CM). The most used Excel Formula that I’m using for this sheet is a SUMIF as follows:

=SUMIF(‘Cashflow – Monthly’!$1:$1, B2, ‘Cashflow – Monthly’!5:5)

Let’s break that down a bit:

SUMIF allows us to add up the values within a chosen row or column based on whether or not a certain condition is satisfied within a parallel row or column.

In this case, we are saying that we want to sum up all the values in Row 5 of CM. We do this if, and only if, the value of the matching column in Row 1 in CM is equal to the year in cell B2.

If you’re looking along with the sheet from the Finance Friday for CM this equates to is generating the yearly value for the “Cash” asset for 2017.

Cash Flow

Cash Flow

We essentially do the same SUMIF’s all the way down. As a result, we get our yearly value for every matching value that exists in our CM sheet.

Expenses – Necessary

Expenses - Needs

I split up the different types of cash flow in the exact same way as CM to allow for a clean translation of data.

Investments

Investments

Investments are a paramount part of our tracking. While they don’t really count as expenses, I track them as such for the purposes of determining where my cash is going.

Other Expenses

Other Expenses

Not all expenses are shown in the picture, but you get the idea that we’re tracking more “frill” spending here as opposed to needs like Utilities or Insurance.

Miscellaneous Data

Misc Data

I also keep track of different metrics like Credit Limits, Salary, Bonuses, and Savings Rates from a year to year basis. These will show up in a future Finance Friday post with the charts created from this data set.

Spending Averages

Spending Averages

Closing Thoughts

Hope you enjoyed this quick little Finance Friday posting. If you’ve got your Cashflow sheet set up already, this one should be a breeze to create and provides a lot of great value to analyze.

Let me know how you go about keeping track of your yearly data. What other kinds of things do you keep track of that I may be missing?

Leave a Reply

Close Menu