BY PETER THAMIN

COURSE DESCRIPTION

Are you tired of not knowing where your money is going towards?  Do you want to better understand your expenditures so you can save for a car or end of year holiday?  Building this expense tracking template is the first step to understand how you are spending your money.

This free course will help to demonstrate that you can use Excel which is a simple, basic and widely available tool to automatically capture your spending obligations and habits by Categories and Sub-categories and the information you have, will help you to be in better control of your finances so you can feel a little more organised with your life along the way.

I encourage you to check out the other 4 courses so you can end up building your own Personal or Home Budgeting Model.

• Intermediate Excel skills
• Can do attitude
• 1 to 2 hours of your time to build this template
• Discipline in keeping invoices and receipts to enter expenditure values

• How to use Excel’s data validation (list criteria) feature

Hopefully, you would have upgraded your Excel to 2016 by now.  If not, the steps presented below will work on other Excel versions.

The internet is a good place to get ideas on what Expense Categories and Sub-categories are relevant to yourself or your household.

I have selected 8 Categories.  Remember, it doesn’t have to be perfect and these can be updated later with relative ease as Excel has that flexibility.

1. Home (Council Rates, Furniture and Tools, Home Insurance, Mortgage or Rent, Renovations, Repairs and Maintenance)
2. Transportation (Car Insurance and Rego, Car Loan, Parking and Toll, Petrol, Public and Other Transport)
3. Food (Coffee /Alcohol, Groceries, Restaurants / Fast Food, Snacks)
4. Entertainment (Hobbies, Other Entertainment, Subscriptions, Toys, Vacation)
5. Health and Medical (Dental, Fitness, Health Insurance, Life Insurance, Medicine, Optometrist)
6. Personal (Clothing, Cosmetics, Other Personal Care)
7. Family Obligations (Childcare, Lessons, School Supplies, Textbooks, Tuition)
8. Miscellaneous (Gifts / Donations, Other Miscellaneous)

A “Validation List” worksheet is required to make tracking expenses more user-friendly.  It needs to include:

• 8 Categories with its corresponding Sub-categories.  This is sorted in alphabetical order (blue background headings in columns B to I)
• Month drop-down list from 1 to 12 (orange background headings in column B)
• Day drop-down list from 1 to 31 (orange background headings in column C)
• Year drop-down list from 2017 to 2020 (orange background headings in column D), and
• Leave the first cell empty under each heading

Please refer to image above for cell references.  For example, data validation for Home list is cells B3:B9 (empty cell is to be included).

The “Validation List” worksheet will be hidden once the model is complete.

6 columns where 1 heading is equivalent to 1 column is required to create the Home Category.  Below are steps on how to build the Home worksheet

1. The first 4 columns consists of using data validation features to create headings for Sub-category, Year, Month and Day
2. The next 2 columns will capture Amount and Description which requires only formatting
3. Create a few testing examples which will be helpful later when we build the Summary worksheet, and
4. Create a formula-driven column to sum up data in the Summary worksheet

• Insert HOME in cell B2 to represent Home Category
• Insert Sub-category as a heading in cell B3
• Select cell B4 > Data tab > Data Tools group > Data Validation
• On the Settings tab > select List > link Source to =’Validation List’!$B$3:$B$9

The end result will give the user a nice drop-down list of Home sub-category expenses.

Make sure the blank list is selected and copy cell to about row 100 and this should be enough transactions to last for 12 months.  Doing this will allow Excel to retain the drop-down list data.

• Insert Year as a heading in cell C3
• Select cell C4 > Data tab > Data Tools group > Data Validation
• On the Settings tab > select List > link Source to =’Validation List’!$D$12:$D$16
• Copy cell to row 100

• Insert Month as a heading in cell D3
• Select cell D4 > Data tab > Data Tools group > Data Validation
• On the Settings tab > select List > link Source to =’Validation List’!$B$12:$B$24
• Copy cell to row 100

• Insert Day as a heading in cell E3
• Select cell E4 > Data tab > Data Tools group > Data Validation
• On the Settings tab > select List > link Source to =’Validation List’!$C$12:$C$43
• Copy cell to row 100

The amount column is where you insert the transaction amount. It is always important to have the correct formatting at all times to create that professional look and feel.

• Insert Amount as a heading in cell F3
• Select column F > go to Home tab > select $ Accounting Number Format in the Number group

The description column will be free text, providing further information on transaction details, if required.  Simply insert Description in cell G3.

The above is a list of an oversimplified home transaction mirroring the life of a bachelor in his mid 30’s, sharing with a flatmate and earning a Gross income of $80,000 a year, living in Sydney.  These transactions will be useful when summarising total expenditure.

The purpose of drop-down list is to achieve a better user experience.  But in terms of automation, it is a nightmare.  To overcome this, column A can be utilised to insert an invisible formula so as to make summing up of data possible.  The steps are as follows:

• Use DATE Function
• Syntax: DATE(year,month,day)
• In cell A4 insert =DATE(C4,D4,E4)

• Use TEXT Function
• Syntax: TEXT(value,format_text)
• In cell A4 insert =TEXT(DATE(C4,D4,E4),”MMM-YY”), where value=DATE Function

Formulas in cell A4 needs to be copied all the way through to row 100.  Problem will start to arise in row 20 onwards.  Since there are no values, the cell will return #NUM!

To overcome this is quite simple

• Use IFERROR Function
• Syntax: IFERROR(value, value_if_error)
• In cell A4 insert =IFERROR(TEXT(DATE(C4,D4,E4),”MMM-YY”),””), where value=TEXT(DATE(C4,D4,E4),”MMM-YY”) Formula above and value_if_error=””(equivalent to blank)
• Lastly, make sure to select white colour font to make the formula invisible