Excel: Finance charting
Monday, June 25, 2012



Okay, you know about how I've been working on charting down all my expenditure? I wrote this about 3 weeks ago. Since then I've looked through my chart and realized there's a better way to visualize the data.

Before I continue, let me say that this is not a pointless post. 

Because I made this for you guys.
Finance.xlsx

I made it using Microsoft Excel 2010. I'm not sure if there are any compatibility issues but it should at least work with 2007 version.

Let me show you how it looks like when you have data in it.



Of course I just punched in random numbers to show you how it works.

All you need to do is to type in your monthly budget, and it will calculate the rest of the stuffs for you. Of course, you will need to type in your daily spending for this to work/make sense.

When you overspend your daily budget, it will turn red. Making it easier for you to spot which days you spend more on. I also included weekly total and average spending. Then amount saved after that.

Beside Total and Saved, I added a change in %. The way it calculates is that it compares the previous week to the current week. The change in amount you save/spend will be represented in % that will change to green or red depending on whether you save or spend more.

Under the Summary tab below, you can find graphs for weekly average/total. Along with total monthly spending and saving.

I started from May to December. Because well, I'll do the full year next year, there just isn't a point for me to do it right now. On that regard, I'm sorry. Though it's really not that hard to extend it yourself when you need to next time.

There's a couple of weird quirks with the file I made.
1. The graph will plot in the $0. I don't know how to remove it. But I made the graph just to give a visual representation and not something that you can get fine details from.
2. When change in % is too huge, it will appear something like ######. Nothing much, it's just that there isn't enough space for it to display the number. Simply widen the cell and you can see the number again.

Apart from that, this is a very simple and efficient finance charting/planning excel file for students.

This is my first time doing such extensive excel stuffs. I'm sure I got all the formulas down correctly so there shouldn't be any problems. But man, this is pretty hard work. Either way, I'm quite happy with it as I've manged to learn the basics of excel.

Anyway, give it a try and see if you like it. 

This is the Lord, signing off! 

Labels: , , , , , , , ,