This knol describes a powerful, friendly spreadsheet tool that will allow you to project into the future and identify times when cash flow will be a problem, giving you time to take steps to minimize hardship.
This tool is a:
- checkbook balancer
- budget planner/tracker
- cash flow forecaster
“Oh, it’s just a spreadsheet…”
Before you dismiss this tool, hear me out. Sure, you can use any one of dozens of “checkbook balancing” programs. The problem is that they are database-based, using entry forms, requiring lots of initial setup. These programs allow you to do much more than this tool, but require a lot of weekly (not daily) work. They are designed for people that can afford to ignore the bank balance most of the time.
This tool is better for those in armed combat with their finances. With the least possible amount of effort (albeit consistent daily use), this tool tells you at a glance what you most need to know, and using the strength of spreadsheets, allows you to “play” with options and instantly see the results.
Since it is a spreadsheet, you can modify it to suit your specific needs. Only a basic familiarity with using a spreadsheet is required to use it “as is”.
Start by downloading a sample spreadsheet from here
- At the very top is the “beginning of the day” bank balance and the very bottom is the “end of day” bank balance.
- In between, the tool is divided vertically into sections for expenses (Bills, Misc, Daily Discretionary, Checks) and incomes.
- The Misc section is for “fuzzy” expenses (clothes, car repairs / mantenence, child care, hair cuts, birthdays, holidays, other gifts, vacations, prom dresses, etc).
- Unpredictable expenses (emergency medical, speeding tickets, vet bills, root canals, etc.) need to be handled by a reserve fund (savings, lines of credit, borrow from daddy?).
- The Daily Discretionary is your “allowance”, the amount you can spend on gas, groceries, entertainment, etc.
- log on to your online bank account, and then adjust the spreadsheet data:
- adjust the beginning bank balance to the actual balance
- delete entries that have posted to your bank account
- move entries to the right that haven’t posted yet (as previously predicted)
- delete the column for the previous day (“water under the bridge”, move on…)
- add new checks and other debits. If an upper expense is paid with a check, move the amount down into a check entry
- scan ahead, double check and adjust amounts and dates of any entries that need it
- scan ahead to see how the new data affects future cash flow
- save and backup
Enter incomes as negative numbers, and read the bank balances the same way. This is done to avoid having to enter a minus sign on every expense.
Tips / Suggestions
- For charges that aren’t committed yet (eg a bill that you haven’t put in the mail or submitted online), make the text color blue, and when the funds become committed, change it to black
- If you need to be able to work on this every day from more than one location, use a password protected memory stick (“jump drive”).
- Many payments have a delay between payment and the time your account is debited. Put the amount in the debit date and fill the cell for the payment date with a yellow fill. This will remind you to make the payment in time, but more closely reflect your actual cashflow.
- Make this part of your breakfast routine. You need to know what the day holds and you want to address stressful things like finances when you are the most rested, not just before you try to go to sleep. You probably check your email every morning, start an extra 15 minutes earlier and get your day off to a proactive start.
- Put expenses in the earliest possible date that they might post to your account.
- Put incomes in the latest possible date that they would be available for deposit.
Customizing the sample spreadsheet for your use / initial data entry
- Write a list of all the bills you pay with dates due.
- Do the same with your paychecks and other income.
- Create labels for each in the left column, preferably in the order in which they occur (in case you need to scroll, they’ll be near each other).
- Put the current date in the first column. Put tomorrow’s date in the second column. Fill right as far as you can.
- Put entries for each expense and income in the appropriate cells for the next year.
- SAVE the file!
- Decide how much you can spend every day, that isn’t included in the specific Expenses rows and enter that amount in the leftmost “Daily Discretionary” cell. Fill right as far as you can.
- Put this morning’s bank balance in the appropriate row and column. All your ending balances for the next year will automatically be calculated.
- SAVE the file!
- Study the spreadsheet, noting your projected cashflow over time.
- Study the spreadsheet and fine tune the data, adding the missing expenses that are always overlooked. Don’t forget to budget in the Misc section.
(These are based on Microsoft Exel)
- “Fill right” – Select the cells, with the desired value in the leftmost cell: <CRTL> R
- “Fill down” – Select the cells, with the desired value in the uuppermost cell: <CRTL> D
- apply fill color & text color
- putting lines around cell borders
- fill in data based on adjacent cells – By dragging the fill handle of a cell, you can copy that cell to other cells in the same row or column. If the cell contains a date, or a check number that Microsoft Excel can extend in a series, the values are incremented instead of copied. Select two adjacent cells which have data in them. Note the little black “fill handle” in the bottom right of the selected cells. Carefully grab it and drag the selection to automatically fill in the desired data.
(t(this knol is still under construction)