With the growing popularity of software services such as Mint, Personal Capital, YNAB, etc., automatically tracking expenses blatantly has its benefits. However, some drawbacks we trade for convenience include lack of easy customization and a reduced pain felt with purchases that do not bring value into our lives. These easy to use software programs remove ourselves from purchases we otherwise would have deemed extraneous, as Adam Carroll depicts in the TEDx talk, “When Money isn’t Real”. The pain of entering each and every one of our purchases into the spreadsheet can snap us out of a consumerist mindset, potentially adding another layer of defense against impulse purchases. Visualizing expenses categorized in larger buckets and its effects over a long period of time can be quite eye-opening. Find out how many years of saving it takes to reach financial independence in Supply Chen Management’s Savings Rate Crash Course!
According to the financial priorities pyramid, budgeting is the second step after the first step which is setting your goal. Being conscious of where money is fleeting from is important. Connecting with each individual purchase and realizing what was and what was not worth it is imperative to developing financial discipline. Think of this as sort of a food journal for money, with a much more powerful analytical potential.
Here are screenshots of the template:
The first worksheet tracks all income and expenses, displayed in a proper table format, ready for analysis. A few examples have already been entered in:
Automatically Calculated Columns:
A) Entry Number
F) Entry Date
G) Actual Amount
Manual Input Required Columns:
B) Entry Title
C) Income/Expense (Data Validated)
D) Transaction Amount (Enter Absolute Value)
E) Category (Data Validated, unhide worksheet to add more categories)
Pivots Income & Expenses Worksheet
The second worksheet shows income and expenses in pivot tables, updating as more transactions are added to the Transactions worksheet.
To ensure a smooth adoption of the template, there’s a few checks to make after entering a few transactions:
- Ensure “Income” is checked off on the left pivot table, and “Expense” is checked off on the right pivot table (In red)
- Ensure all categories excluding (blank) are checked off (In green)
- To refresh a pivot table without VBA, right click on the pivot table you wish up update and click “Refresh”. The information from the “Transactions” worksheet feeds the pivot tables. OR to refresh, click on the Data Tab -> Refresh All
- To dive deeper into a category, click the “+” to see the Entry Title and Entry Date for each Entry Title
If you’d like a copy of this template with VBA (Refresh Button to eliminate the need of #3), please contact me here. I will email a copy of a macro-enabled workbook (Excel Online does unfortunately not support VBA).
The button will look like:
There may be a learning curve at first to familiarize yourself with the workbook, but as you use it more and more, the capabilities of this workbook will bring visibility to what’s going on with your incoming and outbound cash! The potential is endless, from creating PivotCharts, to changing the PivotTable to dive into something else, to customizing Income and Expense buckets!
**UPDATE** – Please make sure Enable Iterative Calculation is checked and maximum iterations is set to 1 to do this: File -> Options -> Formulas -> Enable Iterative Calculation checked -> Maximum Iterations = 1
Best of luck and have fun playing with the workbook! Please do not hesitate to drop me an email. What do you like to use to manage your personal finances? Comment below!