At some point one you will want to keep track of your expenses to gain insight where you can save money. There are many websites and programs which offer insight into your expenses, for instance, YouNeedABudget, MoneyDashboard, GNUCash. However, for following three main reasons I decided to simply create my own expenses spreadsheet using Microsoft Excel.
- The programs often have a learning curve and you have to adapt to their way of working.
- I wanted to use a tool of which I can be almost certain that it will still exist in +20 years.
- Sometimes you will find yourself in the spot in which you want to have insight into some details. But, unfortunately these are not (directly) provided by the tool. Therefor I wanted to be able to program myself to gain insight. However, this still should be fairly simple to do.
Now let me tell you what my spreadsheet provides. Since I wanted to categorize my expenses I created one sheet with categories and subcategories. To keep track of all the transactions, a table was created in which all main transactions details are logged. The table has the following fields: bank account, type of account (saving or payment?), date, type (income or expense?), amount, currency, kind (variabel or fixed?), category, subcategory and remarks. While there is a currency field, it is not used yet. The with main reason for this is because fortunately I do not have to deal with money different currency types. To gain insight into the data from the table, three pivot tables and charts were created. Using the pivot tables and charts, which are semi-interactive, one is able to, for instance, zoom-in in a particular category or time stamp. To finalize, I created a kind of dashboard in which all income and expenses data is summarized per month. First details are displayed about my balance of my payment and savings account. Followed by all the expenses details, sorted by category. Using the outline group functionality one is able to open the category and list the subcategories for that month. For each category and subcategory I show its portion (in percentage) with respect to all expenses, the median per month and the average per month. Using conditional formatting this data is also visualized to give a overview, such that you are able to identify the ratios and relationships quickly. sparklines are being used to show the trend of the expenses through out the year. Concluding, the dashboard sheet makes sure that all the data and details are visible at one glance but still manages to dive into details.
You can download an example with dummy data of the Excel sheet over here: Financien (example)