Using Excel for Bill of Materials (BOM) Management

You are using Excel for managing your BOMs today.
How long can that last?

Most small and mid-size businesses (SMBs) start with their product bill of materials (BOM) in Excel spreadsheets — why should you be any different? Excel is a well-known, flexible application that everyone has — It is easy and it is essentially free. Small companies can make it through the first release of a new product just fine using Excel to manage product data.

But when companies release more products, grow internally or add more manufacturing and outsourcing partners, the act of managing an Excel bill of materials becomes much bigger and more complex. Those seemingly simple problems can impact you in big ways when you least expect it. But there is another way — you don’t have to use Excel to manage your bills of materials.

Common problems with an Excel bill of materials

The first problem most companies have with their Excel BOM is identifying which file is the latest revision and whether that revision is correct. Even if you limit who can change the BOM by locking the Excel file or storing it in a read-only server folder, once people copy the file onto their desktops, the Excel bill of materials can be easily modified and emailed to different people both inside and outside your company. Knowing who has which version of the BOM quickly becomes a question that can’t be answered.

The next problem occurs when two or more products share components. Purchasing can negotiate better volume pricing if they know which components are used in more than one product and are able to calculate the higher inventory requirements. But searching through an Excel bill of material to identify common part numbers is tedious and slow. The task becomes more complicated if a component has been assigned different part numbers in different places, which is a common problem when no easily accessible master parts list exists.

Companies often try to solve that problem by adding complexity into their Excel spreadsheet BOMs. They use tactics like item master tabs, lookup formulas, cross-referenced spreadsheets and Visual Basic programming — especially if they have an Excel guru on staff. This works as long as your Excel master keeps the connections up to date so they correctly fill in the cells. But this person is now also a single point of failure in an intricate web of files. The BOM management process is in his or her head and buried in the details of an unknown number of hidden tabs on countless spreadsheets.

What an Excel BOM could be costing you

These are common problems when using Excel to manage product bills of materials, and they generally cause pains that are all too familiar, such as wasted time and unnecessary scrap and rework. Sometimes it’s “just” 10 minutes here or 20 minutes there, searching for the right BOM spreadsheets on servers, in your email archive or on other people’s desktops. But during a new product introduction (NPI), you can become bleary-eyed from spending hours looking over endless rows and columns of data, trying to make sure everything matches up correctly, before releasing your product to production. You thought Excel was free, but you pay when people spend time looking for the needle-sized error that ruins a product launch instead of doing more productive work.

The time wasted on these annoyingly tedious tasks is hard to track, so the true cost of it is often not known. However, the amount of money lost if errors don’t get caught before hitting the factory floor — or worse, the customer’s hands — can significantly impact a company’s bottom line. Depending on the severity of the error and the organization’s response, it can impact your company’s reputation as well. In recent years several well-known brands have been deeply tarnished when their response to design problems did not meet the expectations of their customers.

Free Excel BOM templates and examples for download

The zip below includes a Basic Excel BOM with document revision table, an Excel BOM with revision table and assembly header, and an Excel item master with manufacturers and vendor info.
Download ZIP »

Alternatives to Excel Bills of Materials (BOMs)

There are alternatives to managing your product bill of materials in fragile, formula-laden Excel spreadsheets that are duplicated across server folders and email inboxes. There are software systems that have been designed to manage bills of materials and the changes that happen to them. These tools allow all users access to the same set of product data, which reduces both the duplication of parts across products and the duplication of spreadsheet BOM files. All users, internal and external, can see the current product revision. The relationships between parts and the assemblies in which they’re included are built in to the software, making it easy for your purchasing person to find all the locations where a particular part is used. The system automatically maintains those relationships — it’s no longer a headache for you. You just use a tool that is easy and powerful.

Comparing Excel Bill of Materials to a modern Bill of Material management system

The following chart compares Excel BOMs with a modern BOM management system, highlighting the strengths, weaknesses and details of each when it comes to managing bills of materials:

  Excel BOM Management System Details
Cost and Value
Cost “FREE” VARIES Excel is purchased as part of the MS Office suite for other purposes within a company, therefore using it for managing BOMs is essentially free. BOM management systems require software licenses and may require services to get started.
Value GOOD EXCELLENT Excel is good at a lot of things, but BOM management systems are particularly suited to managing interrelated product information in a collaborative environment. With Excel comes the risk of hidden costs such as scrap & rework and missed revenue due to late delivery.
Learning Curve FAST MODERATE - FAST The basics of Excel are well known, but advanced functions like VLOOKUP may require online help. Incorporating Visual Basic requires additional courses or reference materials. A BOM management system, as with any new system, has a learning curve; however, most come with classes or online tutorials to get you up and running quickly.
Time to Implement IMMEDIATE DAYS - WEEKS Excel is readily available and chances are you’re already using it. All new systems take time to configure and roll out. Some systems have best practice templates to simplify set up. Others have special tools that make the data transfer from Excel fast, easy, and accurate.
Features
Bill of Materials Management POOR EXCELLENT Excel BOMs can be generated by exporting from CAD or ERP but cannot be shared in a controlled environment. When BOMs become complex or multiple linked BOMs must be managed and shared, control is easily lost. BOM management systems are designed to allow access to those who need it while maintaining control of changes.
Product Revision Control POOR EXCELLENT With Excel, product revisions are tracked within spreadsheets or in controlled file shares. Modern BOM management systems maintain a single version of the product record so the latest version is always available to anyone who needs it. BOM systems also retain product history, which allows redline comparisons between current and previous revisions.
Interdepartmental Collaboration AVERAGE EXCELLENT Spreadsheets can be shared on a file server and locked for one-at-a-time editing. BOM management systems allow secure, concurrent collaboration.
External Collaboration (Suppliers, Contract Manufacturers, Design Houses, Partners) POOR EXCELLENT Emailing spreadsheets to external collaborators creates difficulty in version control, potential for product errors, and delays in communication. BOM management systems provide a central, controlled access point for all stakeholders.
Change Management (ECO, MCO, ECR) POOR EXCELLENT Managing product changes in Excel is challenging for products of any complexity. BOM management systems are designed to control and track every change in the product.
Security POOR EXCELLENT Spreadsheets can be locked and password protected, but the data can be edited and re-saved easily, putting your intellectual property at risk. BOM management systems allow you to share without losing control.
Compliance Management/ Audit Support POOR GOOD With Excel, tracking and reporting on compliance information is difficult and time-consuming. By centralizing all product data and change history, BOM management systems can generate compliance and audit reports with a few clicks.

