Supplier Price Increases: Update a Wholesale Price List With AI

To update a wholesale price list after supplier price increases without errors, never let the AI do the arithmetic. Match every line by supplier code, calculate new prices with spreadsheet formulas, then run five checks: unmatched lines, outlier changes, margin floor, rounding and a before-and-after diff. Use the AI to write those formulas and to summarise what the checks flag.

The reason is simple. A chat assistant is good at reading a supplier's awkward letter and writing a formula, but it can quietly drop or retype a row in a 5,000-line table, and nobody notices until a customer is invoiced the wrong amount. The method below keeps the numbers in a spreadsheet and gives the AI the jobs it is good at.

Follow me on Instagram@sagnikteaches

The shape of the job: what we are updating

I'll use one illustrative business throughout: a wholesaler that supplies grooming products to dog groomers, with 5,000 active lines on its trade price list. Its main supplier, which stands behind almost every line on the list, has sent a new price file and a covering note saying "average increase of 6%, with some categories higher". The wholesaler sells at a target gross margin of 32% and has a floor of 22%, below which no line should ever be sold without a person approving it.

Connect on LinkedInSagnik Bhattacharya

Gross margin here means (selling price minus cost) divided by selling price. It is not the same as markup, which divides by cost. A 32% margin is a markup of about 47%. Mixing the two up is one of the commonest reasons a price list ends up too low, so the spreadsheet in this tutorial labels the column "margin on price" and never "markup".

Subscribe on YouTube@sagnikteaches

The same process works for a bakery supplier's flour and packaging list, a café's coffee and syrup list, or a driving school buying cars and fuel cards. Only the columns change. If you sell through several tiers of customer, finish this update first and then follow the tiered pricing tutorial to push the new base prices through trade, volume and key-account tiers.

Step 1: Freeze what you have before you change anything

Save a dated copy of the current price list as read-only. Name it something you can find in a year, such as price-list_before-increase.xlsx. Everything else is compared against this file, so it must not be edited again. Add a column called row_id that numbers every line 1 to 5,000, so that if sorting scrambles the order later you can always restore it.

Then count. Write down the number of lines, the number of distinct supplier codes and the number of lines per category. These three counts are your tripwires. If any of them changes by accident during the update, you have lost or duplicated a row.

Keep these in the first tab of the workbook, titled "Counts". It takes five minutes and prevents more embarrassing mistakes than any later check.

Step 2: Read the supplier's note and file before you touch a formula

Supplier price increases arrive in three common shapes: a PDF letter with a percentage, a spreadsheet with new costs per code, or a revised catalogue PDF. Each needs a different first move.

When you get a letter with percentages

Paste the letter text (not the whole price list) into ChatGPT or Claude and ask it to turn the wording into rules you can test. Letters are written to sound gentler than the numbers, so the rules often hide in sentences like "selected lines in the professional range will move by a further amount".

Below is a supplier's price increase letter. Turn it into a table of rules:
category or range, % increase, effective date, and any exceptions or
conditions. Quote the exact sentence each rule comes from. If a sentence is
vague (for example "selected lines"), list it separately under "Needs a
question to the supplier" rather than guessing.

[paste letter text]

Illustrative output: five rules (shampoos and conditioners +5%, blades and clippers +7%, towels and dryers +4%, packaging +9%, everything else +6%) plus two "needs a question" items: "selected lines in the professional range" and "freight surcharge may be applied". Those two are why you send the supplier one email before you publish a price. You can use the same AI for that email; this supplier-email tutorial has wording for it.

When you get a file with new costs per code

A file is better than a percentage, because it gives you the actual cost for each line. Before importing it, ask the AI to compare its headers with last time's file and report anything that changed: a renamed column, a new "pack size" column, a switch from price per unit to price per case, a different currency column. A change in the unit of measure is the dangerous one, because a line that looks like it rose 1,200% has usually just moved from a single to a case of twelve.

Here are the column headers from last year's supplier file and this year's.
List every difference: renamed, added, removed, reordered. Then list any
column whose name suggests a unit of measure, pack size or currency, and tell
me what I should check in the data before importing.

