***************************** -->

Week 5 – How to reconcile your budget (Part 1)

Written by Sam on June 15, 2010 – 9:30 am -

Why is reconciling your budget so important?

This week we come full circle in the budgeting process. In week 3 I showed you how to create a budget. It’s time to reconcile your budget. In many ways reconciling your budget can make or break the whole process. If your budget doesn’t reconcile properly and you don’t connect one month’s budget to the next, much of the benefit of budgeting disappears. That’s because budgets that don’t connect don’t hold people accountable and therefore don’t result in a change of behavior. Without connecting your budget from month to month your dollars are slipping through the cracks instead of being powerfully focused to reach your goals.

“Well what if I’m under budget? Then reconciling doesn’t matter does it?”

You bet it matters. In fact, coming in under budget can present a dangerous illusion because you feel so good that you stayed within your spending and then get lazy. If you don’t account for the left over dollars, they will disappear. If they disappear you get no benefit. In contrast, if you consciously redirect those extra dollars, you can accelerate reaching your goals. You’ll reach them faster than you ever thought possible.

On the reverse side of the coin, if you’re over budget you have to ensure that you determine where the overspent money comes from to make sure you’re not unknowingly going further into debt. It has to either come from another category or be made up for in next month’s budget. If not, you’ll either be digging into your savings or going into debt for that overspending.

Now that you’re clear as to why it’s so important to reconcile your budget the right way, let’s get started.


Here’s a brief overview of the process you’ll go through and how much time you can expect to take.

  1. Update transactions in your accounting software (YNAB, Quicken, or Excel). When you first start budgeting this step will probably take anywhere from 45-75 minutes. The amount of time spent can vary wildly and will decrease dramatically as you follow my tips below. Eventually you should be able to get it down to under 15 minutes.
  2. Verify your software matches reality. In this step you’ll compare the balances of your software and bank accounts to make sure they match. Sometimes this step is not as easy as it seems. 5-15 minutes.
  3. Review last month’s budget and identify where you were over and under and how you will deal with these amounts. 5-15 minutes.
  4. Create this month’s budget using the information from step 3 and based on this month’s needs. 5-10 minutes.
  5. Review and sign the budget with your spouse. 5-15 minutes.

The longest part of this process BY FAR is step number 1, updating transactions in your accounting software. If you follow the hints outlined below and in my podcast 10 Secrets to Budgeting Success you can dramatically reduce the time spent on step 1 and therefore your overall time budgeting. The last step can also be painful and time-consuming at first because you have to work out priorities and differences of opinion. But after a few consistent months of budgeting it can become one of the shortest steps.

Step 1 – Update transactions in your accounting software

In this step you need to download or manually enter your transactions for the previous month into your budgeting software. In the rare instance that you enter all your transactions on a daily or weekly basis, you can skip this step.

The secret to doing step one FAST!!!

As I talked about in week 4, one of the reasons this step can take so long is due to the large number of transactions created by budget categories like “groceries,” “household,” or “eating out.” Not only do these transactions represent around 75-95% of all your transactions, they are the hardest to deal with. Trying to figure out which category that Wal-mart purchase should be under (was it household, medical, or grocery?), and trying to track down receipts can be an agonizing and time-consuming process.

Using cash in your budget for this small handful of categories is the best way to decrease the time spent during the reconciling process. Instead of 20 or 30 tricky transactions, you’ll have just one – a cash withdraw. In addition, using cash has a huge side benefit of preventing overspending. If you missed week 4, be sure to read why I think everyone should implement at least a partial cash budget

Using YNAB

If you’re using You Need A Budget (YNAB), you can watch the screencast below to see how to import transactions from your bank account into YNAB. You will have to download your transactions for the relevant period of time in the “.OFX” or “.QFX” format (sometimes called Money or Quicken files). If you’re bank doesn’t support those formats, you can download the “.QIF” format. Most all financial institutions should offer the ability to download transactions in these formats.

Once the file(s) are downloaded, open YNAB and select “File –> Import Transactions” and follow the instructions. You will have to choose what account the transactions are downloaded to and may have to match transactions together if they were previously entered.

I’ve created the screencast below showing this process and how YNAB deals with a couple different transaction situations.

Using Quicken

If you’re using Quicken, you should be able to automatically connect Quicken to your financial institutions to update. If the institution doesn’t support automatic updates, you should be able to download a file from their web site and import it into Quicken. Quicken’s website and support materials should help you through the process if needed.

Using Excel

If you’re using Excel, you can either enter transactions in manually or download your transactions in a “.CSV” file format and copy them into your working register.

Categorize your transactions

Once all the transactions are imported, go through and make sure each transaction is categorized. Doing so is a necessary step so you can see where you were over and under in your budget. YNAB and Quicken can automatically categorize some transactions for you. Where applicable, both programs will prompt you and ask if you want the program to automatically categorize for you.

