Saturday, January 7, 2012

Initializing the Spreadsheet

Now that we’ve got a budget spreadsheet built, it’s time to fill in some of the initial numbers. There are two sets of values that are needed: the Starting Balances and the Semimonthly Allowances. Let’s start with the latter since we determined these numbers in a previous post when we adjusted the category expenditures. At that time we settled on the monthly amounts for each category, whereas for the budget spreadsheet we need the semimonthly amounts. These values are simply half of the monthly amounts as can be seen in this table:

So, all that needs to be done is to enter the Modified Semimonthly numbers above into the spreadsheet on row 79 for the Semimonthly Allowances. We personally like to use even dollar amounts for our allowances, so we would round up the House Upkeep allowance to $38.

Next, we need to determine how much money to allot to each category as a Starting Balance on row 3. Actually, since you are adding money to each category twice a month, the only reason for putting any seed money into a category is that you have a regular payment due during this first budget period. Because you are just starting, you do not have any money from a previous budget in any category. So, if a monthly payment is due during this first budget period, you need seed money in order to have enough money to make the payment. For instance, suppose your $500 monthly mortgage payment is due 10 days into this first budget cycle. If you don’t put seed money into the Mortgage category, then you will only have $250 for making the payment. Thus, you need to set a Starting Balance for the Mortgage category of $250. You need to do something similar for any other budget categories where a payment is coming due soon. For the purpose of this exercise, let’s suppose you also have a utility bill, a TV cable bill, and some car insurance coming due. Also, let’s assume that you currently have $500 in your checking account. Then the Starting Balance allocations may look like this:

There seems to be a problem. You don’t have enough money in your checking account to cover the seed money needed. This means you are starting with a negative amount in the Savings category. That’s okay as long as the overall balance in your checking account stays above $0. If, however, you have too many payments coming due before you have a paycheck coming in, you might need to transfer some money from a another account to prevent an overdraft on your checking account. For our purposes here, let’s suppose your next paycheck will be deposited before any large bills are due so that no additional money is needed in your checking account. Remember too that you will be adding about $140 to the Savings category twice a month, so the $100 initial Savings deficit will go away on the first budget cycle.

Now, all that remains to be done is to enter the numbers from the above table to the Starting Balance row on the spreadsheet and resave it. It will look something like the partial view below:

Click here for image of full spreadsheet.

Now you are ready to complete your first budget spreadsheet. You can download readymade copies of the initialized spreadsheet using the following links: format (.ods): Spreadsheet Initialized.ods

Excel format (.xls): Spreadsheet Initialized.xls

No comments:

Post a Comment