5 Reasons Why BOM is More Complex Than a Spreadsheet and Why You Need to Appreciate It

Bill of Materials (BOM) management is an essential component of product development, manufacturing, and supply chain management processes. These processes involve a lot of data about design, systems, raw materials, and overall product lifecycle information to move between different people in the organizations. This is how you get your business processes organized. It starts from idea generation, moving down to design, product data management, manufacturing process, material requirements planning, quality management, inventory management, and other detailed planning. In all these processes BOMs are a lifeblood of the operations. It is data that connects everyone and creates the concept of product lifecycle management across multiple lifecycle stages.

It is important to get data management done right and to ensure that data is available for all stages of the development and product strategy including product management, predictive analytics, and business models. This is where the digital thread begins. This is a key component in an IT strategy to support all teams in your company, contractors, and suppliers. Seamlessly integrating the data is a very important requirement for IT and all other departments.

Unfortunately, this is a place where things often go wrong. Organizing information is hard and you can often see companies go to the so-called “lowest possible denominator” in information – Excel (or spreadsheets). While spreadsheets are widely used for various tasks, relying on them for product data and BOM management can significantly limit the efficiency and accuracy of managing product data.

In our work with customers, we often see companies that are coming to the point where they are really “stuck” with Excel or spreadsheet management and coming to the realization that they cannot run business processes using spreadsheets.

Here is an example of how it is happening. Read the story – FlyPyka is Replacing Google Spreadsheets with OpenBOM Managing One Source of Truth For Autonomous Electric Airplane Development. And this is a key passage:

Pyka Lead Mechanical Engineer Garrett Rini described the product’s evolution “Moving from prototyping to production manufacturing is really hard.  We needed to button down parts, assemblies, and procedures and pass that information along to the manufacturing teams.”

“Our airplanes have lots of parts and assemblies to keep track of across two distinct teams, Engineering and Manufacturing, and we need to be on the same BOM all of the time. Getting all of that info straight was a big problem and Google Sheets wasn’t going to get us there.”  Says Garrett.

“We were basically trying to build OpenBOM in Google Sheets,” he says. After a bit of research, OpenBOM for Teams was a perfect fit for this rapidly growing company. One thing that was attractive was the low barrier to taking us several steps further than Google Sheets.  We could import directly from Solidworks, an existing Google sheet, or just type in data. OpenBOM functions the way we would expect it to.” says Garrett.

So, let’s discuss why spreadsheets (a very robust and easy way to manage any data) cannot be good for BOMs and product data. I found the following five reasons why BOM is inherently more complex than a spreadsheet and why appreciating its complexity is crucial for your operations.

BOM is a Structure, Not Just a Flat List

At first glance, a BOM might resemble a simple list similar to what you can create in a spreadsheet. However, it is far more than that. A BOM represents a complex hierarchical structure that defines the product’s components, assemblies, and sub-assemblies, reflecting how a product is built from the ground up. This structure is critical for understanding the relationships between parts and ensuring that changes in one component are accurately reflected throughout the hierarchy.

BOM Maintains a Single Source of Truth

In the realm of product development and manufacturing, having a single source of truth for all component data is indispensable. Unlike spreadsheets, where information can be duplicated across multiple files leading to inconsistencies, a BOM ensures that all items share a single source of truth. This centralization of data prevents duplication of information, reduces errors, and ensures that everyone involved in the process has access to the most current and accurate data. To have an “item” definition that is used everywhere ensures that the data you use in all products and BOMs can be trusted. Otherwise, you will find yourself with 5 spreadsheets holding the different data. An even more complex thing is to manage revisions and lifecycle. While the idea to save an Excel copy as a “revision” is simple. it won’t hold the complexity of using multiple revisions in different BOMs and changes over time. As a business, you will find yourself at high risk of failure and exposure in front of customers and regulators.

“Where Used” Queries Are Essential

BOM and product structure management systems allow for “where used” queries, a feature not inherently available in spreadsheets. This capability enables users to quickly identify every product or assembly where a particular component is used. Such queries are crucial for impact analysis, especially when a component needs to be replaced or updated, ensuring that all affected products are identified and appropriately modified.

Need for Rollup Formulas Beyond Simple Summation

While spreadsheets are excellent for basic calculations, including a SUM function, BOM management requires more sophisticated rollup formulas. Think about cost, mass, sustainability, compliance, and many other complex criteria. It is not the results you can get by applying a SUM function to a column. Do you need to literally rollup the data between “levels”? OpenBOM formulas are designed to handle these complex calculations automatically, ensuring accurate costing and materials planning.

Attachment of Files Directly to the Item or BOM Instance Info

In product development, it’s common to have various files associated with components and assemblies, including CAD drawings, specifications, and certifications. Unlike spreadsheets, where managing these files often means storing them separately (e.g., in a ZIP file), BOM systems allow for direct attachment of these documents. This integration ensures that all relevant information is accessible from a single location, streamlining the design and manufacturing processes.

Conclusion

A Bill of Materials and product structure is much more than a list; it represents the entire set of information of a product. While spreadsheets may seem like a familiar and easy tool for managing product data, they fall short in addressing the complexity and dynamic nature of BOMs. 

By relying on specialized BOM management and PLM tools like OpenBOM, companies can avoid the pitfalls of spreadsheets, such as additional costs, late deliveries, incorrect components, and missed functions. 

In essence, the right approach to BOM management is not just about avoiding mistakes; it’s about building a foundation for efficiency, accuracy, and success in product development and manufacturing.

REGISTER FOR FREE and try OpenBOM for 14 days to explore how OpenBOM can help you.

Best, Oleg