Budget Template

Budgeting is one of those life skills that everyone needs to learn – typically, the sooner the better!  Literally – most of what you hear, read or see is trying to influence your spending habits in one way or another.

A good sense of budgeting and cash flow management are required to rein in the impulses that marketers are trying to trigger in you – and a general sense of mindfulness, of course.

Here is a version of the budget that I use.

I made this after several years of working and racking up more debt than savings – much like any type of dieting, the most important thing to do is to find a tool that works for you that you can stick with.  This may represent overkill to some, and maybe lacks the sophistication that others have in theirs, but I find updating the file once or twice a week helps me stay on track with my spending, and therefore with my savings.

Setting up your budget:

The only cells that should require any input from you are shaded yellow.

Monthly Estimate tab:

Here you can change any descriptions in column A and it should update the categories throughout the rest of the file.

Enter the amount of any payments in column B.

If the payments are monthly, enter the date they come out in column D.

If they come out more frequently, enter the frequency expressed in number of days (14 for bi-weekly, 7 for weekly) in column E and the date the first payment of the year came out in column F.

Income and Fixed Expenses should be straightforward to populate.  For Discretionary Expenses, I estimated weekly values, which helps ensure a smooth and somewhat realistic cash flow.

In columns BN and BO you will see a running monthly average for each of your spending buckets for the year – this reflects your actual spending levels.  You will use this information to make your estimates more accurate.  This type of review should be done at least once per year.

Current tab:

On the current tab, you will enter your current chequing and savings balances (cells C3 & 4), as well as any sources of credit and current balances in column N.

‘Month’ tabs (i.e. Jan, Feb, etc)

These are the tabs you enter your spending data.   Most bank accounts will give you the ability to download your transactions as a .csv or Excel file.  You can then copy them into these tabs, one column at a time to not overwrite hidden formulas.  You enter the month’s starting balance in G1, and then assign the appropriate cost code in the Category column, D.

Complete this for the entire year, starting Jan 1, up until yesterday’s date.

Payment Calendar tab:

In cell A2, enter the current year.  Doublecheck to make sure the values you populated in the Monthly Estimate tab are properly showing up here.

In the first row of each month, you will see a ‘formula for actuals’ – highlight the row from D through to AI and drag the formula down for each day that has passed.  There is a new formula for each month – if you are setting up the budget on Feb 20, you will drag the formula for January all the way down to Jan 31, and the formula for February down to Feb 19.

Congratulations!  You have finished setting up your budget, complete with costs to date, a 30 day cashflow forecast, and charts that will show your spend-to-date for the year.

Note for consideration:

After your first year of using the file, you will need to make some slight modifications to the naming of the tabs – you will need to make Jan into Jan ’17, and then create a fresh tab for Jan’18 by copying Jan’17, renaming, and deleting all the transactional data.  In order to keep your monthly average and charts updating, you will also need to insert the appropriate columns in the charts tab to continue pulling the data.  These functions are non-essential features of the budget file though, so you will still get the majority of the value from the file without doing so.  If you are getting hung up at this point, send me a note and I will straighten the adjustments out for you.

Updating the Budget:

Keeping the budget going at this point is an easy task.

Update the ‘Monthly’ tab:

Once or twice a week, log into your bank and download the updated transactions file.

Copy this into the appropriate monthly tab, and categorize each of your costs in column D.

A few notes here – I tend to categorize going out for dinner with my wife or friends as ‘entertainment,’  as I view it as an evening activity where we get to go out and have fun.  If I order dinner in, like pizza or Chinese, I will categorize that as a ‘Dinner’ expense.  ‘Lunch’ and ‘Breakfast’ are for when I purchase those meals from restaurants.  Anything I purchase from a grocery store and prepare myself gets captured in the ‘Groceries’ category.

Update the Payment Calendar tab:

Drag the formula for actuals from columns D through AI down to the last day you have transactional data for (should be yesterday).

Key part of this exercise – doublecheck that no scheduled payments have been missed prior to replacing forecast with actuals.   Before copying down the actuals from the last time you updated the budget to now, review the amounts allocated for payment to ensure all fixed payments have been made.  If you were unable to pay a bill on the scheduled date, make sure you enter the amount manually for a later date in the Payment Calendar tab before copying down the formula for actuals.  This will prevent any payments from being missed, and throwing off your cash-flow forecast.

Updating the Current tab:

Update the yellow-shaded cells with your current balances.  For your cash balances (chequing and savings) you will want to use your start of day balances, assuming you updated the budget for all spending up until the end of yesterday.

And that’s it!  Updating the budget once a week takes approximately between 5 and 15 minutes, depending on the level of spending activity.

Using the Budget:

Once you have updated the budget to the current view, you can use it to effectively quarterback your situation, viewing your near-term financial future and planning strategies and expenditures appropriate.

The Current tab is where most of the detail lives.  It will show you how much of your income and expenses are left to be paid in the month, and give you a projected ending cash position (columns E and F).

It will also give you a cash flow forecast in column J.  I use this for two reasons:  one – it shows me if I am ever risking entering my overdraft, and forces me to adjust accordingly either by changing payment dates, replanning purchases, or pulling money from my line of credit if I have to.

The other, and more preferable, reason I use the cash flow forecast is to determine how much I am able to pull from my chequing into my savings, or to pay down additional debt.  I tend to leave myself a buffer of $100-$200 in my account beyond my forecasted requirements, which acts as a buffer for unexpected expenses, and also gives me a lean enough target to prevent needless overspending.

I will also occasionally glance at the Payment Calendar tab when I am looking to see what is driving my forecast – if it looks like I will enter my overdraft, I go see which payments are coming out on what days, and I will tally up the mandatory payments to see if my cash balance can accommodate them by either reducing or pushing out my discretionary expenses.  You can update the cash flow forecast manually by plugging amounts directly into the payment calendar, which lets you readjust and iterate out your spending plan rather easily.

Learning from your Budget:

The Charts tab is where you can go to get a longer-term view of your spending habits.  It will show you, by category, your total spend each month, and also track an average monthly value.

There are some graphs set up to visualize some common spending areas, which can help you target in on things perhaps more easily than just a chart of numbers.

By examining trends, you will be able to identify opportunities to save money.  Sometimes just quantifying things is enough – realizing you are paying several thousand dollars a year for a service you barely use, like cable television, when there are many lower-cost alternatives that would provide most of the same value, like streaming, becomes a no-brainer for many people.

The main idea is to look to areas of high expense and see where you are able to reduce in a systemic fashion, in order to get better value from your spend.

Notice you have way overspent on entertainment the first two weekends of a month?  Plan to spend the next few weekends in.  Setting record dinner expenses – plan to spend more time cooking and preparing food to bring the number back down to average.   Over time, you will notice various relationships between the categories and learn how to optimize them by developing or changing certain habits.

There will always be one-offs and changing trends, but becoming mindful of where you spend your money will also have the effect of making the money you spend be more effective, which is the end goal of any budgeting system.  Seeing the tangible financial impacts to your decision-making provides strong encouragement to make better decisions!