Now that you’ve got your transactions in order, let’s test the numbers to make sure everything’s accurate.

Step 2 – Verify your software matches reality.

All your transactions are now entered and categorized. Next we need to make sure your software matches reality. This can be a frustrating process because if things don’t match up it can be like looking for a needle in a haystack to find what’s wrong. Don’t worry. I’ll give you some hints to make the process easier and less frustrating.

Select a date and compare balances

First you need to pick a date on which you want to match things up. This is important and frequently overlooked. You want to match up your accounts “as of” a certain date. You’re taking a snapshot of your finances, much like when creating a net worth statement (see How to create a net worth statement).

Next you want to compare balances. Be careful because both your software and your bank may show multiple balances for each account and you want to be sure to use the right ones or you’ll be hitting your head against a wall for nothing. See tip #2 below for more information.

The balances don’t match. Now what?

If your balances don’t match check the following:

1. Am I looking at the same “as of” date?

Let’s say it’s July 5th and I’m trying to reconcile my budget from June. If I look at today’s balance on my bank’s website I’ll get the wrong figure because the balance will reflect transactions in July as well as June. Most banks will show a running balance for every transaction. In this case you’d look at the last transaction of June and look at the running balance after that transaction and THAT’s the correct number to compare.

Here’s a tip. I usually download all my transactions through today’s date and compare balances as of today. It just simplifies the process. If it’s July 5th and I’m reconciling June’s budget it doesn’t matter that I’ve downloaded more transactions than needed. As long as the balances match up AS OF July 5th I’ll know that June’s transactions are entered correctly. Then next month I’ll just download the transactions from July 6th on.

2.Am I looking at the right balance figures?

As I mentioned above, both your budgeting software and your bank account may show multiple balances and you need to know which ones to use. Otherwise you may actually have matching balances but think that they don’t match because you’re looking at the wrong figures. How frustrating is that?

An example of different bank balances

For any given account my bank shows the prior day’s balance, the current balance, and the available balance. Which do you use? The prior day’s balance and the current balance are self-explanatory. After a little searching, I saw that the “available balance” reflects transactions that have already occurred, but haven’t posted on my account yet. These transactions weren’t included in the file I downloaded from my bank. So, if I were trying to reconcile my account as of today, I would want to include those transactions since they occurred before today. In this case I would need to manually enter them into my budgeting software since they aren’t included in the downloaded file. I would then compare the balance in my software with the “available” balance from my bank.

An example of different YNAB balances

YNAB shows a “cleared” balance and a “working” balance. The “cleared” balance reflects all the transactions that were either downloaded from your bank or manually marked as cleared. The “working” balance reflects all transactions in the account both cleared and uncleared. If, for example, you set up YNAB to automatically enter transactions into the register, they will show as “uncleared” until you download the actual transactions from your bank and they are matched up.

To see how to read balance figures in YNAB, view the screencast below.

3. Are there duplicate transactions?

One of the most common reasons balances don’t match is that a transaction was accidentally entered twice. Every software tool I’ve used allows you to either sort your register by the transaction amount or run a report that sorts by amount. You can then quickly look at any transactions of the same amount and see if they’re duplicates. Delete any duplicates you find. Be careful not to delete legitimate transactions that are the same amount such as recurring bills.

4. Was a transaction entered into the wrong account?

If more than one account balance doesn’t match up, you may have entered a transaction into the wrong account. This is likely to be true if both accounts are off by the same amount. For example, let’s say the checking account balance in your software doesn’t match your actual balance by $6.43. Well if your savings account balance is also off by $6.43 you probably just mis-entered that transaction into the wrong account. By the way, this rarely if ever happens if you only download transactions from your financial institution rather than entering them manually.

5. If all else fails, give up

It’s hard for me to just give up on finding the error, but sometimes it’s the best option. You don’t want to do this on a regular basis or if the difference between balances is very large. If you’re consistently having a problem matching up you’re probably doing something wrong or not following the guidelines above and should try and get to the root of the problem. However, there have been times where I’ve searched for hours to find what’s wrong to no avail. It’s just not worth it.

Instead, enter a transaction correcting the difference in balances and put a note in the “memo” field explaining the problem. Often you’ll come across the error months later and have a big “ah ha” moment. You can then fix the error and remove the “correction” transaction.

I know doing this goes against my basic principle of accounting for every dollar, but as long as it’s not a persistent problem, you’ll be ok in the long term.


So now you should have your transactions downloaded, entered, and categorized and you’ve confirmed that the balances match between your software and your actual financial institution. Life is good. In my next post it’s time to do the fun stuff and see the outcome of last month’s budget. This is where the power of your budget lays. Stay tuned.

Tags: , ,
Posted in 12 Weeks to Fiscal Fitness, Budgeting, How to | No Comments »

