How to create a budget in excel

how to create a budget in excel

How to create a zero-based budget

In my last post, I covered why most budgets don’t work and how to fix them. One of the ways to make your budget work is to create a zero-based budget. Today’s post outlines how to create your first zero-based budget. Over the next few weeks I’ll be addressing various aspects of creating and managing a budget. Let’s start with the basics. Some of these steps may seem obvious or simplistic. But for those who just can’t seem to get a budget started, I hope to give you some step-by-step detail that will help make creating a budget easier.

What is a zero-based budget?

A zero-based budget is one where your total income minus your total expenses equals $0. In other words, it forces you to assign every dollar of income to an expense (or savings) category. As Dave Ramsey puts it, you’ll be “spending your month’s income on paper” before you spend it in real life.

Benefits of a zero-based budget

Using a zero-based budget and properly dealing with the difference from month to month will allow you to gain total control over every dollar you spend. If you get a bonus or spend less than you planned during the month, you can easily redirect that money where you really want it instead of letting it dissipate through unfocused spending.

A word about spreadsheets

I highly recommend using a spreadsheet to do your initial budget because it’s very easy to calculate amounts and rearrange the order of items. If you don’t have Excel, you can download the free Open Office CALC spreadsheet software or use a free online spreadsheet like Google Spreadsheets or NumSum.com. Simply using paper and pen is ok too. If you do, you may need to re-write it a couple of times and be sure to double check your calculations.

When first starting your budget I would NOT use the budgeting tools in Quicken, MS Money or other automated tools. You may be able to use those tools effectively once you have a solid hold on your budget, but for now it’s best to make your budget by yourself so you know every nook and cranny. That way you’ll be less likely to make errors due to not understanding how an automated tool is built. You will also be more likely to use a budget if you create it yourself. Once you have a high degree of confidence that your budget is working properly, feel free to experiment with pre-built tools and spreadsheets like pearbudget.com.

Preparation: Get out those statements

Before you get into the thick of things, you’ll want to do a little preparation by collecting the following:

  • Pay stubs
  • Records for other income such as bonuses, gifts, and tax returns
  • Copies of your recurring bills
  • If you track expenses in Quicken or MS Money, print out monthly reports of your expenses for the last few months
  • If you use checks regularly, it may be useful to have your check register on hand

Agree to be civil

Now take a few deep breaths. If you are doing this with a spouse, agree to be civil. Ask yourself “how can I do this and enjoy it?” As you go through the initial steps of allocating, don’t nit-pick too much. If one person wants to budget funds for a category and the other disagrees, let them budget the funds and you can go back later and adjust once you know if you’re over and by how much.

If things tend to get heated, I also recommend setting a time limit for your budgeting. My wife and I tend to do well in chunks of about 30 minutes. Once we go over that, I start to get grouchy. It’s ok to do this a little at a time. If you schedule 30 minutes a night for several days, you should be able to get through everything.

Step #1: Write down all your sources of income for the month

Let’s get started. If you have a fixed paycheck once or twice a month, this step will be easy. Just write down how much you make every month. If your finances are really tight, you should do a budget for each paycheck to ensure you have the funds on hand to pay bills that occur in that time period.

If you are self employed or have an irregular source of income, you’ll want to wait until you get an actual check and then follow this process for just that check. In the meantime, you can follow this process for the money you have available in your bank account. Just use your balance as the income. For example, if your bank account balance is currently $3,000 then put that amount as your income. As we go through this process you’ll be allocating how you’ll use that $3,000 until your next paycheck.

Do I put down net or gross income?

It really doesn’t matter if you put down net or gross. If you use gross (the amount before taxes, insurance, etc that are automatically deducted from your paycheck) you need to be sure to include the categories and amounts that are automatically deducted from your paycheck in your budget. I prefer using net so that I don’t need to write the extra expenses down every month. Because taxes and insurance are the same from month to month I prefer to simply check the amounts every quarter or so to make sure everything is still the same. It’s more efficient to track them separately.

Of course, if you’re self employed, be sure to allocate for paying taxes.

Step #2: Write down a list of expenses

