Regular Processing of Small Batch Data

Regular Processing of Small Batch Data (a.k.a. Preparing a Yearly Personal Budget)

Hi readers! Here is my process for preparing a yearly personal budget. Money topics are generally overwhelming for a lot of young professionals because of the current state of student debt and job prospects. So, if you're in this camp, please read on! I hope my article helps you to make your own budget or financial goals or at least start down that path.

Problem statement

I want to make sure to balance meeting my financial obligations and setting aside funds for having fun and living. Having a budget and plan is a safety net to help me make decisions in the coming year.

This budget is based on net earnings and doesn't include any retirement savings. I subtract retirement funds out of my net earnings because the easiest way for me to save is to set, forget, and never touch.

Step 1: Get financial details and process my data

I use an online service ( Mint ) to capture all my financial transactions throughout the year and this service conveniently allows me to download financial data collected by the website in a comma separated value (CSV) format.

I'd like to quickly address the issue of trusting all my financial transaction data with a single company. Having an easy way to monitor my financial transactions across different institutions is super helpful for understanding what is going on. But even if you don't consider data breaches ( Equifax 2018 hack ), if you read the fine print of most financial accounts, financial data are already farmed out to other financial institutions and sold quite broadly in the USA. I feel that I can accept the risk of giving one more company access to my transaction data. ADD moment. Do you have any websites or apps to help you keep track of your regular financial transactions? Comment down below.

After downloading some 825+ financial transactions dating back to January 2021, I reformatted and fixed the data before they were ingested into my favorite spreadsheet program.  Most data sets require some level of validation and scrubbing to ensure consistency. In this instance, fixing the data involved opening the CSV file in a plain text editor, removing errant newlines, reformatting the dates, and ensuring that each line item was assigned an appropriate category. It was sometimes necessary to add columns to accommodate a general category, whether to filter the data item, or one to keep track of notes. I did the category assignment and column additions after importing the data into the spreadsheet.

I found the following general categories helpful when preparing my personal budget:

1. Accidents - anything that I forgot to account for in the prior year
2. Food - includes what I get at the grocery store and when I eat out
3. Gifts and Charitable Contributions
4. Health - includes any physical or mental wellness items
5. Hobby and Entertainment
6. Home Improvement - for projects
7. Home Regular - utilities, rent/mortgage, household operations, laundry etc.
8. Other Debt - any credit card transaction fees or interest payments go here
9. Personal Obligations - I'm married and count those responsibilities here
10. Professional/Education - any furthering of my education, professional society dues etc.
11. Savings
12. Student Loans
13. Transportation and Travel - car expenses, transit fare, etc., excluding vacation travel
14. Vacation

Step 2: Create the Budget

Now that I have the data validated, I'm ready to shape and re-shape the data to give me the answers I desire! As it turns out, a critical skill I learned in engineering school, was the power of using a spreadsheet to manipulate data to give me answers. Microsoft Excel was the spreadsheet program of choice in school and I continue to use it because it is the devil I know. There is also a great open-source alternative for those looking for a spreadsheet program on a budget ( https://www.libreoffice.org ).

After opening the data into the spreadsheet, I filter out errant and out-of-scope line items.

Next, I pivot the data on the year and then general categories in the rows, transaction type (credit/debt) in the columns, and the sum of the transaction amounts in the values (see image on the right).

I then calculate and assign a percentage of my expected net income to each general category (the percent is based on net earnings and must add up to 100%). I have another column where I multiply the percentage with my expected total net earning for the upcoming year to see my budget for that category. Finding the right percentage is an iterative process and I adjust until I intuitively feel that the percentage and budgeted amount is reasonable.

Posted on the right, are my targets for 2022. For illustration, I've included the dollar amount assuming the average Pennsylvania gross income of $51,340 per year and net of $29,700.

I'll leave this soap-box comment here. I realized that financial budgets don't scale down very well and in some categories just flat out don't make sense. It would feel like a sacrifice to live on $5 a day for food and only have $630 a month budgeted for rent and upkeep. Also, a budget of only $4.7K per year for student loans is very unrealistic. These loans can easily require minimum repayments of over $10K a year without a special repayment plan. If I were earning less than I do now, I'd have to spend a much larger percentage of my net income towards such debt items and reduce my spending on "nice-to-have" items like eating out, vacations, hobbies, and gifts. I'll count my blessings and feel thankful that I work for a company that pays me what I consider is appropriate for the job and skills that I offer.

All that is left to do is be a responsible adult and execute to my plan - that is, use the tool I've created. I'm happy knowing that I can budget for a vacation throughout the year while meeting my obligations. More so, I had a lot of fun playing with spreadsheets and formatting data.

Comments and Suggestions

Thank you for taking the time to read the results of a late night and morning effort towards preparing my personal budget for the year!

Is budgeting so frustrating that you gave up on trying to find a solution a long time ago, do you think another approach, or other percentages would work out better; or maybe you have a favorite app that automates this works for you?

Please comment and share and if you can, take the "reaction" poll posted below!

Some food for thought: looking at what the average salaries are in the USA it is very sobering to think about how many people in our country are supposed to properly budget and support a healthy and happy household when average wages are not living wages.

Last, but not least, thank you to all of my friends who have submitted feedback to improve this article!

Disclaimer

My approach uses the Mint online service through Intuit, and Microsoft Excel. All product names, logos, and brands are property of their respective owners. All company, product and service names used in this article are for identification purposes only. Use of these names, logos, and brands does not imply endorsement and I wasn't paid to include any brand names.

Link to poll