Question about budgeting on a spreadsheet

[Money? What Money?]

So, I've set up a nice budget on an excel spreadsheet to track my spending and project my savings. I love crunching the numbers but I keep running into a problem that I can't get around. I could be over-thinking this....I've been known to do that from time to time

So my budget is broken down by month and each month I have line items set aside for short term savings for things such as car/house maintenance, christmas gifts, etc. So this money is counted as an expense, even though it technically hasn't been spent yet. The problem I'm running into is that when I actually have one of these expenses, lets say an oil change, if I want to track the oil change on my spreadsheet, it is then counted twice. Once when I had a monthly allotment and again when I actual record the expense. Any ideas on how to correct this?

I would like to show the monthly allotment set aside for these short term expenses and to be able to track the actual expense when it occurs.

[Petunia 100]

So, I've set up a nice budget on an excel spreadsheet to track my spending and project my savings. I love crunching the numbers but I keep running into a problem that I can't get around. I could be over-thinking this....I've been known to do that from time to time

So my budget is broken down by month and each month I have line items set aside for short term savings for things such as car/house maintenance, christmas gifts, etc. So this money is counted as an expense, even though it technically hasn't been spent yet. The problem I'm running into is that when I actually have one of these expenses, lets say an oil change, if I want to track the oil change on my spreadsheet, it is then counted twice. Once when I had a monthly allotment and again when I actual record the expense. Any ideas on how to correct this?

I would like to show the monthly allotment set aside for these short term expenses and to be able to track the actual expense when it occurs.

How I handle this is....the money being set aside into savings counts as savings. It is only an expense when I actually incur the expense.

[Money? What Money?]

Petunia,

How do you show the allocation to savings in your budget? This seems to be where I'm getting confused. As an example, let's say I set aside $100 a month for Christmas with a goal of having $1200 allocated in the savings account for Christmas, how do you keep track of what money "belongs" to which line item when you have money sitting in a single savings account?

[mrpaseo]

Any chance we can see your worksheet?

I have two solutions for this.

Option 1

Credit card usage. We have a slot for Christmas and we put $150 a month into the cell. Month after month be "Bring forward" the balance and add the new $150 to the balance.

When we are out buying Christmas gifts, we use the CC. Then, when I come home. I move the amount spend from the "Christmas money" cell, to the "Pay the Credit card" cell. I then pay the CC twice a month (On the 15ht and the last Friday of the month).

Option 2.

Envelope system. Since bank accounts do not offer anything these days, there is no sense in leaving your money in the bank aside from not being robbed. Since we are talking about a few hundred when I do is take out some money (Say $600 a month) and put it into the envelopes. So in this example, I would put $150 cash into the envelope. This way, the money is no longer in your account and you can just use cash to buy your Christmas gifts.

I use both of these options for different things. For example, we allot $200 a month for gas, we always use the CC for gas. When we do, I move money from the gas cell to the Credit card cell. All the money moved from the misc areas to the CC are all added up in that one cell and then the CC is paid in full twice a month.

[Money? What Money?]

Any chance we can see your worksheet?

I have two solutions for this.

Option 1

Credit card usage. We have a slot for Christmas and we put $150 a month into the cell. Month after month be "Bring forward" the balance and add the new $150 to the balance.

When we are out buying Christmas gifts, we use the CC. Then, when I come home. I move the amount spend from the "Christmas money" cell, to the "Pay the Credit card" cell. I then pay the CC twice a month (On the 15ht and the last Friday of the month).

Option 2.

Envelope system. Since bank accounts do not offer anything these days, there is no sense in leaving your money in the bank aside from not being robbed. Since we are talking about a few hundred when I do is take out some money (Say $600 a month) and put it into the envelopes. So in this example, I would put $150 cash into the envelope. This way, the money is no longer in your account and you can just use cash to buy your Christmas gifts.

I use both of these options for different things. For example, we allot $200 a month for gas, we always use the CC for gas. When we do, I move money from the gas cell to the Credit card cell. All the money moved from the misc areas to the CC are all added up in that one cell and then the CC is paid in full twice a month.

We use credit cards for 90% of our daily spending and pay them off each month, so I like option 1. Your suggestions help a lot. I like the way you have your short term savings "outside" of the rest of the budget equation, then "move" money to the appropriate line item as needed.

I have been showing my savings as an expense rather than as savings and that has complicated the math.

[EarlyRetirementJoy]

I do something a bit differently. When I 'credit' an account, which is what you are referring to, I adjust the cell formula accordingly so that the amount in the cell reflects that a credit has been added, which then offsets future debits. I also insert notes into my cells to indicate exactly what monies have been added and/or spent in any given month, and for what, since I can't always remember weeks or months down the road.

[Petunia 100]

Petunia,

How do you show the allocation to savings in your budget? This seems to be where I'm getting confused. As an example, let's say I set aside $100 a month for Christmas with a goal of having $1200 allocated in the savings account for Christmas, how do you keep track of what money "belongs" to which line item when you have money sitting in a single savings account?

I have line items for my various savings accounts. And I do have multiple savings accounts. Do you use an online bank such as ING or Ally? There are no minimums and no fees, so no reason not to set up multiple savings accounts if you choose.

My budget is pretty simple, I think. I post a monthly budget report every month on my blog. You can view it if you like.

[Wino]

I use Excel a lot for almost anything. I think you can do what you're thinking by adding another section. This section will be your "directed savings." The section will have two columns: one is savings in, which is deducted from your monthly credits. The other column is payments out, which are deducted from the first column.

