Great tool, wrong job: The hidden costs of using Excel for BOM management

Is using Excel for bill-of-material (BOM) management really free, or is using a decades-old application intended for a different purpose gambling with your business? Real-life stories provide the answer.


Almost as soon as Excel superseded Multiplan, Microsoft's first spreadsheet application, in 1987, manufacturers adapted it to organize their bills of material (BOM). More than 20 years later, it’s safe to say many managers still routinely fit their product information into Excel's rows and columns.

And why not? Everyone loves Excel. It’s easy enough to use, everyone’s got it, and it came with your Microsoft Office application suite, so it's kind of free.

But is using Excel for BOM management really free, or is using a decades-old application intended for a different purpose gambling with your business? A quick read of a typical manufacturing service contract provides a hint at the answer:

“We require a complete BOM. We are not responsible for any discrepancies between your BOM, CAD, Gerbers, and assembly drawings that affect delivery schedules.”

That’s the problem with Excel. You can never be absolutely certain that the BOM you release to manufacturing is the revision you want, because Excel offers no easy way to prevent or discover discrepancies between your BOM and your drawings—or even within your BOM itself. This means that every cell in your Excel BOM is a potential source of errors since any changes to data require manual intervention—e.g., rekeying, deletion, recalculation, etc.—involving tedious inspection across all affected data.

And since every person in every department uses her or his own formulae, nomenclature, macros, and even formatting, every Excel worksheet can introduce chaos at every stage of your design-through-manufacturing process.

Crunch time
Excel, at its core, is an accounting program, not an engineering and operations management solution. Excel is designed to help you create, format, and analyze numeric data. And while it is terrific for crunching large columns of financial data, Excel is simply not designed to be a mechanism that ensures your engineering and production data are integrated, properly linked with associated records, and updated in response to changes. Automatic auditing, engineering change order and request (ECO and ECR) tracking, and collaboration facilities are not supported. Links across data—especially to external data—break easily.

Still, nothing beats Excel when it comes to manipulating numbers. But could using Excel to manage your BOMs have hidden costs, ranging from the time you spend trying to ensure the accuracy of your data, to such costly disasters as building the wrong product, missing delivery dates, exceeding cost projections, and incurring scrap and rework?

Interviews with managers of small and midsize manufacturers across industries suggest that using Excel to manage your BOMs can indeed be a costly gamble. The same managers have learned that a dedicated BOM management system is a surer bet. A modern BOM management system provides a cost-effective, time- and cost-recouping solution that overcomes the flaws and risks inherent in using a great tool like Excel for the wrong job.

Managers spoke with a surprising uniformity when reflecting on the many ways that Excel failed them as a BOM management system. Almost everyone spoke about how their Excel BOMs simply grew out of control over the years. They described their near-primal reaction whenever VLOOKUP returned the dreaded “#N/A” for a part they knew had been in their BOM for years. Cost run-ups from missed ship dates and releasing the wrong revision to manufacturing were mentioned routinely.

Managers at entrepreneurial companies were especially interested in describing the moment they realized that their small start-up was not so small anymore and they needed a better way than Excel to handle their product data. A typical story went something like this:

The Ballooning BOM
“We started out managing our BOMs with Excel. Everything was fine. We had one product with 50 or so parts. Initially, our BOM was a simple list of three columns—part number, part name, and quantity. Soon we started adding columns—manufacturer part number, supplier cost, budget cost, tolerance, and so forth. Then we got into mass customization, which meant multi-level parent BOMs that we used as a baseline to build multi-level child BOMs. We built these by copying and pasting entire columns of parent data into the child BOM and hand-editing the changes.

“Then different departments, like engineering and purchasing, wanted to manage or expand their part of the BOM. So competing formulas, ad hoc part numbers, and their jargon would slip in. Worse, with all the copies being worked on, the parts of the master BOM got out of sync. All the while the BOMs kept growing until we couldn't figure out how to pull out cost data. We'd run various VLOOKUPs matching the parent against the child and child against the parent just to find and fix every “#N/A” discrepancy. This was maddening, time-consuming, and mind-numbingly dull. Finally one day, we went through all this, gathered up all the drawings, and ended up releasing the wrong revision to our contract manufacturer, which dutifully ran a huge lot of the wrong-size parts.…$50,000 down the drain. We knew right then that Excel was the wrong tool for the job.”

Another story involves someone we'll call “Bob.”

