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.
No comments:
Post a Comment