Family Budget System

Version 2.20

© 2001 House of Myrrh

Disclaimer:

While every effort has been made to provide a bug-free tool and good advice,
the user of this spreadsheet and instructions bears sole responsibility for any outcomes whether good or bad.
Neither the author, website, or ISP shall be responsible for any damages incidental, direct or indirect
resulting from the use of this material.

Contents:

1. Purpose & Philosophy:
2. Introduction:
1. What you need
2. What you get
1. OnHold Page
2. Income Page
3. Banking Page(s)
4. Budgeted Item Page(s)
3. Download and Installation
   
3. Setting up a budget: -overview-
   1. Reliable Income
   2. Normal Spending to Budget
   3. Available Funds
   4. Distribute Available Funds per budget
4. Step by Step - The Pages
   1. OnHold Page
   2. Income Page
   3. Banking Page(s)
   4. Budgeted Item Pages
5. Step by Step - How It Works
   1. Bank Debit - Single Budget Item
   2. Bank Debit - Multiple Budget Items
   3. Bank Credit - Planned Income
   4. Bank Credit - Other
6. Step by Step - The Initial Budget
   1. The Spending Plan
   2. Normal Spending to Budget
   3. Distribute Available Funds
7. Misc. Stuff
   1. Adding a 'Budgeted Item' page
   2. Dressing Up the pages.
   3. 'Distribute' Command Button.
   4. Grade Incentive Area.
8. Changes

Section I

Purpose & Philosophy:

The primary purpose of this spreadsheet is to provide a tool that allows you to keep track of your spending and then take control of it. I firmly believe that it is not possible to be a good steward unless you have a plan for your spending.

Stewardship is much more than giving regularly to your favorite charity or church and then spending the rest with gladness!

A good steward plans their spending and then spends their plan. At least to the extent they have the courage and strength to do so.

Why have a budget?

The primary reason for having a budget is stated above as tracking your spending and taking control of it. This has many advantages, some of which are:

1. The ability to pay a bill when it is received knowing that it is covered and will not take away from something else.
2. The ability to tell your children, (or yourself), that you can't buy that now because it's not in the budget. Living on a budget takes you away from whether there is money in the bank, to whether it is budgeted and, therefore available to spend. Just because there is money in the bank doesn't mean it can be spent now.
3. A chance to lay aside for the possibility of emergencies or layoffs. Many financial counselors say that you should have enough money set aside to weather a period of non-income for a period from three to six months!
4. Lastly, it can help wean you from credit card debt.

On credit cards:

Cut them up! They are a snare against sane budget planning. It is just too easy to get them, use them, and then let them drain your life dry by month after month, bleeding you dry with interest upon interest. Sure, the credit card companies are friendly now. But, get sick and not able to work. See what happens when the bills come due. They don't want the product back, no matter how new or well you've taken care of it. They want cash. And, they really don't care if they drive you insane or to suicide.

It may take a while to get a good working budget. for two reasons:

1. Not having enough money already set aside to cover current expenses while the budget takes hold.
2. Not have any idea of what you make, spend, or, where you spend it.

• If children are not taught early, how to use money wisely, they will have trouble with money most of their lives. It also provides a way for them to save for something they want which you don't think they really need, but can't come up with a real reason why they can't have it. It also takes away any reason, and most temptation to steal from mom or dad's money.
  
  My kids get an allowance each week that is entirely gratis (no work involved). It includes their lunch money, and some extra.



• For the adults the discretionary fund is usually a real eye opener. It's not fair to put every one but dad on a budget! That can really mess things up. Of, course sometimes it's mom that causes the pain. But, we won't go there.
  
  This fund gives both spouses a way to spend with out accountability to the other. Which is real nice for surprise gifts or just having coffee with friends. How you use it is up to you.



• Lastly, you must ask for and get receipts for everything, including McDonald's hamburgers. (Which comes out of the entertainment budget.) If a receipt can't be found to 'prove' an expenditure came from a 'Budgeted Item' it comes out of that persons discretionary fund.

