6 Reasons Why You Can’t Manage Bill of Materials (BOM) in Excel


Excel is an excellent enterprise tool! However, it is agnostic of the end software and requires to be morphed via things such as formulas, macros, scripts, etc. To operate precise functions with respect to accounting, consumer data management, and such. Adding Bill Of Materials (BOM) Management to that pile of tasks only makes it more complex and difficult to manage. Having the proper system for the proper job could restore a company, reduce excess time and increase cash reserves.

Let us look at the 6 ways how managing a BOM in Excel could be your worst nightmare. 

Copy/Paste Errors

Copying and pasting sections facts throughout more than one cell and across a couple of spreadsheets is a frequent source of errors. Was the whole string or Part Number copied from one cell to another, or, did we leave out a character? Are there duplicate part numbers in the spreadsheet?

Hierarchical BOMs

There are a few distinct ways, this can be performed in Excel. We’ve considered businesses add a ‘Level’ column with a quantity indicating the degree of the hierarchy, or implementing S.no codecs such as 1, 1.1, 1.1.1 or maybe using indented lists

What makes managing multi-level BOMs in Excel so hard?

Multiple cases of a section

Problems multiply when you have a couple of situations of a part number at distinctive degrees of hierarchy. Making adjustments in this situation is a ticking timebomb! For eg: let’s say there are modifications to the attribute data (cost, stock info, producer info) of phase P294896. Were all cases of this phase up to date in the BOM? 

Managing BOM Revisions

This is mostly carried out using file naming conventions. Which can be quite convoluted.  This at first, might appear manageable with a couple of BOMs with a handful of revisions. However, as your product lines grow, the danger of sending the incorrect revision file to the manufacturing team will become higher.

Another issue right here is how do you examine distinct variations of bill of materials? There are third-party add-ons to Excel that permit comparing Excel files. However, that is an additional cost.

Ability to find which BOMs get impacted when an underlying part undergoes revision is crucial to approving Engineering Change Orders (ECOs). In simple Product Lifecycle Management (PLM) terminology, this is known as where-used. This is not a regular feature in Excel. Failure to replace part revisions in the referenced BOMs may pose a serious risk, culminating in failed prototypes.

In a collaborative environment, monitoring who made adjustments to the BOM and when is almost not possible in Excel. The only data accessible ‘out of the box’ factor here is data on the individual who modified it last. Workarounds to this encompass manually captured in the ‘Notes’ sections with a date/time stamp and can’t be enforced! There is constantly a threat of an engineer forgetting to update the changes.

BOMs or Parts Documentation

Instructions to collect components in a sub-assembly frequently require extra specific guidelines than simply a ‘Comments’ column in Excel. Pasting hyperlinks to many files in Excel is tedious. A larger issue here is – what if the files have gone through revisions and the hyperlinks are needed to be updated? Sending outdated guideline files to your technicians can end result in a lot of misplaced time in searching for clarifications.

Volume, Cost Roll-up Calculations

While this is pretty easy to do in Excel for flat BOMs, doing this for multi-level BOMs, takes a lot of setup, formulas, VLOOKUPs, and might take hours of manual checking to get it precise on massive assemblies. Here’s an instance of a VLOOKUP method in Excel to get the rollup portions proper for a small BOM.

The hazard of wrongly computing the rolled-up quantities and charges multiplies when components are introduced to/or eliminated from the BOM.

Change Management & Control

BOM modifications frequently require approval from a change control team to verify the price of changes made to the product. This entails giving the correct members authorized access to the BOM and keeping them appraised of the modifications for approval. Having a workflow solution tightly built-in with the BOM can minimize the danger of releasing Excel BOMs with unapproved changes.

Final Thoughts

Building merchandise better, faster, and more cost-effective begins with the use of the proper tool for the job. A proper PLM tool can help you manage your bill of materials while ensuring a smoother product lifecycle management. 

KloudPLM provides a cloud-based PLM platform that can help simplify and improve your Bill of Materials (BOM) management. Streamline your product life cycle with the power of KloudPLM. To find out how KloudPLM helps in BOM management, schedule a demo today. 


Leave a Reply

Your email address will not be published.

Leave a Reply

Your email address will not be published.