Archives for "Bill of Materials (BOM) Management"
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 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 of this company, which did ultimately make the switch to a BOM management system, was recently published (in fictionalized form) on our website. 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. Read the full story here, or skip straight to the recommendations below:
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.
More resources:
An article on using Excel for bill of materials management
Downloadable Excel BOM templates
Arena Dispatches: The Excel Series
BOM management strategies: Four recommendations for better Excel BOMs
BOM management strategies: 10 more tips for better Excel BOMs
BOM management strategies: 10 more tips for better Excel BOMs
Most Arena customers make the move to Arena from Excel. In other words, they manage their bills of materials (BOMs) in Excel until they start managing their BOMs in Arena. We help these companies get their data into Arena, so we’ve seen a LOT (tens, maybe even hundreds, of thousands) of Excel BOMs over the years and learned a great deal about what works best when it comes to managing BOMs and other product data in Excel.
We shared four strategies for better Excel BOM management in a recent post. Here are ten more tips, which we recently published in conjunction with a fictionalized account of a real company that relies on an intricate web of Excel spreadsheets to manage its product data.
Read the backstory if you’re interested, or just get the tips here:
- Keep it simple. Use a simplified, predetermined set of numbers to identify vendors and parts.
- Take care with your conventions. Excel can interpret numeric strings as dates, which can wipe out your data in a flash.
- Secure your data. Minimize the number of people authorized to make changes to a spreadsheet, use locked workbooks, protect vital data with passwords, make sure shared files are read-only and back up your data frequently.
- Watch out when importing data. Excel makes it too easy to overwrite data, so avoid importing data into Excel unless you can guarantee that the source file and destination spreadsheet template are 100% identical—even if you have to enter data twice instead.
- Avoid hidden worksheets. While hidden worksheets provide a measure of protection, formulas remain active and anyone can reveal and edit an unprotected worksheet.
- Sort all your data. Always select the entire spreadsheet when sorting, because that’s the only way to ensure that you’ve included every cell.
- Don’t spread your data too far. Spreading related data across too many Excel worksheets or workbooks makes analyzing data and generating reports more difficult.
- Check yourself. Whenever you modify a spreadsheet, generate a BOM or enter data, inspect and verify your work carefully before you release it to others.
- Keep good records. Maintain a separate log file for auditing, and keep separate digital and physical records for compliance.
- Plan ahead. Lay the groundwork now for expansion to other tools such as BOM management and ERP by standardizing your Excel spreadsheets and using import-friendly templates whenever you can.
Watch for a follow-up post with recommendations that will help when it’s time to make a move: 10 tips for preparing your BOMs and product data to be transferred out of Excel. In the meantime, here are some additional resources:
An article on using Excel for bill of materials management
Downloadable Excel BOM templates
Arena Dispatches: The Excel Series
BOM management strategies: Four recommendations for better Excel BOMs
BOM management strategies: Four recommendations for better Excel BOMs
We recently posted a new article on our website that has a number of useful suggestions for companies that use Excel to manage their bills of materials (BOMs). Following these recommendations will set you up for a faster, smoother and cleaner transition when you’re ready to move to an automated BOM management system (yes, full disclosure, like Arena), but they’ll also help you NOW—and for as long as you continue to use Excel for BOM management.
Here they are at a high level:
- Be consistent. Use the same columns in the same order in every Excel bill of materials. Use a standard format for part numbers, manufacturer names, file titles and other types of data.
- Use standard templates. Get in the habit of hiding (not deleting) columns that aren’t needed in a particular BOM and creating separate spreadsheets for doing analyses that require additional columns. Give each column a single purpose, and label every piece of data in your Excel BOM spreadsheet.
- Have part numbering and part naming conventions – and a single location to store them. Develop and document a standard way to number and name ALL parts, and then manage those part numbers and names in a single location, like an item master or master parts list.
- Minimize repeated data. Include only as much data as is needed for each BOM to perform its core function of capturing the relationships between parts and assemblies. Store additional part data in the item master instead of multiple Excel BOMs, so updates only need to be made in one place.
Following these suggestions will help reduce confusion, duplication and errors in your Excel spreadsheet BOMs. For more details, read the full article and be sure to look for the free downloads at the end – you’ll find several Excel BOM templates and examples that can be adapted for your organization’s use.
Further reading:
Names have been changed…another story from the front lines of manufacturing
If you’re reading this blog, you most likely know the vital importance—and enormous challenge—of good revision control when designing and manufacturing products. Lost in the Woods: The three things you should do to prevent revision control failures is the second installment of the Product Disaster Series and can be found in the Arena Dispatches, where we change names to protect the innocent and retell the classic “…and then something went very, very wrong” stories of our collective past.
Lost in the Woods is an “if only…” story of a company that’s forced to scrap the big roll-out of an extremely promising product redesign when a critical component of the product can’t be sourced in time. Stunned by the loss of so many expected sales and accolades, the company is painfully reminded that true revision control takes more than a spreadsheet bill of materials (BOM) on a server and verbal engineering change notifications (ECNs)—and that even a single discrepancy in the version of the BOM that’s sent to a contract manufacturer (CM) can be disastrous.
Once again, it’s a quick, easy read and a good reminder of why all your hard work to ensure good revision control is worth the effort!
How do you explain a bill of materials?
If you’re like me, this holiday season you spent a lot of time at parties answering the inevitable “so what do you do?” question. Here at Arena we make a software tool for collaboratively managing BOMs and changes and sharing product information with the supply chain. While that’s simple to understand for someone who knows manufacturing, it’s not obvious to someone who doesn’t. When I break it down and start explaining that our software manages bills of materials, the next step of course is to define what BOMs are. And for that, I always try to have a good analogy on hand.
Over the years I’ve tried out many iterations of the BOM analogy but my favorite at the moment is the bill of materials as recipe and shopping list for a manufactured product. The BOM lists the quantity of the parts that go into making the product (the shopping list) and how the parts go together to make up the product’s major components (the recipe). Some people get it, but most give me a blank stare and nod their heads pretending to understand.
So help me out…do you have a better analogy? How do you explain the concept of a bill of materials to people who don’t work in manufacturing?
Names have been changed to protect the innocent — stories from the front lines of manufacturing
Every product company has a story–one that makes you cringe just thinking about it–where something went wrong…really, really wrong. A situation that could have been avoided if only….
At Arena we hear these stories all the time. They wrench our gut and remind us of the days when we worked in manufacturing.
We’ve taken these stories, yours and ours, changed the names, products and companies to protect the innocent, and retold them. The first one is posted in the new Arena Dispatches section of our website. It’s called Up in Smoke: The 3 things you need to know about managing product change.
Up in Smoke tells the story of a toy manufacturer trying to understand how its blockbuster franchise could literally go down in flames at the height of its popularity. It’s a cautionary tale about the perils of relying on spreadsheets and manual processes to manage bills of materials (BOMs), engineering change orders (ECOs) and the new product development (NPD) process.
Though it may bring back a painful memory or two, it’s also a quick, easy read, so check it out!
Subscribe to the Arena Blog
Follow us on Twitter
Like us on Facebook
Read the Arena Dispatches