Are You Limiting Your Business With Excel For BOM Management
Abacus Games isn’t a real company, but for the sake of debate, let’s pretend they are. Let’s also pretend that they’re a manufacturerof children’s toys with a STEM focus. These toys include a certain amount of electronics, so they’re more than just stackable blocks or volcano kits, and thus the bills of materials needed to create them are quite complex. Abacus is looking to develop a number of new products related to space travel, hoping to get young people excited about the private space revolution thanks to companies like SpaceX and Blue Origin.
And while Abacus would like to upgrade to a fully-fledged product lifecycle management (PLM) system, they don’t know if they could achieve a return on investment (ROI) in the near future. They’re sticking with Excel, which is essentially free, but comes with some challenges.
What Abacus wants: Their in-house Excel expert will be responsible for constructing the BOM, using more complex features like Visual Basic programming, lookup formulas, and more. This way, they will have a way to quickly add items to the BOM, thanks to a non-intelligent part numbering scheme that is managed by theVB programming.
What actually happens: The spreadsheet is created according to Abacus’ desire, but other staff find it difficult to work with due to the number of columns and technical issues in entering data. The Excel expert thus becomes the sole proprietor of the spreadsheet, and is responsible for its well-being.
The limitation: Excel allows for a great deal of complexity, but developing ways to get visibility in the ways that matter to a company require one-off solutions that aren’t easily translatable to others.
What Abacus wants: By placing the Excel spreadsheet on a shared intranet drive, they hope that multiple stakeholders will use that as their home base as they make decisions about the in-development products.
What actually happens: One engineer ends up copying the spreadsheet to their own hard drive for edits, with the intention of copying the changes back into the master file at the end of the day.
The limitation:Because Excel spreadsheets can’t handle multiple contributors editing it at once, the file must be locked when another collaborator has it open. Because of this, stakeholders come up with workarounds to deal with the limitation, which leads to poor results—as one might guess, that engineer forgets to copy the changes back over at the end of the day, leading to an unsynchronized BOM.
What Abacus wants: Whenever an engineer wants to create an engineering change request (ECR), they are supposed to edit the spreadsheet, do research into what other assemblies their change affects, and notify those people.
What actually happens: The spreadsheet might be locked, or the engineer might forget to send that notification email. Even if those processes work correctly, spreadsheets don’t inherently handle revision control, which means that engineers keep change documentation in additional columns.
The limitation: Excel doesn’t have the capacity to automatically notify stakeholders of an change that affects the part, assembly, or product area they’re responsible for, which means that they often get left out of the loop, which causes conflict down the road when two stakeholders have opposing ideas about the status of a sub assembly.
What Abacus wants: The Excel BOM will be sent to a contract manufacturer (CM) that creates the electronics used inside of some of the products. Since the BOM has all the information, it’s assumed the CM will be able to produce the number of circuit boards exactly to Abacus’ needs.
What actually happens: The CM finds the spreadsheet daunting for its organizational complexity, and the fact that it’s the full BOM, and not just the assemblies that are relevant to them.On top of that, it turns out that because Abacus sent two BOMs—one on August 19th and a revised one on August 25th—the CM has accidentally been working with the August 19th file.
The limitation: Excel doesn’t inherently offer a hierarchical solution, which can build assemblies into categories all their own for easy sharing with CMs, staff has to spend more time on managing the supplier relationship than doing work that matters. And because there’s no single repository for all the BOM data that can be accessed securely from both within and outside the organization, versions can be mixed up.
The truth about Excel is that in many cases, a product development lifecycle based around it can go perfectly well—none of the “actually happens” listed here would necessarily lead to catastrophic failure of new product introduction. The actual cost of these limitations can’t really be calculated, but we all know that time is money, and any time spent managing or fixing Excel-related mix-ups is time spent away from getting the product out the door as fast as possible. Companies shouldn’t automatically limit themselves—better BOM management means that they’re only limited by their talent,budgets, and desire to make better products.