Last year: [headers]
This year: [headers]

If you only have a catalogue PDF, extract it to a spreadsheet first and check the extraction as described in this guide to what ChatGPT can and cannot read. Extraction errors, such as a decimal point lost or two columns merged, are common in PDFs and must be fixed before matching.

Step 3: Match every line by code, and park what will not match

Matching is a lookup, and lookups belong in the spreadsheet. Put the supplier's new file on its own tab called "Supplier new". On your price list tab, add a column new_cost with a formula like this in Excel:

=XLOOKUP(B2, 'Supplier new'!A:A, 'Supplier new'!D:D, "NO MATCH")

Here B holds your supplier code and column D of the supplier tab holds the new unit cost. Google Sheets has its own XLOOKUP with the same argument order, and older Excel versions that lack it can use INDEX/MATCH; ask the AI to convert the formula for the version you use. The text "NO MATCH" is deliberate. A blank would be read as zero by later formulas, and a zero cost looks like a 100% margin.

In the illustrative run, 4,931 of 5,000 lines match. The 69 that do not go to an "Exceptions" tab, along with the 187 lines in the supplier file that you do not stock. The AI can help here: give it the 69 unmatched codes and descriptions plus the 187 unknown lines and ask it to suggest likely pairs where the supplier has changed a code.

These 69 lines in my list found no match in the supplier's new file, and these
187 lines in the supplier's file are not in my list. Suggest likely pairs
where the description looks like the same product with a changed code. For
each pair give a confidence (high, medium, low) and the words that made you
think so. Do not merge anything; I will confirm each pair.

Illustrative output: 41 pairs suggested, 28 of them high confidence (identical description, new code with a suffix such as "-R"), the rest medium or low. You confirm the high ones in about ten minutes, look at the medium ones against the supplier's catalogue, and leave the low ones for the supplier to explain. The 28 lines with no pair are real discontinuations, which you retire or put on a clearance list. Counting this up matters: once the supplier's reply settles the last of the 41 pairs, 4,931 matched plus 41 confirmed pairs plus 28 retired equals 5,000, and your tripwire count is back to where it started. Until then, the unsettled pairs keep their old price and stay on the exceptions tab.

Step 4: Calculate new prices with rules, not with the chat window

Now the sums. Decide the pricing rule per category in plain words first, because that is the part only you can decide. For the grooming wholesaler the rules are:

• Shampoos, conditioners and towels: restore the 32% margin on the new cost.
• Blades and clippers: restore 32%, but never increase a line's price by more than 8% in one go, because these are the lines groomers compare with the competition.
• Packaging: pass the full increase on and keep the cash margin in dollars the same.
• Everything else: restore 32%.

Put the target margin in a cell on a "Rules" tab (say Rules!B1 = 0.32) and the floor in another (Rules!B2 = 0.22), and have every formula point at them. Nobody should be typing 0.32 into a formula, because next year it will be 0.30 and you will miss one.

new_price_raw = new_cost / (1 - Rules!$B$1)

A worked line: a shampoo that cost $6.40 and sold at $9.41 (margin 32.0%) now costs $6.78 (up 5.9%). Left at $9.41, the margin falls to (9.41 minus 6.78) divided by 9.41, which is 27.9%. Restoring 32% needs $6.78 divided by 0.68, which is $9.97. The trade customer sees a rise of $0.56, or 6.0%.

The AI's part is to write the formulas, including the capped-increase rule for blades. Tell it your column letters and the rule, and ask for the formula plus two test lines with the answer worked by hand so that you can check it.

My price list has: supplier code in B, old price in E, new cost in G, category
in C. Write an Excel formula for column H (new price) that: restores a 32%
margin on new cost (target in Rules!B1); but if category is "Blades" or
"Clippers", caps the increase over old price at 8%. Then give me two example
rows with made-up numbers and the answer worked out step by step so I can
test the formula.

