Supplier price lists in Excel, checked before the ERP import

Every supplier sends its price list in a different spreadsheet, which has to be reformatted and imported without anyone really being able to check it all. A checking step would flag abnormal increases, unknown references and unit errors before the import.

Illustrative use case. It describes a problem we come across often and what we could build to solve it. It is not a client project: no results are claimed.

The starting point

Several times a year, and every month for some suppliers, new price lists arrive by email. Each file has its own layout: several tabs, merged cells, prices per box or per unit, discounts in a second file, sometimes no effective date. Someone in purchasing reshapes the file into the ERP’s import format, column by column, then runs the import. A few suppliers send files in their industry’s standard format; most don’t.

The actual problem

The ERP import checks that item codes exist and that columns are filled in correctly. It doesn’t tell you that a price has doubled, that a pack size has changed or that a reference has disappeared. A unit or decimal error therefore goes straight into purchase prices, then into margins and sometimes into selling prices. Depending on the ERP, a badly prepared import can also create duplicate lines instead of updating the old ones. And the reformatting depends on one or two people who know each supplier’s quirks.

What could be built

An inbox for price lists and a setting per supplier: where the references, prices, unit and pack size sit in their file.
Reshaping into the ERP’s format: numbers and decimal separators, references without stray spaces or lost leading zeros, harmonised units.
Checks before import: unknown references, missing references, duplicates, unit or pack size different from the item record, effective date missing or in the past.
Comparison with the previous price, with a variation threshold per product family above which the line is flagged.
A review screen per file: lines that pass, lines flagged with the reason, lines rejected.
Importing the approved lines through the ERP’s own route, as a draft where it allows it, and archiving the original file with its check report.

What would change for the team

A price list is checked line by line before it enters the ERP, not just put into the right format.
Buyers look at the flagged lines instead of scrolling through the whole file.
An abnormal increase is caught before it reaches purchase prices.
Knowledge of each supplier’s files is written down in a setting instead of living in one person’s head.
For every update, you keep the file received, what was flagged and what was imported.

Going further

The article Supplier price list updates: what to check before you import covers the most common errors in supplier price files, the checks worth automating and where industry formats help.