Section II

Introduction:

What You Need:

1. A working computer capable of running MS Windows.
2. A copy of MS Excel 5.0 or later. The spreadsheet may, possibly, be imported into other programs.
3. A working knowledge of MS Excel and spreadsheets in general.
4. Be reasonably good at balancing your checkbook.
5. This instruction manual.
6. The accompanying Spreadsheet.

What you get

A tool to help you track and control your spending consisting of:

1. 'OnHold' page - 1 'At-A-Glance' view of your budget situation.
2. 'Income' page - 1 page for calculating total household income.
3. 'Banking' pages - 3 check registers that are tied into the rest of the system.
4. 'Budgeted Item' pages - 34 pages that show the current 'Budgeted Items' balance, budget period, and periodic contribution.

NOTE: It is very important that you make frequent backups of your work!

Where:
• yyyy is the four digit year
• mm is the two digit month
• dd is the two digit day
• x is a sequential letter (a, b, c, etc.) if you do significant work several times a day.

Downloading and Installation

Section III

Setting Up A Budget

1. Reliable Income:
   The first step to making a budget is to figure out what your total net spendable income is. By 'my' definition net spendable income is that amount on your check you actually receive and have direct control over. I do not include over time pay. You can't count on it, even if that doesn't seem to be the case right now. It is better to save any difference between a 40 hour check and over time. Then, when you have enough for that special item, pay cash. But, don't plan to live on the over time portion. The 'Income' page is used to do this.
2. Normal Spending To Budget.
   Track down all the receipts you can find, all your regular bills (including the infrequent ones) and use the 'Budgeted Item' pages to start getting a handle on what you are spending.
3. Available Funds.
   Gather up the last statements from all your banking accounts. Especially the ones you are 'into' regularly. Ones that are 'long term' or infrequent can probably be best served as a 'Budgeted item'. Fill out the 'Banking' pages.
4. Distribute Available Funds per Budget.
   Now distribute your available funds among the 'Budgeted Items'. Infrequently used accounts or long term savings should NOT be used for funds distribution. Place a proportional amount as a 'Balance Forward' item on each 'budgeted items' page. each 'Budgeted Item' page.

Section IV

Step by Step - The Pages

1. 'OnHold' page:
   The first page you see in the spreadsheet is the OnHold page. This is a catch all page that allows an 'at-a-glance' view of the state of the family budget.
   
   I try to always leave the spreadsheet pointing at this page as I usually print this before I take the family shopping. It lists everyone's allowances.
   
   There are only three 'User' entries on the 'OnHold' page: Current Income Source selection; Dad's Cash On Hand; Mom's Cash On Hand.
   
   Column 'Freq' (A) simply shows the desired budget frequency from the 'Income' page.
   
   Column 'Budgeted' (B) shows the budgeted amount for the 'Budgeted Item' from it's page in the spreadsheet.
   
   Column 'Expense Description' (D) shows the 'Title' you give the 'Budgeted Item' page in it's (A1) cell.
   
   Column 'On-Hold' (E) shows the current balance for a particular 'Budgeted Item'.
   
   To the right of the 'On-Hold' column are several areas of interest.
   
   • The 'Income Sources' area shows how many income sources are available to choose from when 'Contributing' to a 'Budgeted Item'. The selection in the 'Current Selection' block is used on each 'Budgeted Item' page to modify the green 'Contribution' amount based on where the income is coming from and it's frequency.
   • There is a listing of the current balance for each of three possible bank accounts. Also shown is a total funds available to the family.
   • The light yellow 'Budget Check Balance' is a calculation that shows the total amount budgeted against the total income. This value is mirrored on each 'Budgeted Item' page and is updated as the amount budgeted on each page is updated.
   • The blue 'On-Hold Check Balance' is a calculation of the 'Total Available' in banks against what you have allocated in the current 'Budgeted Items' balances. This value is mirrored on each 'Budgeted Item' page and is updated as money is deposited, withdrawn, or spent.
   
   
   The budget is considered 'In Balance' when the 'On-Hold Check Balance' is zero, the 'Budget Check Balance' is as close to zero as practical, and, there are no negative balances on any 'Budgeted Item' pages.
   
   As this is a modified double entry system, every time you remove money from a bank account the 'On-Hold Check Balance' will follow it making the budget out of balance until the funds are 'accounted for' through a budgeted item. The reverse is also true, when money is deposited.
   
   
