# Configurable BOM Option Cost and Price Rollup Worksheet

> Source: https://netray.co/tools/configurable-bom-option-cost-rollup-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Roll up cost, labor hours and price for a configured product. Each BOM line has an option group, a selected flag, scrap, labor and target margin, and totals update as you toggle options.

## Category

engineering-plm

## Industry

Engineer-to-Order Machinery

## Audience

- Estimator
- Sales engineer
- Product manager

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Item or option

**Key:** item

**Type:** text

#### Option group

**Key:** group

**Type:** select

**Options:**

- Base machine
- Drive and motion
- Controls
- Safety and guarding
- Finish and paint
- Install kit
- Spares package

#### Selected (1 yes, 0 no)

**Key:** sel

**Type:** number

**Help:** Set to 0 to remove the line from the configuration.

#### Qty

**Key:** qty

**Type:** number

#### Unit material cost

**Key:** unit

**Type:** number

**Format:** currency

#### Scrap and yield loss

**Key:** scrap

**Type:** number

**Unit:** %

#### Labor hours per unit

**Key:** hrs

**Type:** number

**Unit:** hrs

#### Labor rate

**Key:** rate

**Type:** number

**Format:** currency

**Unit:** $/hr

#### Target margin

**Key:** margin

**Type:** number

**Unit:** %

#### Line cost

**Key:** cost

**Type:** number

**Expr:** sel * (qty * unit * (1 + scrap / 100) + qty * hrs * rate)

**Format:** currency

#### Line labor hours

**Key:** lhrs

**Type:** number

**Expr:** sel * qty * hrs

**Format:** hours

#### Line price

**Key:** price

**Type:** number

**Expr:** cost / (1 - margin / 100)

**Format:** currency

**Help:** Cost divided by one minus target margin, a margin on price, not a markup on cost.

**Rows:**

**Item:** Welded base frame

**Group:** Base machine

**Sel:** 1

**Qty:** 1

**Unit:** 6400

**Scrap:** 4

**Hrs:** 38

**Rate:** 62

**Margin:** 32

**Item:** Servo drive, 5 kW with cabling

**Group:** Drive and motion

**Sel:** 1

**Qty:** 2

**Unit:** 2150

**Scrap:** 0

**Hrs:** 3.5

**Rate:** 62

**Margin:** 30

**Item:** PLC and I/O panel

**Group:** Controls

**Sel:** 1

**Qty:** 1

**Unit:** 7800

**Scrap:** 1

**Hrs:** 46

**Rate:** 74

**Margin:** 34

**Item:** Light curtain and guarding kit

**Group:** Safety and guarding

**Sel:** 1

**Qty:** 1

**Unit:** 3900

**Scrap:** 2

**Hrs:** 12

**Rate:** 62

**Margin:** 30

**Item:** Stainless wash-down finish (option)

**Group:** Finish and paint

**Sel:** 0

**Qty:** 1

**Unit:** 4200

**Scrap:** 5

**Hrs:** 16

**Rate:** 62

**Margin:** 35

**Item:** Installation kit and fasteners

**Group:** Install kit

**Sel:** 1

**Qty:** 1

**Unit:** 950

**Scrap:** 3

**Hrs:** 4

**Rate:** 62

**Margin:** 25

**Totals:**

#### Configuration cost

**Key:** cost

**Agg:** sum

**Format:** currency

#### Configuration price

**Key:** price

**Agg:** sum

**Format:** currency

#### Total labor hours

**Key:** lhrs

**Agg:** sum

**Format:** hours

**Summary:**

#### Gross profit

**Key:** gross

**Expr:** sum_price - sum_cost

**Format:** currency

**Primary:** true

#### Blended margin on price

**Key:** blended

**Expr:** if(gt(sum_price, 0), (sum_price - sum_cost) / sum_price * 100, 0)

**Format:** percent

**Primary:** true

#### Price per labor hour

**Key:** per_hr

**Expr:** if(gt(sum_lhrs, 0), sum_price / sum_lhrs, 0)

**Format:** currency

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the worksheet

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Bullets:**

- Check exclusive options manually before quoting
- Refresh unit costs at least quarterly
- Keep option groups consistent with your ERP configurator

**Cta:** 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.

**Faq:**

#### What is the difference between margin and markup?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

configurable bom cost rollup, product configurator cost worksheet, bom option pricing excel, configure to order cost estimate, options and accessories cost rollup, bom costing template with options, variant bom cost calculation, quote configuration price worksheet

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json