3 Steps To Take Your BOM Strategy Beyond Excel

Here is the story, I’m hearing every day when I talk to OpenBOM prospects. Usually, the dialog starts with how the company is managing its processes and data. For most companies, it is about designing or assembling the products. Very quickly we come to the point that the company is heavily relying on the data that is located in multiple spreadsheets for different purposes. 

Five Sins of Running A Company Using Spreadsheets

Spreadsheets are quick and easy. Therefore, companies usually start with them to manage and store any information they need. Here are 5 different types of spreadsheets I typically see in every company. 

1- All Items Spreadsheet 

This is a spreadsheet that normally mimics the database to store everything they buy and make. It could be separated into multiple spreadsheets tabs, but usually, it holds some mechanism to generate or source Part Numbers. In the absence of a better solution, the company treats this Excel as a master database. If something happens to this spreadsheet, the company can experience a big shock. 

2- My Product Spreadsheet 

Most companies are building products and it is extremely important to create some sort of digital representation of what is included. This is how the idea of a “product spreadsheet” usually starts. Pulling information from everywhere in a single spreadsheet is a typical activity. This brings multiple problems – redundancy between products and “All items” spreadsheets, inability to manage structured (indented) BOM, and mistakes that are happening when information needs to be copied from one “product spreadsheet” to another. 

3- Purchase List Spreadsheet 

Eventually, things come together and your company must purchase components, create POs, and manage a procurement process. The last thing the procurement department wants is to have somebody stepping over their data when they talk to vendors and suppliers. So, they create a master purchasing list. The biggest problem here is the need to ensure the data completeness and accuracy. Copy from product spreadsheet usually is the biggest contributor to mistakes. 

4- Calculation and Analysis Spreadsheet 

I have never seen a single company that is not interested in product cost. It is one of the major calculations that every company needs to perform based on the up to date information about product BOM. Some information (eg. unit cost) has to come from other places (eg. purchasing). So, companies are creating another “master” by pulling data together and calculating. Besides the problem of data copy (another source of mistakes), there is a real difficulty to run a rollup cost using a spreadsheet. 

5- Production Spreadsheet 

When it comes to an assembly process, there is a need to prepare work orders and also to put information about components and how to assemble them together. Having a spreadsheet is an easy goal and companies are starting to do so very quickly but eventually they realize the complexity of data relationships and updates. 

3 Steps To Stop Using Excel 

There are obvious benefits that come out of leaving Excel. Most companies understand that, but still – leaving Excel behind is a complex thing for them. At OpenBOM, we made the process easy and structured. It relies on a unique multi-tenant flexible data model that allows for the building of a data structure capable of managing reference-instance and product structure relationships. Here are 3 steps to perform to eliminate the 5 sins of spreadsheet guilt. 

1- Organize a single source of truth for all information. 

This can usually be achieved by organizing a system of catalog(s) in OpenBOM responsible for managing typical data (eg. mechanical, electric, standard, and other components; Product assemblies, software, non-mode items. Everything comes together. Nothing should be missed or not accounted for. 

2- Instance items into BOM to create a specific product record.

Building product structures from a database of items ensure not a single piece of data will be duplicated instead of being re-used. This is how it starts – pick the item and put it in the BOM record. OpenBOM gives you a flexible set of instance properties to create any specific instance data (starting from typical quantity and ending up with reference designators, effectivities, and other calculated values. The outcome is a single model, with no duplicates and the ability to make analyses, discover relationships, and many others. 

3- Sync engineering and production views to build work orders and purchase orders

While product information stored in catalogs and BOMs is an insurance that you work on a single source of the product records, to produce another structure to manage production plan and procurement is super essential. OpenBOM gives you an option to create an Order (planning) BOM and keep it in sync with the product BOM. So, the data in work orders and the data that is used in procurement is always up to date. 

Conclusion

To have a single source of information about the product is extremely important and companies that use Excel simple don’t have it. This is where it all starts – duplicated records, duplicated files, wrong values, mistakes during copy/paste, and many others. You should take steps to get rid of Excel sins and to establish a single source of truth for the item and product structure, get calculations and analysis to run on the same data sets, and keep production and procurement planning in sync. 

Want to try this strategy out on your data? Register for FREE to use OpenBOM and test it out for yourself. Start your 14-day trial and get access to all of OpenBOM’s premium features. 

Best, Oleg