Control Your Finances - Budget Step #3

In this post, I want to demonstrate how to combine your actual pay period data in with your budget data. In my world this action allows me to see how much I had when I got paid, how much money I have today, and how much money I can expect to have at the end of the pay period based on the current spending trend.


In the previous post, I showed how to setup a budget (Texas T style). Now that you have a "master template" budget month setup in a spreadsheet, copy it to a new sheet, so that you have your template on one tab and a new month on another tab.

Now for a little tricky part. In order to get your actual data (provided by Quicken, Money, etc) to interact with your budget data, you will need to insert some columns next to the current pay period. The way I built my spreadsheet was based on a Quicken report of category expenses. So for me, I needed 3 columns. It may be different for Money or other financial software. Here is how it should look and how the formulas work:




The next part will be geared for Quicken users, but the concept should be the same for other applications. In Quicken I generally reconcile with the bank once every few days. During the "bad days", it was daily. Now that the pay period is underway, I will have entries in Quicken for any purchases I've made with my debit card and any automatic payments made from my banks billpay service. From this, I setup a basic Income vs Expense report in Quicken for the pay period I am in:


Armed with my "actual" spending data, I click on the "copy report to clipboard" option in the top menu of the report. I switch over to my spreadsheet, and I paste (menu -> edit -> paste) the report under my "actual" column:

Now that you have your actual spending data in with your budget data, you can now begin the process of merging the two. This involves copying your categories and amounts from the Quicken report data and placing it up into the budget section:

Have you wondered what column K and column L are for in the picture above? Well, column K is the amount you have spent in that category as of today. Column L is the amount you have left to spend for that category. As you paste in your Quicken data, you will notice the "At EOP" (or end of pay period) amount goes down. That's because you've spent money in that category.

Also note that if you over spend in a category, the "At EOP" amount goes negative. You can't have a negative amount left to spend, so delete these amounts in column L. It will mess up your end numbers if you don't and make you think you have more money than you actually do!

What you are left with are 3 columns of data: Your budget data, Your "Right Now How Much Is In My Bank Account" data, and your "How Much Will Be In My Bank On The Last Day Of My Pay Period" data. It is very useful for me, because it shows me in one spot where I wanted to be, where I am, and where I will be if spending doesn't line up with my budget before the pay period comes to a close. I can do this exercise 7 days into the pay period and say "wow, I overspent my entertainment category money. I better cut back on grocery money before I go get them!"

I am placing the spreadsheet used in this example out for all to download download my budget. Feel free to use it and change it. Please give any constructive criticism :)