Creating A Budget In Excel

Posted By on December 7, 2012

No matter who you are, creating a budget should be a priority in your life. If you are a homesteader, however, it is all the more important because, typically, we do not have as much money to throw around on frivolous things. I am thankful to have a full time job, but many people do not. Even though I am blessed, that doesn’t mean I have slipped into an unmanageable lifestyle of spending every dime I have on wasteful things. Instead, I spend every dime I can on things that matter, and save the rest. Creating a budget in excel is how I have managed to do this. I want to help you to accomplish the same thing.

A Realistic Mentality

I am not sure about everyone, but to me, the most important part of budgeting is changing your expectations of life to a simpler, peace centered mentality. From that change will flow everything that will give you great abundance in life. It is the foundation of happiness to realize that you cannot have everything you want, and that what you have is precious. This includes things in your house, and the people around you. When you begin to care for things that are important to you, such as tools and books, you have taken an important step in budgeting your life. This is not to say you should love material things, I mean that you want to protect what you own from wear and tear and from the elements. You want your things to last a lifetime of use, so that you can look at something and remember all the times that you have had with it.

Enough of my hot air.

Even if you have not changed what you want from life, you can still benefit from the information I am going to share. Knowing where every penny in your money supply is going protects you in the present and safeguards your future against financial problems. It lets you control your future more than anything else you can do. Using my methods, a person making minimum wage could technically live a more fruitful financial and mental existence than someone making $100,000 a year who ignores all reason around sound money management and being in tune with their bank accounts.

What Is Budgeting?

This may be a question that many people have, since there are so many ways to budget. To put it simply, budgeting is the allocating of resources for specific areas. It is awareness. It is purpose. It is saying, “I will spend this money on this category of items.” or “I will not spend more than this amount of money.” It is that simple.

There are many ways to create a budget. From pen and pad of paper to specialized software like Quicken. I personally use Microsoft Excel because it gives me the greatest flexiblity at the lowest cost. It is a tool that is not specific to budgeting and so can be used for other things as well. You could also use OpenOffice, which is a free version of spreadsheet software, however, I have found it to be slow. Making a budget in excel is the best option in my opinion.

Let’s Get Started – Making a Budget In Excel

*Note Before We Start* – All financial figures in this article are not indicative of my true finances. They are for demonstration purposes only.

Time for the details. These details are in no way inclusive of everything. Over the years, I have refined my budget numerous times with ideas that pop up. Identify a problem and think of the solution.

Define Your Budget Cycle

Your budget cycle will be the length of time in which you break down your budget. If you want a weekly budget your cycle is a week. A monthly budget will be a month. I use a weekly budget because it gives more control. A daily budget might be overkill.

First create your spreadsheet. I always have each column in my spreadsheet dedicated to my defined cycle, so in my case, one week.

Date Columns

Date Columns

Each weekly cycle starts on a Sunday and ends on Saturday. I place my cycle ending date in my column. This date is at the top of my excel column so that I know it applies to everything underneath it.

Define Your Categories

The next logical step is to define your budgeting categories which will consist of your income streams, your expenses, your bank accounts, etc. The best place to place these categories is in your first column, A. Each row will be dedicated to a specific category. That way, as time progresses, your budget moves horizontally across your spreadsheet.

Budget In Excel Categories

Budget In Excel Categories

I like to further divide my budget categories into groups, such as ‘Accounts’, ‘Income’, and so on. Highlight these group headers in a way that makes them stand out. Black and white is excellent. I also use colors in my spreadsheet.

Freeze Your Spreadsheet Panes

At this point, you have defined some rows and columns that you want to be visible no matter where you move on the spreadsheet. To accomplish this, you have to freeze your panes. Here’s how you can do that.

First, click on the cell that will define your freezing structure. The way this works is that every row above your selected cell will freeze, and every column behind your selected cell will freeze. Something like this ->

Frozen Cells In Excel

Frozen Cells In Excel

All the highlighted cells will stay in place no matter your location on your spreadsheet when you freeze your panes at the selected cell.

After selecting your cell, click on ‘View’ and then ‘Freeze Panes’ as shown.

Freeze Panes In Excel

Freeze Panes In Excel (Click To Expand)

Then click on ‘Freeze Panes’ in the drop down. You have frozen your panes. You can follow this process of clicking ‘View’ and ‘Freeze Panes’ to un-freeze those same cells as well. Just click ‘Un-Freeze Panes’.

Create Some Logic

This is the step that highlights the power of creating a budget in excel rather than using other methods. Excel lets you build in logic to your spreadsheet that will automate mathematical tasks such as adding, subtracting, and so on. I can think of a few obvious applications for this logic. Let’s start with your ‘Accounts’ category.

Your accounts will consist of last weeks balance, plus this week’s income, minus this week’s expenses. It’s that simple and it’s easy to create in excel.

Excel Sum Function

Excel Sum Function

Here is how that picture works. It all hinges on an Excel function called SUM. As you can guess, SUM will add values together and output in the cell the total value. All you need to do is to supply it with values to add together. You can specify specific values, such as 1, 2 or 3. You can also (this is powerful) specify other cells within your spreadsheet by giving the function the coordinates of the cell. The SUM function will then add the value in that cell to it’s total.

So in our example above we are adding the following cell values to our Checking Balance for the week ending 1/7/2012->

The values in C3, D16, D17, D20 and D29. These cells correspond to last weeks checking balance (C3), this weeks paychecks (D16 and D17), this weeks interest (D20) and any other deposits (D29).

We are subtracting out the following->

The values in D39 and D58. These cells correspond to any withdrawals from our checking (D39) and all our expenses for the week (D58). D58 is summed across D48-D52. This is a handy shortcut.

