Why “Excel as a Process” Is Holding Back Your Production and 3 Steps To Fix It

If you’re in the manufacturing business, you understand the importance of having BOM in place. Whether it’s accounting for raw materials used, cost tracking, or production optimization purposes – BOMs provide you with important information on their process. When it comes to the organization of the Bill of Materials and the data management to manage it, the idea of having Excel comes very often because of the extreme flexibility and portability of spreadsheets. Unfortunately, as much as spreadsheets feel simple from the start, they can lead you to more problems later on.

Let me bring you a few examples of how your business can go wrong with inefficient BOM management when relying on Excel and what you should look for when choosing a system that works best for your operations.

From CAD Design to Components Purchasing

The two fundamental processes in every company are engineering, which includes the design process done using CAD software, and the procurement process, which requires organization and management of vendors, component stock, RFQs, and POs. In an ideal form, the two processes are connected. The outcome of the engineering activities should provide information about what components need to be purchased and on the other side, procurement services communicate back to engineers when some of the components are not available, discussing alternate options, vendors, costs, and others.

The reality in many companies is to have these processes run by separate people and a company looking for a way to communicate between them. Excel simplicity and ability to export them from CAD, companies pulling the process – getting BOM exports, creating PDF and STEP files from design, placing them together, and emailing them to procurement or placing them on a shared network (or cloud disk) drive.

The complexity of the process I just described above is skyrocketing immediately already after one cycle of communication. Moving forward, companies are trying to organize a “master Excel”, creating multiple BOM spreadsheets and keeping both design and procurement information up to speed. Multiple copies of the files, not updated information about cost, vendor, description, lead time, etc. – the result of this process is one giant mess of information that requires manual handling, updates, and control.

3 Steps To Improve Process Efficiency

There is a better way to organize business processes in the organization. It starts with the creation of a single source of truth about product data. Your end goal is to organize items and hierarchical data structures to support your design, supply chain management, and manufacturing processes. Here are three elements of the data management process:

Item Records

Everything in the engineering and manufacturing world is an item. An item can be a simple part, a custom sub-assembly, or an entire product. Creating item definitions is extremely important. Adding all information about the item will make it the center of your intelligent data management solution – to know everything about the item – cost, manufacturers, suppliers, lead time, specs, regulated requirements, etc. An item record keeps all revisions of the item, all files, and everything else you need. This is a single source of truth about everything.

Product Structures (BOM)s

Building BOMs using items is simply because it allows you to pick and choose items to put them into a BOM by only specifying a Part Number and placing a Quantity. It can be a fully manual process, but it is very important to make it collaborative. No single person in an organization can define all elements of the product. Mechanical, electronics, system, maintenance, sales – everyone needs to be involved in the process to ensure nothing is forgotten. Having people collaborate is one of the most essential capabilities to ensure the product structure is well-defined.

Data handover (from CAD systems to PO records)

It is very easy to make a mistake when you need to manually enter the data in Item definition and product structure. Capturing information automatically from CAD (and other design sources such as catalogs of standard OTS components) can simplify the process. Manually creating orders by retyping information from CAD is another place where you can introduce mistakes. But automating this process by the creation of orders directly from the product structure (Bill of Materials) can save time and eliminate mistakes.

Conclusion

“Excel As A Process” will fail your BOM management and production process. Was proven by thousands of engineers that loved Excel’s flexibility and portability. Nevertheless, happiness doesn’t stay longer. Eventually, even the best “master” spreadsheet will not allow you to build product structures (BOMs) without creating multiple copies of Item Master records. Having copies will bring data discrepancies and it will be only a matter of time until the entire “Excel-ware” will collapse.

You should keep in mind three important things that can create a groundwork for any data-driven BOM process: (1) Item Records; (2) Product Structures (BOMs); (3) seamless data handover between systems and data connections.

Once you implement these three fundamentals, your life will become much simpler and easier. You won’t need to run after all excel files, you will stop asking questions about where you use any specific item and how to get any specific revision. Finally, getting data seamlessly transferred, linked, and connected is a big deal.

Now turn around and check your environment. To be honest, how many Excels (or other forms of spreadsheets) do you have there? I’m interested to learn what is the number that you got for me. Register for free to use OpenBOM and experiment with the process of building item catalogs, and BOMs, managing RFQs and POs as well as generating the right purchase orders for your contractors, and suppliers.

Best, Oleg