Archives for "Arena"

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

1. 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.
2. Enforce data consistency with validation lists. These dropdown lists offer spreadsheet users a restricted menu of data they can enter into cells.
3. 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.
4. 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.
5. 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

1. Only prepare live data for conversion. Archived data can remain in Excel for posterity or for conversion at a later date.
2. 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.
3. 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.
4. Make sure that reference designators are clearly separated. Use commas as separators to ensure proper conversion.
5. 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

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:

1. Keep it simple. Use a simplified, predetermined set of numbers to identify vendors and parts.
2. Take care with your conventions. Excel can interpret numeric strings as dates, which can wipe out your data in a flash.
3. 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.
4. 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.
5. Avoid hidden worksheets. While hidden worksheets provide a measure of protection, formulas remain active and anyone can reveal and edit an unprotected worksheet.
6. 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.
7. 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.
8. 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.
9. Keep good records. Maintain a separate log file for auditing, and keep separate digital and physical records for compliance.
10. 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