Write down a list of all the expenses you expect to have this month. I’ve included a list of possible expenses below to prompt your memory. Be sure to include expenses unique to only this month. Do you have a friend or family birthday? Is your registration due? This step may actually unearth some expenses that you forgot about. If you think of expenses that are coming up but not in this month, that’s ok, just go ahead and write them down and we’ll deal with them a little later.

  • Income
    • Paycheck 1
    • Paycheck 2
    • Other Income 1
    • Other Income 2
  • Expenses
    • Taxes (if using gross income or you are self employed)
    • Mortgage Payment
    • Second Mortgage payment
    • Household (yard)
    • Utilities: Gas
    • Utilities: Elect/Water/ Gar
    • Auto: Gas
    • Auto: Insurance
    • Auto: Maintenance
    • Auto: Registration
    • Satellite TV
    • Life Insurance
    • Debt reduction
    • Babysitting
    • Clothing
    • Grocery
    • Grocery: Eat Out
    • Grocery: Eat Out
    • Grocery: Nonfood
    • Medical
    • Hair cut/personal care items
    • Charitable Donations
    • Emergency Fund
    • New car savings
    • College Fund
    • Dry Cleaning
    • Gifts: Birthdays
    • Gifts: Christmas
    • Gifts: Holidays and Other
    • Household: Maintenance
    • Retirement Savings
    • Magazine Subscriptions
    • Entertainment: Dates
    • Entertainment: Video rentals
    • Personal money (1 for each individual)
    • Cushion

You’ll probably miss an expense or two at first and find yourself part way through the month saying “shoot, I forgot to budget for that.” To address this scenario, be sure to budget a “cushion” account (last week I called it a “grease” account, but I think cushion is simply more understandable and descriptive, so I’ll stick to that). I recommend starting at about $100 at first. Over time, you’ll be able to get a feel if this is too much or not enough.

Include savings and debt reduction in expenses

When I say “expenses,” I really mean “funds that will be spent or allocated to other purposes.” Saying “expenses” is just so much easier. Include any savings allocations, debt reduction payments, or any other monetary outflows in your expense list.

Step #3: Identify your expense types

For this step, simply go through all the expense categories and mark if they are fixed, semi-fixed, or variable. Just write an “f,”"s-f,” or “v” next to the category (or in another column if using a spreadsheet). Fixed expenses are those that don’t change from month to month like your cable bill. Semi-fixed expenses are those that may vary slightly from month to month like a phone bill. As a rule of thumb, semi-fixed expenses shouldn’t vary more than $10 in a month. Variable expenses are those that vary

from month to month more than $10 like groceries or gas expenses.

The reason we marked each expense type was to determine the order to allocate them in. First allocate your fixed and semi-fixed expenses. I recommend doing this simply because it’s easy. Your fixed expenses will probably include your largest expenses, such as your mortgage, so it will be easier to deal with the smaller amount left over. Plus, most of your fixed expenses are probably not very negotiable without dramatic lifestyle changes or disruptions so they give you a sort of “hard landscape” around which you will fill in the variable expenses.

Once we are done allocating all our expenses, we’ll circle back and see if we want to eliminate one or more of the fixed expenses. For now though, allocate them all.

Average out your semi-fixed expenses

For your semi-fixed expenses you’ll have to average out how much you’ve spent over the last 3-4 months. No need to get too crazy or precise as long as your in the ball park. You’ll be wrong anyway.

How to deal with periodic expenses

There will be many expenses that won’t occur this month but that you will need to save for like car registrations, birthday and Christmas gifts, and some insurance payments. To ensure you have enough money when the time comes you need to start saving that money now.

Most people just divide these expenses by 12 and save that amount each month. DON’T TAKE THIS APPROACH WHEN STARTING A BUDGET. You will end up short unless that expense is a full year away. Instead you need to take each expense, count how many months away it is, and divide the total payment amount by the number of months. For example, if I have a car registration payment of $100 due in four months, I will divide $100 by 4. That means I should budget $25 a month to save towards the registration. As soon as I pay the registration, I can then divide the next registration payment by 12 and save little by little for next year.

This approach may cause a little strain on your budget at first because you will need to be saving a larger amount each month for the expenses coming up in the short-term. However, once you make the payment, your monthly allocation will go down for that category freeing up extra cash that you can redirect wherever you want.

