.png)
Bill of materials (BOMs) act as the central records for upcoming builds or products. In our last article, we covered the basics of BOMs - the different types, different formats, how they are used in the hardware development process, and how they can get complex very quickly.
In this article, we’ll dive deeper into the methodologies for creating and managing BOMs - via spreadsheets, or via databases from PDM (product data management) and PLM (product lifecycle management) systems.
Microsoft Excel has been the gift that keeps on giving since inception. It is an extremely powerful tool with rich functionality, offering users significant flexibility in structuring and analyzing data. Excel files can easily be shared and with OneDrive, teams can even collaborate on Excel BOMs similar to Google Sheets. Using methodologies around importing information from various other spreadsheets, teams can theoretically reduce much of the duplicative data entry in managing BOMs via spreadsheets. However, the time saved in duplicative data entry is shifted to the administrative side, having to ensure all formulas and references are always kept up to date and information is still valid across all BOMs. Additionally, teams must still carefully perform the initial data entry on the root BOM for it to apply to all derived BOMs. This initial data entry is what can be further automated when creating & managing BOMs from PDM/PLM systems.
For highly complex products/SKUs and associated BOMs, managing via spreadsheets can lead to poor navigation and edit-ability as recently seen with the Williams F1 team. While BOMs would typically contain subsets of these parts/files (e.g. the Chassis or Gearbox), their associated MBOMs (manufacturing BOMs) may still be several thousand line items long with the additional line items for all documentation, specifications, etc.
The starting point for spreadsheet BOMs can be from CAD. The BOM table that can be included on drawings typically contains the part number, part name, revision, quantity, and the same for children components of a given assembly. This can be exported to Excel, and additional information is then added. Teams will have to do this or manually build BOMs from scratch, especially when creating indented BOMs to indicate levels/hierarchies for the parent/child relationships of assemblies, sub-assemblies, and parts. This is a time intensive process, with the complexity dependent on the amount of fields teams want to track for EBOMs (engineering BOMs) to indicate the products as-designed.
In many cases, MBOMs (manufacturing BOMs) are derived from EBOMs. To help with ease of updates between EBOMs and MBOMs, structures (indented, direct, parts only) are typically maintained. Manufacturing teams will have different fields to track, with the additional line items added to reflect the products as manufactured or shipped. They may contain information from multiple different EBOMs as well to reflect more complete structures (e.g. as assembled by manufacturers, or as shipped to end customers). The complexity for MBOMs is similar to that of EBOMs - based on how many fields the teams need to track for the parts/files in those BOMs. For teams in highly regulated industries like aerospace and medical devices, the additional documentation and traceability requirements increase the amount of line items that need to be captured on these BOMs as well.
Creating BOMs from PDM/PLM systems enables teams to automate much of the initial data entry required. Metadata associated with the files can be used to auto-populate the fields of information in BOMs that teams need to track. Based on the PDM system, this metadata can also be from the CAD file itself, enabling a link between CAD, PDM, and the BOM. As PDM systems understand parent/child relationships, entire products/SKUs can be reflected in the BOMs created, across a variety of assembly CAD files and their children components. Indented, direct, and parts only BOMs can easily be created based on the top level assembly files selected for the BOMs. Generally, teams will generate EBOMs from PDM systems and MBOMs from PLM systems, indicative of the lifecycle stage/teams that typically own those BOMs; though it is common for EBOMs to be generated from PLM systems as well for the latest released files.
Updating spreadsheet BOMs can either be a simple or complex process. If teams are making updates to just a handful of part files - the metadata values (or columns of information) can easily be edited. If those parts are referenced in multiple assemblies within the BOM, teams can do Find & Replace functions to quickly update information across the BOM. If teams have Excel automations in place, this information from the root BOM might even get updated in other BOMs, such as those shared with suppliers. However, in practice, teams generally avoid this workflow. For example, what happens if a net new part was added to an assembly? Something as simple as adding a line item to the EBOM could break references and formulas downstream in other BOMs. As a result, teams are using multiple spreadsheet BOMs requiring manual updates to each.
The higher the amount of fields used to track information on BOMs, the higher the overhead in manually updating BOMs to ensure all stakeholders have the latest information. In general, it is recommended that teams have at least the level/hierarchy, part number and name, description, quantity, and whether the line item is an off-the-shelf or custom component. Additional fields around the color, material, finish or production related values for per unit or extended costs, suppliers, lead times help teams get a better view of the overall product structure, but again, increase the overhead for teams to maintain on spreadsheet BOMs. These fields would add to the BOM structure horizontally (e.g. additional columns of information per row), while rows would be added vertically in MBOMs for additional line items (e.g. consumables, process steps, etc).
As PDM/PLM systems are the sources of record for the information that gets populated on BOMs - there are significant advantages for BOMs to be connected to these databases. Most PDM/PLM systems that can generate BOMs also have user interfaces to support editing line items on BOMs within those systems. For these BOMs, teams can automatically update or validate updates to the BOM as files get checked back in or as metadata gets edited. Teams can also manually overwrite these values on the BOM itself. Updates made to the BOM in PDM/PLM systems do not have to get applied on a file level - these updates can purely remain on the BOMs. This gives teams the flexibility in validating updates from the activities in these systems (check in, metadata updates, releasing), or changing other details for those specific BOMs. On the other hand (depending on the system setup of CAD, PDM, PLM), the metadata from CAD files can also be read or written into for extending the digital thread between CAD, PDM, BOM, and PLM.
Other benefits PDM/PLM systems provide in updating/maintaining BOMs is around understanding parent/child relationships. Net new parts added to assemblies automatically get added as children at the correct level/hierarchy. Additionally, PDM/PLM systems also store the additional documentation that is referenced in the line items of BOMs, such as the manufacturing routing steps or quality procedures. Just like native CAD and drawing files, these additional files can also go through the same check out/check in or release processes. As these files are still connected to the BOM, teams can easily automate or validate updates to BOMs as these files are checked in or released. Due to this, teams do not have as much overhead for ensuring BOMs are up to date; and a lesser risk for incorrect manual updates to the BOM which could lead to downstream production and delivery issues.
BOMs are meant to be a central source of truth for upcoming builds. Stakeholders to the BOM may either be performing updates to the BOM themselves, or just consuming the data. These stakeholders can either be internal teams, or external like suppliers and customers. Sharing spreadsheet BOMs with these stakeholders is easy - just download/send the document or a link. However, maintaining spreadsheet access levels (e.g. view only, editable, ability to see some tabs within the sheet but not others, etc) across these stakeholders is burdensome. Especially combined with the duplicative data entry teams have to do for these shared BOMs. If edit access is given for these stakeholders and if there are updates, those updates may need to be applied to other BOMs as well; further increasing the overhead in maintaining BOMs.
Additionally, teams need to compile all files and documentation as referenced on BOMs separately. Internal stakeholders may be privy to the native formats of CAD files and drawings, but external stakeholders may only have access to universal formats like STEP and PDF. If teams are approaching production and collaborating with external suppliers, documents for other line items from MBOMs like manufacturing process steps or quality procedures also need to be included in these export packages. Teams need to be particularly cautious in ensuring all files match the BOM line items’ versions/revisions, and exporting data from multiple sources to put these bid or RFQ packages together.
Sharing BOMs with internal or external stakeholders is significantly easier with BOMs from PDM/PLM systems. If those systems support user interfaces (UIs) to view and edit BOM, teams can extend view only or edit access to stakeholders - who may also be able to edit BOMs in UIs similar to that of internal users. This can also apply to external stakeholders like suppliers or customers. In these cases, these BOMs operate in much the same way as spreadsheet BOMs from OneDrive/Excel and Google Sheets. Users would collaborate in web views, ensuring involved stakeholders are always kept up to date with the right information. Stakeholders would also have the option to export the BOM to Excel or CSV at anytime as well. If PDM/PLM systems also support viewing files in viewers, stakeholders to BOMs who do not have access to CAD or Microsoft Office could also view these files from those systems.
As the BOMs created from and maintained in PDM/PLM systems are linked to the actual production files within these systems, teams can easily generate exports for BOMs and production files directly from those systems. Some systems also offer the ability to convert native formats to universal (e.g. native CAD to STEP or native drawing to PDF) when exporting the assets within the BOM. Other line items around manufacturing routing steps or quality procedures with their own documents would automatically get included in these exports as well.
Approving and Releasing spreadsheet BOMs is more of a manual process. Teams may often share these between stakeholders who all approve the BOM. The file name for that spreadsheet may then contain “Approved” or “Released” to indicate the status. Teams may even have Approved or Released tabs within a spreadsheet of those BOMs. If teams are revisioning their BOMs upon release, they may do the same, except with Rev A, Rev B, etc within the file name of the spreadsheet or as tabs within. On one hand, spreadsheet BOMs offer a lot of flexibility in handling these processes, but also introduce risk with the wrong information getting approved or released. For traceability purposes, teams may also have to track the approvers of the BOM and dispositions somewhere (likely, another spreadsheet…)
Approving and releasing BOMs from PDM/PLM systems offer much of the flexibility in handling these processes, with the in-built records for these activities. Similar to sharing & collaboration, BOMs can be flagged for approval by stakeholders. Once stakeholders provide their dispositions and the BOM is approved, teams can release the BOM and up-revision just as files get up-revisioned upon release. Released BOMs typically cannot be edited further and are locked, but are still available to stakeholders for reference.
BOM management in spreadsheets requires more overhead than managing PDM based or PLM based BOMs. All processes involved within BOM management are more manual. Creating proper EBOMs requires copying/pasting from CAD generated BOMs. Creating MBOMs requires manual edits to the EBOM. Updating either is a manual process, with room for human error in the data entry across either - potentially causing downstream issues. Sharing and collaboration is a messy process with different versions of spreadsheets across various communication channels like chat or email. Approvals and releases are reflected within file names or tab names.
PDM/PLM based BOMs offer a higher ease of use with lower overhead for teams to keep all stakeholders aligned. If teams are using cloud-based ERP systems like NetSuite, BOMs and the associated production assets can even be pushed upon triggers like releasing.