Learn How to Create a Simple Budget in Excel.
So you need a budget? You’ve read many an article about budgeting. Maybe even one or two on this very site. Like this one. Or this one from last week? And now you’ve finally decided to take the plunge and create your very first budget. Happy New Year! But how do you begin? Creating a simple budget shouldn’t be difficult or time-consuming.
This article is intended to take you all the way through creating a simple budget in Excel to fine-tuning it after a few months of use. Bookmark this article and let’s get started.
I could give you the budget template that we’re going to create, but what’s the fun in that? Besides, everyone is a little different, and financial situations are little different. Which is perfect for Excel, because you’ll be creating a simple budget perfectly tailored for your situation. This budget should take less than 10 minutes to create. It all depends on how well you know your numbers.
Start With a Clean Slate.
So start with a blank sheet and create the following header as shown below.
Click on each cell, and type in each box the words shown. These will make more sense as we go along.
Quick tip: Double click on the area marked by the arrow (between the two column labels “A” and “B”) to automatically widen the column appropriately.
In column “A”, we can start typing the different categories that we need to budget. I like to start with the most important items and work down the column. You can copy the categories that I have, or add your own. This is where the personalizing come in. Add whatever categories you want. You also need to make sure that you include all the debt payments that you have.
Quick Tip: Highlight the entire column and click the “center justify” button in the toolbar as shown to “pretty up” your category list.
Enter Your Money Amounts in Each Category
Now comes the fun part. Well maybe not necessarily “fun”. But now you need to enter all your bills and expenses in the chart. If you pay a bill monthly, then enter the monthly amount in the “monthly” column. You can also enter annual expenditures like car insurance or Christmas in the appropriate column. It’s ok to put numbers in more than one column per category (like “Car” in the sample budget below).
Check out the sample budget shown below, and start filling in your numbers.
Make sure you enter your income in the appropriate column as well.
Once you have all your numbers filled in, it’s time to see how much wiggle room or margin you have in your plan.
Type the following green text in cell “H2” =((B2*52)+(C2*26)+(D2*24)+(E2*12)+(F2*4)+G2) and press “Enter”. You can then drag this cell down to copy the formula into all the categories as well.
Quick Tip: To drag the cell down and copy the formula, mouse over the small solid box on the bottom right corner of the cell. Click on the box when the mouse changes to a “plus” sign, and drag it down as far as you need to.
This converts all your expenses into annual numbers. If you’ve never seen your expenses as annual numbers, this can be eye-opening. It becomes very easy to see where you might have some spending that’s out of line.
Type the following green text in cell “J2” =H2-SUM(H3:H27) and press “Enter”.
One more step to make it easier to plan. We need to add a column to show how to split up each paycheck. I also added a row just under the “Income/Paycheck” line to define the periods per year. You can just type those numbers in the cells.
Then type the following green text in cell “I2”, =H2/$C$3 and press “Enter”. Then drag that formula down like we did before. I chose “C3” in the formula because in the example we get paid bi-weekly. If you get paid differently, just change the formula to the cell under wherever you entered your income
And then I’ll just “pretty up” the sheet a little bit.
Note, if you notice the mortgage/rent budget amount per paycheck is $461.54. If you only have 2 paychecks per month, which is the case most months if you get paid bi-weekly, you’ll end up with a shortfall in that category. The solution is to divide by the value in “D2” instead of “C2 in the categories that you pay monthly – like your utilities. This ensures that every month you have enough money to cover your bills.
We’re almost done – I promise.
Fine-tune Your Numbers.
We need to add up all the planned budget categories, and adjust the amounts to use up the entire paycheck.
In the “I” column, at the bottom of your numbers, type the following green text, =SUM(I4:I27) and press “Enter”.
Now you can fine-tune your numbers until your paycheck equals the number at the bottom. This is called zero dollar budgeting. Plan every dollar you have.
Some Final Notes
Congratulations! You’ve created a budget. It wasn’t that hard, was it? Now you just need to make sure you stick to it. Only spend the money that you’ve allocated for the different categories. You’re already doing better than two-thirds of all Americans. Most don’t budget.
To make sure you know how you are spending, you’ll need to track your transactions. There are several ways to do this. You can download your transactions from you bank, and manually categorize them in Excel. Or you can save receipts and manually enter them in Excel or another program like Quicken. Of course, this is what I do. I told you I was a money/numbers nerd.
Don’t worry! There’s an easier way. With Mint or Personal capital, you can link them to your bank account and let them do all the hard work for you. Then all you have to do is compare your newly created budget to what Mint or Personal Capital says you spent in each category each month. “Easy-peasy”, as my youngest son would say.
How about you? Do you budget in Excel, or do you use another program?
Let me know in the comments, and as always, I thank you for reading and sharing.