Creating a Spreadsheet

Now that we’ve got a budget planned out, it’s time to create a spreadsheet to make it easy to maintain on a regular basis. The new year is a perfect time to begin a budget. We hope to have you in a position to start your budget by January 15, the time that the first transactions for the year need to entered into a spreadsheet.

The first decision to make is what spreadsheet software to use. There are many different programs varying widely in price that will work just fine. If you already own a copy of Microsoft Excel, we recommend that you use it. We currently use Excel 2007. However, essentially any version will do. If you already own another spreadsheet software package, it will most likely be acceptable since a budget spreadsheet does not require any fancy or complex processing. If you don’t already have spreadsheet software, we recommend you download the free open source OpenOffice.org (OOo) suite of productivity software (http://www.openoffice.org). One of the products in this suite is Calc, a spreadsheet program. It supports many of the same features included in Microsoft Excel and should work fine for budgeting. For our current purposes, we will be using OOo Calc just to show how a free product is more than capable of handling our budgetary needs. We are using OOo version 3.3.0. Its native file format's extension is “ods”; however, files can also be saved in Excel format (“xls”) should you ever decide to switch to Microsoft’s product.

There are a number of different ways a budget spreadsheet could be laid out, but we like the layout shown below.

It is not our mission to give instructions on how to use spreadsheet software. There are many existing books and Web sites that do a great job of that. Our mission is to show you how to set up an efficient budget spreadsheet. With that in mind, please note some of the features of the sheet above. Cell A1 shows the current year and the budget number for that year. If you follow our recommendation and update your budget twice a month, then you will be generating 24 sheets over the course of a year. The first budget sheet for a new year will be generated mid-January. The second one will be generated at the end of January, and the final one for the year at the end of December. Cell A1 should read 2012-02 for the second budget of the year. When the final budget sheet for the year is generated, cell A1 should read 2012-24.

Cells A2 through C3 show header information. Column A is formatted as Text and will be used to enter Transaction Numbers, thus its heading. A transaction number can be a check number, an online bill pay sequence number, or text like Deposit, Transfer, Debit, etc. Column B is formatted as Date (MMM DD) and will be used to enter the transaction date. The M-D-12 indicates that all the dates are months and days in the year 2012. Column C is formatted as Text and will be used for entering where the transactions took place, such as Best Buy, JCPenney, or Amazon.com.

Note that the date for the Starting Balance is Jan 01. At the bottom of the sheet in row 80 you see that the Ending Balance is for Jan 15. Once this budget sheet is complete and preparation is being made for the next semimonthly budget sheet, the Starting Balance date will be changed to Jan 15 and the Ending Balance date will be changed to Jan 31. We will discuss how to determine your Starting Balance for each budget category in the next post, as well as how to fill in the Semimonthly Allowance values in row 79.

Columns D through U contain the headers for the budget categories determined in previous posts. Below the appropriate headers will go the monetary amounts of each transaction. Therefore, rows 3 through 80 for columns D through U are formatted as Currency. Row 80 for each of these columns sum up all the amounts in rows 3 through 79. Thus this sum, for each column, represents the Starting Balance minus all the transaction amounts plus the Semimonthly Allowance for that column, resulting in an Ending Balance for the budget period.

Row 82 shows the overall balance for your checking account in column U (not shown in figure). This is simply a sum of all the Ending Balances from columns D through U. There is nothing magical about the number of rows that are allotted for transactions. We use a budget sheet with a total of 82 rows (includes the Total row). Over time we determined that it was rare for us to have so many transactions during a budget period as to exceed this number of rows. Also, it was about the maximum number of rows that would fit on a single landscape 8.5”x11” sheet of paper and still have text large enough to read. If you find that you need larger text, you can either use fewer rows or plan on printing the spreadsheet across two sheets of paper.

Notice that the headers are various colors to separate them. This is nice if you are using a color printer to create hardcopies of the budget sheets. If you use a black and white printer, then this is unnecessary.

Please download the blank budget spreadsheet using the links below so you can see the entire sheet on your computer:

OpenOffice.org format (.ods):
http://www.rkaproductions.com/files/Budget Spreadsheet Blank.ods

Excel format (.xls):
http://www.rkaproductions.com/files/Budget Spreadsheet Blank.xls

In the next post we’ll discuss how to initialize the blank sheet with Starting Balances and Semimonthly Allowances.

Adjusting Expenditures Part 2

We now continue with adjusting expenditures in the remaining budget categories.

Insurance
This category may be difficult to reduce. You really need most of the types of insurance mentioned before in order to avoid debilitating debt should tragedy strike. Even so, with diligence some cuts should be possible. For instance, you might want to cut back on the amount of life insurance you have on family members. Some life and health policies allow discounted rates for people in good health. By exercising more and eating better, you might be able to qualify for these discounts. As a “side” benefit, you’ll feel better, also. By shopping around, you may find another insurance company that is cheaper than the one you currently use. A combination of these suggestions could get the cost of insurance down from $400 to $350 per month.

Debts
If at all possible, you don’t want to reduce your allotment of money for paying off old debts. Instead, you want to increase it to pay them off faster. For now, just leave the amount at $200 per month.



Personal
Whether you like it or not, you’re going to have to reduce the amount of money you spend on yourselves and your children. To become financially secure, such sacrifices are needed. Try cutting your monthly expenditures on each person in half; from $200 per person per month to $100. No, you won’t be able to buy new clothes as frequently or go to as many movies, but in the end you’ll be better off. Perhaps the family can stay at home and play games rather than going to a movie or a concert. Be creative, and cut that spending.

Miscellaneous
Since this category is for unaccounted for and unexpected expenditures, it will be difficult to know how much the allocation for this category can be cut. So, just leave it at $50 per month.

Credit Cards
If there is any category not to cut, it is this one. Most likely, your credit card debt is costing you more in interest than any other debts you have. Why pay all that money in interest when it could be used for buying things to better your family. You really want to pay your credit cards off as soon as possible. But, given there are other things you also need money for, there is only so much you can do. So, for now, leave the allocation for this budget category at $500 per month.

Savings
So, how did we do? With these reductions in your budget categories, how much money will you be able to save each month? The table below shows the original budget alongside the new numbers.

Wow! Can you believe that we were able to cut your monthly budget by over $1000 per month? With those cuts, you can now begin to save almost $300 per month rather than going further into debt to the tune of almost $800 per month. That is tremendous. And to make all these cuts more palatable, let’s look at how your finances will look in five years after paying off your credit cards and other debts (except your mortgage), assuming your income remains constant.

Again, Wow! Once you pay off your debts, you will be able to start saving almost $1000 per month. What would you do with an extra $12,000 each year? You’ll probably want to up your mortgage payment to get it paid off faster. You’ll also probably want to increase the amount of personal money you have available as a reward for a debt reduction job well done. Just don’t go overboard with these increased allotments. You don’t want to get back in the same financial condition you just worked so hard to get out of.

Next, we will discuss how to set up a spreadsheet for easily handling your semimonthly budget.