I have always been known to outdo it every year, because I want nothing but the best for my family and friends. I have done the extreme where I don't really shop for sales and just get what I know they want, and I have done the frugal where I only buy something if it is on sale.
This year, like every year, I went out on Black Friday. Even starting at 6:00 a.m. it seemed as though every where I went I was too late to get that one thing that was on my list for each person. It didn't matter what store I went to, the things on my list were never there. By the end of the day I was tired, frustrated and concerned because this year, I knew I have to be frugal in my Christmas spending.
I had already spent close to half the budget and I only had items for six people on my list but only got everything for two of the people. This set me into panic mode, so I turned to Excel to help me wrap my head around it all.
I created three different spreadsheets. Excessive? Maybe, but it actually is very helpful in showing me what I need to see. These spreadsheets should not include your spouse and children, you will want a separate one for them, this is strictly for your family and friends that you shop for every year. The examples I am using can be tailored to fit any family need.
The first spreadsheet what I did was made four different columns as follows:
Seems simple enough...right? What you need to keep in mind is this; Who are you buying for and how much is your MAX amount you want to spend on each person. We also added an area with what realistically we wanted to spend and I showed the Total Over that amount of our projection. This put it into reality for me. I needed to find some items on sale so that the retail price would hit where we wanted to spend for each person but my Total Spent was lower. For example, one person we bought for wanted a appliance. So I went on a hunt for the best deal for this appliance by searching Black Friday Ads. The first two places I went to, they were out. The third place, I get lucky and find one, original price $30 picked up for $14.99. So when I add up what I spent I add up the retail value as well as my out of pocket cost. So $30 goes towards say a $50 budget and I know I have $20 left to spend for this person. But in reality I only spent $15, so I saved $15 which helps my total overage go down. (note: This is not an actual item I purchased, because I don't want to give it away to those I am buying for, but I did get a $30 item for $14.99 this year) :)
This spreadsheet shows me my overall budgeted and total spent, it doesn't break it down so I can't really see where the money went, but one of the next two spreadsheets will help with that.
To keep in mind, Column C you always start off with what you budgeted and as you spend, you adjust your totals from Spreadsheet 3 as you go. My totals may look funny, but they are actually because the formula is set in place for what it will be like once all the receipts are added.
The second spreadsheet is where I wanted to see how much money was spent per card. I broke it down by credit card and bank card. This was so I could up on my budget and know where the money was taken from. If you use credit cards at Christmas time, it is more helpful if you look at your bank account and figure out realistically what you can spend from your bank account. You want to spend as much cash on hand as you can so that you don't go farther into debt. If you simply don't have the cash on hand and have to use credit cards, I recommend that you pay off a big chunk (or all if you can) on the card you want to use so that it doesn't set you back any farther.
By going to this spreadsheet, I am able to take a quick glance and see my total spent in each area, and I will total the columns up every time I add to it.
This is what the second spreadsheet looks like:
This is what the third spreadsheet looks like, it is a bit more complex so I am going to try and break it down for you:
Column A - Start with the Store Name off of the first receipt in A-1 and in C-1 put what card was used at this store (CC=Credit Card; BC=Bank Card).
A-2 start with the first item on the receipt - who did you buy it for? Put their name in column A and go down the receipt until all names are entered for each line on the receipt.
In C-2, start with the first item on the receipt - what was the price? Put the price for each line down Column C next to the names you put in first.
Now this is where it gets a bit tricky. I decided I needed to give everything a number, so starting in column F, I put numbers down row 1 and then in row 2 I put who each number was going to represent. Be sure to include Tax, Total CC Spent, Total BC Spent and I also included a space for returns because I knew I already have something I wanted to return.
Once this is set up, in Column E, put what each line will represent, I chose Spent, Budgeted and Difference to show me how much I had already spent and what I still had left. You will need these totals to input your total spent on the first spreadsheet.
Once you have your numbering system figured out, you will want to go to Column B and input the number coordinating with each person. Once that is complete, now the fun begins.
In the spent row (i.e. F-3); you will need a formula to calculate from everything in column C what was spent for each person. You do this formula as follows:
Click on the down arrow next to the AutoSum function in the ribbon; Click on "More Functions"; Look for the function "SUMIF" and click on that and then click "OK"; You will see a box pop up with Range, Criteria and Sum_range.
For your Range, you will want to click on Column B by clicking on the "B" in the grey box at the top of the spreadsheet. This will put the marching ants on the whole column and you will see a "B:B" in the box.
Once you have made that selection active, press "TAB" this moves you to the Criteria box. Now, you want to click on the number in the column your cursor is in, so for this example it would be column F, which is number 1 - Her Mom; Click on the number 1 (F-1); you will see the marching ants go around the cell indicating it's active.
Once this is done, press "TAB" to move to the Sum-Range box. In this box you need your totals, what is it going to add up and put in the cell you started in? You will want to click on Column C by clicking the "C" in the grey box at the top of the spreadsheet to select the whole column. By doing it this way, you are automatically adding anything that you may put in this spreadsheet in the future.
Once all three boxes are populated, you should have the following in the boxes:
Range: B:B
Criteria: F1
Sum_range: C:C
If the boxes match, click OK. You should then see in F1 a total amount of all the amounts next to where you put the number 1. If you want to double check that it added it up correctly, go through your list and add up the totals manually for that person to make sure it did it correctly.
Unfortunately, you cannot copy this formula to the next cell, so you will need to repeat the process for all of the cells in order for it to work properly.
Once all of the cells are complete for the "Spent" row, the next thing you do is add your Budget under each of those totals by manually typing in the amount.
For the final row, Difference, it's pretty simple. You do a simple formula that looks like this:
=F4-F3
What this does is it takes the Budget and Subtracts what you spent and shows you what is left (or what you went over).
By using these three spreadsheets you can get a handle on where your money is going so that you don't over spend. Once you reach a level where you are comfortable for each person, then stop. For sale items where you are wanting to figure in the retail value instead of the sale price, you will want to keep this somewhere else. If you were to put retail value then your total CC and total BC will be off and that will not show the real amount you spent.
What I have done with this is went back to Spreadsheet 1 and off to the side in the empty columns is added it up that way and then do a sum across the totals.
For example:
Say for Her Mom, you got an item that retailed at $30 for $15 on sale and you want to figure out what would be left for you to spend on Her Mom using the retail value instead of the sale price.
On Spreadsheet one in Column E you will put $30. Lets say you also spent another $10.50 on her for a different item, so lets add $10.50 to Column F next to Her Mom. You have at this point only bought two items but you want to see where you are at as far as your budget for Her Mom, in Column G, you will want to put a formula for the Sum of Column E and F which would look like this: =E2+F2 Your total should now show $40.50, highlight G2 in yellow so you know this is a total amount spent and not part of a receipt. In column H, you will now want to see what is left to spend on Her Mom, so the formula should be =D2-G2 which should give you $9.50. If the item is in the good (meaning not a negative amount) make the type green to indicate you still have funds available. If the item is in the negative, make the type red to show you went over.
Here is a sample of what it would look like with an overage and with available funds:
Remember, the key to not overspending, is to be organized, and know where every penny goes! It's already helping us this year and I feel so much better about it and know that I will be very close to what we budgeted. If you go over, make sure you don't go over excessively, and keep track of what you buy! (THIS IS KEY!).
When out shopping, I like to carry an envelope in my purse. I have everyone's names on the outside of the envelope with what is left of their budget and as I buy, I write next to the budget what I spent so when I go to the next store, I know what is left. Inside the envelope goes the receipts.
As soon as I get home, I open my spreadsheet up and start on the first tab and move my way to the third until all receipts are added. I then write the names of who the items bought were for along with a check mark in the upper right corner of the receipt indicating I have logged it. All receipts get filed in a Christmas File Folder in our Filing Cabinet so that next year, I still have records of what I bought for each person and how much I spent so I can prepare the budget for that year. It's very helpful to keep these receipts also in case someone needs to make a return and you didn't get a gift receipt!
How do you currently budget? Do you have something already in place? I'd love to hear from you, please leave a comment below and let me know!
Best of luck to you this Holiday Season with your budgets and your shopping.
May your Holiday's be Merry and Bright,
Shonda
