Hi Leo-Finance community,
in my previous post I have elaborated how to create a personal balance sheet.
Well as the balance sheet is only a snapshot of a certain point in time regarding your personal finances and the status of such ... let's have a look at a simple possibility of using Microsoft-Excel/Google-Sheets to track the flow of your capital.
First of all this method is applicable for any currency or unit of measure.
The basic idea is to have a master table in place, where a pivot-table sits on top. By assigning standardized categories on several levels to every single row/transaction you will have the possibility to aggregate any number of transactions and see a sum on each level per month and year.
I personally do a download of all movements on my main bank account and copy the data into the master table:
As you can see I have more than 7.000 rows entered to this sheet ... dating back to when I started using this method in 2010 :)
I decided to use the following categories:
Level 1:
Earnings, Fixed costs, Investments, variable costs
(Einnahmen, Fixkosten, Investments, variable Kosten ... in German)
Level 2:
some more details on this level.
All earnings are assigned to where the earning is coming from.
All fixed costs and investments are precicly categorized based on where the money is flowing.
All variable costs are categorized by "Luxus" (expenses which could have been avoided), "Sonderausgabe" (non-planned expenses which could not have been avoided) and "Sondereinnahme" (non-planned earnings)
Level 3:
for even more detail.
I mainly use it to assign "person responsible" so to see who inside my family caused the expense for "Luxus" and "Sonderausgabe" ...
With this structure you can create a pivot table to see movements on monthly and yearl basis easily.
Monthly view for 2020:
Yearly view since 2010:
Well, obviously this is quite some manual work, but never the less once you have the standardized structure and categories established it is very helpful to have the pivot tables in place to manage your personal-finances.
I update this table once a week in order to see IN TIME and even AHEAD OF TIME where my money is flowing to be prepared and not spend more than I earn.
More details on how to create personal-finance calculations on top of this method will follow in my next posts.
Would love to read your feedback!
Stay safe!