BOM management strategies: 10 ways to get your Excel BOMs ready for a fresh start
Manufacturers around the world rely on Excel for managing their bills of materials (BOMs) and other product data. From working with many of these companies, we’ve learned a lot about how to get the most out of using Excel for BOM management, and we’ve developed a number of recommendations on the topic, including the four strategies and ten tips outlined in recent posts.
Over time, however, many companies outgrow Excel. Whether it’s because their product team is larger and more dispersed or they’ve decided to outsource manufacturing or the complexity of their product offerings has increased, the end result is the same: They need more control over their product data than Excel can provide. When that time comes, these companies often make the switch to a BOM management system (of which Arena BOMControl is an example)—and they get the best results if they can start using the system with clean data in it.
One manufacturer we know got tired of how much money was wasted using valuable engineering and operations resources to identify and fix spreadsheet BOM problems. And the company could no longer absorb the scrap and rework costs that resulted when faulty BOMs got sent to the contract manufacturer anyway. The final straw came when the company’s Excel guru quit: Manufacturing slowed to a crawl because no one could navigate the complex Excel BOM spreadsheets—and management finally had enough.
The story concludes with two sets of suggestions, one to help companies prepare their bills of materials and product data for the move out of Excel and the other to help get the transfer off to a smooth start. Both sets would be useful for any company thinking of making a similar move. (And even companies that expect to keep using Excel for a while can find helpful advice in the first set of tips.)
The story also offers a good look at some of the signs that may appear when a company has outgrown Excel. Here are the lessons to take away:
Phase 1: Get your house in order
- Always enter supplier names in exactly the same format. Your computer does not know that “AVX Corp.” in your master supplier list is the same part source as “AVX.” Establishing a single convention for naming avoids confusion and mitigates the need for data cleanup at some later date.
- Enforce data consistency with validation lists. These dropdown lists offer spreadsheet users a restricted menu of data they can enter into cells.
- Avoid fancy colored text or cells. They add non-transferable complexity to your data conversion, which, in turn, risks data loss and manual data entry errors. Instead, use a separate column to draw attention to important information.
- Use the “text” setting for your columns and cells. This prevents unwanted data truncation, like the loss of leading zeros, and reduces the likelihood of conversion errors like the interpretation of part numbers as dates.
- If a part number remains to be determined, leave it blank. Do not insert placeholder values. Placeholders never die. Unexpectedly one day, a mock entry will be interpreted as a valid value and everything will get mucked up.
Phase 2: Get ready for moving day
- Only prepare live data for conversion. Archived data can remain in Excel for posterity or for conversion at a later date.
- Prepare your smallest dataset for conversion first. This will build up your understanding of the process and make it easier to uncover and repair any kinks in your current data entry procedures. Document what you discover and how you addressed the problems to create a guide for converting your next dataset.
- Review and repair the data to be converted. Ensure that it uses consistent standards for part naming and descriptions so you can avoid reproducing problems with bad data in your new system.
- Make sure that reference designators are clearly separated. Use commas as separators to ensure proper conversion.
- Do a final check of your data from the point of view of a computer. Make sure all values are entered consistently and labeled clearly, and avoid relying on visual cues (like tabbed indentions) to convey important details.