Tiered Wholesale Pricing: Set Tiers and Margin Floors With AI

To set tiered wholesale pricing without underpricing key accounts, work out a margin floor first: unit cost plus delivery and payment costs, divided by one minus your minimum margin. Then build three tiers (trade, volume, key account) from that, price each above the floor, and back-test them against the last twelve months of orders before any customer sees them.

The back-test is what most price lists skip. A tier table can look sensible in isolation and still be wrong for the accounts you actually have, because your largest customers have usually been negotiating for years and some already pay below any rule you would write today. The worked example below shows that happening, and what a spreadsheet and an AI assistant do about it.

Follow me on Instagram@sagnikteaches

The starting point: costs and a floor, not a discount ladder

Most tier tables begin with discounts off a list price: 10% off for this, 15% off for that. The trouble is that discounts say nothing about whether you still make money. Start from the other end.

Connect on LinkedInSagnik Bhattacharya

The illustrative business sells packaged bakery mixes to cafés and independent shops. One standard bag has a recommended retail price of $10.00. The numbers behind it:

Subscribe on YouTube@sagnikteaches

The floor is the one number in this tutorial that you must not let anyone, human or AI, guess. Everything else is a choice; the floor is arithmetic from your costs. Write it on the Rules tab of your workbook and have every price formula refer to it.

A word on the margin figure. This is margin on price, meaning (price minus all costs) divided by price. Twenty-five per cent margin after delivery is a markup of 33% on total cost. If you work in markups, convert before you compare; the two are routinely confused and a floor of "25% markup" is a different and lower price than a floor of "25% margin".

The tier table with the rules that go with it

The three tiers, with the rule for each stated in numbers a customer and a salesperson can both follow:

Check the key-account row, because it carries the whole argument. At $4.25 the margin before delivery is (4.25 minus 3.00) divided by 4.25, which is 29.4%. Take away the $0.15 of free delivery and it is (4.25 minus 3.15) divided by 4.25, which is 25.9%, just above the floor. The headroom is five cents a bag. A single 5-cent increase in ingredients, and every key account falls below your own rule. A table that passes the floor by that little should be flagged as fragile, and you want a spreadsheet to say so, not to find out in the next cost review.

The formulas behind the table, in Excel or Google Sheets, with annual units in column C, a small tier table on a tab called Tiers (minimum units in column A, price in column B) and the floor in Rules!B1:

Tier price:   =XLOOKUP(C2, Tiers!A:A, Tiers!B:B, "NO TIER", -1)
Final price:  =MAX(D2, Rules!$B$1)
Flag:         =IF(D2<Rules!$B$1,"BELOW FLOOR",IF(D2<Rules!$B$1+0.10,"THIN","OK"))

The -1 at the end of XLOOKUP means "exact match or the next smaller value", which is how you land an account with 3,200 bags in the 1,000-bag tier. Start the trade tier at 0 units so every account with a unit count finds a row; a "NO TIER" result points to a units cell that needs fixing before you trust the rest. Google Sheets' XLOOKUP takes the same -1 match mode, and an older Excel without XLOOKUP can use VLOOKUP with an approximate match on a sorted tier table; ask the AI to convert the formula for the version you use, and test it with accounts at 999, 1,000, 4,999 and 5,000 bags.

Back-testing against last year's orders

Now the step that makes this reliable. Export last year's sales to wholesale accounts, grouped by account: account code, units bought, revenue, and (if you have it) delivery cost. A summary of a few hundred rows, not the raw order lines, is what you work from; it keeps the data small enough to inspect and avoids sharing every customer's order detail with an AI tool.

For the illustrative business, last year looked like this by tier, using the tier rules above to sort each account into the group it would fall in:

Three findings fall out of one table. First, the structure is almost revenue-neutral (up $1,650 on $826,650), but it moves money between groups: the 40 volume accounts would pay 10 cents a bag less than they did, and the six key accounts would pay more. Second, the volume tier is priced below what those accounts actually paid, so you are giving away $7,800 for no reason a customer asked for. Third, the key accounts, who matter most, are the ones whose prices change most.

The key accounts one by one

Averages hide the problem, so list the six individually:

Four of the six biggest accounts were already paying below the floor. Between them they bought 39,000 bags, and the shortfall against the floor came to $6,750 over the year. These are the accounts that a polite negotiation each January had worn down, with nobody adding up the total. That is what the back-test is for: it replaces "our key accounts are our best customers" with a figure for what that loyalty costs.

It also exposes the risk in correcting it. Account A, on 12,000 bags, would see a 30-cent rise, 7.6%. If it left, you would lose its contribution, which at last year's price was $3.95 minus $3.00 cost and $0.15 delivery, or $0.80 a bag, so $9,600. That is almost the same as the whole $9,450 the new key tier brings in. Pushing every key account to $4.25 at once is therefore a gamble on account A, and a more careful plan phases it: move A to $4.10 now and $4.25 in six months, and attach something in return, such as a longer agreement or a forecast of volumes.

What to ask the AI to do

