How to check a supplier price increase letter against your current list
A letter announcing "a 5% increase" is a summary. The new price file is what you will be billed. Check that every line moved by what the letter says, and work out what the increase really costs you at your own volumes.
1. What to pull out of the letter
- Effective date, and whether it applies to the order date, ship date or invoice date. Orders already placed may or may not be honoured at the old price.
- Scope. All items, named product groups, or "selected items". "Selected" means you need the line-level file.
- What the percentage applies to. List price or your net price. On a discounted account those give different results.
- Everything else that moved. Freight terms, minimum order values, surcharges, payment terms. These are not on the price file and cost money too.
- The file. If no new price file is attached, ask for one. You cannot check a percentage without it.
2. Expected price per line
Old price in B2, new price in C2 (pulled in by SKU, see comparing price lists in Excel), and the stated increase in $H$1 as 5%:
=ROUND(B2*(1+$H$1),2)Put that in D2. Then flag the difference in E2:
=IF(ROUND(C2-D2,2)=0,"OK",IF(C2>D2,"Above letter","Below letter"))The money each line costs you beyond the letter, with annual quantity in F2:
=(C2-D2)*F23. Your real increase, weighted by what you buy
The simple average of line percentages treats a part you buy twice a year the same as one you buy every week. Weight it by quantity, with old price in B, new in C and quantity in F:
=SUMPRODUCT(F2:F500,C2:C500)/SUMPRODUCT(F2:F500,B2:B500)-1Worked example: "prices increase 5%"
| SKU | Old | Expected at 5% | New | Actual | Check | Qty/yr | Beyond letter/yr |
|---|---|---|---|---|---|---|---|
| FS-100 | $12.00 | $12.60 | $12.60 | +5.00% | OK | 400 | $0.00 |
| FS-110 | $8.40 | $8.82 | $8.82 | +5.00% | OK | 1,200 | $0.00 |
| FS-120 | $3.20 | $3.36 | $3.42 | +6.87% | Above letter | 5,000 | +$300.00 |
| FS-130 | $45.00 | $47.25 | $47.25 | +5.00% | OK | 60 | $0.00 |
| FS-140 | $19.99 | $20.99 | $21.99 | +10.01% | Above letter | 300 | +$300.00 |
| FS-150 | $2.25 | $2.36 | $2.36 | +4.89% | OK | 8,000 | $0.00 |
Two lines are above the letter. Together they cost $600.00 a year more than a true 5% would have. The simple average increase across the six lines is +6.13%; weighted by what you actually buy, it is +6.01%, or $3,459.00 a year on these lines. That weighted figure, not the letter, is the number to use in a budget or a negotiation.
FS-150 shows how rounding works: 5% on $2.25 is $2.3625, which rounds to $2.36, an actual increase of +4.89%. That is the letter applied correctly, so it is not flagged.
If the new file is long, run it through the free price list compare tool first to get every changed line with its percentage, then check those against the letter.
Pricecheck takes the supplier's email as it arrives, PDF included, compares it with the last version and sends back only the lines that moved. $49/mo, first month free for early signups. No payment now.