A Wallet Resolution
In 2015, I will keep to my monthly budget… If I repeat this three times while clicking my heels together, it will miraculously come true! But in all seriousness, this is my goal for 2015. Make a monthly budget, and stick to it.
Anyone else out there with me on the path to financial security? Growing up, I watched my parents work so hard to make ends meet every month, and still carry hefty credit card debt. I wracked up huge student loans going to college, to enter a great career field so I would never worry about money… but no matter what you make, you still worry. And 10 years later, I’m staying home to raise our kids, while my husband works 70+ hour weeks to put money in the bank. No matter how much money is in the bank, it seems like there are always more bills to pay… there are mortgages to pay, insurance to buy, kids to feed, and college tuition to save for!
Analyze Historical Spending
To stick to a budget, you have to first make a budget… and to make a budget, you have to start by figuring out where all your money goes now. For me, this was a truly enlightening process – it’s not like you don’t know what you spend money on, but sometimes you lose sight of how easily it adds up… those morning runs through Dunkin’ Donuts at $3-5 a trip; those trips to Target for a birthday gift that somehow always end with a cart full at the checkout; and the eye-opener for me – how much I was spending on clothes for the kids.
Guru Tip: To make a budget, you have to figure out what you are spending first!
The first of the year is the perfect time to do this. Most online banking services and credit cards produce annual reports and/or archive this data for easy, user-friendly data download in 12 month increments. If you operate in cash, this is going to be a little tougher – you will have to spend a few months keeping all your receipts and manually inputting data. For me, I find using one credit card that I pay off every month, along with my checking account, helps me stay on track best.
Disclaimer: I am a finance nerd, and an Excel junky… I have tried to make this explanation as straightforward as possible, but if I lose anyone along the way, let me know, and I will try to clarify!
All online accounts will offer an option to download your transaction history. Choose to download it in an Excel or CSV (a file readable by Excel). The data you download will have far more information than you need – reference codes, transaction IDs, long detailed descriptions, running balances. And the file from each source will look very different. To make the data useable, you will need to do the following:
- Open all your downloads and import each sheet you downloaded into one, master file. You can move sheets between separate files by right clicking on the Sheet tab, selecting Move or Copy, and choosing which file, or Book, you want to move it to. I moved all my files to one Book and saved it as 2014 Budget. Be sure to maintain a clean copy of your source data! In case you need to go back and reference it, never change the original data you downloaded on these individual sheets. This will save you a lot of headaches if you mess anything up along the way!
- Double click an empty sheet tab, and rename the sheet “Data.” We will copy and paste the raw data from our various downloads here to make the data consistent, sortable and easily analyzable. Set up 4 columns: Date, Description, Amount and Category.
- Next, we will copy and paste the data into the date, description and amount columns from our raw data sheets. Be very careful not to mix up the raw data – you want the right description with the right transaction date and amount.
- Once all your data is consolidated onto one sheet, in the proper columns, you can now assign a category to each transaction. You will need to manually type categories in the Category column. Categories will be personal to each individual – it may be helpful to look at your list of transactions and brainstorm a list of categories before you start. You want to use categories that sort your spending into meaningful buckets. Examples may include: Housing, Utilities, Insurance, Car, Gas, Groceries, Restaurants, Clothes, Health, Tuition, Activities, Cash, Other. I also called out places I frequent to specifically examine my spending there, like Amazon or Target. You will need to assign a category to every expenditure. For deposits, credits, or transfers, you may leave the Category column blank.
- Once all your transactions are assigned categories, we need to add two more columns to our consolidated data sheet: Month and Year. Both of these will be calculated by formula.
Month Formula
Year Formula
Filled Month, Year
- Now, the data is ready to be easily be sorted using a Pivot Table. Select all your data columns and corresponding data. In the menu bar, click Insert, and select PivotTable.
- The PivotTable will be created on a new sheet. First, check the boxes next to Amount, Category, Month and Year. That will add those fields to your Table. Next, click and drag the fields to the appropriate boxes, as depicted below: Category in Row Labels, Year and Month (in that order) in Column Labels, Amount in Values. This should create a table that looks similar to the one below.
- With the data totaled by month and category, you now have a better picture of where your money goes every month. Examine these totals – what is your largest monthly expenditure category? Are you surprised by how much you spend on a given category? What categories do you know you can reduce spending in starting now?
- Drill into your totals – Is there a total in a given month that jumps out at you? Double click on any number in the table and it will take you to a list of the data points it sums, including their descriptions. Maybe you need to split a category into several categories and run your PivotTable again.
Historical Spending, Census Data to Plan Future Budget
Having examined your historical spending, you can now look forward at what you want your 2015 budget to look like. Certain categories, like Housing (mortgage, home insurance, utilities) are relatively fixed, unless you plan to move. Other large spending categories like Car and Groceries also have limited flexibility, unless you undertake major changes in lifestyle, but you can set small savings goals. Need some tips to cut back on your grocery bill? Check out Save Money by Meal Planning. Another large category if you have young children is diapers. You can save money on diapers if you buy smart – find out where to get the best deals on diapers from our detailed analysis in Best Deals for Diapers.
If you want to sanity check your budget, the web offers some great resources. Compare how your spending stacks up to the “Average American.” The Bureau of Labor Statistics puts out an annual summary of Consumer Expenditures every year. Here’s the same data in a pretty picture by CreditLoan.com:
Since our reader demographics skew slightly younger, and have kids, so MORE people per household, chances are you spend a lot more per month on food than is depicted above. The USDA analyzes food consumption costs per person and publishes a monthly report. Compare your monthly food budget to the latest report from November 2014.
Considering adding another child to your annual budget for 2015, or want to know how much your existing children cost you? The USDA also analyzes the cost of raising children by region… clearly, I need to move South! Input your family demographics for your specific annual costs here.
How do your 2014 expenditures compare to these national averages? In what key areas do you think you can tighten your belt? Share your best money saving tips with us, so we can all move toward a better financial future. Happy 2015!