Microsoft Excel is no doubt one of the most beloved of all engineering tools, it's easy to use, almost infinitely adaptable, and yet extremely accessible. With 1 billion licenses of Office now in the wild, chances are Excel is lurking somewhere nearby. Unsurprisingly, Excel spreadsheets often become the default choice for bill of material (BOM) authoring at small engineering firms. But at what cost?
As products grow in quantity and complexity, Excel BOMs quickly become both difficult and costly to maintain. The only way out of this cost spiral is to move away from the spreadsheet model: it's time to drop the Excel BOM.
The Perils of Unstructured Data
Excel is rather proficient at organizing data while remaining very flexible, but that freedom comes with a hefty price. Excel, for the most part, just doesn't care about relationships from one cell to another, and that creates quite a few problems for authoring BOMs quickly and accurately. Some of the issues include:
- Large amounts of repetitive data entry (every engineer's favorite task!)
- No validation or protection for part numbers or associated metadata
- No convenient way to create indenture for subassemblies
- Unnecessary duplication of work already accomplished in CAD
- No efficient method to delete, insert, or re-arrange BOM lines
Sure, one could employ an Excel wizard to design all sorts of rule-based protection and visual basic scripting to try to keep things under control, and you'll have a mechanism that would undoubtedly make Rube Goldberg proud. However, when product complexity reaches even relatively modest levels, expensive errors will compound. Your BOM solution must be more robust, especially when the need arises to view or derive the BOM in a variety of different ways. The key is to recognize the substantial value in using a tool that truly models BOM structure. That would be a tool that's not just a bunch of loosely related bits in cells, but a relational hierarchy of parts and their relevant metadata.
Handling Change
There's one fundamental constant in all of engineering: change. Aligning with the capability to change quickly and often is crucial in fighting back ever-increasing schedule pressures. Excel BOMs provide no help here. A separate Excel BOM has to be manually synchronized with each design change. It's usually in this confusion where some of the bigger and most expensive errors tend to happen. Conflicts are common and notoriously difficult to set straight. Recognize that the information in a BOM is every bit as vital as your CAD design, and should be managed accordingly. For the very same reasons you benefit from managing CAD, so should you do the same with a BOM.
It's only the beginning
So far we've been talking about BOMs strictly from a design perspective. But the expectation that there can be only one BOM to rule them all is unrealistic. There are different ways to slice BOMs, different disciplines may have a need for their own specific view or information. How manufacturing looks at a BOM in ERP will be fundamentally quite different from how engineering looks at a BOM. Trying to rely on one manually edited document to accomplish this task is quite frankly a bit of a nightmare.
Moving Forward
Fortunately CAD tools have been steadily evolving and most are now BOM aware - with features to help easily append BOM data to your product structure right within the CAD file. Aside from being able to quickly accommodate changes, the fact that CAD tools can understand some level of BOM information has a number of potential future benefits. Managed BOMs open up new possibilities to take that relational data, and give you actionable results. Wouldn't it be nice if the moment you added a bolt, you'd know if it was available, how many were in stock, or what the available substitutes might be? Providing real actionable information within the context of the design product structure so it's not an after-the-fact research job. Such things are certainly possible with the right BOM management.
Ed Lopategui is a technology evangelist, entrepreneur, and aerospace engineer specialized in the software tools and technology which enable engineering, design, and product development, specifically Product Lifecycle Management (PLM), Product Data Management (PDM), Knowledge Management, and Computer Aided Design (CAD/CAE/CAM). Ed runs blog eng-eng.com
Struggling with CAD file management?
Unmanaged CAD data can lead to wasted time and expensive mistakes, but traditional PDM and PLM solutions are too costly for many companies. This guide, written by industry analyst Jim Brown, will help you find the solution that fits your needs.
