Making A Budget
Setting Up a Budget in an Excel Spreadsheet
I’ve seen a few of these around (here and there) on personal websites but in my searches I found them only mildly helpful. Any time you set up a budget, it’s going to be different from someone else’s; everyone has different needs. That in mind, I thought that instead of giving you an already-made spreadsheet to download and plug in values, I’d show you how to make the spreadsheet yourself, including the necessary calculations. You can then build off of my instruction to make the perfect budget spreadsheet for your needs.
I’m going to be building off of my spreadsheet so just keep that in mind while reading this article.
You’re going to need to create the following headers in whichever format you choose. Remember to leave space below each to put in values: Necessary Values, Monthly Budget, and Weekly Budget. There will be subheaders within each of these, but I’ll explain those further…
Necessary Values
First, you’re going to want to set your necessary values. These, among others, will affect the rest of your spreadsheet and provide the basis for your budget. Namely, your hourly wage at work and the total number of hours you work each week.
Monthly Budget
From this, you can obtain your monthly income. Welcome to your first equation in this particular Excel spreadsheet! Let’s call this your page so far.
You’ll have to adjust your equations based on where your fields lie, but I will always show you where mine are so you can get a relation between the equations I give you and the screenshots.
In box B12, you’ll want to calculate your monthly income by multiplying your hourly pay (B5) by your weekly hours (B6), then again by how many weeks there are in a month (we’re assuming four, since that’s generally the case). The equation, in Microsoft Excel terms, should look like this:
=B5*B6*4
Simply put that in box B12; when you hit enter, your monthly income will appear. You could, if you wanted, change “weekly hours” to “monthly hours” and then just multiply your hourly pay by that (leaving out the four, of course), but I prefer to have each step of my spreadsheet broken down into bits so I can see the whole process. It’s more fun that way! If you don’t work the same hours every week, however, using a monthly hour value may be more beneficial to you. Again I stress that all spreadsheets will be different depending on the needs of the individual.
Note also that the monthly income I’ve calculated here is the simplest value and thus before taxes. You will have to take taxes into account, but as taxes are different everywhere, I can’t really give a lot of instruction about that. This is just a basic spreadsheet.
Next we’ll want to put in some other necessary values, so you’ll probably have to add to your rows below that heading. Here you will plug in all your necessities from every month. This does not include things like your domain hosting expenses, hanging out with your friends, buying gifts, etc - all of which will go under “miscellaneous spending.” I’m talking bills, rent, and food.
Again, you’ll have to plug in what is important to you, but here is mine:
I’ve chosen to round my values up because I’d rather have a strange amount of extra money at the end of the month than find I’m several hundred in debt because I was too skimpy on my budget. If my values seem low to you, keep in mind that I share an apartment with someone and we split the bills down the middle (or try to). How sad it would be if I had to pay the whole $971 in rent by myself!
Anyway, now that you’ve defined your monthly spending you can get into the calculations of your monthly and weekly budgets.
Your total payments per month is simply a sum of all those payments (duh). You can use spreadsheet tricks (dragging the box, for example) to do this, but I’m going to clearly outline what you’re adding so you get a better sense of it. In box B19 you’ll want to put the following equation:
=B7+B8+B9+B10+B11+B12
(Your tricksy option is =SUM(B7:B12), but this isn’t a lesson on the different ways to make equations.)
This will yield the sum total of your expenses per month.
After all your payments, you’re going to (hopefully) have some money left over. Not much, perhaps, and when you consider the fact that you didn’t take out taxes, it’s going to pinch your results a bit… But this is the extra money that I recommend you keep in a savings account. If you’re at the point where you need a budget, you probably want to start building a savings account so that future emergencies won’t break your life.
This one is simple. The following equation goes into B21:
=B17-B19
It subtracts the total payments from your monthly income.
So! There’s your monthly budget. It looks simple (it is simple) but hopefully once you’ve put it together you can get a better idea about your spending. I’m going to take it one step further, however, and break down my approximate weekly spending. Some bills will get paid right away at the beginning or end of the month - rent, for example, is usually due within the first five days. Other bills, like gas and groceries, may be sprinkled throughout the month depending on when you need to spend. A weekly budget will help you visualize when to set aside certain amounts of money
Weekly Budget
Keeping your monthly budget up there with all your necessary values in tact, your weekly budget should be pretty easy to construct. Under your header, you’ll need to make a skeleton. Your skeleton includes your weekly pay, payments/bills, total money spent for each week, and if you’d like, your money left for spending (again).
First step is to plug in your weekly pay. I’ll be dividing it out evenly - so your weekly pay will be your monthly pay (still in B17) divided by four (for each week). In B25, C25, D25, and E25, you’ll be putting this equation:
=B17/4
Next, divide your bills accordingly. I pay my cell phone bill at the end of the month, but I buy groceries rougly once a week. I only (thankfully) have to fill up my gas tank once a month and I try to do that in the middle, but some other people may have to fill their tanks 3-4 times a week. So just divvy out your bills as you see fit. I’ll tell you what I’ve done.
For the bills I only pay once a month, I simply told the spreadsheet to copy that value into the week that I pay that bill. For example, in box E27, I needed the value of my cell phone bill to repeat. Instead of writing $60.00, I wrote the following:
=B7
That way, if my cell phone bill goes up or down I only have to change the entry under my “necessary values” and it’ll change in my weekly budget. I did the same for gas (=B8), other bills (=B10), and rent (=B11), in their appropriate places.
For groceries, I had to divide the amount per week, as I tend to buy a lot of fruit and thus have to visit the grocery store often. It is the same equation as I used for the weekly pay: The total cost of groceries per month divided by four, in each box.
=B9/4
Obviously I have an easy number ($40) but what if your groceries were $56.02 on average every month? You’ll be thankful for making a spreadsheet.
My miscellaneous spending, on the other hand, I only try to do in the last three weeks of the month. Obviously the first week is my biggest spending week since I have rent, bills, and groceries to take care of. If I can avoid it, I don’t spend any other money during the first week of the month. Thus, I have to calculate accordingly. Instead of dividing my miscellaneous spending by four, I divide by three, and put those values in the last three weeks of the month.
=B12/3
It really puts into perspective just how little I have to spend each week: this is what really helps, I think, when making a budget!
You could be done here if you’d like, but I’m going to take it a step further and add up my total money spent each week. If for whatever reason you end up spending more than your weekly allowance, you can plug in a different value somewhere and this spreadsheet will tell you how much you have left over after that, too. However, I’ve created my budget to tell me how much I have to spend, not to tell me what will happen if I spend too much or save a little more. For my needs, I just want to know how much more I’ll have left over at the end of the month if I make more money or work more hours.
Again, you’ll be calculating the sum of several cells (boxes). You can either take it the long way or use one of the shortcuts specific to the program. I’ll be telling you the long way.
In row 35, under B, C, D, and E accordingly, you’ll put the following (filling in the correct letter for each column):
=B27+B28+B29+B30+B31+B32
And your remaining money, should you choose to repeat this value, can be calculated from this spreadsheet by first calculating how much money was spent over the month, then subtracting that value from your monthly pay (B17). In cell E37, I have put the following equation:
=B17-SUM(B35+C35+D35+E35)
This is valuable as it allows you to plug in (if necessary) any other spending you’ve done - if a bill is higher than normal, for example, you could put the higher value under “other bills” - and give you the remaining value you have after all spending. The monthly budget doesn’t allow you to do this; it would require for you to change the actual bill value every time you went over a bit.
You’re Done!
So, there you have it, a budget for your expenses. My next budgeting articles will teach any of the following things: Figuring out how much you spend on gas every month, the same article as this one except taking taxes into account, projecting how much you need to make/how many hours you need to work in order to afford a long-term financial goal, and perhaps more.
