These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, April 14, 2015

Final Project: Family Income and Expense Report

Executive Summary

Project Description: For the final project I would like create a budget builder and expense tracker for me and fiancée. For the past few year we have been supported by our family. We believe it is the time that we have to take upon our own financial responsibility and the first thing we decide to do is to create a budget builder and expense tracker.
Keeping a budget is something that we take very seriously. We try to keep track of where I am spending money and for areas where I can save. In the past, I would spend three to four hours a month manually entering each transaction into each designated category. Needless to say this process is tedious and frustrating.

The Problem: My fiancée and I need a spreadsheet that allow us to keep track of our budget, actual income and expenses. Also, we would want spreadsheet to automatically generate reports that can allow us to compare the estimated and actual income.

The solution: For my final project, I wrote several macros and created new worksheets and tables that automate all of the above listed tasks. This is broken down into the following steps:
1.      I created a budget form that allow users to enter the beginning date, ending date, estimated income, and estimated expense. After entering the information, the budget form will automatically generate the estimated saving

2.      I created an actual cash flow form that allow users to enter the transaction date, amount, description, category, and users/earner for the cash flow. User can change the category manually by editing the category spreadsheet.

3.      I wrote marcos that allow users to input their expenses on credit card automatically. The user only need to download the expense CSV file and click the button. Then the marcos will automatically write all the transaction into the actual expense workbook. The marcos will also update the expense category sheet.

4.      The final marcos that I wrote will generate a monthly statement for the specific date range you selected. The marcos will first prompt you to enter the beginning date and ending date, then it will generate (a) a report and a chart that compares the actual and estimated income/expenses, (b) a pivot table and a pivot chart that categorize the expenses spent in the period, and (c) a pivot table and a pivot chart that categorize the income earned in the period.

5.      I created a tab on the ribbon with buttons for each of the above macros.

No comments:

Post a Comment

Blog Archive