Sometimes I make things complicated.

In the spirit of tax season, here is a “financial” post:

In the never-ending quest to become better organized and to stick to our budget, I’ve tried a few different ways of managing my money:

  • First – NOT managing my money. This is a good way to describe my college years, where I lived within the bounds of my checkbook and kept it balanced but had no idea what a budget was.
  • Next – the “envelope system.” After reading a budgeting book, we withdrew a ton of cash and split it up amongst different envelopes labeled “groceries,” “fun,” “clothing,” and so-forth. We dropped this system after a while because I like using our credit card to get cashback rewards (we’re very good about paying them off every month). However, I really liked the cumulative effect of saving money in envelopes — if you didn’t use the money one month, you still had it for the next month. I also liked the self-imposed limitation — if you didn’t have money in the envelope, you couldn’t spend anything.
  • Next – Quicken. I had fun setting up a budget and micro-level categories (Fun: Movies. Fun: Eating out. Fun: Books. Fun: Music. Fun: Dates.). We dropped the micro-level categories after two years — it was totally unrealistic and unnecessary.
  • Possibly because I don’t understand Quicken enough to really make use of all its features, I felt that I had to add another level of complexity to our budgeting system to help with tracking and allocating cash flow. Enter the Complex Excel Workbook.

The main spreadsheet I’ll discuss in this post is our monthly assets tracker.

The bottom part of the first spreadsheet tracks expenses. It has rows for categories and columns groups for each month. Each month’s columns consist of the month’s budgeted amount, a “carryover” column from previous months to give a “real” amount for that category, the actual amount spent, and what’s remaining. Here’s a diagram and explanation:

expenses part of worksheet

How it works:

  • I color-code rows. Green for “savings” categories that we are either saving into (such as generic “Auto”) or categories that have a large payment annually (such as Auto:Insurance) that we have to save up for. Yellow (not shown) for set amounts, such as subscriptions and rent.
    • It’s not shown in this diagram, but I have savings categories for things like “house” and “vacation.” So theoretically, every penny we have is accounted for somewhere on this spreadsheet — EXCEPT…
    • The money we put into our IRAs is counted as an ‘expense’ because it’s not available to us. I track our IRAs in Quicken; on our spreadsheet, it’s just a normal yellow line item (set amount going out every month).
  • Column A has the category names. I just add spaces before “subcategories” to help visually sort them. These categories match up with our Quicken categories.
  • Column B is the “set” monthly budget; or, how much is typically allocated to a category each month.
  • Column P, the “month’s budgeted column” (and similar columns for each month), copies the amount from B. For example, P16 = $B$16. However, I will also go through and adjust this amount as necessary. For example, if I know ahead of time that we have to get the car fixed and we don’t have enough in Auto:Service, I may subtract amounts from other categories as I can and then increase the Service category. I know that I can’t touch categories like Auto:Insurance because we have to pay a specific large amount at some point in the year, but variables categories like Fun or Clothing or Groceries can be adjusted month to month.
  • Column Q, the “adjusted monthly amount,” adds P to the previous month’s “left” column, to show how much money is allocated to that category. In this example, Q16 = O16 + P16 (O is not shown in the diagram).
  • Column R, the “spent” amount, gets updated at least once a week with expenditures. I typically enter in receipts into the cells by typing individual receipts (“= 12 + 1.20 + ….”) so that I can match up with Quicken if necessary.
  • Column S is “what’s left,” or S16 = Q16 – R16.
  • As you can see, categories can “add up” from month to month, with the total available amount shown in column Q. This is an envelope system in spreadsheet format; I get an actual picture of how much money has been allocated for a specific category.
    • This is one thing I didn’t like about Quicken — Quicken has a “yearly total” for how much you have “left” in your budget, but those numbers are projected from your monthly budgeted amount and are hard to adjust, and it doesn’t really show you how much “cash” you really have left month-to-month. In my Excel spreadsheet, if for some reason I needed to spend every penny in each category all at once, at least I know how much I’m working with in real-time cash from my bank accounts.

The top part of the spreadsheet tracks income and total [cash] assets. I have rows for “income” split up by my business income, Steve’s income, other income, and gifts/non-taxable. That shows how much money we have coming in that month. Below that, I enter in the balance from our various savings and checking accounts, then subtract any balances (as of the 1st of the month) from credit cards. The total number after this represents our total available cash assets for that month. Here’s a diagram… (I felt funny about posting my financial info for everyone to see, so I’ve blurred out actual numbers…)