The AI doesn't set the tiers. It speeds up four tasks around them, each with a prompt you can reuse. First, structure options:

I sell packaged bakery mixes to cafés and shops. Full unit cost $3.00,
delivery for large accounts $0.15 a unit, minimum margin after delivery 25%,
RRP $10.00. Last year: 150 accounts under 1,000 units, 40 between 1,000 and
4,999, 6 above 5,000. Propose two alternative tier structures (different
thresholds or prices) and for each give: the floor price, margin per tier
before and after delivery, and one risk. Show the arithmetic. Do not
recommend one; list the trade-offs.

Illustrative output: a structure with a fourth "pallet" tier at 10,000 units for the two largest accounts, and a variant that keeps three tiers but lifts volume to $4.70. What you fix: the margins it reports should match the formula above to the cent, so recompute two of them. If they're off, ask it to show the working again, and use your sheet's figures regardless.

Second, the back-test summary. Paste the account-level summary (account code, units, revenue, tier under the new rule) and ask for a reading.

Here is a table of last year's wholesale accounts: code, units, average
price paid, and the tier each would fall in under the new rules, with the
floor price of $4.20. List: (1) every account paid below the floor and by how
much per unit, (2) accounts within 5% of a tier threshold, (3) the ten accounts
whose price would change most as a percentage, (4) any account whose annual
change exceeds $2,000. Use only the table. Show counts so I can check them.

Illustrative output: 4 accounts below the floor (A to D), 11 accounts within 5% below a tier threshold, and the ten biggest percentage movers, nine of them in the volume tier. Check the counts against filters in your spreadsheet. If the AI says 5 below the floor and your filter says 4, the spreadsheet wins, and the AI usually has counted an account on the floor as below it.

Third, formula help for the tier lookup and floor, described above, with the instruction to include test rows. Fourth, the messages to customers once decisions are made, which is covered in the trade price increase letter tutorial.

Threshold cliffs and the accounts just below them

Any tier rule creates a cliff. An account at 4,980 bags pays $4.60 a bag, or $22,908 for the year; an account at 5,010 bags pays $4.25, or $21,292.50. Buying 30 more bags would cut the first account's bill by $1,615.50, and customers near a line work that out quickly. In the back-test 11 accounts sit within 5% under a threshold. Three ways to handle it:

• Smooth the steps. Add a middle tier or lower the jump between tiers, so the gain from crossing a line is a few cents and not a reason to restructure orders.
• Price the increment, not the whole order. Charge the lower price only on bags above the threshold. It's harder to explain, so use it only where the cliff is large.
• Review on a rolling twelve months, quarterly. An account moves up when it crosses and doesn't fall back until a full year below, which stops tier-hopping and keeps the price list predictable.

Whichever you pick, decide it before the first customer asks. "We looked at it with the accounts in mind" is a better answer than an exception made on the phone, because an exception for one account becomes the price list for the next.

Applying tiers in different kinds of business

• A café roaster selling beans to offices and other cafés: tiers by kilos a month, floor set after roasting loss, and a fourth line for a cleaning-and-maintenance contract that is priced on its own. The roasting loss uses the same wastage logic as the tea sheet.
• A bakery supplying standing orders to shops: volume is steady, so annual units work well, but an account that varies a lot should be judged on a rolling average. See wholesale and standing orders for a bakery for how the orders come in.
• A dog groomer selling shampoos and treats to other groomers: two tiers are enough (groomer and salon group). The test against last year's orders may show that the salon group's volume doesn't justify a discount at all.
• A driving school selling lesson blocks to employers and colleges: the "unit" is a lesson. Tiers by lessons per year, a floor set from instructor pay plus car running costs, and a check that a college's rate doesn't sit below the floor that a private learner's block would cover.
• A producer selling through a marketplace as well: the marketplace sets its own wholesale rules and fees, so test each channel separately. The marketplace comparison shows what each platform does with brand prices.

A flat rise that a key account talked straight back down

An easy way to get this wrong is to publish tiers designed on average numbers. Imagine the business above set its key tier at $4.25 and sent it to all six accounts on the same day. Account A, whose buyer had been told "you are our best price" years earlier, replied that it had a quote from another producer at $4.05. The owner, who had no back-test showing the contribution A was bringing in at $3.95, conceded on the call and gave $3.95 again, plus free delivery for a year. The back-test would have shown that a smaller rise, phased in, kept most of the money without the risk. The error wasn't the tier table. It was announcing a flat change to accounts whose positions were already different, with nothing to say what each one was worth.

An exception rule you can apply without a debate

Exceptions are where floors die. Decide in advance who may approve a price below the floor, for how long, and what must be recorded. A filled-in version for the bakery-mix business, in the form of a one-line log:

The rules around the log: only the owner can approve a price below the floor; every exception has a review date no more than six months ahead; and at each review the account either returns to its tier price or earns a reason to stay. Ask the AI to read the log every quarter and list exceptions that have passed their review date, which is the sort of tidy checking it does well. It will not chase them for you, so put the dates in a calendar.