2. 'Income' page:
   The 'Income' page is designed to allow easy computation of your combined incomes.
   
   It also will compute several other values to help make 'Contributing' to 'Budgeted Item' easy and equitable for multiple income families.
   
   Most items are self explanatory.
   Simply give the source of income an identity in the source column, Type in the code letter from the Frequency Code Table, and finally the amount. Presto! You have all valid variations of that income.
   
   Continue this process until all regular and stable sources of income are entered.
   
   Lastly, select a frequency for budget planning. I like to use the most frequent of either income or expenditure.
   
   The percent column is used with the 'Current Selection' on the 'OnHold' page to give a 'fair and equitable' 'Contribution' amount in the green cell on each 'budgeted items' page.
   
   
3. 'Banking' pages:
   Each 'Banking' page has two balances on it. One is the running balance based on your last 'paper' statement. The other is for those that have some form of Internet Access that lets you check for cleared items during a statement period.
   
   This is a combined form. It includes both debits and credits of all forms and types. The single most important thing is to remember to put deductions from your account balance in the Debit column and additions to your account balance in the Deposits column.
   
   Getting your first balance can be tricky, and may actually take two or more statement periods.
   
   The best method is to drag out the last statement and use it as a starting point.
   
   Place the ending balance in the 'Previous Balance' cell, then, add a line for each item that did not show up on that statement.
   
   Once all this is entered save the file. Then save as another name. I usually append a sequential letter after the date. The reason for frequent saves is so that you won't have to re-enter all this stuff again if you get fumble fingers.
   
   If you have on-line access down load all items since your last statement and place a character of your choosing or the date in the 'on-line Cleared' column for each item that shows up on the on-line statement. Both balances should match.
   
   Did you notice the value disappear from the left most column when you entered your special character? This is how the system knows whether to count that value as being cleared on-line or not. I usually assign a different character each time I download an on-line statement.
   
   If the two balances don't match an error will be generated. Errors can be caused when something doesn't get entered. Usually, because an ATM or Debit Card slip got misplaced. It is usually worth looking for, as something may have gotten posted to your account by mistake. While rare, it does happen.
   
   But, if after a thorough search you still can't find the error use the smaller of the two balances as your working balance.
   
   If the smaller balance is on the Statement Side no further action is required at this time.
   
   If the smaller balance is on the On-line side then add an item in the Debit column equal to the amount of the error and then mark it as On-line Cleared.
   
   If there is a second or third bank account, fill in the data for those as well.
   
   If there are more than three bank accounts you will have to add another bank page, or, if it is used very infrequently use a 'Budgeted Item' page for it.
   
   When your next statement comes, backup your work to a new file, and delete the data in the lines that appear on that statement, copy up the remaining line data, delete any duplicates, update the Previous Balance cell and you 'should' have the same 'Current Balance' as you did before.
   
   
