How to Make Your Own “Intensive Spending Tracking” System Using Excel: The Budget Sheet

This post is part two in a series on using Excel to track every penny you spend without wasting your entire life away with a calculator, pen, and paper. This post focuses on building the “Budget” sheet of your tracking workbook. This series is designed for the person who has little-to-no clue how to use Excel, and hopefully makes the process simple and painless.

Budget Sheet: Overview
This sheet is the plan of attack – it lists only what we have and what we plan to do with it. We list our sources of income, and the sheet calculates our total combined income. We list our fixed monthly expenses with their amounts. We list our less-frequent expenses, with their annual frequency and amounts, and the sheet calculates the amount that must be set aside monthly to cover those once or twice a year expenses. We list our variable expenses, preferably in order of importance, and the amount we can afford to spend on them. The sheet calculates how much we can spend on each variable expense weekly, which makes it easier to plan our shopping and outings. The sheet also calculates the total monthly expenditures and subtracts that from our total income, making it easy to see how changing how much we spend on variable accounts effects how much we can save or pay towards debt each month.

Income
Start at the top of our Excel sheet. Type “INCOME” in one cell (let’s say A1). Under that, type in the following column headings: Source (A2), Amount (B2), Times per year (C2), and Monthly Amount (D2). Fill in the columns: DH Job (A3), 1200 (B3), 12 (C3); DW Job (A4), 2400 (B4), 6(C4).

Now comes the first fun part: In D3 (under Monthly Amount) type: “=B3*C3/12” (without the quotations, they are there to delineate what goes in the cell). We highlight cells D3 and D4, hold down the CTRL key and press d (CTRL+d). This automatically calculates that DW’s monthly income is 1200, which should now show up in the D4 cell. If we had more sources of income, the method would be the same, you’d just highlight D3, D4, D5, etc. before hitting CTRL+d.
Then we move over a few cells, let’s say to E2, and type “TOTAL MONTHLY INCOME.” We go down a cell (E3), and type “=sum(“ then (without pressing enter!) we highlight the cells that show the monthly income, for us D3 and D4, then type “)” and hit enter. This cell should now show “2400,” as it’s automatically calculated the total income from all sources.

Fixed Monthly Expenses
Move down a few cells from our Income section. For us, we’re at about A6. Here we type in “FIXED MONTHLY EXPENSES.” Under that cell, we type in new column headings: Expense (A7), and Amount (B7). Then we enter each of our set monthly bills: Rent (A8), 895 (B8); Comcast (A9), 67 (B9); Loan 1 (A10), 150 (B10); Loan 2 (A11), 200 (B11), etc. (We actually add a total for this set of expenses like we do for income, but it’s not essential and not something we use very often.)

Fixed Less-than-monthly Expenses
We move down a few cells from our Fixed Monthly Expenses section, for us, about A18. Here we type in, because we are so very creative with our wording, “Other Fixed Expenses, Calculated Monthly.” Yes, I know, you’re astounded by our creativity. Under that cell, we again type in new column headings: Expense (A19), Amount (B19), Times per year (C19), and Amount per Month (D19). We enter our less-than-monthly bills: Car 1 Insurance (A20), 300 (B20), 2 (C20); Car 2 insurance (A21), 600 (B21), 1 (C21); Car 1 Parking (A22), 300 (B22), 3 (C22); etc. Now for the next fun part: In cell D20, we type “=B20*C20/12” (again without the quotes). Highlight D20, D21, D22, etc., then hit CTRL+d, and we have automatically calculated how much we need to account for monthly for each annual, biannual, etc. expense.

Variable Monthly Expenses
We move down a few cells from our Fixed Less-Than-Monthly section, for us, about A24, and type in “Variable Monthly Expenses.” Yes, the creativity is flowing today! Under this, we type in new column headings: Expense (A25), and Amount (B25). Here’s where you really need the rest of the family to help prevent large gatherings involving pitchforks and torches, because you’re about to decide how much goes to each of several accounts that some will think are vital and other will think are useless (Clothes, Laundry, Food… some people just don’t understand the value of beans and rice!). Our expenses for this section are: Gas for Car 1, Gas for Car 2, Food, Laundry, General Upkeep, Garden, Dinners Out, Random Fun Stuff Together, DH Allowance, DW Allowance. See, this is where family meetings are essential – I wouldn’t have guessed DH would agree to fund my gardening habit, and really wouldn’t have guessed that we could both agree that new clothes aren’t essential any time in the near future.

Total Expenditures and Savings / Debt Payment
Now we go back up to the top of the sheet, where we calculated our Total Monthly Income. We find a handy cell, I’ll use E5, and type in “TOTAL MONTHLY EXPENDITURES.” Beneath that, in cell E6, we get to have a little more fun. First we type “=sum(“ (without the quotes and without hitting enter!), then highlight the cells that contain the fixed monthly expenses (for us B8, B9, B10, etc.), then type “)+sum(“, then highlight the cells that contain the less-than-monthly expenses (for us D20, D21, D22, etc.), then type “)+sum(“, highlight the cells that contain the variable monthly expenses (for us B26, B27, B28, etc.), then type “)” and hit enter. The entirety of what is entered in that cell should be something like =sum(B8:B10)+sum(D20:D24)+sum(B26:B28). What displays in the cell should be the sum of all your monthly expenses. You can check it with a calculator if you want…. At least until you build your confidence with telling Excel what to do.
Now we go down a few more cells, to E8, and type in a handy catch-phrase “Amount Towards New House,” or “How Much Less We’ll Owe This Month.” In E9, we type in “=E4-E6”. Hopefully, we have a positive number. If we have a negative number, it might be time to call the family back in to renegotiate the variable expenses (or we can change them ourselves and hide all the pitchforks).

A Few Closing Remarks
You’ll want to make sure you hit CTRL+s or click the little disk icon in the top right before closing and save your budget somewhere safe. This budget should not be changed frivolously; it should only be done when it becomes obvious that the plan of attack is unreasonable (you discover that you need four times as much money to pay for gas to get to work), based on insufficient information (such as forgetting car registration as an annual expense), or unsustainable (the rest of the family gets their pitchforks and torches in protest of another month of beans and rice three meals a day, every day). You don’t change the budget because you really, really, want that new designer handbag this month (get a knock-off that fits in your budget, money doesn’t grow on trees, you know!).

List of Excel Tricks Used In This Sheet

• = When you type this into a cell first, it performs the operations that come afterwards.

• * This is the multiplication operation in Excel.

• / This is the divide operation in Excel.

• sum( ) This command adds up the cells contained within the parenthesis.

• CTRL+d Typing this takes whatever is in the top highlighted cell and applies it to whatever cells are highlighted beneath it.

• CTRL+s Typing this saves your workbook. If you have not yet designated a name and location for saving the workbook, it will open a window that will allow you to do so (the Save As dialogue).