How to Make Your Own “Virtual Envelope” System Using Excel: An Overview

I’ll try to explain here how I’ve designed my Excel system to intensively track my spending on every single item. At the end of the month, I can track how much was spent on each major budget category (food, general upkeep, garden, etc.), and I also get a deeper look at how money was spent within each major budget category (e.g. the food breakdown into dairy, meat, fresh fruits and vegetables, frozen vegetables, etc.). I can also sort each category by price to determine if the budget mainly went to large bulk-item purchases or to those little purchases here and there that add up. It has its pros and cons, but works well for me so far.
In this post I’ll give a broad overview of the workings of the Excel workbook, and in future posts I’ll cover each sheet of the workbook in more detail.

Note: This series of posts was inspired by a question to Mrs. Accountability about how to use Excel, so here I am generally attempting to give a quick primer on how to build something useful in Excel for people with little to no experience using it. The question, and Mrs. Accountability's answer can be found here:
http://www.outofdebtagain.com/2009/10/answers-to-mrs-as-qa/

A Very General Overview
The workbook is divided into multiple worksheets (these are the tabs at the bottom of your workbook that indicate what worksheet you currently have open). The first worksheet is the “Budget” sheet, which I plan once and make modifications only if necessary, only monthly. The second worksheet is the “Current Month” worksheet, where progress on the broad budget categories are tracked and compared with the budget. The remaining worksheets each track in detail the spending within a budgeted category.

The Budget Sheet
This is the sheet we created when we sat down to design our budget. We have our incomes listed and totaled, so we know exactly how much we make per month. Then we listed our fixed monthly expenses and their monthly amounts: rent, debt payments, phone, internet, etc. We then listed the expenses that come only once every few months, and calculated the amount we’d pay on each if it were monthly instead: car insurance, parking permits, medical and dental check-ups and prescriptions, etc. Finally, we listed our variable monthly expenses in order of necessity: gas and electric for the house, gas for the vehicles, food, general upkeep (think toilet paper and soap), laundry, garden, eating out, other fun (rock climbing, bowling, etc.), and allowances. We total up the amounts budgeted for all expenses, and subtract this amount from the total income so we can easily see how modifying our variable expenses changes the amount we are able to pay towards debt. This sheet needs to be modified when it becomes apparent that the budget is unrealistic or missing crucial information, and should be updated monthly as we grow in our budgeting skills and learn better how to reduce our spending sustainably (as opposed to saying, “we will buy nothing this month and just learn to make do with the dried beans in the cabinet and junk mail for toilet paper”).

The Current Month Sheet
I save a new version of the entire workbook each month, and update the name of this sheet for the month, i.e. “October,” for this month. This sheet is basically a copy of the budget sheet, with the addition of the capability to track what has been spent in each category this month, and how much is left in each category this month. It also automatically calculates how much has been spent, subtracts that from the income, and displays the amount that is left for spending and paying down debt. This sheet never needs to be modified, as it updates automatically from the Budget and the Category sheets. However, it should be consulted prior to every single shopping trip or other outing that costs money, so you can make notes as to how much money you should limit yourself to spending this time.

The Category Sheets
These sheets allow me to track exactly what is being spent for individual items within each category, and enter additional information such as date, subcategory, or store of purchase. An example is the “food” sheet. Here I enter the food item, the date it was purchased, how much it was purchased for, the store it was purchased at, and a number of subcategories: “need, could reduce, want,” “dairy, meat, veg, fruit carb,” “milk, cheap cheese, specialty cheese” etc. This sheet allows for a number of tracking tools that are very useful, for example, with a click of the mouse I can group all purchases by “dairy, meat, veg, fruit, carb,” then sort the dairy group by “milk, cheap cheese, specialty cheese,” and then compare prices on milk at the different stores I’ve purchased at in the last month. These sheets need to be updated after every single shopping trip. The exceptions are the cash “allowances” that we simply calculate as a loss to the budget and do not attempt to do any itemized expense tracking or control (the items tend to be small, frivolous, and generally don’t come with receipts, i.e. vending machines – remember, the allowance is our way to control the frivolous spending by putting a set, low limit on it).

Summary and Work Involved
The initial, one-time investment of time is about one to two hours to build the budget and the rest of the workbook. For the rest of the month, it’s just a matter of checking the data and doing data entry – I spend less than 10 minutes after each shopping trip inputting the data. Since I primarily only shop once a week, this is very little time spent on maintenance. Once a month, a copy of the workbook must be made for the new month, the budget from last month reviewed, and the budget for the next month modified accordingly. This usually takes about an hour, since we spend too much time playing around with the features instead of just getting the job done (which should only take 15-30 minutes). Speaking of playing around with features, I probably waste half an hour a day on this, so the system is dangerous in that regard!