Using Excel: Part 2 – Creating a Budget

So you’ve been tracking your expenses, that’s great! I’d suggest tracking your expenses for two months or so, just to get a feel for how much you spend on average.

For example, after one month, my spending tracker might look like this:

Budget1_2

Of course, this only gives a partial picture. Obviously I won’t be paying $500 for auto insurance every month, and maybe I had exams this month, so I didn’t go out to eat or to the movies as much as usual. However, I’m a bit impatient to start budgeting, so maybe I’ll try to set something up now.

Copy the contents of the first tab (Sheet 1) into the second spreadsheet tab (Sheet 2). If you’re slightly obsessive, like me, you can right-click on the tabs and rename them for the month of your spending. If you’re REALLY obsessive, you can format your numbers so they’ll appear with $. To do this, highlight the numbers, right click, and choose “Format Cells.” In the menu that appears, select “Currency” and the correct symbol (I’m using “$”) from the symbol drop-down menu.

Budget2_2

In the February tab, delete all of the old expenses but leave the “Balance” column and your expense-tracking cells alone.

Budget3_2

Now, I’m going to assign amounts for each of my budget categories; these amounts will be in Column C. I’m also going to add a line item for “Deposits” and a row for my monthly net – i.e. what’s left over at the end of the month. To add my deposits (Use row 20 here or the formula will be off!), I change the cell formula to

=SUMIF(A$26:A$90,A20,C$26:C$90)

To determine the money left over or overdrawn at the end of the month, use the following formula:

=B20-SUM(B1:B19)

Budget4_2

Finally, in Column D I’m going to show the difference between my expected spending and my budgeted amounts. In the top row, enter the following formula:

=C1-B1

The “Deposits” row is again going to be different:

=B20-C20

As you enter and categorize your spending, you can begin to see how realistic your budget is. You can see where your categories are lacking. In the example below, I can see that I forgot about the cell phone bill when setting the budget. We spent much less on Entertainment because I had exams, but I overspent on clothes fromt he stress. We also overspent on “Miscellaneous,” but then I see that my parents wrote us a check for the boat tickets, which is where we overspent.

Budget5_2

Once you’ve been tracking your spending, setting limits is the natural next step. The comparison column can give you an idea of when you’re close to your limits – and when you’ve gone over them. It can also help you see where you’re NOT going to spend so you can go over a bit in a few other areas.

Next time, I’ll go over some of the trickier stuff, like figuring out interest payments and principal payments on loans – as well as helping to calculate the effects of paying exta on the loans.

Advertisements

One Response

  1. Thanks for sharing this! It’s very handy to see it all in categories. I used to have a daily expenses Excel file but it wasn’t broken up into categories, it was by date. I think I’ll keep that in monthly tabs but also have this as a main file because the breakdowns of expenses are clearer!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: