Easy to Use Budget Method and Spreadsheet


Creating a yearly budget should be easy because all you need to know is your income and your expenses. How you organize, prioritize and analyze your expenses takes a little time, but it also is an easy exercise because you probably know where you spend your money. This post discusses a method for creating and updating a budget and provides a spreadsheet that simplifies entering your income and expenses that provides visual clues as to when you are overspending.

You Already Manage Your Money

You are already managing your expenses and cash flow if you have an income and spend your money. You also make daily decisions as to what has highest priority by what you spend your money on. But are you following a plan?

Are the daily decisions you make affecting your financial (and often emotional) health? Are they causing you to go into debt or are you able to save? For many people, it is living paycheck-to-paycheck while for others there is money that can be saved.

Many people feel that they have no control over their spending for a variety of reasons while others consider that they have spending under control. No matter what your situation, if you want to better manage and prioritize your expenses there is a relatively easy way to do it as explained in this post.

Why is Budgeting Not More Widespread?

I believe that making a budget frightens most people. Most advisors and articles indicate that you must allocate money to many different groups, set priorities and keep track of every cent spent. This is difficult work and if you are doing it as a family there will be differences on what is important. Many people just find in less stressful to do the best they can without the details of a budget. While using envelopes or other such allocation methods works for some people, it can be a real burden.

Many spreadsheets available list pages of groups for every imaginable expense and require that you enter totals for each month. This can be extremely time consuming and hard to keep up. The method and spreadsheet presented in this post are easy to follow and should help you make a budget.

Why Should You Care?

How you manage your money will determine if you have the “things” you want in life (e.g. house, holidays, car). It also determines if you have a comfortable life and retirement. You spend a large part of your life earning money, so why not spend a little bit more time figuring out what you really want to do with what you earn.

Saving a few dollars a week, such as on banking fees, drinks after work, a bought lunch every day or a coffee each day all add up. For example, if you save as little as $100 per month and put it into a Tax Free Savings Account, you will have over $33,000 after 20 years if invested at 3%. For 30 years it is more than $58,000. Try this yourself by using the spreadsheet provided in Estimating Capital Totals at Retirement.

Also, you may have heard the old saying that “a dollar saved is a dollar earned”. However, it is even better that this. If you want a few more dollars to spend, you can get a raise or take on an extra job. If it is a wage there will be deductions for taxes, Employment Insurance and pension, and perhaps other items. You will be paying taxes at the marginal rate, see Canadian Marginal Tax Rates – 2018, which can be anywhere from 20% to 53% depending on your normal income and which province you live in. In addition, in Canada, the Canada Pension Plan deductions are 4.9% and EI is 1.66% in 2018. That is, your take-home pay is reduced by one-quarter to one-half of what you made.

Put another way, if you save $100 from your existing expenses, you would have to increase your income by $133 to $200 to have the same $100 to spend. Thus, a dollar saved can be up to two dollars earned.

While it is always good to earn more, it is always prudent to make better use of what you already have by reducing your expenses wherever you can. You can only do this by examining your expenses and making savings that make sense to you.

Keep in mind that to get a mortgage or a large loan, you will have to provide information that shows that you can handle the added debt. Therefore, it is important for you to know where you spend your money and if you can save the downpayment needed and then cover the monthly (and property tax) payments.

Easy to Use Budget Method

There are five steps involved in making and managing a budget. You can gain a lot of insight into your finances by completing steps 1 to 3. The cash flow of 4 and the comparison of 5 can be done later if you are really keen and get interested in tracking your finances. How they are used on the spreadsheet is described later.

  1. Yearly Budget Planning consists of listing your income and expenses. Income is easy, because all you really need is your pay stub. Yearly expenses are a little more difficult, but you probably have many of the bills such as utilities, etc. You can then estimate the rest. It helps to put your expenses into 4 classes: Fixed, Necessary, Savings and Discretionary.
  2. Assign a priority to the expenses in each class (but focus on the Discretionary ones) because as some point you may exceed your income.
  3. Adjust the yearly expense amounts as you look at past spending and determine what you want in the future to ensure that expenses do not exceed income.
  4. Develop a Monthly Cash Flow Plan, if you want more detail on how the expenses are made during the year. For most expenses, all you have to do is divide the yearly total by 12 and enter this amount into each month. Those expenses that only happen a few times a year require special attention, such as property taxes, insurance, vacation, to ensure that you have money available for them that month.
  5. Compare the Actuals to the Plan and adjust as the year goes on. This may require that you keep receipts, but there are other ways to get the details as discussed below.

Dealing with Major Expenditures

The above primarily covers everyday and a few times a year expenses. However, there are other expenses that do not happen often, but when they do, they are usually very large. For example, each of the following will happen sometime and you need to be prepared for them. Many are needed if you have a house, but others are for everyone. Most have a lifetime of 10 to 15 years with costs between $500 to over $3,000. I am sure you can think of more.

  • Refrigerator
  • Dishwasher
  • Hot Water Heater
  • Roof Shingles
  • Computer or Laptop
  • Furnace
  • Air Conditioner
  • Upgrades to Kitchen, Floors, etc
  • Replacement of Furniture, Mattress, etc.

You must have savings to cover these major expenses as they will occur every few years and often many will happen at the same time. The best approach is to put some money aside each year, in addition to the recommended 6 months of expenses.

An Easy Way to Keep Track of Your Budget

I recommend that you use a chequing account and have your income direct-deposited into it and pay your bills electronically from it. You can also pay most everything else with a credit card. There are many that have no fees and provide such perks as Air Miles, etc.

The bank and the credit card companies provide detailed statements each month that contain the amounts and places you spent your money. By examining these you can determine how much you spent on each item in your budget for the month. Download or copy them to a spreadsheet so that you can review them at your leisure. (A method to doing this is given in Managing Expenses and Cash Flow – Part 2: Spreadsheet.)

For everything else you can then withdraw cash from it when needed and keep the receipts to be examined at the end of the month.

In this way you can easily keep track how you are doing compared to your budget plan.

Once you have entered income and expenses in the spreadsheet described below and created an cash flow, you will see which months have excess income and which months have excess expenses. You can then transfer funds from/to a savings account (where you will earn interest) as needed to deal with the shortfall/excess.

If your budget is balanced and your spending is under control, you should end up at the end of the year with the same amount in the account as you had at the beginning of the year. Keep in mind that any savings that is not payroll deducted must also be moved out of the account like any other expense, but at least you get to keep it.

Using the Spreadsheet

Use the Excel spreadsheet to enter your own income and expenses by clicking on Download Spreadsheet. It has no values in it, so follow along as I describe how to use it and you can enter your own amounts. There are two macros that make it easier to enter and clear monthly cash flow values, so make sure that you enable macros when opening the spreadsheet. If you want the example used below, please click on Download Example.

Only enter data into the cells that are outlined. The other cells contain the formulae needed to produce the results and the charts.

The spreadsheet has a frozen pane at the top. The top pane does not scroll but the bottom does. This ensures that the charts and buttons at the top are always visible.

The spreadsheet has 6 sections: Year, Charts, Income, Expenses, Groups and Cash Flow. The cash flow section is purposely not showing when the spreadsheet opens so as to not make it too distracting. (Just widen the spreadsheet to see it.)

1) Enter the Year

This is cell F3. The year is only used in one of the charts, but you can save the spreadsheet with the year as a suffix and then reuse it for next year or make copies for the plan and actuals.

2) Charts

Chart 1 at the top of the page shows the Total Yearly Income as a short blue bar. The expenses for each class are shown as a stacked column and are colour coded to match the group totals in the Expenses area. This chart clearly shows how the Total Expenses compare to the Total Income and the relative values of the totals for each Expense class. The legend shows the total amounts and percentages so that it is easy to see what each contributes. The titles include the amount for the Income less the Expenses. If negative, you have too many expenses. If positive, you can spend or save more.

Chart 2 at the top of the page shows the total for each Group as a bar chart. Each group has the amount and the percent of the total income. The title shows how much all groups are relative to the total income. If more than 100%, you have too many expenses. If less than 100%, you can spend or save more.

3) Enter your Income

This starts at row 19. Change the Source name as desired and enter the Amount and the #/Year (number of times per year) it is received. Most people get paid every two weeks (26 times/year). The Yearly amount and the Total Income is then filled in by the spreadsheet. The Total Income is plotted in Chart 1. Enter the total amount, not the take-home pay. Enter any other income that you receive that you can use to pay expenses.

4) Enter your Expenses

This is located below the Total Income row and is divided into 4 classes: Fixed, Necessary, Savings and Discretionary. Depending on your screen size, you will probably have to scroll down as you enter data. You can reorder, add or delete expenses using the tips below.

There are 4 columns that you can enter data into as follows:

  1. In the Description, either use the text shown or type in your own.
  2. The Group has some preset groups. You can use the arrows to the right of the cell and select a group. You can also type a few characters and Excel will present you with some choices. You can copy and paste to other cells rather than having to either type or use the arrows. If the preset groups are not what you want, you can change them as discussed later. The group totals are shown in Chart 2.
  3. Enter the Amount. It can be for the full year or for each period, but you must adjust the #/Year value (see below) to match.
  4. The #/Year is the number of times per year the amount is spent. Normally, enter the yearly amount and leave the #/Year at 1 rather than having to guess at smaller amounts. If there are some expenses that you know are always the same per month or some other period, enter it and change the #/Year to the appropriate value.

Once an Amount and #/Year are entered, the following change: the Yearly amount, the Left value, the percent the expense is of your total income, the Class Total and the class percent. The class totals are plotted on Chart 1. All percents are relative to the Total Income. Here is the Expenses section for the example used for the charts. Click on the image for a larger view. Note that the there are more expenses than income. Something would have to be reduced to bring it into balance.

The Left column is very important as it counts down how much of your income is left. When all of the income is gone, the Left value is shown as a negative red number. Once this happens, you need to either

  1. reorder your expenses to prioritize the ones you are willing to not include in your budget by moving them to the bottom or
  2. change some of the Amounts to ensure that all of the income is not used up.

The Total Expenses are shown at the bottom. This is followed by a value for the Income less Expenses, which will be the same as the last value in the Left column.

The objective of the budget plan is to have your yearly expenses exactly match your yearly income. If you have money left over, increase the amount in the Savings class.

What Should Your Budget Look Like

If you search the internet for how your expenses could/should be allocated you will find many posts. Every person and household has different priorities and spending habits, so do not take any of them as absolute rules that you must follow.

One I like is on the Credit Counselling Society website and it provides a breakdown by 9 categories. It gives you an idea of what can go into each category and nominal ranges as a percent. You do not have to use what is given and you can decide to use different categories and different assignments. (Note that the spreadsheet they recommend is one that has a many pages of predefined detailed expenses that they want you to fill in as apposed to the spreadsheet presented in this post which has a few example expenses and you can delete, add or change to meet your needs.)

You need to ensure that all of your debt payments are included, such as paying down a credit card or a line of credit. Make sure your savings includes putting some money aside so that you have enough for at least 6 months of your Fixed and Necessary expenses in case you lose your job or there are emergency expenses. You should also be saving up to 10% of your income for retirement.

5) Groups

The preset groups are given below the Expenses. Use the Excel tips below to add, delete, rearrange or sort them. Only change the Group column as the others have formula. I you change any of the text in a group and the old value was used in an expense, you will have to change the expenses to match the new text. Try to keep the length of the group to less than about 10 characters or Chart 2 will not show the $ and % values.

6) Monthly Cash Flow

When you are comfortable with the yearly income and expenses values you have entered above, you can move on to creating a monthly cash flow. This is included on the right-half of the spreadsheet. To expose this area, just widen the spreadsheet as much as you can. The last column is X. The cash flow area consists of 1 chart, 2 buttons and cells for each month for each Income and Expense row.

Chart 3 – This shows, for each month, the Income, Expense and Income less Expenses as columns. A line graph is shown for the cumulative amount of the Income less Expenses columns. This chart makes it easy to see when you need extra funds for months where there are one-time, large, expenses.

Fill Monthly Row Button – If you select any cell in any income or expense row, clicking on this button will enter a formula into each monthly cell that is the Yearly total divided by 12. It will automatically move down to the next row when finished.

Clear Monthly Row Button – If you select any cell in any income or expense row, clicking on this button will remove any data in each monthly cell. It will automatically move down to the next row when finished.

Enter Monthly Income – Select a cell in the row and enter the value you want. If you are paid every 2 weeks, and you need to enter 2 times the amount each month. You then need to check a calendar to see when you are paid 3 times in a month (there will be two of them) and enter 3 times the amount. For Other Income, this could be, for example, a bonus at Christmas.

Enter Monthly Expenses – Use the “Fill Monthly Expense Row” button if expenses are the same, or almost the same, each month. This will save you a lot of time and any change to the Yearly amount will update the monthly amounts. For one-time or a few-times-a-year expenses, either enter a formula or the amount expected. A formula such as “=$E$29/2” in two months will automatically update when the yearly amount changes.

Error Messages – There are formula to check the monthly and class totals in the cash flow area against those in the yearly area. The text will either say “Not Enough” or “Too Much” so that you do not have to compare the totals yourself. Just adjust the monthly values so the error message disappears.

Monthly Totals – At the bottom of the Income section are the monthly totals. The same is true for the line below the Expenses section.

Difference – The second line below the Expenses section subtracts the Expenses totals from the Income totals.

Cumulative – The last line takes the results of the second last line and cumulates them by month.

Excel Tips

To Add a Row, select the complete row by clicking on the row number. Using the Edit menu, select Copy. Then select another row number where you want the copy to be placed and using the Edit menu, select Paste. This will ensure that all of the formulas are also copied properly. Change any of the cells to what you want.

To Delete a Row, click on the row number and select Delete from the Edit menu.

To Move a Row, select the complete row by clicking on the row number. Using the Edit menu, select Cut. Then select another row number where you want the copy to be placed and using the Edit menu, select Paste. 

To Sort Rows, select the complete rows that are to be sorted. Use the Sort on the Data menu if you want them in alphabetic order. For expenses, only select rows in a class and then sort only on the Description and Group columns. For groups, sort only on the group column.

An Alternative to the Spreadsheet

I have written before about budgeting and cash flow in two posts, with accompanying spreadsheets. The first is fairly simple but does not help in analyzing the results (Making a Yearly Budget). The second is a four part post of which one presents a very detailed spreadsheet. It has been referenced above (Managing Expenses and Cash Flow – Part 2: Spreadsheet). Some of the features in it are used in the spreadsheet include with this post.

However, if you are really interested in planning a budget and tracking your expenses and income, one of the parts summarizes the FinanceBase application that is available on this website at a nominal cost. For details, please click on this link: Managing Expenses and Cash Flow – Part 3: FinanceBase. It contains a description of the cash flow feature and how it makes use of Transactions which can be easily updated from your chequing account and credit card statements. This then permits comparing the budget plan to actuals.