Bob Retires
Only human resources really knew how long Bob had been at the company before his retirement party. Bob was the Excel guru. Zealously, and, frankly, to everyone's relief, he had managed the company's master BOM as his job security blanket for years. And he did it well—all 64 multi-megabyte, 2500-line BOMs with 20-25 levels of parent/child relationships, color-coding, hidden columns, annotated charts, and untold numbers of links to external workbooks.

It was on the Monday morning after Bob drove off in his RV to see America that everyone realized the keys to unlock their corporate knowledge were in the head of some guy who no longer worked for them. Teams of project managers spent weeks trying to decipher Bob's Excel BOMs. What had been a free, easy-to-use corporate asset had become a giant roadblock to growth.

And none of these kinds of problems are the fault of Excel. The tool was simply being asked to do a job it's not meant to do. Excel does not have—nor should it—the automated tools and mission-critical capabilities for BOM management.

This chart provides a quick comparison of Excel and a modern BOM management system, highlighting the strengths, weaknesses, and relative costs of each. And as you will clearly see from the chart, Excel is an excellent tool for many everyday business jobs. But when it comes to mission-critical engineering and operations management, it is the wrong tool for the job that you have to get right every time.

About the author: Helen Shaughnessy is a product marketing manager with Arena Solutions , a supplier of Software-as-a-Service (SaaS)-based product life-cycle management software.

The Top Plant program honors outstanding manufacturing facilities in North America. View the 2015 Top Plant.
The Product of the Year program recognizes products newly released in the manufacturing industries.
Each year, a panel of Control Engineering and Plant Engineering editors and industry expert judges select the System Integrator of the Year Award winners in three categories.
Doubling down on digital manufacturing; Data driving predictive maintenance; Electric motors and generators; Rewarding operational improvement
2017 Lubrication Guide; Software tools; Microgrids and energy strategies; Use robots effectively
Prescriptive maintenance; Hannover Messe 2017 recap; Reduce welding errors
The cloud, mobility, and remote operations; SCADA and contextual mobility; Custom UPS empowering a secure pipeline
Infrastructure for natural gas expansion; Artificial lift methods; Disruptive technology and fugitive gas emissions
Mobility as the means to offshore innovation; Preventing another Deepwater Horizon; ROVs as subsea robots; SCADA and the radio spectrum
Research team developing Tesla coil designs; Implementing wireless process sensing
Commissioning electrical systems; Designing emergency and standby generator systems; Paralleling switchgear generator systems
Natural gas engines; New applications for fuel cells; Large engines become more efficient; Extending boiler life

Annual Salary Survey

Before the calendar turned, 2016 already had the makings of a pivotal year for manufacturing, and for the world.

There were the big events for the year, including the United States as Partner Country at Hannover Messe in April and the 2016 International Manufacturing Technology Show in Chicago in September. There's also the matter of the U.S. presidential elections in November, which promise to shape policy in manufacturing for years to come.

But the year started with global economic turmoil, as a slowdown in Chinese manufacturing triggered a worldwide stock hiccup that sent values plummeting. The continued plunge in world oil prices has resulted in a slowdown in exploration and, by extension, the manufacture of exploration equipment.

Read more: 2015 Salary Survey

Maintenance and reliability tips and best practices from the maintenance and reliability coaches at Allied Reliability Group.
The One Voice for Manufacturing blog reports on federal public policy issues impacting the manufacturing sector. One Voice is a joint effort by the National Tooling and Machining...
The Society for Maintenance and Reliability Professionals an organization devoted...
Join this ongoing discussion of machine guarding topics, including solutions assessments, regulatory compliance, gap analysis...
IMS Research, recently acquired by IHS Inc., is a leading independent supplier of market research and consultancy to the global electronics industry.
Maintenance is not optional in manufacturing. It’s a profit center, driving productivity and uptime while reducing overall repair costs.
The Lachance on CMMS blog is about current maintenance topics. Blogger Paul Lachance is president and chief technology officer for Smartware Group.
The maintenance journey has been a long, slow trek for most manufacturers and has gone from preventive maintenance to predictive maintenance.
Featured articles highlight technologies that enable the Industrial Internet of Things, IIoT-related products and strategies to get data more easily to the user.
This digital report will explore several aspects of how IIoT will transform manufacturing in the coming years.
Maintenance Manager; California Oils Corp.
Associate, Electrical Engineering; Wood Harbinger
Control Systems Engineer; Robert Bosch Corp.
This course focuses on climate analysis, appropriateness of cooling system selection, and combining cooling systems.
This course will help identify and reveal electrical hazards and identify the solutions to implementing and maintaining a safe work environment.
This course explains how maintaining power and communication systems through emergency power-generation systems is critical.
click me