A quote template that handles part numbers, quantities and price breaks

By Ranjit Bhinge, founder of Blur Studio · Updated · 7 min read

Short answer: A B2B quote template needs a quote header, customer details, line items with part numbers and units of measure, a price break table, totals, terms and a valid-until date. You can build it in Excel or Google Sheets with one lookup formula that picks the right price for each quantity. A sheet like this works well for a few quotes a day. It starts to fail when requests arrive as emails and PDFs, when supplier costs change often, or when several people quote at once. At that point you want a quote builder that reads live prices from one source, such as the one described in my quote generator guide.

Ranjit Bhinge

You quote parts in different quantities, the unit price drops as the quantity goes up, and you want a template that gets the right price on every line.

I'm Ranjit, and I run Blur Studio: 15+ years building software, 120 apps taken live, and clients who have gone on to raise $10M+. I build AI systems and processes that help businesses do a lot more with a lot less time. I also work differently from most of what else is out there, more on that further down this page. Say hello.

What is a B2B quote template?

It is a fixed layout that holds everything a quote needs, so your team never starts from a blank page. The simplest version is a spreadsheet. The fuller version lives inside quoting software.

A distributor or manufacturer quote is not a simple price list. Each line has a part number, a unit of measure (each, box, foot, pound), a quantity and a price that usually falls as the quantity rises. Customers also expect a fast answer. For more detail, see a price list template with customer-specific prices.

You will also see the word CPQ. It stands for Configure, Price, Quote. Cincom, a vendor of quoting software for manufacturers, describes it as software that sits between your CRM (the tool that holds customer records) and your ERP (the system that runs orders, stock and accounting), drawing customer data from one and product data from the other. A spreadsheet template is the small, do-it-yourself version of the same idea.

What fields does the template need?

SectionFields
HeaderQuote number, quote date, valid-until date, revision number, prepared by
CustomerCompany, account number, buyer name, email, phone, bill-to and ship-to addresses
RequestThe customer's own reference, request date, the customer's part numbers if they differ from yours
Line itemsLine number, your part number (SKU, meaning stock keeping unit), description, specification (size, grade, finish), unit of measure, quantity, unit price, line total
Price detailPrice break used, list price, discount percent, lead time or stock status
TotalsSubtotal, freight estimate, tax, total
TermsPayment terms, shipping terms, minimum order quantity (MOQ, the smallest order you will take), substitutes allowed
Next stepHow to accept: reply, sign, or send a purchase order (PO)

Two fields save the most pain later. The first is the valid-until date, because supplier costs move and you do not want to honor a price from four months ago. The second is the customer's own part number. Buyers often quote you their number or a competitor's number, and a mismatch is the most common reason a quote stalls while someone hunts for the right item.

How do I build price breaks in Excel or Google Sheets?

A price break is a lower unit price you give when the quantity passes a set point. Build a tab called Prices with one row per part. Put the break quantities across the top and the price under each one. A closer look is in a sales order template and confirmation email.

Part15002,500
FT-200 (made-up brass fitting)0.520.470.41
FT-210 (made-up brass fitting)0.540.490.43

On the Quote tab you need the price for the part at the quantity ordered. The rule is: find the largest break that is not above the quantity. Microsoft's XLOOKUP function does this when you set its match mode to -1, which Microsoft describes as an exact match, or the next smaller item if none is found. Google Sheets has XLOOKUP too, with the same match mode.

First, give three ranges names (in Excel, use the Name Box; in Google Sheets, Data, Named ranges): PartList for the part numbers in column A of the Prices tab, PriceBreaks for the break quantities in row 1, and PriceTable for the grid of prices. Then one working formula, with part number in A5 and quantity in C5, looks like this:

=XLOOKUP(C5, PriceBreaks, INDEX(PriceTable, MATCH(A5, PartList, 0), 0), "Below MOQ", -1)

Read it in three parts. MATCH finds the part's row. INDEX pulls that whole row of prices. XLOOKUP then picks the break at or below the quantity. If the quantity is below your first break, the cell shows "Below MOQ" instead of an error.

Microsoft's page says XLOOKUP is not available in Excel 2016 and Excel 2019. If you use those, swap in INDEX(row, MATCH(quantity, breaks, 1)), which does the same job as long as the break quantities are sorted from small to large.

A worked example with made-up figures

A customer asks for 1,200 of FT-200 and 40 of FT-210. The 1,200 falls above the 500 break and below 2,500, so the formula returns $0.47. The line total is 1,200 times 0.47, which is $564.00. The 40 of FT-210 is above the first break of 1 and below 500, so it prices at the 1 tier, $0.54, for $21.60. If your first break were 100 instead, 40 would show "Below MOQ", and your rep knows to ask the buyer to order more.

Test the formula before you trust it. Try a quantity on each side of every break, a quantity exactly on a break, and a quantity below the first one.

