Data Analysis Project : Tracking Income and Expenditure Using Excel
I will be entering the workforce at the end of the year, so I began tracking my income and expenses from July 2022 to April 2023 using the Money Manager application in order to know how much I earn and how much did I spend and make appropriate adjusments based on the information that I have acquired. To streamline my data analysis process, I utilized the app’s feature to export data to an Excel file.
The workbook contained 12 columns, including Date, Account, Category, Subcategory, Note1, MYR, Income/Expense, Note2, Amount1, Currency, and Amount2.
To enhance the clarity of the data, I separated the Date column into three separate columns — Day, Month, and Year — using formulas such as =Day() for Day, =Month() for Month, and =Year() for Year.
Next, I applied my basic knowledge in ETL (Extract, Transform, Load) using Power Query Editor to perform data cleansing on the table. I identified redundant columns, such as Account, Date, Subcategory, Note1, MYR, Note2, Currency, Day, and Amount2, and removed them from the table.
Afterwards i close the editor and load the new table into a new worksheet.
I used my skills in data analysis to create five charts using Pivot Table and Pivot Charts. These charts showed Income & Expenditure per Month, Amount per Category, Amount per Year, Income & Expense Percentage, and Expenditure Percentage per Category.
Through my meticulous approach to data recording and analysis, I was able to generate valuable insights that informed my personal financial decisions.
According to the graphs shown above, for income I have earned a total of RM7258 throughout these 10 months that I started recorded, with the highest being in the month of September 2022, where I had earn RM1950. While for expenditure, a total of RM5611.13 has been spent in those 10 months, with the highest spending is on December 2022, which is RM1075.41.
In correlation with the income and expenditure per month graph, there is a nett of 12% from the income that I have used it for my expenditure throughout the duration of the tracking of the ins and outs of my money.
According to the chart above, RM3319.22 are spent in the year 2022 while the remaining of the total expense is on 2023.
Afterwards I breakdown the expenditures according to the following categories, which is Meals, Transportation, Other, Health, Game and Credit Topup. Meals (which include drinks) makes the bulk of the total expenses, taking up RM2631.25 or 47% of the total, followed by Credit Topup (RM776.34 or 14%), Other (RM736.49 or 13%), Health (RM696.45 or 12%), Game (RM550.20 or 10%) and finally Transportation (RM220.50 or 4%). If we further breakdown the expenses into per month average, the table below shows the results, and I want to know how much is the average monthly expenses and it amounts to RM561.11 or RM625 monthly on average since I want to overestimate the expenditure costs so that I can have more room to wiggle around the leftover from the monthly expenses to other things.
Although the expenditure doesn’t take in other expenses such as rent cost, savings, and student loan payment and also the fact that I didnt have my first job yet as a graduate student, at the very least I need to have RM625 in order to cover the expenses of the things that I have already aware of. With this analysis, it helps me to plan and prepare myself for getting a job that covers the initial expenditure plus other things and to be able to save a portion of my income after deducting the expenses for a rainy day.
