Template · Sales engineering
Solution quote configurator with discounts and margin
Price a configured solution from a parts list, with line discounts, approval flags, sales tax, margin and lead time.
Downloads are included in the $19.85 yearly membership. Sign in
XLSXCSVODSXMLNumbers| Solution quote configurator | |||||
| Example data — replace the blue input cells with your own. | |||||
| GRAND TOTAL | GROSS MARGIN | LONGEST LEAD TIME (WEEKS) | LINES NEEDING APPROVAL | ||
| $132,661 | 45.2% | 10 | 1 | ||
| Quote header | |||||
| Customer | Northwind Example Ltd. | ||||
| Quote number | Q-2026-0147 | ||||
| Quote date | Oct 9, 2026 | ||||
| Validity (days) | 30 | Expiry date = quote date plus validity | |||
| Expiry date | Nov 8, 2026 | ||||
| Sales engineer | Alex Example | ||||
| Discount approval threshold | 15% | Line discounts above this need approval | |||
| Freight | $1,850.00 | Passed through; not part of gross margin | |||
| Sales tax rate | 8.0% | Applied to net hardware only; use your jurisdiction's rate |
Showing the first 16 of 88 rows and 12 of 14 columns. Cells with formulas show the formula on hover.
| Price list for quoting | ||||||
| Parts available to quote. Keep part numbers unique; the Quote tab looks each part up by its number. | ||||||
| Part no. | Description | Category | List price | Unit cost | Lead time (weeks) | Check |
| EX-GW-100 | Edge gateway, 8-port, DIN rail | Hardware | $2,450.00 | $1,640.00 | 6 | OK |
| EX-GW-200 | Edge gateway, 16-port, rack mount | Hardware | $4,800.00 | $3,260.00 | 8 | OK |
| EX-PLC-300 | Programmable controller, 32 I/O | Hardware | $3,900.00 | $2,610.00 | 10 | OK |
| EX-PLC-310 | Programmable controller, 64 I/O | Hardware | $5,600.00 | $3,780.00 | 10 | OK |
| EX-RTU-400 | Remote terminal unit, cellular | Hardware | $1,950.00 | $1,280.00 | 12 | OK |
| EX-SN-010 | Pressure transmitter, 0 to 100 psi | Hardware | $420.00 | $265.00 | 4 | OK |
| EX-SN-020 | Electromagnetic flow meter, 6 inch | Hardware | $1,850.00 | $1,190.00 | 6 | OK |
| EX-SN-030 | Turbidity sensor | Hardware | $1,120.00 | $730.00 | 4 | OK |
| EX-SN-040 | Ultrasonic level sensor | Hardware | $680.00 | $430.00 | 3 | OK |
| EX-CAB-500 | Control cabinet, 24U with power | Hardware | $3,200.00 | $2,240.00 | 9 | OK |
| EX-UPS-600 | UPS, 2 kVA, rack mount | Hardware | $1,380.00 | $960.00 | 3 | OK |
| EX-SRV-700 | Server, 2U, dual power supply | Hardware | $6,900.00 | $4,650.00 | 8 | OK |
Showing the first 16 of 104 rows and 7 of 7 columns. Cells with formulas show the formula on hover.
| Solution quote configurator |
| Notes: what this workbook does, how to use it and the method behind it. |
| What it does |
| Prices a technical solution from a parts list. The Price list tab holds the part numbers, and the Quote tab looks each line up, applies a line discount, and totals the quote with freight, sales tax, gross margin and the longest lead time. |
| How to use it |
| 1. On the Price list tab, enter or replace the parts with their descriptions, categories, list prices, unit costs and lead times. Keep part numbers unique. |
| 2. On the Quote tab, enter the customer, quote number, quote date, validity in days, sales engineer, discount approval threshold, freight and sales tax rate. |
| 3. Enter each part number, quantity and line discount in the line table. Description, price and lead time fill in from the Price list. |
| 4. Read the grand total, gross margin and longest lead time in the tiles and the totals block. |
| 5. Resolve any line marked Not in price list or Needs approval before the quote is sent. |
| Formulas and method |
| Net unit price = list price times (1 minus line discount). Extended list = list price times quantity. Extended net = net unit price times quantity. Extended cost = unit cost times quantity. |
| Margin % = (extended net minus extended cost) divided by extended net. Approval: Needs approval when the line discount is above the threshold. |
Showing the first 16 of 32 rows and 1 of 1 columns. Cells with formulas show the formula on hover.
What does this template do?
This quote configurator prices a technical solution from a parts list. The Price list tab holds part numbers with descriptions, categories, list prices, unit costs and lead times, with room for 100 parts. The Quote tab takes the customer, quote number, quote date and validity period, and works out the expiry date. Each line looks up its part number, then applies a quantity and a line discount.
Line formulas give the net unit price, extended list, extended net and extended cost, the margin percentage, and an approval flag that reads Needs approval when a discount is above the threshold. The totals give the list total, discount total, net total, sales tax on net hardware only, freight, grand total, gross margin in dollars and percent, and the longest lead time. Subtotals by category use SUMIF.
All parts, prices and costs are fictional examples. Freight and tax are passed through and left out of the margin, so the margin reflects the line items only. Check the discount policy and the tax rules with your own finance team before you send a quote.
What’s inside
- Part lookups with VLOOKUP for description, category, list price, unit cost and lead time
- Line discounts with a Needs approval flag above the discount threshold
- Sales tax on net hardware only, with freight added at the end
- Gross margin in dollars and percent, and the longest lead time in weeks
- Room for 40 quote lines and 100 parts, with a duplicate part number check
Which tabs does the workbook have?
| Tab | What it holds |
|---|---|
| Quote | Quote header, KPI tiles, the line table for up to 40 lines, totals, margin, lead time and category subtotals. |
| Price list | Part numbers with descriptions, categories, list prices, unit costs, lead times and a check column, for up to 100 parts. |
| Notes | Purpose, steps, formulas used, assumptions and limits. |
What formulas does this template use?
This template holds 574 formulas in 893 cells across 3 tabs, so 64% of its cells calculate. They use 11 distinct functions; the longest formula is 151 characters and 200 of them read from another tab.
| Function | Uses | What it does |
|---|---|---|
IF | 969 | one result when a test is true, another when false |
ISNUMBER | 340 | true for a number |
NOT | 340 | reverses true and false |
IFERROR | 200 | swaps an error for a fallback value |
OR | 200 | true when any test is true |
VLOOKUP | 200 | looks a value up in a column |
COUNTIF | 102 | counts cells meeting one condition |
SUM | 8 | adds numbers |
SUMIF | 5 | adds values meeting one condition |
ABS | 1 | absolute value |
MAX | 1 | largest value |
Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.
How do you use it?
- On the Price list tab, enter or replace the parts, prices, unit costs and lead times.
- On the Quote tab, enter the customer, quote number, quote date, validity and sales engineer.
- Enter each part number, quantity and line discount in the line table.
- Enter freight and the sales tax rate, then read the totals and the margin.
- Resolve any line marked Not in price list or Needs approval before the quote is sent.
What is it good for?
- Quoting an industrial controls package with hardware, software and services
- Checking the margin on a discounted proposal before approval
- Producing a quote with an expiry date and lead times
- Comparing the margin of two configurations of one solution
Questions about this sheet
Why does a line show Not in price list?
Its part number is not in the Price list range, or it was mistyped. Correct the part number or add the part to the Price list tab.
What counts toward gross margin?
Gross margin is the net line total minus the total cost of the lines. Freight and sales tax are excluded, so the margin reflects the line items only.
How is the expiry date worked out?
The expiry date is the quote date plus the validity in days. Changing either input updates the date.
Which lines are taxed?
Sales tax applies to net totals in the Hardware category only. Software, services and support lines are not taxed in this model.