Finally, apply the same eye to your own wording. A volume tier that quietly offers larger accounts better payment terms and free delivery has a cost, even if the price per bag is the same. Convert the terms to cents a bag: 30-day terms instead of 14 days on $4.25 at a financing cost of, say, 8% a year cost about 0.09 cents a day, or around 1.5 cents for the extra 16 days. Small, but it is nearly a third of the five-cent headroom above the floor, which is why the floor should be calculated on all the costs of serving an account, not just the cost of the goods.

Checks to run before you publish the list

• Floor check. No tier price below the floor price, with at least a few cents of headroom. Re-run if costs change.
• Monotonic check. A bigger account never pays more than a smaller one. Sort the tier table and confirm the price falls as volume rises.
• Back-test totals. Revenue at the new prices equals the sum over accounts of units times the new price; the table above should reconcile to the cent.
• Largest movers. Look at the ten accounts with the biggest annual change, in dollars and in percent, and decide each one by name.
• Cost sensitivity. Raise unit cost by 5% and see which accounts fall below the floor. Cost increases often arrive as supplier price rises you need to pass on, so plan the response before they arrive.
• Explainability. You can say in two sentences why an account is in its tier, with no exceptions in the list.

If you are choosing between a chat assistant and a dedicated pricing app for this work, the comparison of AI pricing tools for wholesale goods sets out when each makes sense. For a business of this size the spreadsheet plus a chat assistant is usually enough: the spreadsheet holds the rules, the assistant reviews the table, and nothing is published until you have read the back-test yourself.

Keeping tiers healthy after launch

Once the tiers are live, add one tab that records the date of each tier change, the reason and the owner who approved it. Review three numbers each quarter: how many accounts moved tier, the average margin per tier, and the number of exceptions granted outside the table. If exceptions grow, either the tiers are wrong or the rules are being bypassed, and both are worth knowing. AI suggestions about price have limits, and the checks in this tutorial are the practical answer to them: do the arithmetic yourself, ask the AI to look for what you missed, and let the back-test, not the discount ladder, decide.

Questions about tiered wholesale pricing

How many wholesale tiers should a small business have?

Three is usually enough: a standard trade price, a volume price and a key-account price. More tiers create arguments about which one an account belongs in, and each tier needs a clear rule, such as units bought in the last twelve months. Add a tier only when a real group of accounts doesn't fit.

Should tiers be based on order size or annual spend?

Annual volume suits most businesses, because it rewards loyalty and is easy to explain. Order-size breaks suit products with real picking and delivery costs. You can combine them: tier by annual units, and add a small break for full cases or pallets. Write down which one decides the price.

What is a margin floor and how do I set it?

A margin floor is the lowest margin on price at which you will sell a line without a person approving it. Set it from your costs: unit cost plus delivery and payment costs, divided by one minus the floor percentage. Anything priced under that figure needs a named person to say yes.

Can AI set my wholesale prices?

It can propose structures, write the formulas and review a table for inconsistencies, but it doesn't know your costs, competitors or relationships. Use it to test options quickly, and keep the decisions on tiers, floors and exceptions with you. Always back-test the result against real orders before publishing.

How do I stop accounts gaming a tier threshold?

Measure on a trailing twelve months, review quarterly, and don't let accounts move down mid-year. Watch for customers just under a threshold who might split or combine orders. If many accounts bunch below a line, the gap between tiers is probably too steep, so smooth it.

Further reads

• Can AI Read Emailed Orders Into a Wholesaler's System? — Get emailed orders into your system at the right price.
• Can AI Help a Small Shop Set Prices and Promotions? — Set the retail side of the price ladder.
• How to Check Your Margins Product by Product With AI — Check product margins one by one after prices change.
• Is AI Dynamic Pricing Worth It for a Small Hotel? — See how price rules work where demand moves daily.
• AI Bias in Small Business Decisions: Hiring, Pricing and Credit — Check that pricing rules treat similar customers alike.
• AI Supplier Negotiation Emails for Importers: Price, MOQ, Terms — Negotiate costs on your side of the margin.
• Can AI Tell You What to Charge? The Limits of AI Pricing Research — Learn when an AI price suggestion can be trusted.
• Check a Bill of Materials With AI: Template, Gaps and Revisions — A bill of materials layout you can copy, an AI gap check that found three missing groups of items adding 8.7% to material cost, and a simple revision log.
• Clothing Cost Sheet: AI Costs a Style From Its Tech Pack — A sweatshirt costed line by line from its tech pack, from fabric metres to wholesale margin, with the checks that stop AI under-costing a style.
• DTF Printing Business: Price Transfers, Gang Sheets and Reorders With AI — Work out cost per foot of a DTF gang sheet, set length-based prices with margins, and automate the quote and reorder emails.
• 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.
• AI for Fashion Companies: Line Sheets and Buyer Emails From Product Data — Start with the wholesale line sheet built from product data, then buyer emails. A fashion brand's order, prompts, sample outputs and pre-send checks.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft Excel and Google Sheets XLOOKUP documentation (match mode -1); the products, costs, accounts and volumes in the example are illustrative.