There is one other approach I should mention. My wife and I find that we will fairly consistently receive “windfall” money two or three times a year in the form of bonuses, gifts, or tax returns. Occasionally we will budget portions of the windfall to periodic expenses so we don’t have to worry about saving from month to month. The only problem with this approach is that if you don’t have enough windfalls, you could end up having a periodic expense and not enough money to pay it.

Step #5: Allocate your variable expenses.

Now that you’ve gotten a good chunk of your income out of the way, it’s time to deal with what’s left (hopefully it isn’t depressingly little). So far we haven’t worried about calculating income minus expenses. If you want to, you can do a quick calculation at this point so you know how much left over you’re dealing with. Or you can just speed through and budget your variable expenses and do a mass calculation at the end.

Try not to scrimp too much on your necessity categories like food, clothing, and transportation/gas. Most people underestimate these categories.

Personal money

I highly recommend allocating personal money for each spouse. Having your own money to spend however you want is crucial to making a budget work. Even if you can only afford to budget $10 or $20 dollars, it will help your budget feel more manageable.

Step #6: Calculate the difference between income and expenses.

Ahhh, the moment of truth. Subtract your total expenses from your total income. This is where a spreadsheet comes in handy. You might want to be sitting down when you do this.

Step #7: Adjust your categories until income = expenses

Now comes the hard part. You need to adjust your categories until your income equals your expenses. This is where you will need to make some trade-offs between one category and another. This step is usually where the most conflict occurs between couples because it exposes their conflicting values. If things get too heated, it’s probably better to take a break and continue later. Just remember that this is your first budget and you will refine things as you go. You don’t have to feel locked in to the decisions you make now.

What to do with a positive difference

If you’re in this situation, congratulations! Now you just have to allocate the remaining money. The whole point of a zero-based budget is that you need to ALLOCATE EVERYTHING. That way the remainder won’t just disappear through unconscious spending. The good news is you can allocate it any way you want. If you are going to allocate it as money to blow, that’s fine as long as you consciously do so. Some other suggestions for allocating this money include:

  • Pay down debt
  • Save for retirement or your children’s college
  • Save for larger purchases like vehicles or furniture
  • Save for a vacation
What to do with a negative difference

I’m guessing that the vast majority of people will have allocated more expenses than they have income resulting in a negative difference. Don’t be discouraged! The first time we did this, reality hit us hard. We had to do a major evaluation of our priorities and really distinguish between our wants and needs.

For many families this process will expose that they have been spending more than they make and can’t support their current lifestyle on existing income. It can be extremely hard to realize that lifestyle changes are in order, but at least you now know the truth and can fix your problem instead of going into more debt.

Here are some suggestions for adjusting your budget:

  1. Identify all your non-necessities. Yes, cable is a non-necessity.
  2. Each spouse should rank the non-necessities in terms of importance to them
  3. Eliminate or reduce those that both spouses agree are a low priority

Hopefully by eliminating or lowering the easier “consensus” items you will now be at a zero balance. If not, you will have to negotiate which categories are most important to each of you. You may have to make a lifestyle change by either earning more income or lowering your cost of living. In some cases, moving to a less expensive place may be in order. Housing is usually the largest expense and can make the biggest difference to your expenses. Not long ago, my wife and I almost had to move in order to live within our means because we had a bad year with some unexpected medical expenses. That is what prompted us to really take control of our finances. If we hadn’t got on a budget, we would have had to move to a less expensive home.

Step #8: Print out your final budget

I strongly recommend you print out your final budget and put it in a binder. This gives you a hard-copy record of your decisions. The problem with keeping only an electronic version is that you sometimes can’t be sure if it’s been changed from the original. Printing a copy allows you to put a stake in the ground for your decisions up to that point. It will also be useful when reconciling at the end of the month and planning next month’s budget.

Next steps

Congratulations! You’ve now completed your first zero-based budget. Now that you have a budget in place you will need to execute your plan and follow up at the end of the month to deal with what you actually spent. Over the next few weeks, I will be covering some ways to make tracking your spending and reconciling your budget much easier. The first month you use a budget, review it as often as you need to stay on track. Take a few moments each day to review your spending if necessary. I recommend reviewing your progress at least each week at first. Once you get your budget down, and with a few tips and tricks, you’ll be able to stay on track with a single monthly review.

Source: www.gettingfinancesdone.com

Category: Bank

Similar articles: