How to Make a Budget with a Spreadsheet

Making a budget is pretty simple. But before I outline how to make a budget, I need to explain what I believe is the number one key concept in budgeting: A Budget is a Plan. "Budgeting" and "Planning" are synonymous.

A popular cliche applies here: "If You Fail to Plan, You Plan to Fail". When you break your budget, you are messing up your plan. When you stick to a budget, you are sticking to a plan. Which one sounds more optimistic?

A budget is often hated because people are always fighting against their budget. So what I want you to do when you make a budget is think of it and talk about it as a plan. Make a plan for how to spend your money and stick to your plan. I think this new point of view will help you be more optimistic, and it is certainly more empowering.

How to Make a Budget - 3 Simple Steps

To make a budget, you need to know how much money you receive (income), and where your money is going (expenses). Another key principal in budgeting is that a budget is designed to help you live within your means. That means that your expenses must be less than your income.

Step 1: Track your Income and Expenses

It is difficult to make a budget or plan without having some information to base it on. After making a budget, you have to track your expenses in order to stick to your plan, so we are going to start by tracking expenses as well. Get out all your bank statements, credit card statements, receipts, and all your cash. You need to know how much money you have and exactly where your money is going.

From this point on, you need to ask for receipts and track the flow of your cash. It is easy for cash and coins to disappear quickly without realizing where it is going: vending machines, toll roads, tips, kids, etc. Some people find it useful to carry a 3x5 card in a wallet or a small notepad in a purse to record every time cash is received or spent.

Download the Income and Expense template if you want a simple worksheet to help you track your expenses.

Step 2: Group Income and Expenses into Categories

When you've gathered all the information, you need to group all your income and expenses into categories. I can't do step 1 for you, but I can help with step 2. At this point, I'd actually start by downloading the yearly personal budget spreadsheet or the family budget planner if you have family obligations. These spreadsheets work in Excel, OpenOffice, and even Google Docs. They list many of the most common categories that people use for tracking income and expenses.

The yearly worksheets are great for making a budget because you can record your actual income and expenses for multiple months. Below is a snapshot from the budget planner showing how I've recorded my actual expenses for Groceries and Clothing for January through March.

Making a Budget: Recording actual expenses.

See how the amounts change from month to month? By tracking you expenses from month to month, you will find that you make some purchases only once or twice a year (like going to the dentist). Magazine subscriptions, car repairs, Christmas gifts, and birthday gifts are all examples of expenses that are sprinkled throughout the year, but these must be carefully considered when making a budget.

To prepare for the final step in making a budget, you need to calculate your average monthly income and average monthly expense for each of the categories. The above spreadsheets do this for you, but if you haven't completed an entire year, you need to first delete the columns containing the months for which you don't have complete information. That is because the monthly average in these worksheets is calculated based on the number of columns in the row.

In my opinion, the ideal budget is based on a whole year's worth of income and expense data. That is how the dentist appointment gets factored in. To know how much to budget each month for a category that is highly variable or is paid only once or twice per year, divide the total yearly amount by 12 to figure out how much to budget in that category each month.

Step 3: Make a Budget to Live Within Your Means

We are now ready to make a budget. At this point, you should download either the personal monthly budget spreadsheet or the monthly household budget worksheet.

For a first cut at creating your budget, enter the monthly average for each category in the Budget column within one of these spreadsheets. In the snapshot below, you can see how I've entered the average for Groceries and Clothing.

Comparing your budget to your actual expenses.

The above snapshot also shows values in the "Actual" and "Difference" column. There Actual column is where you'll record your actual expenses, to compare to your Budget. Don't worry about these other columns for now.

Remember that your budget is your plan for how you are going to make your expenses less than your income. So, after taking a first stab at creating your budget, look at the Monthly Budget Summary table. If your Total Expenses are greater than your Total Income, the Net will be negative. In accounting, a negative value is usually represented as a red number in parantheses. In these budget spreadsheets, red is not good.

Net equals Income - Expenses.

The final step to making a budget is to make adjustments so that your Net is "in the black", meaning that your expenses are less than your income. Once you have done that, you have developed a plan for living within your means. Congratulations - now you just need to stick to your plan (easier said than done).

Sticking to Your Budget

In "How to Make a Budget", I haven't talked about how to save for large expenses or how to follow your budget on a month-to-month basis. In the follow-up article, "How to Budget", budgeting professional Jesse Mecham of YouNeedABudget.com provides 5 excellent tips that will help you stick to your plan and even modify it when you need to.