How To Create Your First Working Budget With Excel Or Pen And Paper

Written by Sam on April 9, 2010 – 9:19 am -

This video shows how to create your first working budget with excel. If you want to use pen and paper I would recommend printing out a blank copy of my Working Budget Excel spreadsheet. You can get a free copy by subscribing to my newsletter on the right. You could also create your own spreadsheet if you know how to use Excel or another spreadsheet program.

The advantage of doing a budget exclusively in Excel is that it’s pretty straight forward and you only have to deal with a single software tool. The downside is that you have to calculate and enter all your expenses manually which not only takes a long time, but can result in mistakes. But if this approach works best for you, check out the screencast below.

Overall, I recommend using You Need A Budget (YNAB) software for your budget. Check out my YNAB review or my YNAB overview screencast for more information.

Tags: , , ,
Posted in 12 Weeks to Fiscal Fitness, Budgeting | No Comments »

How To Create Your First Working Budget With Quicken And Excel

Written by Sam on April 8, 2010 – 9:23 am -

To start off, if you haven’t viewed Emily’s screencast on using Quicken with your zero-based budget, you’ll want to do that first.

You can receive a copy of the Working Budget excel file shown in the screencasts below by subscribing to my newsletter on the right.

Step 1 – Set up your accounts

If you choose to use Quicken to keep track of your money, I’m going to assume you already use Quicken and know how to set up your accounts. If you don’t already use Quicken, I would recommend using You Need A Budget software. You can read my You Need A Budget Review or watch my screencast You Need A Budget Overview.

Step 1 is to simply set up your accounts in Quicken so do that first.

Step 2 – Allocate existing account balances

Allocate any existing account balances. There are three general ways to make your allocations.

  1. Funds are reserved for a specific purpose. If you already have a specific use in mind for the funds in your accounts, allocate them accordingly. For example, you may have some money reserved for a large purchase or vacation.
  2. Use funds for this month’s budget. If you’re starting your budget mid-month or are tight financially, you may need to use some or all of the money in your account for this month’s budget. If that’s the case start allocating the money according to the priorities you established in your Master Budget.
  3. Use the funds for next month’s budget. If funds are neither reserved for a specific purpose nor are needed for this month’s budget, put them in a “buffer” category and start building up a month’s worth of expenses so you don’t have to live from paycheck to paycheck. It will also will act as an insurance policy to ensure you don’t overdraw on your account

You’ll want to allocate funds in your Working Budget and within Quicken. Watch the screencasts below for more details.

Step 3 – Budget your income

As you receive your paychecks, enter them and allocate the total amount into your budget categories according to the priorities you established using your Master Budget. Allocate your income in both your Working Budget excel document and in Quicken. See the screencasts below.

Step 4 – Print your budget.

If you have a buffer of one full month’s worth of expenses and can budget the whole month at once, print the Working Budget once all the funds are allocated. If you are allocating paycheck to paycheck, print out your Working Budget each time a new paycheck is received and allocated. Put the print outs into your financial binder.

Step 5 – Entering expenses.

You don’t need to enter expenses into your Working Budget until the end of the month when you’re ready to reconcile your budget. You can, if needed, enter transactions throughout the month into Quicken. You should do this if you are very tight and need to keep tabs on your accounts to ensure you don’t overdraw.


In a couple of weeks I’ll detail how to reconcile your budget at the end of the month. Until then you don’t need to worry too much about entering transactions unless you are very tight financially and risk overdrawing your account.


That’s it you are now up and running on your first working budget using Quicken and Excel. If you want a copy of the Working Budget spreadsheet, just subscribe to the newsletter and you’ll be automatically emailed a link to it.

Tags: , , , ,
Posted in 12 Weeks to Fiscal Fitness, Budgeting | No Comments »

Zero-based budgeting with Quicken

Written by Sam on April 8, 2010 – 9:21 am -

For years my wife and I had to make due with trying to make Quicken work using a zero-based budget. While Quicken simply isn’t made for zero-based budgeting, we did find a cool hack that lets us keep track of our money in a way very similar to a zero-based budget. It’s kind of like zero-based accounting.

Using this method combined with a zero-based budget spreadsheet, we were able to get the control we needed. In the screencast below, my wife Emily takes you through an overview of this hack and how it works.

For those ready to create their working budget, you need to watch this video first before you read about how to create your first working budget using Quicken and Excel

Tags: , ,
Posted in 12 Weeks to Fiscal Fitness, Budgeting | 1 Comment »

Join my FREE newsletter and get
Exclusive content for the "12 Weeks to Fiscal Fitness" course.

12 Weeks to Fiscal Fitness

Get exclusive content by entering your First Name and Email below:

I hate SPAM and won't share your email address with anyone!

GFD Marketplace