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

Thursday, December 11, 2014

Final Project: Family Cash Flow/Budget

Final Project: Family Cash Flow/Budget

Executive Summary

Family Cash Flow description: This last summer my wife and I found ourselves in a situation in which we needed a VERY specific cash flow/budget to see how much money we needed to make and budget in order to make ends meet. Between graduate tuition and having our first baby this year, we need a lot of cash on hand at very specific moments, and yet we have lower-income paying jobs that pay us bi-weekly. As I designed a cash flow to meet our needs, I discovered that the most accurate cash flow system was to show how much cash we have on hand every pay period, but this is complicated by the fact that many cash outflows, or expenses don’t occur in even number of weeks, but rather by month (like rent). So I created a rather complicated cash flow/budget that accounts for which months have three rather than two pay periods and accurately shows how much cash we have on hand and will need for 12 months out.

The Problem: While accurate and necessary, our cash flow budget take a long time to update due to the large number of manual tasks, such as figuring out which pay periods have monthly expenses, since they do not all fall evenly. Also, if there were significant changes to our estimates of monthly expenses, this is time-consuming to update and easy to mess up.

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 macro that updates today’s date, the last pay-date, deletes old columns, creates new columns for at least a year out, enters in needed formulas, and reformats the spreadsheet as needed.
2.       I created separate worksheets to allow the user to know exactly where to ender the following data: Income estimates, Investment income/liquidations, Recurring expenses (fixed and variable), one-time expenses, savings, and current bank balances.
3.       I wrote macros for each of these worksheets that deletes out old cash flow/budget information, reads the new or updated information in each worksheet, and enters it into the appropriate row and column. This includes reading dates and deciding by logic which columns to enter the data into depending on a specific date, or if it is a monthly or bi-weekly cash flow.
4.       I created a macro that assists the user in entering in current bank account and credit card information to determine how much cash we have on hand. This assists the user in entering the information without error while looking at other sources for the data.
5.       Lastly and most importantly, I created a ribbon with buttons for each of the above macros that will allow my wife to easily and simply update the cash flow/budget too so she is not dependent on me to determine if an expenditure is within our budget.

No comments:

Post a Comment

Blog Archive