Thrifty Thursday: Making a Spreadsheet Spending Chart

Are you ready for Step 3?  If you missed Step 1 and 2, check them out and determine your expenses and income (here and here).  After you completed both those steps, you are ready to set up a spending chart.

The spreadsheet is easy with the work that you have already done to research all of your expenses and all of your income. 

- Top of the spreadsheet: Put each month starting January and going to December (even if you are starting in the middle of the year because it helps to have a history if you have it available). Make sure to skip the first column because you are going to use that for the left column of bills.

- On the left column: Write each bill that you have to pay in the first column (example:  Verizon). 

- In second column: The due date of each bill.  You don't need to write August 15th but just the beginning, middle or end of the month.  This helps later if you think that you missed a bill to know if it should have already come in the mail or electronically.

Here is an sample outline:

Company	Pay By
Gas bill	By Middle of Month
Electric bill	End of Month
Home Phone bill	End of Month
Internet bill	By Middle of Month
Credit Card bill	Beginning of Month
Mortgage Payment	By 7th of Month
Preschool Costs	By 1st of Month
Trash	On Receipt, quarterly
Water	By Middle of Month Quarterly
Car insurance	Once a year, October
House insurance	Once a year, April
County House taxes	Twice a year, July and December
County Car tax	Once a year, truck
County Car tax	Once a year, car
Federal Taxes	Once a year, April
State Taxes	Once a year, April

I wrote the general category but you can write the actual company you pay your money to.  Make sure you include any other bills you pay on a monthly basis like cable, cell phone, and any others.  Now is the easiest part, take all the information that you have and fill in the blanks.  Go backwards if you have them and forwards if you don't have any other information.

The easiest way to finish off your spreadsheet is to use formulas to sum up each column and line.  If you use excel, there is a button or you can type =sum(click on the cells top to bottom that you want to add) and it will make a formula where it adds everything at the bottom.  If you are happy with your formula, you can copy and paste the formula across the line where you need it.  Make another formula to go down the column then across because it won't work if you copy the same formula all over the sheet. I label the bottom line across Total Expenses and the line down is Total Yearly Cost.

I skip a line and fill in the income section.  Fill in as much information as you can get from your past paperwork and fill in everything else going forward.

Here is a sample outline:

Paycheck
Cash
Rebates
Stock Checks
Interest from Checking
Interest from Savings
Interest from CD

Then, the last line is the Difference.  I make another formula that adds the Income section and subtracts the Total Expenses.  It looks like this =SUM(D20:D30)-D19 but obviously yours is going to be different based on the columns but that is the outline of the formula for you to copy.

 On the Dr. Oz Show, they used the average household income of $60,000 in the United States (meaning $45,000 take home after taxes) which is $3,700 monthly income.  Here is the breakdown listed on the show:


Housing    33%         ($1200)
Debt         15%         ($575)
Savings     10%         ($375)
Food         8%          ($300)
Everything Else 33% ($1200)
Reward Money 1%   ($50)

How do you stack up??  The DC metro area is an expensive place to live so we are always over on the Housing category and with this breakdown, we are also over on the everything else because that includes all the utilities and taxes which are also high in this area.  But we don't have any debt or use rewards so it all seems to come out even in the end because we are breaking even in the spreadsheet and the bank accounts.  We obviously also cut some out of the food budget to try to make ends meet.

Remember that we only have a spreadsheet of our bills and don't yet have a budget...just the first step.  Stay tuned next week for using the first three steps to set up a budget.

If you are interested in this post, here is the link to all of the posts in the series:

Figured out expenses
Figured out our income
Made a Spreadsheet
Making a Budget
Setting Your Budget and Spreadsheet
Budget Busters
Take the Emotion out of Budgets