4. 'Budgeted Items' pages:
   
   
   In addition to the transaction entry area there are two gray user entry boxes.
   
   • The amount you plan to spend periodically on this item.
   • The frequency code applicable.
   Examples:
   
   1. If your son gets his allowance once a week then you would use the 'W' code.
   2. If you are on a monthly expense page then you would use the 'M' code to budget based on true months, or the 'L' code to force all months to a four week period.
   
   
   Other Items of Interest Are:
   
   • Green 'Contribution' cell. Displays a suggested 'contribution' amount based on the 'Current Selection', (from the 'On-Hold' page).
   • Blue 'On-Hold' cell. Displays the current 'On-Hold Check Balance', which is used to remember how much and what sign to use when making an entry on a given page.
   • Light Yellow 'Budget' cell. Displays the current 'Budget Check Balance', which is used to see what is available to distribute from the available planned and budgeted income during the budget planning stage.
   • An 'Annual' cell to let you see what you are budgeted annually.
   • A 'Partial Amount' scratch pad consisting of gray amount and tax rate entry cells, and tax and total display cells. This is used when several 'budgeted items' are on one sales slip.
     
     Add the amounts in place, in the top cell, place the tax rate in the adjoining cell and viola! you have the tax amount and total to deduct from that budgeted item.
     
     As you deduct these 'partial' values from various 'budgeted items' the 'OnHold' amount decreases. The final value is what needs to be deducted from the final 'Budgeted Item' on that receipt.
   • On the allowance pages: a 'Pre-Expended' scratchpad. This is used when you don't want to put all of a persons allowance 'on budget' at one time. For example a weekly allowance but you get paid monthly. The Pre-expended amount shows up on the bottom entry on the 'OnHold' sheet. Remember if you 'Pre-Ex' something to decrease the count as you use it up.

Section V

Step by Step - How It Works

This section assumes that you are starting with the 'OnHold' check balance at Zero.

1. Bank Debit - Single Budget Item:
   Whether a Check, Debit Card, or ATM transaction when money comes out of a bank account it's a debit and has to be accounted for from a 'Budgeted Item'.
   1. Fill In: Date, Time, Transaction/Check Number, Description/Type, and put the Amount in the debit column.
   2. On the 'On Hold' page the 'OnHold' check balance should now show a negative balance.
   3. Select the page that matches the expenditure. Ex. Housing if your debit is to pay the rent or make a mortgage payment. Enter the amount and sign from the Blue 'OnHold' cell. (In this case a negative number.) The 'OnHold' check balance should now be zero, again.
2. Bank Debit - Multiple Budget Items:
   1. Fill In: Date, Time, Transaction/Check Number, Description/Type, and put the Amount in the debit column. As an example we will use a department store debit card purchase of $99.76
   2. On the 'On Hold' page the 'OnHold' check balance should now show a negative $99.76 as a balance.
   3. On this purchase you bought a dress for your daughter for $12.50, six cans of cat food for $.94 each, and a can of freon for the car at $2.57, the rest was food.
      1. On the Girl's Clothing page enter $12.50 in the 'Partial' 'Amount' cell. For a 6% tax rate you should get a total of $13.25. Enter a line item for the dress with a negative $13.25. Remember, the 'OnHold' balance is negative so we need to enter negative values in the 'budgeted items' to bring the 'On-Hold' balance toward zero.
      2. On the Pets page, in the partial amount cell, enter +6*.94 and press enter. Now you have a total of $5.98. Make a negative line item entry for that amount.
      3. On the car repairs page enter $2.57 in the partial amount cell and the total should be $2.72
   4. This leaves a balance of negative $77.81, as shown in the blue 'On-Hold' cell, to be entered on the food page to bring the 'OnHold' check balance back to zero.
3. Bank Credit - Planned Income:
   1. Enter the Deposited Amount in the applicable Bank Account's Deposit Column.
   2. The 'OnHold' check balance should now reflect the deposit entry amount as a positive value.
   3. In the 'Current Selection' cell on the 'OnHold' page enter the number that matches the Income Source.
   4. Now walk through each 'Budgeted Item' page and create a positive line item entry in the amount of the Green 'Contribution' cell. (OR, try the new 'Distribute' Command Button.) Repeat on each page until the funds are depleted (OnHold check balance is zero) or, you run out of pages.
   5. If you run out of pages before you run out of money (greatly preferred!) then create a line item entry on the page of your choice (savings is a good one) for the remaining amount.
