Tips for Excel spreadsheet budgets

By Stew

The Microsoft Office suite of programs is nearly universal when it comes to personal computers. Nearly everyone has a working knowledge of Word and is familiar with PowerPoint. Outlook, Microsoft’s email software, also has wide use and acceptance. However, my favorite MS program is Excel. When I worked as a soccer coach, I used Excel in almost every facet of my program, from budgets to planning practice to recruiting to filling out rosters. There was almost nothing that I did in the office without Excel.

Naturally, Excel is my program of choice when it comes to our household budget. There are many quality household budget software programs available to consumers, Excel might be the most readily available solution to your budget needs and with a few tips, you can learn to keep track of your finances with the Excel program that is probably already installed on your home computer. To get you started, here are a few of the features of the spreadsheet that I have developed for our family over the years:

As you can see, I left out a few months in order to allow the screenshot to fit into the post. Also, in case you were wondering, the numbers are completely fictional.

Projected Income/Expenses

When I set up the budget in January, I project our spending in every category for every month of the year. Sometimes, the projection is the same in every month, for example, the life insurance payment never varies. Excel’s “drag” feature works quite well for this. However, when it comes to recreation or food expenses, I can change the numbers from month to month based on planned activities in the future – vacations, holidays, etc.

This budget also has a place for cash “on hand”. This is where we keep track of the money in our emergency fund. This money is not designated to be spent in any one category, but we have to dip into it from time to time throughout the year. Major car or home repairs, dental work and other things along those lines can affect the cash “on hand”. If you keep that number up to date, you can keep an eye on your complete financial picture at the end of the year.

Actual Income/Expenses

At the beginning of the year, all of the numbers are filled in and as each month passes, I simply adjust the amounts to reflect actual spending as each month passes. When those numbers are changed, I can immediately see the impact of that change on monthly spending, surplus/deficit as well as yearly spending, surplus/deficit. Obviously, income numbers can also be changed in order to reflect projected fluctuations in this area.

Formulas

Not everyone is comfortable in setting up formulas in Excel, but you can learn to do this relatively quickly. You really only need simple addition and subtraction formulas for most of the budget. I have set up a few percent formulas, for instance, the our tithe is always set at ten percent of gross income. I also set up formulas for Medicare or Social Security withholding in this budget.

Everything at a glance

My favorite feature on this budget is that you can see almost all of our budget information on a single page. Everything from our Average Monthly Income to our Yearly Surplus to what we spent on Gasoline in March is readily available on a single screen. This budget also allows you to see your total annual spending in any category immediately. For instance, according to this budget, it is easy to see that at the end of the year, this family will have made $1,800 worth of payments on their auto loan.

I like an Excel budget because I enjoy tinkering with it. Excel is a great way to keep track of your budget, but it is not a “set it and forget it” program when it comes to budgets. This type of budget record keeping is only as good as the information that you feed it. If you neglect to keep the numbers up to date, then the picture of your finances will quickly become inaccurate. The end of December is a great time of year to begin working on your budget for next years and if you like this style of budgeting, please feel free to email me: glblstew (at) gmail.com and I will send you the template.

 

 


5 Responses (including trackbacks) to “Tips for Excel spreadsheet budgets”

  1. Corey @ Passive Income to Retire Says:

    It sounds like we have a similar hobby. I like to have a new page for each month, so that I can include more details. Then I have it set up to have each month’s figures automatically contribute to a yearly overview page. Best of both worlds, if you ask me.

  2. Jen Says:

    Hi, I just downloaded ver. 1.1 in order to get my family on track for the coming new year, and I had a question. Both my husband and I have multiple things deducted from our paychecks pre-tax such as health insurance, 401(k), etc.. Since this isn’t considered part of our Net income, should we be tracking it on the spreadsheet, and if so, how? Any input is appreciated, thank you for providing us with this tool, we are feeling very hopeful and positive about getting our financial house in order!

  3. Stew Says:

    Jen . . . I think that you can successfully budget without tracking that type of income, however, I do track them for three reasons: 1) I like to know where every penny goes 2) sometimes I need the totals for tax filing and 3) because I tithe on my gross.

  4. SEO Says:

    Great tips. Not very good at Excel but I can see the positive on learning how to use the app.

  5. Jennifer Says:

    Fantastic tips to say the least. I have never been able to catch up with such awesome tips before. Really can;t thank you enough for sharing them. I am going to bookmark it for future reference.
    Jennifer Goldblum

Leave a Reply

css.php