When it comes to engineering and manufacturing companies, Excel is usually quoted as one of the most widely used tools. I said it many times. If I will go back to my articles written a decade ago, I can find these – Why do I like my PLM Excel Spreadsheet? and PLM Excel Spreadsheet: From Odes to Woes.
Kevin Power of Tata Technologies article Do you use Excel for BOM? reminded me of my articles by publishing his analysis of how engineering and manufacturing organizations are using Excel to manage master Excel and what are good and bad things of using Excel for BOMs. Kevin is promising existing and proven technology to help companies to escape BOM Excels.
There is the technology that overcomes all the Bad and the Ugly. It exists today and is proven. The change to these systems may be difficult and potentially expensive but given the critical nature of an eBOM to a Product Development organization, it must be embraced!
PLM vendors are using “escape Excel” mantra for a very long time by advertising PLM products as a replacement of Excel. All because of the reasons I mentioned in my articles. I look forward to Kevin’s article.
Meantime I want to share some of my observations and practical experience of helping engineers and manufacturing companies to escape Excels and manage product information (including Bill of Materials) since I co-founded OpenBOM.
Excel Gaps as a Platform for Bill of Materials
There is one thing I agree with Kevin – Excel is perhaps one of the great inventions of the IT revolution. It is conceptually a simple tool but can be used in sophisticated applications. However, I can see 3 most important gaps that preventing Excel to be used to manage Bill of Materials data. To illustrate the problem, I will use my favorite Skateboard example (see picture below).
Let’s imagine a possible Excel to model this Bill of Materials:
1- Part (Item) information redundancy
If I create a spreadsheet with a skateboard Bill of Materials, you will realize that each line of the Excel will represent information about the part. It means if I use a particular screw in 3 different places (assemblies), the line with the information about the screw will be totally redundant in 3 places. Description, manufacturer, suppliers, cost, etc. All these attributes will become redundant in your spreadsheet.
2- Structure information redundancy
If I will create an assembly for my skateboard wheel and use it in multiple places, the parts included in this assembly will become redundant in the structure. So, each time, I will be using a wheel assembly in a current skateboard or other BOMs (eg. configurations of the skateboard), I will have to copy an entire structure of the wheel. The same is about updates.
3- No granularity in data management
Excel is a document that cannot be structured and separated into pieces. If despite problems I described in #1 and #2 above, I will decide to use Excel to manage BOM, I will have to place an entire Bill of Materials in Excel. Because I cannot separate assemblies from sub-assemblies.
During my professional life, I’ve seen so many techniques on how to overcome the problems I described above using Excel. However, the number of Excel-ware I’ve got exposed to literally skyrocketed during my recent years at OpenBOM as we’re helping to the engineering teams and manufacturing companies to escape Excel.
Here are a few things I’ve learned that I can share with you that will help you regardless if you decide to use OpenBOM or any other PLM or ERP software. I can summarize them as my three DOS and one DON’T.
When you use Excel to manage information of the product you create a conceptual model of the data in an Excel spreadsheet. You do it to overcome the limitations I mentioned above and many others. You’re making assumptions and create a flow to enter data, create calculations, make changes, validate against mistakes and finally do reports. I’ve seen this process many times and I was impressed each time I’ve seen engineers or IT people do it.
DON’T try to replicate this model with any other software you will use to replace Excel. The most instinctive and unfortunately wrong decision is to take your Excel and try to import it “as is” into a system that managing data about the product.
However, this is also a place where you will face the main challenge. It is about change. Regardless of how good or bad the Excel you use, it will be a change and this is the most complicated part. And this is a place where I’m coming with my 3 DOS.
1- Separate information about items, structure, and changes. There are 3 logical blocks – (1) information about items, (2) information about BOM structure and (3) the process and information about changes. Think about items as a database of everything you have at the house – standard parts, engineering assemblies, purchased assemblies, etc. You need to have a record of all these pieces of information combined with the identification of each item. It will become a solid foundation for your information model. BOM (or product structure) is another information block. It defines how items are combined into products. The same item can be used in multiple products and to manage these structures is really important. Finally, you need to create a logical schema of how to change items, structures, and related information.
2- Define interoperability with engineering applications. Identify how logical blocks of information (items, BOM, changes) will be intertwined and connected to other engineering software and applications. This is really important because it will save time in entering data and also will help you to identify data management constraints. CAD application is usually the first suspect in this list, but others will come as well. As products, today are more complex than ever, think about everything – electronics, software, systems, etc. Simulation is extremely important, don’t forget about it. The biggest mistakes and difficulties in BOM is when data is changing in engineering applications and it requires re-entering of data manually.
3- Create value downstream. Plan how the BOM (or product information) model and data can be used downstream. This is one of the places where the value of your BOM model will be created. Product information is consumed in so many ways in modern manufacturing. And mistakes from wrong product information use downstream are the most expensive.
What is my conclusion? A good escape plan must be planned. The reality of Excel used in engineering and manufacturing is complex. Data is created for years, scripts and applications written by people that left a long time ago and sometimes logic is hard to get. For all companies, I’ve been helping to, it was a journey to find a way how to escape Excels and to move in the data management application (eg. PLM, ERP or special tool). I’m happy to talk to any manufacturing organization that looking at how to escape Excel-ware and help them to find their path from Excel to a better way to manage Bill of Materials and related product information. Just my thoughts…
Best, Oleg
Disclaimer: I’m co-founder and CEO of OpenBOM developing cloud-based bill of materials and inventory management tool for manufacturing companies, hardware startups, and supply chain. My opinion can be unintentionally biased.
Pingback: GHZ Partners | IT Services, Software()