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