Illustrative output (what you'd check): =IF(OR(C2="Blades",C2="Clippers"),MIN(G2/(1-Rules!$B$1),E2*1.08),G2/(1-Rules!$B$1)). You test it on the two sample rows, then on three real lines from your own list worked on a calculator. Only then do you fill it down 5,000 rows. If the formula comes back with a feature your version of Excel lacks, say which version or app you use and ask again.

Step 5: The five checks that catch the mistakes

This is the stage most people skip. With 5,000 lines, one wrong price in a hundred is fifty lines, and a pasted-in table cannot be proofread by eye. Build the checks as columns, then filter on them.

Check 1: Outlier changes

Add a column change_pct = new price divided by old price, minus one. Sort it. In the illustrative run the median change is +5.8%, 212 lines move more than 15%, 14 lines go down, and 37 lines do not change at all although the letter implied a rise on every line. Each group tells a different story: the 15% movers are often a pack-size change, the 14 decreases are either genuine or a transposed digit, and the 37 unchanged lines usually still carry last year's cost in the supplier's file, which is a question for the supplier, not a reason to celebrate.

Here are the 212 lines whose price changes by more than 15%, with old cost,
new cost, pack size and description. For each one, say which of these is the
most likely explanation: pack size or unit change, typing error (for example
a transposed digit), genuine large increase, or unclear. Give the evidence
from the row. Do not recalculate any prices.

Illustrative output: 131 look like a unit change (the description now says "case of 12"), 19 look like typos (a cost of $41.10 that was $14.10), 52 look like genuine increases concentrated in one range, and 10 are unclear. You open the supplier file for the 19 and the 10 and ask the supplier. Notice what the AI did here: it read and classified a few hundred rows, which suits it, and recalculated nothing.

Check 2: The margin floor

Add new_margin = (new price minus new cost) divided by new price, and a flag column:

=IF(J2<Rules!$B$2,"BELOW FLOOR",IF(J2<Rules!$B$1-0.05,"WATCH","OK"))

Lines that are capped (the blades) are the ones most likely to land under the floor. In the run, 23 lines show BELOW FLOOR and 118 show WATCH (more than five points under target). Don't let the formula fix them silently. Put them on a "Floor review" tab and decide each one: raise the price anyway, accept a thin margin on a deliberate loss-leader, or drop the line. The AI can summarise the 23 by category so you see that 17 are clippers and decide once.

Check 3: Rounding rules

Calculated prices such as $9.97 or $23.4412 look untidy and can differ by a cent between systems. Round up, never down, because rounding down can push a line under the floor by a hair. A sensible set of rules: under $20, round up to the next 5 cents; from $20 to $100, to the next 25 cents; over $100, to the next whole dollar. In Excel:

=IF(H2<20,CEILING(H2,0.05),IF(H2<=100,CEILING(H2,0.25),CEILING(H2,1)))

Then check that rounding did not change the margin by more than a point or two. A line at $0.31 rounds to $0.35, a 13% jump, so for very cheap lines round to the next cent instead. Keep a column showing the rounding amount; the average should be a few cents, and a large average means the rule is too coarse.

Capped lines are the one exception to rounding up. A blade that sold at $31.50 with an 8% cap comes out at $34.02; rounding that up to $34.25 is a rise of 8.7% and breaks the promise the cap was meant to keep. For capped lines, round down to the step instead (FLOOR(H2,0.25) gives $34.00, a rise of 7.9%) and let the margin floor check catch any line that this pushes too thin. The shampoo from Step 4 goes the other way: $9.97 rounds up to $10.00, which lifts its margin from 32.0% to 32.2%.

Check 4: Unit and pack-size sanity

Make a column price_per_unit where a pack applies, and compare it with the same measure last time. A line that costs 30 times more per unit than before is a pack-size problem, however plausible the case price looks.

Check 5: Row count and totals

Go back to your tripwires. The list should have 5,000 lines less the 28 retired, plus any new lines you added on purpose (here 22 products from the supplier's new range), which makes 4,994. Category counts should match the "before" file except where you added or retired. Then compute the total of old prices and the total of new prices; the rise from one to the other should sit near the median change you saw in Check 1. If the totals have risen by only 1.4%, a block of rows has not updated.

Step 6: The before-and-after diff, which is the real safety net

A diff lists every line that changed, and what it changed from and to. Build it as its own tab. For each row_id, pull the old price from the frozen file and show: code, description, old price, new price, change in dollars and percent, old margin, new margin, and the check flags. Filter out lines with no change, and separately list lines that exist in one file but not the other.

The second row is exactly what the process is for: the 8% cap protects the customer relationship but lands the line at a 21.4% margin, below the 22% floor, so a person decides. The packaging row follows its own rule: the pouch cost $8.70 and rose 9% to $9.48, the $4.05 cash margin is kept, and $13.53 rounds up to $13.55, so the customer sees 6.3% while the margin percentage slips to 30.0%. The last row is a line whose cost the supplier file left unchanged, so the restored-margin formula returns the old price; you query it before assuming the supplier has held the price.

Ask the AI to read the diff, not to build it. Give it the filtered diff (a few hundred rows at a time) and a one-page summary request:

Here is the diff between the old and new price list: only lines that changed
or are flagged. Summarise it for the owner in under 200 words: the count of
lines changed, the median and largest increase, which categories moved most,
how many are below the margin floor, and the five lines I should look at first
and why. Use only the figures in the table. If you cannot tell, say so.

Check its numbers against your own counts. If it says 4,812 lines changed and your filter says 4,806, trust the spreadsheet and ask it where the six went. Often it has counted a header row or a repeated line.

A mistake that looked like a success

An easy way to see why the five checks exist: imagine the wholesaler had skipped them and pasted 5,000 lines into a chat window asking for "each cost plus 47%". The reply came back with 5,000 rows and looked right. Two things were wrong, and neither was visible. The assistant had silently reordered a block of 60 rows, so the prices sat against the wrong codes, and it had applied 47% to a case-of-twelve line as if it were a single. Orders at those prices were taken for nine days before a customer asked why a pack of towels cost less than one towel. The row-count tripwire would not have caught the reorder (the count was right), but the diff would, because the price for a code would have moved by a random amount. Count checks catch missing rows; diffs catch scrambled ones; you want both.

Smaller examples from other kinds of business

The method flexes without changing shape. Four quick variants:

• A bakery buying flour, butter and packaging: the supplier emails a revised sheet monthly. The owner keeps the rules tab small (target food-cost percentage per product family) and uses the AI mainly for the outlier check, since butter swings and a typo is more likely than a real 40% rise.
• A café buying coffee, syrups and cups: a 2% rise on cups changes the cost of every drink. The update flows into the drink-costing sheet, so the diff also lists which menu items fall below their margin floor, and a drink that drops under it gets repriced or reformulated before the next menu print.
• A dog groomer buying shampoo and blades for resale at the front desk: 80 lines, so no formulas beyond the margin check. A pasted table is fine at this size, but the margin column is still built in the spreadsheet.
• A driving school with a fuel card and car-lease schedule: not a price list, but the same discipline applies to a lesson-price change. Old price, new price, margin per lesson, and a rounding rule (to the nearest $0.50) before the price goes on the website.

What to keep for next time

When the update is done, save the pieces that were hard to build: the rules tab, the formulas with their test rows, the prompts above and the counts. Next year's increase should take an hour, not a day. Store the prompts in a shared note so a colleague can run it. If you want one place to track supplier prices and lead times beyond a single list, AI supplier management covers it, and checking margins product by product is the natural companion to the floor check here.

Finally, read the diff against the supplier's own wording one more time before publishing. If the letter said "effective from the first of next month" and your price list goes live today, you have raised prices a month early and been generous to the supplier. Dates and notice periods in your own customer terms are not something AI should settle: check the contract wording, and ask a qualified adviser if a key account has a fixed-price agreement. Once the numbers are right, the covering message to customers is the next job, and the trade price increase letter tutorial handles that.

How to tell it worked

Three signs, a week after publishing. First, your average margin on the new list, weighted by last quarter's sales, sits at or just under target rather than at a surprising figure. Second, the invoices going out match the list: pick ten orders at random and recompute them by hand. Third, there are no customer queries of the "why did this triple" kind. If any of the three fails, open the diff tab, since it records exactly what you changed and why, and correct the lines, not the whole list.

Questions about updating a price list after a supplier increase

Can I paste the whole price list into ChatGPT and ask it to add 6%?

You can, but you shouldn't rely on it. A chat model reads long tables as text and can drop, merge or retype rows without telling you. Ask it to write the formula or script instead, run that in your spreadsheet, and use the model for checking and summarising, not for the arithmetic itself.

How many lines can a chat assistant check at once?

Treat a few hundred rows as the comfortable limit for pasted tables. Beyond that, upload the file and ask for a summary of the flagged rows only, or work from a spreadsheet with the checks already built in. Always compare the row counts it reports with your own count.

Should the new price always protect my old margin?

Not always. Protecting the percentage is the default, but some lines are price-sensitive, tied to a contract or used to win accounts. Decide per category whether to pass the whole increase on, part of it, or none, and write that rule down before you touch the numbers.

How do I stop a supplier file with different column names breaking my sheet?

Map the columns once and keep a small lookup tab that says which supplier column feeds which field. Ask the AI to compare the new file's headers with last time's before you import anything, and stop if a header, unit of measure or pack size has changed.

What if a supplier line has no matching code in my list?

Put it on an exceptions tab rather than guessing a match. Unmatched lines are either new products, discontinued ones or a code change, and each needs a person to decide. A name-similarity match from AI can suggest a candidate, but you confirm it.

Further reads

• Supplier Scorecard Template: Score Delivery and Quality With AI — Score the supplier whose prices keep rising.
• Can AI Read Emailed Orders Into a Wholesaler's System? — Handle the orders that arrive against the new prices.
• How to Compare Supplier Quotes Side by Side With AI — Compare the increase with what other suppliers quote.
• Best AI Tool for Pricing Wholesale Goods: Chat Assistant or App? — Choose between a chat assistant and a pricing app.
• Plumbers AI: Turn Supplier Price Lists Into a Parts Markup Sheet — See the same markup-sheet idea in a trade business.
• How to Match Supplier Invoices to Purchase Orders Automatically — Catch invoices that still bill the old price.
• How Can a Coffee Shop Use AI? Start With Supplier Prices — Carry supplier price changes into a café's menu costs.
• Florist Pricing Formula for Bouquets and Arrangements, Built With AI — One bouquet priced line by line: stems, waste allowance, labour, overhead and profit, plus the prompt that gets AI to build the formula sheet from your costs.
• AI Tools for Exporters: Answer Overseas Buyer Enquiries and Draft Quotes — Reply to overseas buyer enquiries and draft quotes from your price list with AI, plus a document cross-check and the sign-off steps before anything is filed.
• Line Sheet Template: Turn a Product Export Into a Wholesale PDF With AI — Export your products, let AI add wholesale prices, MOQs and case packs in a sheet, then check the maths and produce a PDF buyers trust.
• Wholesale Nursery Availability List: AI Builds and Emails It Weekly — A weekly routine that turns a grower's stock sheet into a priced availability list, emails it to trade buyers and marks lines as sold.
• Supplier Onboarding Automation: AI Forms, Checks and Reminders — How a wholesaler can collect supplier documents with one form, have AI check them, chase gaps on a schedule and stop bank-detail change fraud.
• Supplier Specification Changes: AI Spots Edits That Break Labels — Compare a supplier's new spec sheet with the old one, catch allergen and additive edits, and know exactly when a label update is triggered.
• Tea Blending Business: Cost Each Blend and Set Trade Prices With AI — Cost a loose-leaf blend per kilo, add wastage and packaging, and set pouch, tin and bulk trade prices, with AI checking your spreadsheet.
• Wholesale Price List Template for Food Makers, Drafted With AI — A checklist-style wholesale price list for food makers: columns, costing from your own numbers, terms, and an AI-drafted cover email.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft and Google spreadsheet function documentation (Excel XLOOKUP, CEILING and FLOOR; Google Sheets XLOOKUP); worked figures are illustrative.