Second Budget

Well, hopefully you made it through setting up your real budget just fine and were able to get all your transactions entered for the first half of the month. It’s now time to examine how your second budget might look. Remember that the Savings column can look either better or worse than it really is after the first budget. It depends on when your paychecks come in. If all paychecks are received twice a month or perhaps every two weeks, then the Savings numbers should reflect reality. However, if one or more paychecks are received only once a month, then the numbers can look good or bad depending on what time of the month they are received. In the case of our sample budget for Jack and Jill, Jill receives two paychecks per month while Jack gets one during the first two weeks of each month. This made their Savings category look really flush with money on the first budget. But as you can see below, the second budget brings the Savings back in line (with a balance of over $300) because Jack has no paycheck being deposited during this budget cycle.

                                Click image for an enlarged view

Download OpenOffice Calc version of this spreadsheet
Download Microsoft Excel version of this spreadsheet

As you can also see, some other regular bills were paid during this half-month cycle. Fortunately, most budget categories held their own. However, it appears that an unexpected plumbing problem cost Jack and Jill $85 and thus put their House Upkeep category in the red. This is okay as long as no other unexpected repairs are needed in the near future. If the overall balance in their checking account remains above zero, then there is no need to panic. Hopefully, the deficit in any given categories can be made up over the next few budget cycles.

The only other possible problem seems to be in Jack’s personal money column. He’s in the red by almost $16. If Jack has at least $16 in his wallet, he is okay; but if not, he needs to watch what he spends over the next budget cycle to make up the deficit.



Notice that Jack and Jill did real well with their Food budget. They ended the month with a surplus of $34. As long as they can maintain this level of spending, all will be well. However, if they begin to loosen up too much and start eating out more, they could find themselves in trouble.

If you found yourselves, in your real budgets, overspending left and right, then all we say is that you must begin changing your mindset right now! We know it can be difficult going from freely spending money to spending a controlled amount. You must begin settling for less. It can be done. Many other people have done it. As you work towards your goal of reducing spending, one thing to keep in mind is that most of what we buy are things we want rather than things we need. If you were to cut your spending to just your needs, you would most likely find yourself spending much less than you are taking in. So, whenever you find yourself wanting to make a purchase, evaluate the necessity of the item and how the purchase will affect your budget’s bottom line. If it’s not really needed and your budget can’t handle it, don’t buy. Keep working at this until the lower spending amounts just come natural to you.

In the next post, we’ll give you a few tips about controlling spending in case you continue to have problems.

First Budget

Alright, it’s time for the action to begin. Let’s put some transactions for the first half of the month on the initialized spreadsheet. We have put together a sample budget to show you what it might look like. Yours will probably look much different, but the elements should be similar. Here’s a screenshot of our sample budget. (Click on the screenshot for a larger image.)

Download OpenOffice Version
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 OpenOffice Version
Download Excel Version

In the next post, we’ll create some more sample transactions for the second budget cycle.

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:

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

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