Reading a Supplier Price List Without Getting Burned

Every supplier invents their own spreadsheet. Some of the traps inside cost you an afternoon; a few will happily write wrong costs into your entire catalog. Here's what to check, in the order it bites.

First: which price is the price?

Open almost any distributor file and you'll find several money columns. Guessing wrong here poisons every downstream number, so identify them before anything else:

• List / MSRP — the manufacturer's suggested retail. Not your cost. Never your cost.
• Jobber / trade / dealer — a discounted tier that may or may not be yours, depending on your account level.
• Net / your price / cost — what you actually pay. This is the one you want.
• MAP — minimum advertised price. A constraint on what you may display, not a cost.

Some files skip the money entirely and give you a discount off list ("40/10" meaning 40% then a further 10%). That's list × 0.60 × 0.90, not list × 0.50 — chained discounts multiply, they don't add. Get this wrong and you're 4% out on every line in the file.

Second: the price of what, exactly?

This is the single most expensive trap in supplier files: unit of measure. The row says $18.00 and you sell the item for $12.00, so you panic — until you notice the file prices a box of 12 and your true unit cost is $1.50.

Look for columns named UOM, pack, pack qty, case qty, inner/master, or a note buried in the header rows. Then normalise:

unit cost = line price ÷ pack quantity

The same problem runs the other way with items priced per 100 (fasteners, terminals, clips, o-rings) or per metre. And it changes between files: a supplier that moves a line from "each" to "box of 10" without changing the SKU will make that product look like a 900% price increase. If a cost change looks absurd, check the pack column before you check the supplier.

Third: is the number comparable?

Before you compare new against old, make sure both are on the same basis:

• Tax. Some files quote including VAT/GST, some excluding. Mixing them across two versions of a file invents a price change that never happened.
• Currency. A file in EUR versus your USD costs means you're partly tracking the exchange rate. Decide whether you record cost in supplier currency or store currency, then never mix.
• Quantity breaks. If the file has price-per-qty tiers, pick the tier that matches how you actually buy — and use the same tier every time. Comparing this month's 1–9 price against last month's 100+ price produces a fictional increase.
• Freight terms. A cost that changed because delivery moved from free-over-$500 to always-charged is real, but it doesn't live in the price column. See landed cost.

Fourth: matching their codes to your SKUs

Matching is where most of the manual work goes. In descending order of reliability:

1. Their part number = your SKU. The clean case, if you set your SKUs up from their catalog.
2. Barcode / EAN / UPC. Excellent fallback — but watch for the same physical item carrying different barcodes across suppliers.
3. Brand + manufacturer part number (MPN). The auto-parts standard. Requires normalising the brand name, because Bosch, BOSCH and Robert Bosch GmbH are three strings and one company.
4. Fuzzy title matching. A last resort. Useful for building a mapping once, dangerous to trust on a recurring basis.

The normalisation checklist

Before comparing any two codes, strip the noise on both sides:

• Trim leading/trailing spaces — the most common cause of "0 matches found".
• Upper-case everything.
• Decide about separators: is BP-1234 the same as BP1234? Usually yes. Strip hyphens, dots and slashes consistently on both sides.
• Watch supplier prefixes and suffixes (ACD-, -OE, /2) that exist only in their system.
• Preserve leading zeros — see the spreadsheet warning below.

Fifth: the rows that aren't there

A price file tells you about changes in two ways, and the second one is silent:

• Disappeared rows — a SKU in last month's file that's missing from this one. That's usually discontinued, sometimes moved to a different brand line, occasionally just an export glitch. Either way, you're about to sell something you can't reorder.
• New rows — additions you might want in your catalog before your competitors list them.

Neither shows up if you only compare the prices of items present in both files. Compare the key sets too — it's one extra step and it's where the operational surprises live.

Sixth: the shape of the file itself

Real-world files rarely arrive as clean tables. Expect and handle:

• Two or three rows of letterhead above the actual header row.
• Merged cells spanning a category — meaning the category only appears on the first row of its block.
• Category separator rows with no price, mixed in among products.
• Multiple sheets, one per brand, with slightly different columns on each.
• Totals or notes at the bottom that a naive import will read as a product.
• PDFs. If your supplier only sends PDF, ask for CSV — most can, it's just not the default. Failing that, extraction tools work but must be spot-checked against the source every single time.

And, of course, the columns get renamed and reordered without notice. Any process you build should map columns explicitly per supplier rather than trusting position or exact header text.

A repeatable process

1. Archive the file on arrival, named supplier_YYYY-MM-DD. Your file archive is your cost history — Shopify won't keep one.
2. Normalise to a flat table: code, unit cost, pack qty, currency, tax basis.
3. Diff against the previous normalised version: increases, decreases, new, gone.
4. Match to your catalog by SKU, then barcode, then brand+MPN. Keep the unmatched list — it only grows if you ignore it.
5. Compute margin at your current retail and flag everything below your floor.
6. Sanity-check the extremes. Anything above +100% or below −50% is a data problem until proven otherwise.
7. Decide, reprice, then update Shopify (safely).

Steps 2 through 6 are identical every month for every supplier. That's the part worth automating — not because it's hard, but because doing it by hand is exactly how a $3 increase on a 300-unit line goes unnoticed for a quarter.