Are You Still Managing Your Bill of Materials (BOM) in Spreadsheets?
Don’t let spreadsheets overwhelm you
Most businesses start out managing bills of materials (BOMs) in spreadsheets, so why should you be any different? Microsoft Excel and Google Sheets are well-known, flexible tools that you can easily access. While smaller to mid-sized companies might be able to get by using spreadsheets for the first release of a product, they will encounter growth and scaling issues later.
When companies release additional products, experience fast growth, or add more supply chain partners—the act of managing BOMs in spreadsheets becomes a much bigger issue. Those seemingly simple problems can impact you in big ways. But there is another way. You don’t have to use spreadsheets to manage your BOMs.
Common problems managing BOMs in Spreadsheets
The first problem most companies have with their spreadsheet-based 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 spreadsheet file or storing it in a read-only server folder, once people copy the file onto their desktops, the bill of materials in spreadsheets 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 a spreadsheet 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 with their spreadsheet-based BOMs. They use tactics like item master tabs, lookup formulas, cross-referenced spreadsheets, and Visual Basic programming—especially if they have a spreadsheet-formula guru on staff. This works as long as your spreadsheet 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 BOM Spreadsheets could be costing you
These are common problems when using spreadsheets 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 Google Sheets and Excel were 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.
Alternatives to Spreadsheet-based Bills of Materials (BOMs)
There are alternatives to managing your product bill of materials in fragile, formula-laden spreadsheets that are duplicated across server folders and email inboxes. There are bill of materials software systems that have been designed to manage bills of materials and the changes to the product assemblies. 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 into 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 Spreadsheet BOMs to a modern BOM Management Software
The following chart compares the management of BOMs in spreadsheets vs. a modern BOM management software solution (e.g., product lifecycle management or PLM), highlighting the strengths, weaknesses, and details of each.
|Google & Excel Spreadsheets||BOM Management Software||Details|
|Cost and Value|
|Cost||“FREE”||VARIES||Excel can be accessed via Office 365 making it easy to use. Google Sheets is free. BOM management software systems require software licenses and may require services to get started.|
|Value||GOOD||EXCELLENT||Spreadsheets are good at a lot of things like managing costs and financial information, but BOM management software solutions like PLM are particularly suited to managing interrelated product design information (e.g., drawings, specifications, AML, AVL, files) in a collaborative environment. Using spreadsheet-based solutions increases risk of having BOM records disconnected from the rest of the product record. This can lead to product design errors, launch delays, and costly scrap & rework.|
|Learning Curve||FAST||MODERATE – FAST||Using Excel or Google Sheets is fairly easy, but advanced functions like VLOOKUP may require online help. Leveraging formulas or Visual Basic requires additional expertise. A cloud-based BOM management system is designed to be easy to use via a web browser and requires little training by internal teams of supply chain partners.|
|Time to Implement||IMMEDIATE||DAYS – WEEKS||Spreadsheets are readily available. New systems take a little time to deploy and use. Most BOM management solutions come with best-practice implementation tips and templates.|
|Bill of Materials Management||POOR||EXCELLENT||Excel- or Google Sheet-based BOMs can be created by exporting from CAD or ERP, but cannot be easily shared in a controlled environment. When BOMs become complex or multiple, linked BOMs must be managed and shared, teams have a hard time finding the latest revision. BOM management systems are designed to make it easy to bring electrical, mechanical, and software designs together along with the related drawings.|
|Product Revision Control||POOR||EXCELLENT||With spreadsheets, product revisions are often 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. Every released or pending change order (ECO) is linked to the part of the assembly, making it easy to view historical or future changes. BOM software systems also provide revision comparisons between current and previous revisions with the click of a button.|
|Interdepartmental Collaboration||AVERAGE||EXCELLENT||Spreadsheets can be shared on a file server or in the cloud. But they aren’t connected to change management processes and associated product or quality records. BOM management systems allow secure, concurrent collaboration with links to every related part of the design, change, quality, or project record.|
|External Collaboration (Suppliers, Contract Manufacturers, Design Houses, Partners)||POOR||EXCELLENT||Emailing spreadsheets to external collaborators creates difficulty in revision control, the potential for product errors, and delays in communication. BOM management systems provide a highly-secure single source of product design truth.|
|Change Management (ECR, ECO)||POOR||EXCELLENT||Managing frequent product changes in spreadsheets is challenging for products of any complexity, especially when these changes ripple up and down the product structure or across multiple product lines. BOM management software solutions are designed to control and track every change in the product and keep track of using assemblies, environmental compliance issues, approved manufacturers, vendors, and more.|
|Security||POOR||EXCELLENT||Spreadsheets can be protected, but the data can be edited and modified easily, putting your intellectual property (IP) at risk. BOM management software applications ensure real-time sharing without losing control.|
|Compliance Management/ Audit Support||POOR||GOOD||With Excel or Google Sheets, 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. Furthermore, they can tie to electronic component databases like Octopart or SiliconExpert, bringing key compliance and material availability directly to the user.|
BOM Management Tips—Spreadsheets and Beyond
You may feel that you need to stick with Excel or Google Sheets for a little while longer, but consider your company’s needs to drive product development and share product designs (BOMs and related information) anytime and anywhere around the world. There are a few simple things you can do to improve your current processes as well as prepare you for moving away from antiquated spreadsheet-based BOMs to a modern cloud-based bill of materials management system.
Keep your BOMs consistently formatted
When transferring your assembly information from spreadsheets to a BOM software management system, the information can be transferred based on the rules that you set. When your BOMs are inconsistently formatted, then the information transfer can be easily configured in software to bring it over in a consistent format. For example, import rules can be set up to place information from ‘Part_No’ cells on your spreadsheets into the new system’s ‘Part Number’ slot. A person 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 spreadsheets then a rule only needs to be set up once. If the name of a column varies across spreadsheets (e.g., Prt#, Part_No, Part Num), then each field name must be independently set to be associated with the part number in the new system.
But consistency goes beyond column names, it includes worksheet tab names, tab order, manufacturer names, part descriptions, filenames, columns on the BOM, and more. When importing BOMs into a new system, they go through matching algorithms at various steps in the process. For example, one BOM spreadsheet references the manufacturer name ‘Texas Instruments’ and another BOM spreadsheet calls out ‘TI,’ then duplicate entries for the manufacturer name are likely to be created.
Consistency clearly helps when you choose to migrate to a BOM management system, but it also helps your employees and suppliers now. When all the 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 differently, then each new BOM spreadsheet must be interpreted and interpretation of data always creates opportunities for data errors.
Use standard Spreadsheet BOM templates
Leveraging a template to standardize your bill of materials is a great start. When creating BOMs, it is good practice to hide the columns that you don’t need instead of deleting them. When conducting analysis on BOM information such as cost or reference designators—consider creating separate spreadsheets in lieu of adding non-standard columns.
Each column should include a header that clearly labels every piece of information in the BOM. To prevent confusion, ensure that the information in each column corresponds to the actual label and avoid assigning columns a dual purpose such as ‘REF DES & BOM Notes.’
If you need to include general assembly information such as dates at the top of a worksheet before the start of the BOM, ensure that this information is also clearly labeled. This will enable teams to distinguish a ‘revision approval date’ from a ‘file last edited date.’
Establish simple or non-intelligent part numbering conventions to avoid costly issues
Part numbering and naming conventions help ensure regularity across all bills of materials. When using spreadsheets, a good way to store this information is in a master parts list or item master that can be accessed by everyone. Generating part numbers from a central location greatly reduces the likelihood of creating duplicate part numbers. When standard naming conventions are established, engineering teams can easily find and reuse existing components and eliminate confusion that often leads to the creation of duplicate parts. There are many pros and cons to consider with part number schemes, so check out our ebook to help leverage best practices.
Minimize repeated data
When a part is used in more than one product, it 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 requires to plan and build a given assembly. Having additional details repeated in multiple locations adds complexity and increases the chances that dynamic information will need to change—causing confusion, investigation, and reconciliation. For example, does each manufacturer or alternate manufacturer for a part need to be listed on every BOM where the part is used? Details like that should be kept in a single location, like the item master (part record), so that any changes only need to be made in one place as the item or part is referenced in multiple assemblies.
So, if you continue to use spreadsheets, consider these suggestions to create a solid foundation. This will help you now and in the future when you are ready to scale and ramp production using dispersed teams and supply chains. Then, when you’re ready to switch to a BOM management software solution that is the foundation of cloud-based product lifecycle management (PLM) and quality management system (QMS) solutions—you will be in good shape to make the transition and ease the migration of your part and assembly information.
Is it time to break up with spreadsheets? We think it worth considering the value of a modern BOM management software solution like Arena PLM or QMS.