Excel is a great business tool! However, it is agnostic of the end application and needs to be morphed (via formulas, macros, scripts etc..) to perform specific tasks viz. accounting, customer record management and such. Let’s add Bill-Of-Material (BOM) Management to that list of tasks. Having the right tool for the right job could save a company, more time and money than is apparent.

1.Copy/paste errors

Copying and pasting part information across multiple cells and across multiple spreadsheets, is a common source of errors. Was the entire string or Part Number copied from one cell to another, or, did we miss a character? Are there duplicate part numbers in the spreadsheet? 

2.Multi-level or Hierarchical BOMs

There a few different ways, this can be accomplished in Excel. We’ve seen companies add a ‘Level’ column with a number indicating the level of hierarchy, or using S.no formats such as 1, 1.1, 1.1.1 or indented lists, as shown below: 

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

• Add/Remove parts – Are you inserting parts at the right level in the hierarchy and the right row in Excel? Has the ‘level’ or ‘S.no.’ column been manually updated to reflect the desired level? While removing a sub-assembly, have you deleted all the underlying parts as well?
• Multiple instances of a part – Problems multiply when you have multiple instance of a part number at different levels of hierarchy. Making changes in this scenario is a ticking timebomb! For eg: let’s say there are changes to the attribute information (cost, inventory info, manufacturer info) of part P294896. Were all instances of this part updated in the BOM? Also, was this information updated in ALL the BOMs that reference this part?
• Sorting – Cannot rearrange line items OR sort columns, because that could potentially break the ‘S.no’ column. Cleaning this mess could set you back a few hours or even days.
• Indentation views – Creating indentation for parts at different levels is a pain. Don’t let anybody convince you that this is cakewalk in Excel!

3.Managing BOM revisions

• This is most likely done using file naming conventions. “AX-video-measurement-CN-Rev6.xlsx’. This may seem manageable with a couple of BOMs with a handful of revisions. However, as your product lines grow, the risk of sending the wrong revision-file to the manufacturing team, only gets higher.
• Another challenge here is how do you compare different versions of BOMs? There are 3rd party add-ons to Excel that allow comparing Excel files. However, that is an added cost.
• Ability to track which BOMs get impacted when an underlying part undergoes revision, is critical to approving Engineering Change Orders (ECOs). In common Product Lifecycle Management (PLM) terminology, this is called where-used. This is not standard functionality within Excel. Failure to update part revisions in the referenced BOMs could pose a serious risk, resulting in failed prototypes.
• In a collaborative environment, tracking who made changes to the BOM and when, is nearly impossible in Excel. The only information available ‘out of the box’ is information on the person who modified it last. Workarounds to this include manually captured in the ‘Notes’ column with a date/time stamp and cannot be enforced! There is always a risk of an engineer forgetting to update the ‘Notes’ column.

4.Documentation for BOMs or parts

Instructions to assemble parts in an sub-assembly often require more detailed instructions (in the form of drawing files, datasheets) than just a ‘Comments’ column in Excel. Pasting links to multiple documents in Excel is tedious. A bigger problem here is – what if the documents have undergone revisions and the links need to be updated? Sending outdated instruction documents to your technicians can result in a lot of lost time in seeking clarifications.

5.Cost and quantity rollup calculations

While this is fairly convenient to do in Excel for flat BOMs, doing this for multi-level BOMs, takes a lot of setup, formulas, VLOOKUPs, and could take hours of manual checking to get it right on large assemblies. Here’s an example of a VLOOKUP formula in Excel to get the rollup quantities right for a small BOM.

The risk of wrongly computing the rolled-up quantities and cost, multiplies when parts are added to/or removed from the BOM.

6.Controlling change

BOM changes often require approval from a change control team to assess the cost of change to the product. This involves giving the right people access to the BOM, and keeping them informed of the changes for approval. Having a workflow solution tightly integrated with the BOM, can reduce risk of releasing Excel BOMs with unapproved changes.

Building products better, faster and cheaper starts with using the right tool for the job!

To see how FusePLM can help you get over the above hurdles with minimal setup and training, signup for a free trial at www.fuseplm.com.