BOM Management tips — Excel and beyond

You may feel that you need to stick with Excel for a little while longer, but now is the time to plan ahead for the switch to real BOM control. There are simple and smart things to do that will improve your current processes as well as prepare you for moving off Excel BOMs to a bill of materials management system.

Keep your BOMs consistent

When transferring your data from Excel to a BOM management system, the information will be transferred based on rules that you set. When your BOMs are in consistent formats then the information transfer is easily programmed. For example, rules are set up to place information from ‘Part_No’ cells on your spreadsheets into the new system’s ‘Part Number’ slot. A human can see that these names represent the same thing, but a computer only knows that when someone tells it so.

When you are consistent in the naming of your columns across all BOM Excel spreadsheets then a rule only needs to be set up once. If the name of a column varies across spreadsheets — Prt#, Part_No, Part Num — then each name must be independently set to be associated with Part Number in the new system.

But consistency goes beyond column names — it includes worksheet tab names and tab order, manufacturer names, descriptions, file names, columns on the BOM… everything. When importing your BOMs into a new system, they go through matching algorithms at various steps in the process. For example, if in one Excel BOM someone uses the manufacturer name ‘Texas Instruments’ and in another Excel BOM someone uses ‘TI,’ then duplicate entries for the manufacturer name — which will have to be cleared up manually — are likely to be created.

While being consistent clearly helps when you choose to move to a BOM management system, it also helps your employees and suppliers NOW. When all BOMs are in the same format and use the same naming conventions they are easily and instantly understood. If every person formats bills of materials his or her own way, then each new BOM spreadsheet must be interpreted — and interpretation of data always opens up additional opportunities for data errors.

Use standard Excel BOM templates

Standardizing your bill of materials is a great start to being consistent. Employees who create BOMs should get in the habit of hiding columns they don’t need rather than deleting them. When doing analysis that requires BOM information and structure, such as cost comparisons, prototype inventory or purchase order tracking, they should create separate spreadsheets instead of adding non-standard columns to the BOM.

Each column should have a header so every piece of information in the BOM has a label and can be identified by both humans and computers. The information in each column should be whatever the label says is there. Columns should not have a dual purpose, like ‘REF DES & BOM Notes’ for instance, because a computer transferring the BOM to another system won’t know which type of data each entry is.

Sometimes people put general assembly information at the top of a worksheet before the start of the BOM. Even this information should be labeled, so someone reviewing the BOM knows that 7/24/2010, for instance, is the ‘revision approval date’ and not the ‘file last edited date.’

Have part numbering and part naming conventions — and a single location to store them

Part numbering and naming conventions help ensure regularity across all bills of materials. When using Excel, a good way to store this information is in a master parts list or item master that can be accessed by everyone who needs it. Generating part numbers from a central location or list greatly reduces the likelihood of creating duplicate parts. When naming conventions and item masters are in place, engineers can more easily find and reuse existing components instead of unnecessarily creating duplicate parts.

Minimize repeated data

When a part is used in more than one product, it clearly needs to be listed in more than one bill of materials. But for each part, try to only include as much information as the BOM needs to do its core job: relating parts to particular product assemblies. Having additional details repeated in multiple locations adds complexity by increasing the potential that one of the details will be changed, cause confusion and need to be investigated and reconciled. For example, does each manufacturer and alternate manufacturer for a part need to be listed on every BOM where the part is used? Details like that should be kept in one location, like the item master, so that any changes only need to be made in one place.

Following these suggestions will help you now while you are managing your bills of materials in Excel — and they will also set you up for a faster, smoother and more accurate transfer of data when you’re ready to switch to an automated BOM management system. Get more BOM management tips.

arena partslist learn more info

You may also like:

©2014 Arena Solutions, Inc. Arena and Arena Solutions are trademarks of Arena Solutions, Inc., Reg. U.S. Pat. & Tm. Off.
All rights reserved. Other product and company names are the property of their respective holders. Contact Arena at questions@arenasolutions.com for permission to repost or syndicate this content.