4. Bank Credit - Other:
   • Do the same thing as above, except don't worry about the 'Contribution Amount', just put the money where you want to. Again, savings is a good place to put it.
   • If the Amount equals or exceeds your regular Income Amount you might consider doing a regular income distribution to pad the various budgeted items.

Section VI

Step by Step - The Initial Budget

1. The spending Plan:
   1. Calculate reliable take home pay. This is take home pay based on a 40 hour work week. If you have a 40 hour pay stub, good. If not, then you'll need to use a best guess. The idea is to budget an amount less than your normal pay to allow for exceptions.
   2. On the 'Income' page enter a description of the income source. Mom, Dad, Dog, Cat, etc. A code letter from the frequency table and amount you are budgeting. Repeat this for all regular and reliable incomes.
   3. Select and enter a frequency code for your budget system and enter that letter code in the 'Most Frequent Income' cell.
   4. Assuming there are no amounts entered on the budget pages the total income amount matching the code letter entry in 'Most Frequent Income' will be displayed in the 'Take Home Pay' cell on the 'OnHold' page. If no entries have been made on the 'budget items' pages the full amount should also appear in the 'Budgeted Check Balance' cell, and in the Light Yellow 'Budget' cell on each 'Budgeted Item' page.
2. Normal Spending to Budget:
   1. Gather up all your regular expenses. The tab labels on the 'Budgeted Items' pages should help you think of all of them.
   2. On each 'Budgeted Item' page, in the top left Gray entry cell, enter the amount of the expense.
   3. In the adjacent gray cell enter the frequency code letter matching the frequency of the expense.
      
      As .ii, and .iii are completed the total amount for each budget frequency will appear in the next cell to the right. The value in the Light Yellow 'Budget' cell will also decrease.
      
      Note: using any code letter that is not 'legal' will cause the formula to default to a period where each month is assumed to have only four weeks. This has the advantage of forcing a little extra into the expense item. Also, it ensures that when there really is a four week month the funds will be there to meet the expense.
      
      
   4. Repeat for each budget item, or until the value in the Light Yellow 'Budget' cell reaches zero. Negative numbers are to be avoided as you really shouldn't plan to spend what you don't bring home.
3. Distribute Available Funds:
   1. Calculate the funds available to distribute among the 'Budgeted Items' by filling in the applicable 'Banking' pages. There will be a value in the 'OnHold' Check Balance cell. This cell is replicated on all 'Budgeted Items' pages as a Light Blue cell.
   2. On each 'Budgeted Item' page create a line entry and make a positive entry proportional to the planned expense and available 'OnHold' check balance amount.
   3. As each entry is made, the value in the 'OnHold' check balance will decrease informing you of how much you have left to distribute. Stop at zero! You really don't want to plan to spend more than you have.
      
      You may have to juggle the amounts or, even cut back on some areas until you get a handle on your spending. It may take a while. Be strong in your resolve to control thyself, and patient with yourself as you learn how.

Section VII

Misc. Stuff

1. Adding a 'Budgeted Item' page;
   1. Make a back up copy of the file before proceeding!
   2. The first step to adding a page is finding an existing page that most closely matches the page you want. A child's allowance page has the most bells and whistles. So, it's a good candidate.
   3. Select the appropriate page.
   4. Select: Edit; Move or Copy; Check the 'Create a Copy' check box and press Enter to create a copy of the page.
   5. On the new copy dbl-click the tab and give it a new name. (Remember this name, you will need it later.) Use only alphanumeric and underscores.
   6. In the A1 cell give it an appropriate title.
   7. On the 'OnHold' page select a line to insert a row. There must not be any items on the page to the right of the 'Amount' column. If there is you will insert a blank line in the middle of that block. Let's use the 13th row.
   8. Rt-Click anywhere on the selected row. select: Insert; Entire Row.
   9. Highlight the active (data) area of the row above the new line and Select: Edit; Copy. {Ctrl-C}
   10. Now select the first cell on the new line and Select: Edit; Paste. {Ctrl-V}
   11. The first cell (column 'A') does not need editing.
   12. Select the cell in 'B' column by dbl-clicking on it. This will allow you to edit the formula in the cell. You must change the target page name (the text between the equals sign and the exclamation point). Use the same name as you gave the new pages tab. Once the name is right the displayed value should reflect what is in the new page budgeted amount cell.
   13. Repeat for the cell in column 'D'. Now the title you put in the A1 cell title block on the new page should appear.
   14. Repeat, once more, for the cell in Column 'E' and the current balance for the new page should appear.


