Configurable BOM Option Cost and Price Rollup Worksheet
Configure-to-order and ETO builders often quote from a base machine plus a set of options. This worksheet lets estimators and sales engineers toggle each option, apply scrap and labor, and see how cost, price, hours and blended margin move with the configuration.
| Item or option | Option group | Selected (1 yes, 0 no) | Qty | Unit material cost | Scrap and yield loss (%) | Labor hours per unit (hrs) | Labor rate ($/hr) | Target margin (%) | Line cost | Line labor hours | Line price | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
$9,012 | 38 hrs | $13,253 | ||||||||||
$4,734 | 7 hrs | $6,763 | ||||||||||
$11,282 | 46 hrs | $17,094 | ||||||||||
$4,722 | 12 hrs | $6,746 | ||||||||||
$0 | 0 hrs | $0 | ||||||||||
$1,227 | 4 hrs | $1,635 |
Line cost = selected flag x (quantity x unit cost x (1 + scrap) + quantity x labor hours x rate). Price = cost / (1 - target margin), so margin is stated on price. Use one row per BOM line or option, set Selected to 0 for options the customer has not chosen, and remember that mutually exclusive options need a manual check. Replace example costs with current standard cost from your ERP.
Quote from live BOM and routing costs
Optional: see how a private assistant could roll up configured cost from your ERP data.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
List each BOM line or option as a row with its option group, quantity, unit material cost, scrap, labor hours per unit and labor rate. Set the Selected flag to 1 for lines in the quoted configuration and 0 for options not chosen. The worksheet computes line cost, labor hours and price at your target margin, and the totals give configuration cost, price and blended margin. Change a flag and the totals update immediately, so you can price several configurations in turn.
- Use current standard or latest purchase cost for materials
- Keep labor hours per unit from routings or time studies
- Export to Excel to attach the configuration to the quote
Formulas used
Material cost per line is quantity times unit cost times one plus the scrap or yield loss percentage. Labor cost is quantity times hours per unit times the labor rate. Line cost is their sum multiplied by the selected flag. Price is cost divided by one minus target margin, which gives the stated margin on selling price rather than a markup on cost; a 30 percent margin needs a markup of about 43 percent. Blended margin is total gross profit divided by total price.
Reading the totals
Compare the blended margin with the sum of the individual targets: if a high-margin option is deselected, the blended margin drops. Price per labor hour is a quick check on how well the configuration monetises the shop. If it falls below your target, the options with heavy labor and low margin need repricing. Build one tab per typical configuration to compare, such as standard, extended and fully equipped.
Common mistakes
Common errors are applying markup percentages as if they were margins, forgetting scrap on purchased and fabricated parts, ignoring labor on supposedly simple options, and not updating costs after supplier price changes. Mutually exclusive options also cause errors: selecting both a standard and a premium drive double counts. Finally, avoid rolling up engineering for custom options here; add a separate line for it.
- Check exclusive options manually before quoting
- Refresh unit costs at least quarterly
- Keep option groups consistent with your ERP configurator
Frequently Asked Questions
What is the difference between margin and markup?
Margin is profit as a share of selling price, markup is profit as a share of cost. A 30 percent margin equals about a 43 percent markup. This worksheet uses margin on price, because it is the figure finance and sales leaders usually track.
How do I handle options that exclude each other?
Group them and set only one to 1. The worksheet does not enforce exclusivity, so check each configuration before quoting. Your ERP configurator can enforce these rules when you are ready to automate.
Where should unit costs come from?
Use the standard cost or latest purchase price from your ERP item master, and update them regularly. For quoted purchased items use the supplier quote, noting its validity date.
Does this include engineering for custom options?
No. It prices the BOM and labor content of standard options. Add engineering as a separate row, or use the engineering hours estimator to build that line for custom work.
Netray can connect a configurator to live ERP costs, BOMs and routings so quotes roll up cost automatically with human approval. Ask for an assessment of your quote process.
Related Tools
ETO Engineering Hours Estimator by Drawing Count and Complexity
Estimate engineering hours, cost and elapsed weeks for an ETO job from drawing count, complexity, design reuse, analysis, revision allowance and project coordination load.
Industry TemplatesETO Bid or No-Bid and Quote Readiness Checklist
A pre-submission checklist for ETO quotes covering requirements, engineering basis, capacity, supply chain, risk and contract terms, with critical items flagged.
Costing & FinanceETO Quote Risk Register and Contingency Reserve Worksheet
List quote risks by phase with probability and low and high cost impact. The worksheet computes expected monetary value per risk and the total contingency reserve.
Industry TemplatesEngineer to Order Machine Quote Worksheet
Line-by-line cost build-up for a one-off machine quote with a risk allowance per line and the resulting price at 20 to 35 percent gross margin.
Developer ToolsBOM Cost Rollup Recursive CTE SQL Generator
Name your BOM and cost tables and get a recursive SQL query that explodes a bill of material and rolls up purchased component cost.
More Engineering & PLM tools
See all- SAP Super BOM Design Worksheet
- SAP Engineering Change Number Checklist
- SAP Document Info Record Template
- SAP Change Master and Revision Level Log
- SAP Engineering Change Request (ECR) Template
- SAP BOM Usage and Item Category Reference
- SAP Engineering Change Obsolescence Exposure Calculator
- SAP Engineering Change Affected Materials Impact Worksheet
- Cloud MRP BOM Data Cleanup Worksheet
Keep exploring
Free tools
- Product Cost Rollup CalculatorRoll material, direct labor, applied overhead, machine time, and scrap loss into a single defensible unit cost the way your ERP cost rollup should.
- PCBA BOM Cost Rollup WorksheetRoll up the material cost of a printed circuit board assembly line by line, with overage for fall-off and attrition.
- SAP Business One BOM Cost Rollup WorksheetAn editable component cost worksheet with scrap allowance that rolls up material cost per finished unit, for checking a Business One BOM before loading it.
ERP fixes and how-tos
- Product Configurator in Sage X3: How to Set Up Configurable ItemsHow to approach configurable products in Sage X3: features and options, rules, generated BOMs and routings, and when a configurator or a simple option BOM is enough.
- How to Run a Cost Rollup in SyteLineRun a SyteLine standard cost rollup: use Simulate mode first, fix variance exceptions from missing routings or zero-cost items, then post new standard costs.
- How to calculate item cost prices in Infor LNRun item cost price calculation in Infor LN: cost components, simulation vs update mode, BOM/routing roll-up, and how the result reaches inventory valuation.
Runs with your ERP
- Infor CPQ with Infor SyteLine: Configurator in Order EntryHow Infor CPQ opens from SyteLine order entry for configurable items, returns validated options and creates the order line. Setup, pitfalls, alternatives, FAQ.
- Epicor CPQ (KBMax) and NetSuite: sending configured orders and BOMs to the ERPHow Epicor CPQ, formerly KBMax, sends configured orders and BOMs to NetSuite or other Oracle ERP via its connector, database, web service and REST options.
Guides
- How to Fix SyteLine Cost Rollup VariancesDiagnose and resolve cost rollup variances in Infor SyteLine including material, labor, and overhead cost discrepancies with SQL validation queries and BOM audit steps.
- How to Run Cost Rollup in SyteLineComplete guide to running cost rollups in Infor SyteLine. Learn how to calculate rolled-up costs across BOM levels, validate results, and update standard costs.
- Configuring SyteLine for Engineer-to-Order ManufacturingConfigure SyteLine for engineer-to-order manufacturing with estimate-to-job conversion, project-linked BOMs, and configurable routing workflows for custom products.
AI on your ERP
- AI on Infor SyteLine for Industrial Machinery Makers: Configured Orders, Projects and Service PartsAI on Infor SyteLine for industrial machinery manufacturers: configure-to-order BOMs, project cost, spare parts and warranty, private and read-only by default.
- Infor LN AI for Engineer-to-Order Industrial Machinery BuildersPrivate AI on Infor LN for engineer-to-order machinery builders: project cost, engineering BOM changes, long-lead purchasing and CE file support, on-prem.