Price Sheet Template Available for Download

Hello again dearies! As I mentioned in my last post, a reader had requested that I help her create a similar price sheet like the one the Tall Cute Guy and I cooked up to help me track the prices of items I purchase on a regular basis. I use this sheet to see at which stores I would score the best deals. TCG has a lot more spreadsheet experience than I do, so instead of me trying and inevitably failing to explain the formula he used to create the columns that generate the unit price, best price and best stores, I just turned the version I use into a blank template that you can just download and fill in with your own information. Let's get started!

First I should mention, I use Google Docs (Drive, whatever they call it now) to host my spreadsheet, not Microsoft Excel. This way, I have access to it through my iPhone and can edit the prices right there in the store should I notice that there has been a change. You will need to have a Google Account to access the link. I have tried and tried to find a way around this, but apparently this does not exist. However, if you don't already have a Google Account and are willing to sign up for one to access the file, you will then be able to export the document as an Excel file if you wish to do so. If you go this route, you will have to do some adjustments to the row/column widths, etc. to pretty it up. All my settings were done on the Google Docs version and sometimes the two programs don't like to "play nice" with one another and things don't look as nice when converted from one to the other. Anyway, here is the link:

Google Docs Version: Price Sheet Template

(When the page opens, click the "Use this Template" button below the description.  If you aren't already logged in to Google, you will have to sign in, and maybe click the link again.)

Now, if you want to convert it to an Excel file, simply click "File" on the top left of the page, go down to "Download As" and then select "Microsoft Excel".

Click to enlarge

Now that you have downloaded it as an Excel file, it will probably look something like this, which is why I mentioned you will have to "pretty it up":

Click to enlarge

OK, from here on out my screenshots will be shown working with the Google Docs version.

Using the price sheet is pretty easy:

1. In the "Catagory" column, simply type in the category of the item (Cosmetics, Hygiene, Pets, Canned Goods, etc.).

2. In the "Brand" column, enter the brand of the item (Cover Girl, Huggies, Purina, etc.)

3. In the "Item" column, enter the name of the item (ColorStay Eyeliner, Green beans, Chicken nuggets, etc.)

4. In the "Need" column, I have the formatting set up with a numerical code.  If I enter a "1" here, the cell will turn green.  If I enter a "2", the cell will turn yellow.  If I enter a "3", the cell will turn red.  As I explained in my previous post on this topic, I use this to help sort what my level of need is for the item.  You can do the same, or simply leave this column blank or hide it.

5. In the "Cnt/Qty" column, enter the count of items in the item you are looking to buy.  For example, if it were a 184 count pack of Huggies baby wipes, I would enter "184" in this column. If it was a tube of mascara, I would simply enter "1".

6. In the "Measure" column, enter the type of measurement used for the item.  Again, in the case of the baby wipes, I would enter "pack" here, or in the case of the mascara, I would enter "each". Other forms of measurements could be ounces (oz) when buying shampoo or other liquids, or pounds (lbs.) when buying things with that type of measurement.

7. THIS STEP IS IMPORTANT: DO NOT TOUCH THE NEXT COLUMN MARKED "UNIT PRICE". This column is automatically generated later after you have entered some pricing data.

8. In columns H-S you can now change the names of the stores (colored blue). Right now I have them labeled things like "Store A, Store B, etc.", but you will want to change this to which ever stores you shop in. I have included room for up to 12 stores to be entered, but adding more later is very difficult and messes up the formula Tall Cute Guy set up for me, so hopefully 12 is enough. If there are too many stores and having such a wide spreadsheet annoys you like it does me, I simply hide the ones I am not using so they are out of my way. To hide the columns, simply click at the very top of the column (where there are only letters) and and arrow will appear. Click on the arrow and a drop down box will show, where you can then select "Hide column".

Click to enlarge

9. ANOTHER IMPORTANT STEP: DO NOT TOUCH THE COLUMNS MARKED "BEST PRICE" AND BEST STORE". Again, these have formulas that will generate this information for you.

10. Now is the fun part- freaking out sales clerks as you go from store to store writing down prices on the items you regularly buy. Make sure you are getting the "base" prices and not writing down a discounted price if something happens to be on sale that day. You can then enter that data into the spreadsheet.

11. Once that is done, the spreadsheet will automatically generate the lowest price for each item and tell you what that price is, which store that price is listed at, and the unit price based on the best store's price.

To show you what I mean, try practicing with this item:

Let's say you want to price Huggies One and Done baby wipes in the 184 count pack. You would enter something like "Kids" as your category, "Huggies" as your brand, "One and Done Wipes" as the item, "1, 2, or 3" as your level of need (if you chose to use this column), "184" as the count/quantity, and "Pack" as your measurement. It should look something like this:

Click to enlarge

Now you can enter the prices for this item from the different stores, and the Unit Price, Best Price and Best Store will automatically be generated for you:

Click to enlarge

Ta da! This will also show you the unit price for the item. In this case, it shows that each wipe in that 184 pack costs $0.03 cents each when purchased at the best store.

Once you have your spreadsheet filled out, you can then use it to sort the list. I mainly sort using the "Need" Column, to see what I am out of and need to stock up on. To sort, simply click the little arrow next to "Need" (or whatever category you want to sort by)

Click to enlarge

and a drop down will appear. In this case I want to sort "Z-A" so my highest needed items (my level 3s) will appear at the top. If you are sorting by brand name or category, you can also sort going "A-Z" to make things appear alphabetically.

Click to enlarge

That's pretty much it!  It's actually very easy, but I tried to flesh this out step by step for anyone who isn't spreadsheet savvy. If you have any questions, please leave me a comment and I'll do my best to help you out!