Import supplier price lists automatically, before the increase eats your margin

A supplier price increase costs a wholesaler money on every day the purchase price is already new and the selling price is still old. That gap opens because the new price list arrives as a file. Someone has to read it, match it to your own items and key it into the ERP, and then someone else has to work out what it means for customer prices and framework agreements. If you import supplier price lists automatically, that calculation is on the table before the effective date. Purchasing approves, sales decides what to pass on.

The example below is constructed and does not describe a client case. It shows how we would build such an agent.

Importing supplier price lists automatically: three suppliers, three formats

Say a wholesaler of packaging and catering supplies serves restaurants, bakeries and canteen operators. It carries about 9,000 items from just over 100 suppliers and has framework agreements with fixed prices and fixed terms for some customers. In October the prices for next year start arriving. One week brings these three:

The film manufacturer sends an Excel file with 1,400 rows: its own item numbers, the old price and the price from 1 January.

The cleaning products manufacturer sends an 18 page PDF. From now on it prices per case of twelve bottles instead of per bottle. A footnote on page 17 adds a 3 percent energy surcharge on every line from February.

The cup supplier sends an email with no attachment. All prices in the cups product group go up 6.5 percent on 1 January, except items covered by framework agreements.

Today one person in purchasing types the new prices in, usually just before year end and sometimes only when the first invoice at the new price shows up. Sales hears about the increase from the margin review.

Seven steps from the file to approval

1. Recognize the price notice and record the dates

The purchasing inbox also gets order confirmations, delivery notices and invoices. The agent picks out the price notices and records per supplier what applies from when. For the cleaning products there are two dates: the new case price from 1 January and the surcharge from 1 February. Miss the footnote and the purchase price in your system is wrong again from February. The original file stays attached to every record.

2. Read the rows, whatever the format

The Excel file is the easy one. The first time, the agent learns which column holds what and stores that mapping for the supplier. If the supplier reorders its columns next year, the values give it away and the agent asks instead of misreading.

A language model reads the PDF, including tables that break across pages in the middle of a product group. The cup email has no list at all, so the agent builds one. It finds every item from this supplier in the cups group in your item master, 64 in the example, and applies 6.5 percent. Which of them fall under the exception the email does not say. That becomes a question to the supplier in step 7.

3. Match to your own item master

The supplier uses its item numbers, your ERP uses yours. The bridge is the source of supply record that says which supplier number belongs to which of your items. In the example, 1,352 of the 1,400 film rows match cleanly. 31 are items the wholesaler does not stock; they go on an info list and nowhere else. 17 rows carry new numbers because they are successor items. The agent proposes a match from description, dimensions and EAN and flags it as uncertain. Purchasing confirms it, because a wrongly matched film would otherwise get another film's price.

4. Check units and plausibility

The cleaning products are where reading alone gets dangerous. A bottle of dish soap used to cost 2.10 euros. The new PDF says 26.40 euros per case. Without conversion that looks like a twelvefold increase, or, if someone puts the case price into a per bottle field, like a selling price far below cost. The agent converts using the pack quantity from the item master and gets 2.20 euros per bottle, an increase of just under five percent. If the pack quantity in the PDF differs from the one in the master, it stops there and puts the row on the review list.

On top of that come fixed rules the wholesaler sets in advance. In the example, every change above 15 percent and every price cut goes to review, since both usually point to a reading error or a changed pack.

5. Work out what happens to selling prices

For every changed purchase price the agent checks three things: the list price under your own costing rule, customer specific prices, and framework agreements.

With the dish soap it looks like this. A canteen operator has a framework agreement at 2.65 euros per bottle, fixed until 30 June, and buys 800 cases a year. At the old purchase price that left 0.55 euros per bottle, 20.8 percent of the selling price. From January it is 0.45 euros, from February with the surcharge about 0.38 euros or 14.5 percent. Over a year, gross profit on this one agreement drops from 5,280 to about 3,690 euros. If your minimum margin is 18 percent, the agreement lands on the sales list with its end date, annual revenue and the account manager responsible. Nothing about the price can change before 1 July. But sales knows in October and can prepare the renewal instead of finding out from the margin review in July.

The price increase is the first of five places where wholesalers lose margin without anyone booking an error. The other four are in our article on optimizing wholesale processes.

6. Create proposals, overwrite nothing

The agent does not change any price directly. It enters the new purchase prices as future dated prices in the ERP, which most wholesale systems support. Where yours does not, it produces an import file that goes in on the effective date. Proposed new list prices go through the same approval. For customer prices and framework agreements the agent delivers the calculation from step 5 and stops there. Behind each of those is an arrangement with a customer, and that is a sales decision.

7. Approval in purchasing, a list for sales

Purchasing approves the cleanly matched rows with no flags in one go, which in the example is most of the film list. It spends its time on the review cases: the 17 successor items, pack quantity mismatches, changes above the threshold. Next to those sit drafted questions to suppliers. Here that is the cup supplier, asked which item numbers the framework agreement exception covers. Nothing is sent until someone in purchasing has read it.

Sales gets its own list, sorted by account manager: which customer prices and agreements fall below minimum margin, from when, and with how much annual revenue. Passing the increase on to the customer stays with sales.

What January shows you

Once the new purchase prices are stored with their dates, invoice checking has the right reference from day one. If the cup supplier bills more than the announced 6.5 percent in January, the first invoice shows it. How that check runs as an agent is in our article on invoice checking in wholesale.

Sophera Consulting builds this agent for a fixed price with no subscription, and approval stays with purchasing. The build takes one to two days, and we test it in the same week on the price lists sitting in your inbox right now. Which access we need, typically the purchasing inbox and the ERP interface, we sort out together beforehand in the free automation check.

Our recommendation

Start with the price round that is happening now, and with three suppliers rather than all of them. Pick the three with the largest purchase volume and, if you can, one with an awkward format: a PDF that switches units, or an email with no list. Let the agent build the purchasing and sales lists for those three. Then put the sales list next to last year's margin review. Every customer price that surfaced there in summer and is on the list now, in October, is what the agent is worth to you.