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