Income part of spreadsheet

  • B4 and B5 are average estimated income amounts. B8, our estimated average monthly income, should hopefully be equal or more than B15, our estimated average monthly expenses.
  • P4 and P5 represent our estimated monthly income. I do not copy from B (the way P does in Expenses). Instead, I manually type in our expected income for the month, since that varies for me. I base it on what invoices are due and how many shifts Steve is scheduled for.
  • Q is for our actual income and assets. Near the beginning of the month, I fill out Q9-Q12 with the balances from our cash accounts (basically what happens to be in my wallet, which is also tracked in Quicken), checking accounts, savings and CD accounts, and credit card accounts, pulled from latest Quicken data. As the month progresses and checks come in, I’ll add those to Q4 through Q7. Q13 shows me the total “cash assets” that I have to work with for that month.

More notes about process:

  • At the end of the month, I’ll go through and adjust the monthly budgeted amounts even more (P16 down). My goal is to make Q13 (total assets) = Q14 (total allocated expenditures/savings). I can see already that we’re spending a lot more on gas this month than was budgeted because of various trips to the Bay Area; I’ll probably take money away from what is going into “house savings” to make the Auto:Fuel category balance out to zero.
  • Entering in actual amounts from various accounts (Q9-Q12) and adjusting the monthly budget (P16 down) is my way of error-correcting. It’s very likely that I forget to enter a receipt or type something in wrong. By always figuring out the actual cash assets that we have and then making sure our spending allocations match, I cancel out any mathematical errors I may have made in the previous month.
    • I’m not so worried about being 100% accurate — this spreadsheet is really more of a “guide” so that as the month progresses, we can see how we’re doing.
    • At the same time, though, it’s a very helpful way to keep track of month-to-month allocations.
  • We save all our receipts (or write down amounts that didn’t have receipts) and track every penny.
  • I try to update the spreadsheet and Quicken on Wednesdays and Saturdays by entering receipts as well as reconciling any bank statements that have come in and paying bills that are due. When I do this, it keeps the pile of receipts manageable enough so that I don’t dread doing it, plus I don’t have any surprise bills or late fee problems. Sometimes I miss my Wednesday time, but I am pretty consistent with doing finances at least once a week.
  • The reason I still use Quicken is because I love how it syncs with our accounts and downloads information… just in case I miss a receipt.

The rest of my Excel workbook has various pages for keeping track of business income and expenses, tracking mileage (which isn’t much), tracking phone calls made on our home line, etc. I will also add in new worksheets whenever I need to calculate something that we need to save or allocate money for, such as estimating school costs or putting together a vacation budget. The monthly budget view is the one that I work with the most.

Our current challenges:

  • Overspending in certain areas, most noticeably our grocery budget. Perhaps because we have actually spent within our grocery budget about three out of the fifty-six months that we’ve been married, I believe that I can do it every month. I may need to readjust my expectations and our grocery line item.
  • Because I like doing it, I’m the “finance person” in our house. But we haven’t developed a good reporting system that allows Steve to quickly know the status of our spending. Unfortunately this means that he always has to ask me before he spends money (which then throws in a weird control dynamic into everything). I tried printing out weekly reports at one point but felt like I was wasting too much paper.
  • Figuring out if we should start a new line item for “allowance” for each of us and possibly new separate checking accounts for aforementioned allowance as well as allowing us to buy gifts for each other without the other knowing. I’ve already made things complicated enough; do we want more complications…?

That was perhaps a much more extensive view into the complicated inner workings of my budget worksheet than I had originally intended to post. I’m definitely not setting myself up as a role model, but since I like to see other people’s processes, I thought it would be fun [for some] to read about ours! (And if you happen to be a Quicken expert and know how to do the “envelope system” within Quicken without too much more work than what I’m already doing, definitely let me know!)


3 thoughts on “Sometimes I make things complicated.

  1. Oh golly! Just reading about your system makes my head spin. I am not as diligent as you (but should be!).

    Could you post the spreadsheet in an online location that Steve could access from anywhere. I’m thinking of this because my household posts our bills spreadsheets in a private yahoo group. No one has to ask permission for anything. We just access it and retrieve/add our numbers as needed. And for us, a reminder to “settle acounts” gets emailed to each of us, so no one has to run around asking for reimbursement checks.

    If Steve doesn’t want to wade through the whole big spreadsheet, you could create a “cover page” for the spreadsheet that just pulls the totals from other sheets that he would be interested in.

    Of course, I guess this is a moot point if steve isn’t able to get to a computer/the Internet, huh? LOL! Maye my idea just makes things even *more* complicated!

  2. Doodah – Yeah, the fact that Steve’s computer is out of commission doesn’t help the situation! But the idea of a “cover page” is interesting to me — part of the solution might be making my spreadsheet more accessible (perhaps adding a shortcut to my desktop) so that when Steve does use the computer, he can take a quick look at an easy-to-look-at cover page to see where we’re at.

  3. That may work well. It took us a bit of monkeying around with the design of our spreadsheet before we got one that communicated the right information quickly. The roommate who created it knew how to read it, but our other roommate was always lost. ]

    Now I’m contemplating a personal spreadsheet…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s