Microsoft Office and its cloud-based cousins like Google Docs are extremely versatile tools for writing white papers, creating charts and performing dozens of other everyday business tasks. But in the 21st century, manufacturing and design organizations that use these 20th century, general-purpose applications as the core of their complicated bills of materials (BoM) undoubtedly find themselves eating the dust of their better equipped competitors.
There are more appropriate solutions on the market specifically designed to connect to anyone, anywhere via the cloud, collaborate with hundreds of current and potential suppliers, embed quality information directly within the product record, and enable nearly anyone within the organization to flag a quality issue for engineers and suppliers. No longer should the BoM, which contains critical product records and quality process issues with corrective/preventive actions, be stored in a combination of Office files on the corporate server.
Why Is Product Design Using Excel at All?
The simple fact of the matter is that most startups begin with their product BoM in Excel spreadsheets. After all, Excel and Google Docs are well-known, flexible spreadsheet applications that are easy to use, and either free or very inexpensive. Many small companies made it through the first release of their initial product using Excel to manage product data.
When organizations start to ramp production volume and release more products, grow internally, or add more manufacturing and outsourcing partners, however, the act of managing an Excel BoM becomes much more cumbersome and complex. Throw quality management into the mix and the problem only compounds. The seemingly simple problems that occur when using basic business productivity tools to manage the BoM can magnify and impact a growing business in devastating ways when least expected.
Ironically, organizations often try to solve this problem by adding complexity into their Excel-based BOMs. They use tactics like item master tabs, lookup formulas, cross-referenced spreadsheets and Visual Basic programming—especially if they have an Excel guru on staff. This works as long as the Excel master keeps the connections up to date so cells are populated correctly. But it makes this person a single point of failure in an intricate web of files. The BoM management process lives solely in his or her head and on his or her computer, and crucial information is buried in the details of an unknown number of hidden tabs on countless spreadsheets.
Purchasing becomes problematic when two or more complex products share components. Purchasing agents can negotiate better volume pricing if they know which components are used in more than one product and are able to calculate the higher inventory requirements. But searching through an Excel BoM to identify common part numbers is tedious and slow. The task becomes more complicated if a component was assigned different part numbers in different places, which is a common problem when no easily accessible master parts list exists.
The Problem of Quality
Another significant problem with spreadsheet-based BoMs is the inability to integrate quality management, a problem that often persists even after the organization deployed a product lifecycle management (PLM) system for its BoM. Quality remains a silo managed by a growing team of people who, unfortunately, do not often communicate effectively with engineering.
So, because quality issues are not represented within the BoM where the product design lives but instead somewhere else—a file cabinet or a separate quality management system (QMS)—even if issues are resolved, there’s a good chance these same issues show up again in the next product, costing the organization a great deal of unnecessary time and money, increasing costs, lowering margins, and ballooning product cycle times.
The Hidden Costs of a Spreadsheet-Based BoM
These common problems when using Excel to manage complex or multiple product BoMs generally cause pains that are all too familiar, leading to the scenario in which everyone quickly becomes bleary-eyed from spending hours looking over endless rows and columns of data, trying to make sure everything matches up correctly, before releasing the product (or products) to production. Far from being free, companies pay dearly for their haystack of spreadsheets when mistakes creep into the record and engineers spend hours searching for the needle-sized error that can ruin a product launch. If an engineering change order wasn’t communicated because there was no simple way for everyone to lookup the BoM, that failure could lead to a less than perfect customer experience … or worse.
Email or fax is often used to share spreadsheet-based product information (like the BoM, lists of changes and testing data) with project teams in diverse geographic locations. As noted above, over time, different revisions of spreadsheets may scatter throughout various desktops in different organizations. As a result, the sourcing team may order incorrect parts or the contract manufacturer (CM) may execute a wrong build. These mistakes lead to excess or obsolete inventory, as well as significant recall, scrap or rework, all of which directly impacts the bottom line.
But it doesn’t end there. Even after the first product is built, the product record continues to evolve—due to defect fixes, design improvements, part substitutions or supplier switches—until the product reaches end of life. The time spent to manually make and document changes, and fix mistakes throughout the lifecycle of a product may amount to a substantial delay in its shipment
With multiple teams inputting frequent changes, manual revision control and documentation processes can easily become overwhelming and expensive. It is difficult to track where change orders are in the change review process. Documentation specialists are required to spend large amounts of time filing product data, test information and change information. In many cases, these document control specialists must actively query the product development team for updates and route change orders by hand, using paper folders.
In either case, the change order process is inefficient and onerous. There is no “single version of the truth”—representing the latest product and change information— to which all project teams and management can consistently and confidently refer to throughout the lifecycle of a product.
Many companies have multiple products with layers of data that are interrelated, such as part data, mechanical files, electrical component sheets, bills of materials, the approved vendor list and costing data. In a manual spreadsheet environment, the associated data is updated and communicated separately for each product, yet must be kept in sync. For instance, BoMs and an item master are created and updated separately.
The inability of a company to manage information across multiple products limits its opportunities for categorization and part reuse. And when a supplier end-of-lifes an item, the company must find and change the item across multiple products that may contain it. In the case that engineering leverages a BoM from a similar earlier design as a starting point for new product development, a lack of visibility into supplier parts may result in the use of obsolete parts. The issue is only identified later in the design phase, resulting in potentially costly redesign and retesting efforts.
Next-Generation Quality Process Management
Many seasoned product development and manufacturing teams recognize the significant scope of today’s document control requirements and the severe limitations of spreadsheets in meeting their objectives. They fully recognize the need for a next-generation solution to replace inadequate manual spreadsheet tools.
Demonstrating strong market growth in the past few years, next-generation, cloud-based PLM solutions are effective tools that bring together the design, quality, compliance and manufacturing worlds. With these tools in hand, manufacturers need no longer worry about wasting time finding that needle in a haystack that could kill the company—it is readily available in an easy-to-use BoM. And with the efficiencies that come along with use of the right tools for the 21st century manufacturer, they also no longer worry about being left behind. Instead, their competitors are the ones eating dust.
John Papageorge is content director for Arena Solutions.