This is the full line of text in that highlighted cell (D3) -> =SUM(C3+D16+D17+D20+D29-D39-D58). It is that simple, and very intuitive.

With this knowledge, you can do a lot more. You can do the same thing for the savings account and all it’s deposits and withdrawals. You can also drag the cell with your function in it, into any adjacent cells and it will create the function for you, using the cells for the next column over. So dragging from column D, would shift your function to look at the cells in column E. It’s very handy!

I have also summed across the Savings Account and the Checking Account so that I can see a ‘Total Value’ for my accounts. This is done in the same way as up above.

As you can also see, I have entered some data into our spreadsheet, including paychecks, interest and expenses. Now all you have to do is enter these variables, and your balances will update automatically. No need to re-enter them. If you feel like it, you can add up the numbers in this picture, just to verify their accuracy.

Data Entered In Excel

Data Entered In Excel

Some Tips and Tricks

As I said, over the years I have changed my excel spreadsheet more than once. I have thought of nifty tricks that let me squeeze more value out of creating a budget in excel. I’d like to share some with you in the hopes they help you as well.

Indicate Which Weeks Are Over

This has been one of the handiest little tricks that I have come up with. Initially, I just used a separate row beneath my end date to indicate that the budget cycle was over. Now I shade out the entire column as shown below. I use gray because that week is essentially “set in stone”, since it’s over and you can’t change it.

Shaded Excel Cells

Shaded Excel Cells

To do this, just highlight the cells you want to shade, ‘Right-Click’ on them and select the ‘Color-Fill’ button (it’s next to the ‘Text-Color’ button). Then just select your color.

Adding Comments

Adding comments to your budget cells is a very important part of making a budget in excel. This lets you add details that would otherwise be lost in your memory. Particularly, adding them to individual purchases and other similar categories lets you look back and view what an expense may have been.

To do this, just right click on a cell and click on ‘Insert Comment’. Then just type away as shown below. I usually add three things – The items purchased, the place I bought it, and the data I bought it. You can then just move your mouse over the cell and the comment will appear for you to read.

Comment in Excel

Comment in Excel

Create A New Year on Each Sheet

Another handy tip I have is to separate each year within it’s own sheet.

Separate Years on Sheets

Separate Years on Sheets

Tracking Individual Purchases

This is a key step to organizing a budget in excel. I like to make a distinction between monthly expenses that occur regularly and individual purchases that are mainly impulse buys. There is a big difference between them – one is very controllable, the other is not so much unless you make life changes like getting rid of cable service and so on.

I keep all of my individual purchases at the very bottom of my sheet as shown below. I then sum them together using the same function I showed earlier in a row called ‘Ind Purchases’. This is right above my ‘Total Expenses’ row which includes this ‘Ind Purchases’ row, since they are expenses.

Tracking Individual Purchases

Tracking Individual Purchases (Click To Expand)

Display Your Weekly Overage/Underage

Building on that previous trick I showed, a great addition to tracking individual (or variable) purchases is giving yourself a weekly allowance for these types of things and then keeping track of how you are doing in relation to that allowance.

The way I do this is that I define two additional rows – my ‘Allowance’ row and my ‘Over/Under’ row. Both of these can be better explained by the picture below.

Weekly Allowance Over/Under

Weekly Allowance Over/Under (Click to Expand)

This idea is very simple. The ‘Over/Under’ row calculates the difference between all the ‘Individual Purchases’ for a given column (week) as compared to the ‘Allowance’ row. If I have gone over my allowance for the week, the number will be positive and I will change the text color to red. If I am under for the week the number will be negative and I will color it green. Red is bad and green is good. To top it off, I sum across the entire ‘Over/Under’ row to display how I am doing for the year. You can see, despite having two major ‘Over’ weeks, I am still ‘Under’ for the year by $37.50 in this example.

Calculate Savings Rates
Another thing you can do is to calculate your savings rate for the year. To do this, use the sum function on your ‘Income’ row, your ‘Savings Deposits’ rows, and your ‘Savings Withdrawal’ rows. Then just subtract your ‘Total Withdrawals’ from your ‘Total Deposits’ and divide by your ‘Income’. This will give you a very basic savings rate.

What Does This Give You?

For starters, this gives you a way to view trends in your spending. You can create graphs from this data. You can look back and see where your money has gone. More importantly, you can see where your money is going based on how you are handling it. You can anticipate problems (such as a depleted checking account) that stem from saving too much or having to many expenses. This, to me, is the most valuable part of budgeting and can only be fully and easily realized when budgeting in excel. This type of power would be very clunky and prone to error using a notepad and pen.

Looking To The Future

Looking To The Future

How Has This Helped Me?

I paid off my mom’s credit card debt in 6 months. Her balance was $10,000. I paid off my student loans in about 9 months. The total balance was over $15,000. I also paid off a $1,300 loan while in college. I have paid off (as of now) just over 30% of my mortgage which totaled $72,000 when it started. I started paying it (full time) one year ago. We will have it paid off in just about 2 years from now. That is a $50,000 balance in two years. So since graduating from college, 3 years ago, I have dealt with nearing $50,000 in debts. By the time I have been out of college for 5 years, that number will sit at over $100,000. I am 26 years old (right now) and will be debt free with a paid for house 5 years out of college when I turn 28. You can do it too! It’s entirely doable.

That may seem like a lot to you but it is merely $20,000 a year. How many people make $20,000 or more per year in after tax income? Chances are you do. Most do. With what I have laid out here, you can accomplish what I have done and beyond. It just takes identifying a goal and sticking to it no matter what. It also takes a very frugal person, one who knows that his sacrifices now will bear fruit down the road. After all, what would you do with an extra $1,600 a month after paying off your debts? I’ll probably just buy some more books. 🙂

About The Author

Comments

Leave a Reply