Tracking home budget

More than a year ago I noticed that my money is missing! I never doubted members of my family, but I started to check ATM transactions. Problem is that, most of the time, I take same amounts of cash. And since I am not very penny-wise it was very hard for me to „connect the dots“. Sounds familiar? That’s why I decided to end this phenomenon and started to track family’s incomes & expenses. I created „Simple Home Budget“ which helped me to track money transactions…

At my humble opinion, „Simple Home Budget“ is a stage play performed by several living and one dead actor. Living actors are Cash Register (the place where we keep the money) and other registers which are, to simplify things, „wallets“ of my family’s members. „The dead actor“ is my savings, so I would rather not talk about it. Shoosh! Living actors, from time to time, receive some money or give it away. When they give money the reasons (EXPENSES) are: food&drink, clothes&shoes, hygiene, medical expenses, rent, hydro, phone, Internet etc. When they receive the money reasons (INCOMES) are: salary, gift, other incomes etc. Both expenses and incomes have one specific category which I called TRANSFER. Transfer is what happens each time when „actors“ exchange the money.

At the very beginning let’s make three worksheets: Code Lists, Transactions and Analysis. The first worksheet should contain a table with three columns named: OWNER, EXPENSES and INCOMES.

Further, in Transactions worksheet we should make a table with following columns:

• DATE (Date of transaction)
• MONTH (Month, derived from previous column by using MONTH Excel function)
• OWNER (Cash register or other cash owner that we choose from the list)
• EXPENSE (Decimal number)
• EXPENSE CATEGORY (Category of expenses that we choose from the list)
• INCOME (Decimal number)
• INCOME CATEGORY (Category of incomes that we choose from the list)
• REMARK (A simple note that holds more information about incomes & expenses)

Entering values in a table is a simple task. To make it more simple, for columns OWNER, EXPENSE CATEGORY and INCOME CATEGORY we’ll use Excel option which enables choosing categories from a list. Let’s start with the owner. Select a cell C2 (right below OWNER column header), and then go to Data ribbon and choose the Data Validation option from the homonymous menu. The dialog box will appear in which you should choose option List from Allow combo box. Right below, another box will appear in which is necessary to enter the range of cells that contain values of owners. In our example it is range ‘Code List’!$A$2:$A$6. We’ll repeat the process for expense and income categories and we won’t have furthermore to enter these categories manually, we’ll chose them from the list.

Next, it is necessary to transform data table to structured table. We’ll perform that action by choosing entire table (CTRL+A) and by pressing CTRL+T afterwards. The dialog box will appear in which is necessary to check the option My Data has headers. After clicking the OK button, structured table is created. Now we’ll populate transactions table by entering some values.

After some time we can analyze our home budget. What’s important? Most of all it’s necessary to know who owns which amount of money. Next we’ll analyze our expenditures by categories, to see where we spend the most and what action should we take to save money. We could also analyze incomes, but in this example we’ll focus on expenses.

How do we calculate who holds which amount of money? Total is always a difference between receiving and giving. The easiest way to calculate a total for each owner is to use SUMIF function by entering a formula:

=SUMIF(Table1[OWNER],A4,Table1[INCOME])-SUMIF(Table1[OWNER],A4,Table1[EXPENSE])

Table1 is a name of a structured table we created and it can be changed to more appropriate name. Second argument in every SUMIF formula is a condition which is, in our example, name of the owner. By copying formula in the cells below we’ll get totals for all owners.

To analyze expenses we’ll use SUMIFS function. Its first argument is a range of cells to sum (EXPENSES). Then we specify the conditions by entering ranges of cells for evaluation followed by conditions, which are values in Analysis worksheet. In our example we’ll examine expense categories and months. We enter the formula:

=SUMIFS(Table1[EXPENSE],Table1[EXPENSE CATEGORY],D4,Table1[MONTH],$B$1)

After entering first formula we can copy it below and we’ll get amounts for all expenditure categories in current month. At the end we can calculate a total, as a sum of all expenses, and be able to see how much did we spend in current month.

This is a very simple home budget. As you can see, there are no budgeted categories, so we don’t have budgeted/actual/variance columns. Also, there are no charts. You can adapt this example to meet the need of your home or small business.