# Cloud MRP BOM Data Cleanup Worksheet

> Source: https://netray.co/tools/cloud-mrp-bom-data-cleanup-worksheet
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Clean each BOM line with unit of measure, scrap allowance and cost before loading data into MRPeasy, Katana, Odoo, ERPNext or Fishbowl.

## Category

engineering-plm

## Industry

Small Manufacturing

## Audience

- Manufacturing engineer
- Planner
- Operations manager

## Tool

**Kind:** worksheet

**Mode:** editable

**Currency:** $

**Columns:**

#### Parent item

**Key:** parent

**Type:** text

#### Component

**Key:** component

**Type:** text

#### UoM

**Key:** uom

**Type:** select

**Options:**

- EA
- KG
- M
- L
- SET

#### Qty per parent

**Key:** qty

**Type:** number

#### Scrap %

**Key:** scrap

**Type:** number

**Unit:** %

#### Unit cost

**Key:** cost

**Type:** number

**Format:** currency

#### Extended cost

**Key:** ext

**Type:** number

**Expr:** qty * (1 + scrap / 100) * cost

**Format:** currency

#### Check status

**Key:** status

**Type:** select

**Options:**

- To verify
- Verified
- Missing cost
- Wrong UoM
- Obsolete

**Rows:**

**Parent:** FG-2200

**Component:** PL-0125-AL

**Uom:** EA

**Qty:** 1

**Scrap:** 6

**Cost:** 8.4

**Status:** Verified

**Parent:** FG-2200

**Component:** HW-M6X20

**Uom:** EA

**Qty:** 4

**Scrap:** 2

**Cost:** 0.11

**Status:** Verified

**Parent:** FG-2200

**Component:** PC-BLACK

**Uom:** KG

**Qty:** 0.15

**Scrap:** 10

**Cost:** 9.8

**Status:** To verify

**Parent:** FG-2200

**Component:** BOX-12X8

**Uom:** EA

**Qty:** 1

**Scrap:** 0

**Cost:** 0.62

**Status:** Missing cost

**Parent:** FG-2300

**Component:** TUBE-25X2

**Uom:** M

**Qty:** 0.8

**Scrap:** 4

**Cost:** 3.15

**Status:** Wrong UoM

**Totals:**

#### Material cost, all lines

**Key:** ext

**Agg:** sum

**Format:** currency

#### BOM lines

**Key:** component

**Agg:** count

**Format:** integer

**Summary:**

#### Average cost per line

**Key:** avg_line

**Expr:** sum_ext / count

**Format:** currency

**Notes:** Costs here are material only. Add labor and overhead after routings are loaded. Resolve every line not marked Verified before import.

## Content

**Intro:** Bad BOMs cause most MRP failures: wrong quantities, mixed units and missing costs produce wrong purchase suggestions on day one. This worksheet lets you review each BOM line, add scrap, and flag problems before the data goes into a new system.

**Sections:**

#### How to use it

**Body:** Paste or type one line per BOM component. Choose the unit of measure that the component is purchased and stocked in, then enter quantity per parent, a scrap percentage, and unit cost. Extended cost is computed as quantity times one plus scrap, times cost. Use the status column to track verification, and filter in Excel after export to build a to-do list for each engineer or buyer.

**Bullets:**

- Start with your top 50 revenue parts
- Fix units of measure before costs
- Use scrap only where you really lose material
- Mark obsolete components, do not delete them yet

#### Why units and scrap matter

**Body:** MRP multiplies quantity per by the order quantity and nets against stock in the stocking unit. If a tube is stored in metres but the BOM says each, the requirement is wrong by an order of magnitude. Scrap percentage increases gross requirement so purchasing covers expected losses. Without it, you short the job; with too much, you overbuy and tie up cash.

#### Reading the results

**Body:** Total material cost gives a quick sanity check against your quoted price. If a top assembly shows material cost above the selling price, either a cost or quantity is wrong. The lines marked Missing cost or Wrong UoM are your cleanup backlog. Set a rule that no BOM loads into the new system with any such status.

#### Common mistakes

**Body:** Copying old BOMs without checking revision levels, keeping phantom subassemblies that no one builds, and treating one BOM as valid for all variants. Also avoid loading hundreds of dead part numbers, since they clutter planning and slow search. Archive them instead.

**Cta:** Netray can run an automated check across an exported ERP or spreadsheet BOM set and list lines with unit or cost anomalies.

**Faq:**

#### How clean must a BOM be before MRP?

**Answer:** Component, quantity per, unit of measure and lead time must be right for every active item. Costs can be refined later, but wrong quantities or units give wrong purchase suggestions immediately.

#### What scrap percentage should I use?

**Answer:** Use your measured loss for the process, such as cutting or coating yield. Start from historical usage data rather than a round guess, and review quarterly.

#### Should I include labor in this sheet?

**Answer:** Not here. Labor and overhead come from routings and work center rates. Keep this sheet for material so you can verify purchased parts and quantities clearly.

#### How many BOMs should I clean first?

**Answer:** Start with active products that make up most revenue or most purchasing spend. A good first target is the top 20 percent of parts by annual usage value.

## Search terms

bom cleanup excel, bom data cleansing template, bom migration worksheet, bom cost rollup excel with scrap, mrp data preparation, bill of materials audit template

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