What a supplier price increase does to your maintenance supplies budget
Maintenance, repair and operations supplies are consumed, not resold, so an increase does not squeeze a margin. It lands as budget variance. The figure finance needs is the money, weighted by what you actually use, for the rest of the budget year.
The method
- Take annual usage per item from your purchase history (last 12 months of quantities by SKU).
- Pull old and new prices by SKU. The free price list compare tool lists every changed line between the old and new supplier file.
- Annual spend change per line = usage x (new - old). Sum it.
- Weighted increase = total new spend / total old spend - 1. Report this, not the average of the line percentages.
- Budget variance this year = annual change x months remaining after the effective date / 12.
Weighted increase, usage in B, old price C, new price D:
=SUMPRODUCT(B2:B500,D2:D500)/SUMPRODUCT(B2:B500,C2:C500)-1Variance for the rest of the year, with months remaining in $H$1:
=SUMPRODUCT(B2:B500,D2:D500-C2:C500)*$H$1/12Worked example: six consumables from one supplier
| Item | Usage/yr | Old | New | Change | Spend before | Spend after | Difference |
|---|---|---|---|---|---|---|---|
| Nitrile gloves (case) | 120 | $62.00 | $71.30 | +15.0% | $7,440.00 | $8,556.00 | +$1,116.00 |
| Hydraulic oil 5 gal | 40 | $118.00 | $121.54 | +3.0% | $4,720.00 | $4,861.60 | +$141.60 |
| V-belt A48 | 200 | $9.80 | $10.29 | +5.0% | $1,960.00 | $2,058.00 | +$98.00 |
| Bearing 6205-2RS | 300 | $6.40 | $6.40 | 0.0% | $1,920.00 | $1,920.00 | $0.00 |
| Shop towels (box) | 90 | $48.00 | $52.80 | +10.0% | $4,320.00 | $4,752.00 | +$432.00 |
| Safety glasses (12 pack) | 25 | $30.00 | $31.50 | +5.0% | $750.00 | $787.50 | +$37.50 |
| Total | +8.6% | $21,110.00 | $22,935.10 | +$1,825.10 |
The simple average of the six line increases is +6.3%. Weighted by usage it is +8.6%, because the gloves, with the largest increase, are also the largest spend. With the increase effective and 7 months left in the budget year, the variance to report is $1,825.10 x 7 / 12 = $1,064.64.
Before you accept it
Check the new prices against what the supplier announced (how to check an increase letter), and check for items that disappeared from the new list: a discontinued consumable means a substitute at an unknown price (finding discontinued SKUs).
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.