Budget Spreadsheet

[sillygoosegirl]

So last year's budget spreadsheet was a bust. If any of you tried it, you found out that I didn't fully debug it. Plus, there were too many little sheets, hard to keep track of things, and a pain to correlate with statements since we never knew which category each purchase belonged to based on the info in the credit card statement.

This year I am trying a different approach. I've learned how to use the auto-filtering feature, and now I'm taking an approach where I have one (1) sheet that is a log of all purchases. Each purchase has it's own line with such useful information as the date, store, description, budget category, sub category, type (ie sub sub category), amount, payment method, and status (cleared this month, cleared before, not yet cleared).

Another sheet has all those recurring expenses that happen every month and I don't want to have to write down or find to copy and paste each month: rent, utilities, car insurance, student loan payments, etc. It's the same as the one above, but only has the regular bills in it.

For reconciling with statements, I can filter by "not yet cleared", and "payment method", so I'm only looking at uncleared purchases made with the account at hand. Further, I can filter by date and store if I need to.

For giving myself a nice overview of spending, I can then generate reports based on the data I have. Using the wonderful =ProductSum() function, I can find out how much I spent in a given category, sub category, etc in a given time period. I can categorize a purchase of winter bike clothing under "transportation", still be able to find it when I reconcile receipts, and furthermore have it come out of the "transportation" budget, but still contribute to the total if I was to ask the question, "just how much have we spent on clothes this year?" I am working up a single sheet of common information I think I will want to see graphed or tabulated, so we can see how we are doing and stuff.

Furthermore, I am thinking of adding 3 more sheets which will help us keep track of how much money we have/owe in each of our accounts. One of them would record transfers (paying the statement balance at the end of the month on a credit card is a transfer from checking to that card account). Another would allow us to define allocations so we could say, "We're setting aside our extra $50 of 'entertainment money' to spend on season tickets to the theater next fall." And then actually have a record of making that "transfer", how much is saved up so far for the particular thing, and what the total "available" money in checking (or savings) is since we'd be treating the "allocated" money like an uncleared check. The final sheet would have all the running "available balance" records for all accounts, and hopefully also keep track of what the balance should be in each account, given what has cleared or not... to verify that everything is really and truly reconciled the way it should be. I'm still not at all sure what these last 3 sheets will look like, but the ideas are forming.

And before you ask me why I don't just get an off-the-shelf program to do this... well, my way is more fun and if I ever get it working right, it'll be exactly what I want. Besides Excel is fun crack. So :-P

Comments

[artemis-lizzie.livejournal.com]

Maybe instead of putting things in separated categories, you should find a way to label them. Then, that "winter bike clothing" could be labeled as both transportation and clothing. I think this would apply for a lot of auxiliary purchases for things.

[nemene.livejournal.com]

I bet your bugs weren't as bad as the ones in the spreadsheet I built when Newt and I were first married. I endedup counting income as income AND as a deposit, adding it to balances twice. This caused some big problems when we thought we had multiple thousand dollars and our account balance hit zero...