Download Excel Version
Looking down the left columns you will see that the transactions have been divided into three general groupings. The first group is for direct transactions. The second is for credit transactions. The third is for cash transactions. Let’s look at these groupings in more detail.
Direct Transactions
Direct transactions are those that affect your checking account directly and include such things as checks, a debit card, online Bill Pay, automatic deductions, withdrawals, deposits, and so on. The sample budget shows a number of these. The Trans Number column is used to distinguish between them. For instance, when checks are written, the check number is entered. When an automatic payment is made, “AutoPay” is entered. Deposits and withdrawals are designated with abbreviations “DEP” and “WDL”. No debit transactions are shown, but we normally use “DEB” to indicate it on the budget. If you have Bill Pay available on your account, you can easily make payments to many different institutions via online transactions. Typically, a confirmation number is automatically generated when a Bill Pay transaction is finalized. We use “BP-” followed by this confirmation number to designate a Bill Pay transaction on our budget.
The sample budget has 10 direct transactions. Three checks (numbered 1103, 1104, and 1105) were written. Jack and Jill have automatic payments set up for their mortgage and their utility bills. Two Bill Pay transactions occurred. One was for a MasterCard payment, and the other one for a phone and internet bill with AT&T. Jill also made a direct withdrawal in order to have a sufficient amount of cash in her purse. And now the good news. Jack’s monthly paycheck was deposited as was Jill’s semimonthly paycheck.
Notice that when a payment or withdrawal occurs, the amount is subtracted directly from the column(s) designated for those type of payments. The AT&T bill was paid directly from the Communications category, the utility bill directly from the Utilities category, and so on. When Jill withdrew money to have cash in her wallet, the amount was subtracted from Jill’s column. We will discuss handling cash in more detail later.
Since we already have semimonthly allowances built into the spreadsheet, all paycheck deposits go directly into the Savings column. In Jack and Jill’s case, Jack receives a paycheck at the beginning of every month while Jill receives two checks each month, one in the middle of the month, the other at the end. So, don’t be deceived into thinking Jack and Jill have lots of money in Savings. They do at this time, but remember that only Jill will be receiving a paycheck during the next budget cycle. Each budget cycle requires subtracting $1943 from Savings for allocating to all the budget categories. So, the amount that will be in Savings at the end of the next budget cycle will be $1391.33 (current balance) - $1943 (for allocations) + $857.33 (Jill’s paycheck). This totals to $305.66. This better represents the true amount of Savings that Jack and Jill have.
Credit Transactions
When you make a purchase with a credit card, you are not directly affecting your checking account. Rather, you are simply building up a balance with the credit card company that will have to be paid at a future date. Yet, you still need to account for the spent money on your budget. For this reason, whenever a credit card transaction occurs, money needs to be subtracted from the appropriate budget columns. An equal amount of money then needs to be added to the Credit Card category. This methodology insures that you will have enough money available when the credit card bill comes due, allowing you to pay it off in full each month.
The sample budget indicates that Jack and Jill had 11 credit card purchases during the first half of the month. Notice that in some cases one transaction can result in deductions from multiple columns. For instance, Jack bought $72.50 worth of food at WalMart, but also bought a magazine for $5.28. The latter is a personal item and thus is subtracted from Jack’s budget category. Of course, the amount of the total purchase, $77.78, gets added into the Credit Card column. A similar split occurs for the family’s outing to the movie theater. Based on the low cost of the tickets, it appears that they went to a matinee.
Remember that Jack and Jill are also putting an extra $250 every budget cycle into the Credit Card category in order to have extra money to pay off existing debt. Thus, when a credit card bill comes due, their total payment will be the amount of all the purchases made during the billing cycle PLUS the amount designated for paying off their existing debt. Let’s suppose that $100 was allocated for paying off their existing MasterCard debt. Further suppose that $400 in charges were made during a billing cycle. Then, the payment for that cycle will be $500.
Cash Transactions
All the money carried in an individual’s wallet should be thought of as being that person’s personal money. Therefore, if Jack pays cash for something personal, such as a book, then nothing needs to be put on the budget spreadsheet. However, if Jack pays for a snack with his cash, then he will need to be compensated for this on the budget by transferring the cost of the snack from the Food category to the Jack category. This means it is a good idea to make a note of any non-personal cash transactions to help you remember to compensate yourself on the next budget.
Since this is your first budget, you may not want to start out having a personal claim on the money in your wallets. You may have different amounts and it wouldn’t be fair starting out with one person having more money than the other. So, let’s just say that any money in your wallets at the beginning of the month is money that belongs in Savings. There is no need to actually deposit this cash into your checking account. You can simply transfer money on the spreadsheet. For our sample budget, Jack started the month with $100 in his wallet while Jill started with $25. The “Initial Cash Balancing” entry on the spreadsheet handles the compensation by transferring $100 from Jack’s column and $25 from Jill’s column to the Savings column. After doing this, all the cash in their wallets can now be considered their personal money.
How Did They Do?
So, how did Jack and Jill do? Quite well, actually. They managed to keep most of their budget categories in the black. You might thing they messed up by overspending on food. Perhaps, but another explanation could be they bought enough food this budget cycle to last through much of the next one. If this is so, we can expect them to spend less on food during the next budget cycle. However, if they did truly overspend, then they will just have to hunker down and spend less during the next budget cycle.
It also appears that both Jack and Jill severely overspent their personal money. Not so. Remember that they both started with some cash in their wallets and Jill also made a cash withdrawal. So, if Jack started with $100 in his wallet and spent only $22 for personal stuff, he would still have $78 left. He also spent $2 for a snack, leaving him with $76, but keep in mind this was reimbursed to Jack on the budget from the Food category. So, if Jack has $76 in his wallet and his budget column is left with a deficit of $59.28, he still has a net amount of $16.72 ($76 - $59.28). So, Jack is good. Jill started with $25 and withdrew an additional $40 from the bank. This gave her a total of $65. Of that, she spent $5.46 on food, $4.50 on miscellaneous items, and gave $20 in cash to charity. Of course she was reimbursed these amounts on the budget, but that still left her with less cash. $35.04 to be exact. If she spent $9.50 on personal items, she would be left with $25.54. Subtracting her budget deficit of $23.72, Jill ends up with an overall balance of $1.82. Above zero, but not quite as good as Jack’s overall balance of $16.72.
Finishing Up
Once the budget spreadsheet is complete, we recommend saving it with a filename of Budget-YYYY-##, where YYYY is the current year and ## is the budget number for that year. So, for this first budget of the year, the name would be Budget-2012-01. As more budget spreadsheets are created during the year, the last two digits will increase by one for each new budget. If you want to keep a hardcopy record of your budget sheets, then print this spreadsheet.
Once you have saved this file as Budget-2012-01, you need to prepare the sheet to accept the transactions for the next budget cycle. There are several steps you need to take.
1. Since the Ending Balance for this cycle is the Starting Balance for the next, you need to copy and paste the Ending Balance numbers to the row showing the Starting Balance. So, select cells D80 through U80, select menu item Edit/Copy, select cell D3, and then select menu item Edit/Paste Special, choosing Numbers as the method to paste. The Paste Special option is needed because the Ending Balance cells are actually formulas that add together all the cells above them. By performing a Paste Special – Numbers, the numerical results of the formulas are pasted into the Beginning Balance cells rather than the formulas themselves. Upon selecting the menu item Edit/Paste Special, OpenOffice Calc shows the following dialog box:
Excel presents something similar. In either case, the important point is that the Numbers option is the only thing selected.
2. All of the transactions for this first budget cycle need to be deleted to make way for the second budget cycle transactions. Select cells A4 through U78 and press the Delete key on your keyboard. OpenOffice Calc will show the following dialog box:
You want to delete all the content of these cells, but not the formatting since that will rid the cells of their Currency formatting.
3. Finally, you need to modify three cells. First, change cell A1 from 2012-01 to 2012-02. Next change the Starting Balance date to Jan 15 and the Ending Balance date to Jan 31. Now the sheet is ready to accept transactions for the next budget cycle and can be saved with a filename of Budget-2012-02. Here’s what it should look like.
Download Excel Version
In the next post, we’ll create some more sample transactions for the second budget cycle.
No comments:
Post a Comment