Price Comparison Template
This new price comparison template can be used for comparing office supplies from multiple vendors or grocery prices at your favorite stores. I created this based on a request from a user who wanted to list prices and item IDs for up to five vendors and then filter the list to create a shopping list.
Price Comparison Template
for Excel
Description
This spreadsheet contains two different cost comparison worksheets. The main difference is that one of them lets you include product IDs unique to each vendor.
You can create a shopping list by filtering the table to display only the items where the quantity is greater than zero.
The Subtotal formula uses the SUMPRODUCT function to multiply the price by the quantity and sum the results.
You can leave the Tax(%) and Shipping fields blank, but don't forget that some online stores make up for discount prices by charging more for shipping.
How to Use the Cost Comparison Template
Unit Prices
Vendors and stores may offer packages with different weights or quantities. For a more accurate cost comparison, you may want to list the Unit Price. For example, if Costco sells a package of 25 pens for $4.50 and WalMart sells a package of 5 pens for $1.25, you can enter the price as the unit price: =4.50/25 for Costco and =1.25/5 for WalMart. Then, the quantity for Costco would be entered as multiples of 25 and the quantity for WalMart would be entered as multiples of 5.
TIP: You can press CTRL+ALT+F9 to calculate the result of "=4.50/25" as you are editing the cell if you want to store the value "0.18" rather than the formula "=4.50/25".
Creating a Shopping List
The List column in the price comparison table contains a formula that checks whether the sum of the quantity (Qty) amounts is greater than zero. If it is, the formula returns an "x". You can use the AutoFilter feature in Excel to hide all the rows except for the rows where the List column includes an "x" - just click on the Arrow in the header of the List column and unselect everything but the "x" option.
Adding More Items (rows)
This is extremely easy to do. The price comparison worksheet uses the Excel Table feature (previously called a "List" in older versions of Excel). Just right-click on a cell within the Table and go to Insert > Table Rows Above or Insert > Table Rows Below.
Adding More Vendors (columns)
You will need to be fairly comfortable with Excel to make the modifications necessary to add more vendors. You can start by inserting new columns, renaming the table headers, copying the formats for the columns from other columns, updating the List formula to include the new Qty column, and updating the inputs and formulas below the table.
