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.|
|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.|