2. Distribute Command Button:
   • The purpose of the 'Distribute' Command Button is to automate the process of distributing income throughout the system. Honestly, I got tired of doing the same thing every week.
   • Always make a back-up copy before using the 'Distribute' Command Button.
   • The Cell 'A3' is used by the macro to decide whether to process a sheet and how.
   • 'F' - Financial Sheet - No Action.
   • 'A' - Account/Active Sheet - Adds 'Contribution' Amount to Page.
   • 'S' - Savings Sheet - 1 per Workbook Must be last sheet. - Adds or Subtracts final 'On-Hold' amount.
   • 'Other' - Sheet is Skipped.
   • Has not been tested for multiple income families. Lack of 'need' and interest has precluded further develeopment of Multiple Income Scenarios. Let me know if you need this support.
   • The macro will sequence through the Workbook and if an 'A' is found in Cell 'A3' will post the 'Contribution' Amount as a credit.
   • If an 'S' is found in Cell 'A3' the macro takes the entire 'On-Hold' amount and posts that amount to 'Savings'. If inadequate income was available a negative amount will be posted to savings.


3. Grade Incentive Area:
   • The Grade Incentive Area is just below the Income Area on the Income Sheet.
   • The purpose of the Grade Incentive Area is to automate the process of calculating how much a Report Card's Grades are worth. I have provided three choices and two children. If more children columns are needed the rightmost should copy and paste just fine. Let me know.
   • The three columns were obtained from the Class Agenda Book our local school uses. It seemed to me that the Grade Point values looked like reasonable dollar values.
   • One of the three columns is chosen by placing any useful character in the selection space at the top of the column desired.
   • The number of times a specific Grade/Value is 'hit' by the child is placed in the child's left column.
   • The appropriate dollar amount then appears to the right. The total appears at the bottom. As does a total grades tally to see if all grades were included.
   • NOTE: The system is weighted to select the right most value column that has anything in it, even a [Space].

Section VIII

Changes

• Version 2.20 - Posted July 5, 2003
  • Removed Mileage Sheet. If there is enough interest will release as a separate .xls
  • Added 'Distribute' Command Button to allow a one-click distribution of the On-Hold amount
  • Moved the Grades Incentive scratchpad area on each 'Child' sheet to an area under the Income Area on the Income Sheet. Improved and automated the process.
  • Fixed a bug where a 'Value!' Error would occur if the Contribution amount was zero.
• Version 2.01
  • Changed formula on 'Mileage' in column 'Driven' to: =if(B'y'-B'x' < 0,0,B'y'-B'x'). This causes a 0 (zero) value to be placed in the cell until you fill in an odometer reading. Where 'y' is the current row and 'x' is the previous row.
  • Replaced formula on 'On-Hold' cell I16 with the annual_from function: annual_from(Income!K18,Income!B19)
• Version 2.00
  • Created Excel Functions: annual_to; annual_from; and contrib, to clean up messy nested IF statements
  • Added a Mileage Table. Actually it was in my personal copy. So, I left it in.
  • Moved the 'Total in Banks' cell to the upper right area of page.
  • Moved & renamed the 'On-Hold Check Balance' cell
  • Moved & renamed the 'Budget Check Balance' cell
  • Created a defined print range for the On-Hold page that limits the printed area to the Budgeted Items and their current balances.
  
  Return to Table of Contents