FREE EXCEL TEMPLATE FOR WHOLESALERS AND DISTRIBUTORS
A wholesale price list template with customer tiers
A wholesale price list is more than a column of prices. The same item is sold to a walk-in customer, an account customer and a volume contractor at different prices, and again at different prices as the quantity grows. Written out by hand, that is a grid per item, and it is where margin quietly leaks.
This free Excel template keeps one list price and one landed cost for each item and calculates the rest: a price for each customer tier, markup and margin at list, the lowest price your rules can produce, and a flag when that price leaves less margin than your floor. A second sheet checks any item for any tier and quantity.
Last updated
Download the wholesale price list template
There is nothing to sign up for. The file is a plain Excel workbook with working formulas: no macros and no external links. It holds sample data, clearly marked, that you replace with your own products.
An Excel workbook with room for 25 items (twelve sample lines to replace), customer tiers, quantity breaks, markup and margin, a margin floor check and a price-check sheet.
- Price list: one row per item with SKU, description, unit of measure, pack quantity, landed cost and list price. Tier prices, markup, margin and the floor check are calculated.
- Price check: pick an item, a customer tier and a quantity, and see the net unit price, the line total, the margin and a grid of every tier and quantity break for that item.
- Settings: the price list name and effective date, your minimum margin, the customer tiers and the quantity breaks. Change a value here and every price follows.
- Instructions: a one-page guide to the sheets and the fields.
We built and tested it in Microsoft Excel. It uses only standard spreadsheet functions (IF, ROUND, VLOOKUP, INDEX, MATCH, LOOKUP), so it should also open in LibreOffice and Google Sheets; after importing, check a few prices by hand.
Every column, explained
| Column | What it holds | How it is calculated |
|---|---|---|
| SKU / part no., description | Your item code and name | You type them |
| UoM and pack qty | Unit of measure and units per pack | You type them |
| Landed cost | What the item costs you delivered | You type it: supplier price plus freight in, duty and handling, less rebates that belong to it |
| List price | Your base selling price | You type it |
| Tier A, B and C price | The price for each customer tier | List price × (1 − tier discount), rounded to the cent |
| Markup at list | Profit as a percentage of cost | (list price − cost) ÷ cost |
| Margin at list | Profit as a percentage of the price | (list price − cost) ÷ list price |
| Lowest net price | The lowest price the rules can produce | Lowest tier price × (1 − largest quantity break), rounded |
| Margin at lowest | The margin left at that price | (lowest price − cost) ÷ lowest price |
| Floor check | “OK” or “Below floor” | Compares the margin at the lowest price with your minimum margin |
If you are not sure about the difference between markup and margin, read the markup versus margin guide before you set your tier discounts: the two numbers differ for the same price.
Worked example: one item, three tiers, four quantity breaks
The first sample item is a 29-piece drill bit set with a landed cost of $12.00 and a list price of $20.00. The sample Settings define three tiers (0%, 8% and 15% off list) and four quantity breaks (0%, 3%, 6% and 10%, each applied to every unit once the quantity is reached). The Price check sheet builds this grid.
| Customer tier | 1 to 9 | 10 to 49 | 50 to 199 | 200 or more |
|---|---|---|---|---|
| Tier A (0% off list) | $20.00 | $19.40 | $18.80 | $18.00 |
| Tier B (8% off list) | $18.40 | $17.85 | $17.30 | $16.56 |
| Tier C (15% off list) | $17.00 | $16.49 | $15.98 | $15.30 |
Sample figures. Price = ROUND(ROUND(20.00 × (1 − tier), 2) × (1 − break), 2). Example: tier B at 50 or more is $18.40 × 0.94 = $17.296, rounded to $17.30.
The same grid shows what each price leaves in margin. The margin falls as discounts stack, which is the point of looking at it:
| Customer tier | 1 to 9 | 10 to 49 | 50 to 199 | 200 or more |
|---|---|---|---|---|
| Tier A | 40.0% | 38.1% | 36.2% | 33.3% |
| Tier B | 34.8% | 32.8% | 30.6% | 27.5% |
| Tier C | 29.4% | 27.2% | 24.9% | 21.6% |
Margin = (price − $12.00) ÷ price. Example: tier C at 200 or more is ($15.30 − $12.00) ÷ $15.30 = 21.6%.
An order of 60 sets for a tier B customer falls in the 50 to 199 column: $17.30 a set, a line total of 60 × $17.30 = $1,038.00 and a gross profit of $1,038.00 − 60 × $12.00 = $318.00, a 30.6% margin. The Price check sheet shows exactly those figures for the sample item.
Reading the floor check
Most sample items clear a 12% floor. One does not. A 250 ft roll of NM-B 12/2 cable costs $118.00 and lists at $165.00, a 28.5% margin at list. A tier C customer buying 200 or more pays $165.00 × 0.85 = $140.25, less 10%, which is $126.23. The margin at that price is ($126.23 − $118.00) ÷ $126.23 = 6.5%, so the Floor check says “Below floor”.
That is a warning to look at one line, not a verdict. You might raise the list price, lower the quantity-break discount for this product class, or accept the thin margin on purpose to win volume. What you cannot do any longer is give the discount without knowing what it costs. The two discounts together take 23.5% off the list price (1 − 0.85 × 0.90), which is more than either appears to be alone.
How to use the template
- On the Settings sheet, name your price list, set the effective date and the minimum margin, and edit the customer tiers and quantity breaks.
- On the Price list sheet, clear the twelve sample lines (columns A to F) and enter your items. Leave the formulas in columns G to N.
- Read the grey columns. Check any line marked “Below floor” and decide what to change.
- Use the Price check sheet to test items for different tiers and quantities before you publish.
- Print the Price list (it is set to one page wide on US Letter, landscape) or save it as PDF, and give your team one current version.
When your landed costs change, update the cost column. The list price does not move by itself: you decide whether to pass the change on, and the margin columns show what it does if you do not.
Where a price list in Excel reaches its limit
A spreadsheet price list works while one person owns it. Trouble starts when copies spread: a counter with last month’s file, a salesperson with a private column of special prices, a customer-specific price that lives in someone’s head. The rules are sound, but they are not applied the same way every time.
Qotora AI applies the price rules you define, including customer tiers, margins and discounts, to the lines it finds in your own catalogue when it prepares a quote. Every price traces back to your own data, and a person on your team reviews and approves every quote before anything leaves.
Frequently asked questions
Is the price list template really free?
Yes. You can download the Excel file without signing up and use it for your own price lists. It has no macros, no external links and no hidden costs.
What is the difference between a price list and a price book?
A price list is one set of prices, such as the list for a customer tier. A price book is the organized whole: every price level, quantity break, contract price and effective date, with the rules that decide which one applies. This template is a small price book with one list and its rules.
How many customer tiers can I have?
The template has three, A, B and C, which you can rename and re-price on the Settings sheet. For more tiers, add a column to the Price list sheet and a row to the tier table, and copy the tier price formula across.
Are quantity-break discounts applied to every unit?
Yes. Once the order reaches a break, the discount applies to every unit, which is how most wholesale quantity breaks work. Some businesses use graduated breaks, where only the units above each threshold get the lower price; this template does not do that.
What is landed cost, and why does the template use it?
Landed cost is what an item really costs you once it is in your warehouse: the supplier’s price plus freight in, duty and handling, less any rebate that belongs to that item. Margin calculated on the bare supplier price overstates your profit.
Will the prices update when my costs change?
Margins and markups update when you change a cost, because they are calculated from it. List prices do not change by themselves: you decide whether and how much to pass a cost increase on.
Does it work in Google Sheets or LibreOffice?
We built and tested it in Microsoft Excel. It uses standard functions that LibreOffice and Google Sheets also provide, so it should open there, but check a few prices by hand after importing.