Another website that can help is "chandoo.org/forums" I know the website name sounds strange, but true Excel gurus hang out there and are willing to help out.

[AsiaTraveler]

I kept looking for a spreadsheet that would do something like this, and in the end, I built my own.

Mine looks like this- four columns.

First column = previous balance. This is automatically (via the = function) brought from a previous month. Only exception is the first month of the year, when I start a new workbook, I enter them manually from December of the previous year.

Second column = Current Payments. Expenses are entered as negative numbers, deposits are entered as positive numbers. The first two lines of my budget track our income; the rest the expenses (when we set it up, we make sure that the income is at least equal to the expenses).

Third column = Budgeted Amount. Here comes the tricky part. The budgeted amount of INCOME is entered here as a NEGATIVE number, while the budgeted amount for all other accounts is entered as a positive number. If we earn more than we budgeted for, it rolls over in our "income" area for future months.

Fourth column = New Balance. This is a simple =SUM(A2:A4) (for example) of that line. This is the balance that will be brought forward into the previous balance line for the next month.

Hopefully that makes sense. It works for us. If you had a highly variable income, it might not be the set up you are looking for. Ours is also set up so that we can download all the statements and then use the IF statements to code individual expenses so that Excel does all the math for us. That was a little more complicated to set up.

Also, because we pay our credit cards in full every single month, we don't have a separate "pay credit card" line. We simply take all the expenses, whether they came out of checking or on a credit card, and allocate them to their relevant budget line item.

[Wino]

Asia Traveler:

Instead of bringing the amount from last year forward manually, you can set up another equal formula that targets the other spreadsheet. You just have to make sure you keep all of your spreadsheets in the same folder on your computer.

=[Book1]Sheet1!$A$3

Just open the new and the old sheets at the same time.

1. In the cell where you want the balance brought forward, type the equal sign.
2. Now, click on the "old" spreadsheet and select the cell where you want to grab the previous value.
3. Go back to the "new" spreadsheet, and the formula is complete.
4. Repeat: save both sheets to the same folder location.

The formula above takes the value from the spreadsheet named "Book1" from the table/spreadsheet named "Sheet1" and the value that is in cell A3 on that sheet.

Now if you make any adjustments to "last year" it will carry forward without you doing anything.

[Money? What Money?]

Thank you all for the advice. I've decided to create a separate section on my spreadsheet that deals with short term savings, as suggested above. It seems to be the easiest way for my brain to separate it from the rest of the expenses.

[AsiaTraveler]

Asia Traveler:

Instead of bringing the amount from last year forward manually, you can set up another equal formula that targets the other spreadsheet. You just have to make sure you keep all of your spreadsheets in the same folder on your computer.

=[Book1]Sheet1!$A$3

Just open the new and the old sheets at the same time.

1. In the cell where you want the balance brought forward, type the equal sign.
2. Now, click on the "old" spreadsheet and select the cell where you want to grab the previous value.
3. Go back to the "new" spreadsheet, and the formula is complete.
4. Repeat: save both sheets to the same folder location.

The formula above takes the value from the spreadsheet named "Book1" from the table/spreadsheet named "Sheet1" and the value that is in cell A3 on that sheet.

Now if you make any adjustments to "last year" it will carry forward without you doing anything.

Thanks! I never really looked into it- obviously the first year I did it I was starting from scratch. I was just so proud to get the rest of the spreadsheet set up correctly that I didn't dig into it any further. Nice to know!

[sandrark]

I use Excel too. One book per year, one sheet per month.

At the top of the sheet, I have my "Income" section for the month, and my savings account carryforwards:

Paycheck Income:
Checking Account Balance:
Reimbursement Income:
Transfers from Savings:
Gifts:

I then total them, and have a Total Income line.

Then after that, I itemize each monthly expense. Included in these expenses would be any transfers to savings. That would be a line item in the expenses, added to the Savings Account Carryforward. Then that cell is copied into the next month's carryforward, once I make the deposit/transfer to the savings account.

If I have to transfer money FROM the savings, I make the transfer, and then add it to the monthly income. So I have the amount to spend, say, when our property taxes are due. So the savings amount is located as income, and the expense is listed as a cost.

Works well - I can total transfers, expenses, and match up balances. Income and transfers are income or savings, and expenses are expenses.

[Petunia 100]

I use Excel too. One book per year, one sheet per month.

At the top of the sheet, I have my "Income" section for the month, and my savings account carryforwards:

Paycheck Income:
Checking Account Balance:
Reimbursement Income:
Transfers from Savings:
Gifts:

I then total them, and have a Total Income line.

Then after that, I itemize each monthly expense. Included in these expenses would be any transfers to savings. That would be a line item in the expenses, added to the Savings Account Carryforward. Then that cell is copied into the next month's carryforward, once I make the deposit/transfer to the savings account.

If I have to transfer money FROM the savings, I make the transfer, and then add it to the monthly income. So I have the amount to spend, say, when our property taxes are due. So the savings amount is located as income, and the expense is listed as a cost.

Works well - I can total transfers, expenses, and match up balances. Income and transfers are income or savings, and expenses are expenses.

I'd love to have a copy of your spreadsheet. Would you be willing to e-mail it to me? With your numbers taken out, of course. I will PM you my e-mail address.

[dunnrobert700]

Most CCs, banks etc let you export your spending/statement into a CSV file. They pretty much all at least try to assign a category to each transaction. If you organize your budget by the categories they use, you could probably do this without having to manually track all your spending by importing the latest CSV for some time period (like year to date) into a separate worksheet, sorting by category and summing for each category.