Monthly Totals - How To

Photo courtesy of lolcats

On the WIR message boards yesterday, a couple folks were asking about how to track savings to come up with monthly totals. Here's how I keep track of mine (its all in an Excel spreadsheet). Erica has a great spreadsheet available for download on her website here.

I wish I could upload a sample or post screenshots so you could see what I'm talking about, but as far as I know, I can't do this in Blogger. Anyone know how?

Ok, so here's what I have in each column on my Excel spreadsheet:

• Date of Transaction (so I can separate everything out by month)
• Store
• Merchandise Total - this is the value of all the items before coupons & store sales (or sometimes just what they ring up as if I'm feeling lazy) - fill in manually
• Coupons Used - this is the value of all the coupons I used in that transaction (plus store sales and reusable bag discounts) - fill in manually
• Tax - fill in manually
• Total - calculated for me (Merchandise Total - Coupons Used + Tax)
• % Saved - calculated for me (Coupons Used/Merchandise Total)
• ECB/Rewards/Gift Cards Used - any rewards that I used to pay instead of cash - fill in manually
• ECB/Rewards Earned - includes catalinas, Extra Care Bucks (CVS) and Register Rewards (Walgreens) that I earned on the transaction - fill in manually
• Rebates - any money that will be coming back to me from a rebate on this transaction (store or manufacturer) - fill in manually
• Cost - this is my OOP - calculated for me (Rewards Used + Total - Rewards Earned - Rebates)
• Profit - anytime there is a negative cost, I include this in my profit column as well. So if my cost was -$3, $3 goes in the profit column - filled in manually although I'm sure there's a way Excel could do this (just haven't felt like messing with it to figure out how)
• # Items - many stores list this on the bottom of the receipt so you don't have to count
• # Free Items

Then I make a row of totals under each column. So for example, this is what it might look like:

• 11/1/08 A&P $50 Merch. Total, $20 in coupons, $3 Tax....
• 11/4/08 CVS $20 Merch. Total, $7 in coupons, $1 Tax...
• Summary Row would be: $70 Merch. Total, $27 in coupons, $4 Tax

This summary row helps me see how much I've spent so far during the month and makes figuring out the monthly totals a snap. I usually highlight and bold the whole row so its easy to find.

I also keep many separate tabs within the same spreadsheet for my Rite Aid rebates, Walgreens rebates and manufacturer's rebates. This helps me keep track of which rebates are confirmed, which ones I've received and which ones I'm still waiting for. For manufacturer's rebates, I make a note of the date I mailed it in and how long it should take for me to receive it. I check the rebates off as I receive them, so its real easy to see what I'm still waiting for. And before I mail any rebates in, I scan the receipt, rebate form and UPC code into my computer so I have a record of what I sent it (I used to print these, but that was a big waste of ink). This is helpful if the company comes back to you saying you didn't submit something - just print and resend. I've had to do this a couple times.

If you're not familiar with Excel, it can calculate a lot of this information for you. For example, to calculate Total (Merchandise Total - Coupons Used + Tax), click on the first cell in the Total column, type "=", then click on the first value in the Merchandise Total column, then type "-", click on the first value in the Coupons Used column, type "+" and then click on the first value in the Tax column. Then hit enter - voila! You have created a formula! You can then copy and paste this formula into the cells below.

To create the summary row, click on the Sum button (looks like a capital E), then select the cells you want Excel to add up and hit enter. You can also do an average (this is what I use to see my average % saved) by clicking on the Average button (clicking on the arrow next to the E should give you other options including Average).