How do I build the template step by step?

  1. Create a Parts tab: part number, description, unit of measure, list price, lead time. This is your single source of truth. Do not type prices onto the quote.
  2. Create the Prices tab above, one row per part, break quantities across the top.
  3. Create a Customer prices tab with customer, part and price. Your formula checks this tab first and falls back to the break price if the customer has no special deal. My guide to customer specific pricing goes into this part.
  4. Create the Quote tab with the header and customer blocks from the table above.
  5. Make the part number cell a dropdown that reads from the Parts tab, so nobody mistypes it.
  6. Pull the description and unit of measure from the Parts tab, and use the formula above for the unit price.
  7. Add line total, subtotal, freight and total. Add a valid-until date as quote date plus 30 days, or whatever your policy is.
  8. Create a Quote log tab: quote number, date, customer, total, status (open, won, lost) and the reason if lost.
  9. Save a copy as PDF to send. Keep the master file private.

The quote log is the step most owners skip, and it is the one that tells you the truth. Count how many quotes you send each week, how long each takes, and how many become orders. Without that count, slow quoting never shows up as a cost.

What can quoting software do that a sheet cannot?

Cincom's material for manufacturers describes quoting software that adds rules that stop a customer ordering a combination you cannot build, prices taken from approved lists with margin limits, approval steps for big discounts, and links to your ERP and CRM. Those are real gains for a company that builds to order with many options.

For job shops that make custom parts, there are tools built for that world. Paperless Parts, quoting and estimating software for contract manufacturers, reads part files such as sheet metal and machined parts and pulls out the details that drive cost, and it connects to ERPs such as Epicor and Infor Visual (ERP systems for manufacturers). It prices by request, so you need a call to learn the cost.

For a distributor selling catalog items, the problem is different. You do not need part-file analysis. You need the right customer price, the right break and live stock. That is mostly a data problem, and I cover it in my guide to the tool stack for a B2B distributor.

When does a quote template stop working?

  • Requests arrive as emails and PDFs. Someone has to read each one and retype the parts. The retyping is the slow part, not the pricing. See my guide to using AI to fix quote software versus Excel.
  • Prices change often. If supplier costs move monthly, copies of the sheet drift apart. Two reps quote the same part at different prices.
  • Several people quote. Shared sheets get overwritten, and formulas get deleted by accident.
  • Stock and lead time live somewhere else. If you must open the ERP to check each line, the sheet is only a calculator.
  • Customer deals multiply. Once you have hundreds of customer-specific prices, the lookup tabs get hard to audit.

A simple test: time ten quotes from request to sent. Write down the minutes, and write down how many of those minutes were typing and checking, not pricing. If most of the time is typing and checking, a tool that reads the request and fills the template will pay back faster than a fancier template.

How I'd approach this

Start with the sheet. It costs nothing, and building it forces you to write down your price breaks, your terms and your valid-until rules. Those are the things any quoting tool will ask you for anyway.

Keep one rule: prices live in one tab, never typed onto a quote. When you outgrow the sheet, move that same structure into a quote builder that reads prices from your ERP or price database, so the template you designed carries over. I do not push owners toward a big CPQ platform with per-seat fees until the simple version has clearly failed. If your quotes are mostly catalog parts with customer prices, a small custom quote tool on your own data is usually the better fit, and I can build one around the template you already use.

Questions people ask

What should a manufacturing or distributor quote include?

A quote number, a valid-until date, customer details, line items with part numbers, units of measure, quantities and prices, totals, shipping and payment terms. Add lead times and allowed substitutes. The fields table above lists all of them.

How do I do price breaks in a quote template?

Put one row per part on a Prices tab with break quantities across the top and a price under each. Then use a lookup that returns the break at or below the quantity ordered, such as XLOOKUP with match mode -1. Test quantities on each side of every break.

What does CPQ mean?

CPQ means Configure, Price, Quote. It is software that builds a correct quote from product rules and price lists, and it usually connects to your ERP and CRM. A spreadsheet template is the simple version of the same idea.

Is there a quote template for Google Sheets?

You can build one in Google Sheets with the same fields and tabs. Google Sheets has an XLOOKUP function that supports the same match mode, so the price break formula in this guide works there too. For a contractor-style version with a website form, see my instant quote template guide.

When should I move from a spreadsheet to quoting software?

Move when requests arrive by email and PDF, when prices change often, or when several people quote. Time ten quotes first, so you can compare what the current process costs against the price of a tool.

Does the quote template need a minimum order quantity?

Yes, if you have one. Put the MOQ in the terms and let the price formula show a warning for any line below it, so the rep catches it before the quote goes out.

More in this topic: B2B: quotes, CPQ and pricing (8 guides) →

Sources

Ready for the next step?

A 30 minute call. If you do not need me, I will tell you.

Ranjit Bhinge You talk to me, Ranjit. I reply within a few hours.

Not sure what you need? Tell me what is going on. I will point you the right way, even if it is not me.

Blur Studio · 30 minute call

Book a 30 minute call

You talk to me, Ranjit. If you do not need me, I will tell you.

  • We talk about your business and what is going on.
  • You leave with a clear next step, even if it is not me.
  • I reply to every message within a few hours.
Loading the calendar…