Copy this robotics bill of materials (BOM) template into Excel or Google Sheets to record parent-child relationships, selected revisions and quantities. Use the filled multilevel example to calculate assembly totals and reconcile a five-robot pilot by harness revision before approving a purchasing view.
The template below follows R-01, a fictional indoor inspection robot moving into a five-unit pilot. It contains two identical drive modules, a purchased controller PCBA, a harness, a distance sensor and a chassis plate. The sensor has two mounting screws. Each drive module contains four more screws, so one robot needs ten.
Use the multilevel table as the design record. Derive a flat quantity view and an approved purchasing view from it. Give each view a named purpose and release reference so a buyer can distinguish design quantities from an order instruction.
For a CAD source, follow the Onshape BOM-to-CSV procedure or the SOLIDWORKS BOM-to-Excel procedure before mapping the export into this parent-child format. Keep the original source and selected BOM view recorded.
Copy the blank BOM template into Excel or Google Sheets
Paste this tab-separated header into cell A1. Excel and Google Sheets normally place tabs in separate columns. If everything lands in one column, use the application's text-splitting or import option with tab as the delimiter.
parent_id item_id item_revision description quantity_per_parent unit supply_policy manufacturer_mpn distributor_sku supplier_reference design_reference applicability
Keep part IDs, revisions, manufacturer numbers and serial numbers as text. This preserves leading zeros such as serial 001. Make quantity a numeric field and unit a separate field. A quantity of 2 mL cannot be summed with two pieces.
One row describes one parent-child relationship. The combination of BOM release, parent and child identifies that relationship in this simple example. If the same child occupies several distinct positions under one parent, add an occurrence or position ID. The part-numbering guide explains why an item ID and an occurrence ID serve different purposes.
Copy the filled multilevel BOM example
The following nine relationships belong to BOM-R01 A and configuration CFG-R01-A for serials 001–002. Quantities are per parent. Supplier S-01, manufacturer number SEN-X12 and distributor SKU DIST-1204 are synthetic. Blank design references indicate source documents that a real team would attach and check before release.
parent_id item_id item_revision description quantity_per_parent unit supply_policy manufacturer_mpn distributor_sku supplier_reference design_reference applicability
R-01 DM-100 A Drive module 2 ea Make in house DW-R01 A 001-002
R-01 CB-200 A Controller PCBA 1 ea Buy complete assembly ECAD CB-200 A 001-002
R-01 HR-300 A Harness 1 ea Buy to controlled drawing DW-HR-300 A 001-002
R-01 SN-400 A Distance sensor 1 ea Buy approved source SEN-X12 DIST-1204 S-01 001-002
R-01 CH-500 A Chassis plate 1 ea Buy to controlled drawing DW-R01 A 001-002
R-01 SC-130 A M4 screw 2 ea Buy standard part 001-002
DM-100 MT-110 A Geared motor 1 ea Buy approved part 001-002
DM-100 BR-120 A Motor bracket 1 ea Buy to controlled drawing 001-002
DM-100 SC-130 A M4 screw 4 ea Buy standard part 001-002
SC-130 appears under two parents. Keep both rows. Deleting one as a duplicate would remove a real use of the same item.
Multiply quantities along each assembly path, then sum paths for the same item and revision. For SC-130, the robot quantity is 2 + (2 * 4) = 10. MT-110 is 2 * 1 = 2. DM-100 remains visible as an in-house assembly, while the purchasing view orders its children. CB-200 is bought complete, so its electronic children are outside this purchase boundary.
Reconcile the five-unit pilot by revision
ECO-017 approves harness B, drawing DW-HR-300 B, from serial 003. It improves strain relief during manufacture while retaining the controlled installation interfaces and acceptance requirements. Serials 001 and 002 retain harness A. There is no automatic retrofit instruction.
Create BOM-R01 B and CFG-R01-B for serials 003–005. Copy the relationships into that release and update every row's applicability to 003–005. Change HR-300's selected revision and drawing to B. Keep the approved A release available.
| Item and revision | Per robot | Pilot quantity | Purchasing treatment |
|---|---|---|---|
| DM-100 A | 2 | 10 | Build in house from children |
| MT-110 A | 2 | 10 | Buy |
| BR-120 A | 2 | 10 | Buy to drawing |
| SC-130 A | 10 | 50 | Buy, combining both uses |
| CB-200 A | 1 | 5 | Buy complete PCBA |
| HR-300 A | 1 in A builds | 2 | Keep separate revision group |
| HR-300 B | 1 in B builds | 3 | Keep separate revision group |
| SN-400 A | 1 | 5 | Use S-01 source relationship |
| CH-500 A | 1 | 5 | Buy to drawing |
These are gross build requirements. Reconcile approved stock, open orders, packs and minimum order quantities separately before issuing a purchase order.
Check the workbook before approving a view
Reject blank item IDs, duplicate relationship keys and quantities that lack units. Check each parent exists, detect assembly loops and confirm the selected revisions have their required source documents. Protect calculated cells and use validation lists for supply policy and release status.
Record the approver, approval date, BOM release and applicable serials beside the exported purchasing view. Spreadsheet version history helps recover edits. Engineering approval needs its own recorded decision.
Start by reproducing the ten screws per robot and the two-A, three-B harness split. Then have another engineer trace those totals back to the relationship rows. Use the revision and configuration matrix to record the full release and the EBOM-to-MBOM example when adding manufacturing stages and consumables.