Top Pros And Cons Of Using Spreadsheets To Collaborate in BOM Management

If you’re working on product design, planning and manufacturing, collaboration is one of the key requirements. 

Products are complex these days and contain many intricate components such as mechanical, electronics and software. The teams that work on these products are distributed and work around the clock. 

How do you survive in this environment making sure you keep up to speed and stay on the same page as your coworker? Most importantly, how do you keep the data in a way so that everyone can access it without looking for the last message or email? 

New Tools That Have Helped Along The Way

The recent wave of technological advancements for collaboration brought so many new and improved tools to your table. We are Zooming and Slacking with our teammates, but when it comes to sharing information things are still left to files that do not integrate with your design tools. 

Companies are developing new collaboration tools reports showing huge growth. Here is one of them coming from Microsoft – Microsoft Teams usage jumps to 145 million daily active users. 

This similar growth is happening with other tools such as Slack. The following article speaks about the growth of Teams within the Slack ecosystem. The growth was driven by an immediate necessity. Teams were thrown out of the water of their daily office work and had nothing else to do, but start using digital tools. 

As much as Team and Zoom solved the problem of communication, the data problem is still there. If you’re a mechanical engineer working with your team and need to connect and communicate with purchasing people, production planner, cost estimators and contract manufacturer, you will understand the importance of having an accurate BOM and all the data at the tips of your fingertips as well as those of your coworkers.  

When there is no tool, Excel or Google Spreadsheet is always a second best choice. Engineers love Excel because of their independence and their flexibility, so when they need to share data online Google Spreadsheets comes as a fast solution to share data. 

However, here is the thing… what is good for a spreadsheet, might not be so good for product information such as product structure and BOMs. 

5 pros and cons of working with Google Sheets

Here are my 5 pros and cons of working with Google Sheets to manage and share Bill of Materials. 

1- Isolated data

The data is isolated in a spreadsheet instead of being connected. Items in product structure are used in multiple places, but in Google Spreadsheets, you will create duplicates between multiple sheets. So, you won’t have a single place to change Item cost, Description, supplier, etc.  

OpenBOM catalog on the other hand is a single source of truth and it is as flexible as Google Spreadsheet. With one small difference – when you change the data in the OpenBOM catalog, it is immediately updated in all BOM using this catalog. 

2- Structures 

This is where complications really start to get worse. Product structure is hierarchical and that is how it should be. Also, it is modular and multiple pieces of structure are re-used in different places. 

Using Google Sheets, you can visually create an indented structure, but won’t know anything about it. So, when you need to re-use modules and sub-branches, you will need to copy (again) and make mistakes.  OpenBOM automatically creates a product structure and manages “Where used” relationships. 

3- Calculations and Rollup 

When you need to calculate cost, stock or make an assessment about the product total mass, you need to write a formula. You can do formulas in Excel or Google spreadsheets, but because of the reasons I mentioned above, they will either have wrong data (copy mistake) or won’t be able to figure out Parent-Child relationships for rollups. 

You can create formulas in Excel, but you will quickly need to become a Chief Excel Officer to manage this stuff. OpenBOM has an embedded formula mechanism capable of calculating rollups and many other things based on the consistency of data (catalogs/BOMs). 

4- Visuals information 

A picture is worth a thousand words and there is a good reason for that. Don’t even dream of inserting an image to Excel, but you can do it in Google sheet. This sounds like something that can work, until you realize that you need to do it for 500 images in your BOM. This is the moment you start thinking about some automation. And this is what OpenBOM add-ins for CAD systems do – extract data automatically and place them in OpenBOM catalogs. 

5- Consistent Data Sharing. 

Office 365 and Google Sheet allows you to share data with anybody. Sounds like a super useful and easy feature. Indeed it is super simple, but only until you need to share a structured set of information and you want to limit it to a specific subset. Though you can hide columns in Google Spreadsheets, it is a royal pain to do so.  Some people will be looking for hidden columns and others will wonder why they need to see a specific column. At OpenBOM, a user-defined view is the way to go. You can define the data views and assign them consistently to a group of people. 

In my article earlier this week, I shared the story of how Solidworks customers can escape Excel madness. Check this out here. This is the passage from our customer Garrett Rini of FlyPyka, speaking about the comparison between OpenBOM and Google Sheet. Here is the article.

“FlyPyka relies on OpenBOM for Solidworks integration to reliably capture product structure and design intent. Our airplanes have lots of parts and assemblies to keep track of… 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.”

Conclusion 

OpenBOM’s flexible data management in the cloud is the ultimate solution to help engineers and manufacturing companies capture information from multiple engineering sources, recombine with important business information and set up an intuitive and collaborative environment for an entire team, contractors and suppliers to work together. This is the new standard for collaboration in engineering and manufacturing companies. 

Check out how OpenBOM can help you – Register for FREE and start your 14-day trial today! 

Best, Oleg