Replace your Excel BOM spreadsheets with real bill of materials software
Are you using Excel BOM spreadsheets for bill of material management?
In smaller organizations, perhaps the most-used tool for
bill of material management is Microsoft's Excel spreadsheet
software. It's a commonly-available application that is
simple to use, has good sorting capabilities and easily
works alongside other business applications.
Unfortunately,
Excel bill of material spreadsheets cannot
enforce product data consistency: one cell is treated pretty
much the same as any other cell, so there's no easy way to
discover that, say, one part number has been incorrectly
associated with the wrong description, quantity or reference
designators. Each individual cell is a continuing source of
potential errors.
New companies often begin their path to product lifecycle
management software by looking for Excel bill of material
spreadsheets. These BOM spreadsheet templates are easy to
create, and can be useful for a short period. (We've even
created a
free Excel bill of material template & example.) After you have only a few dozen product
structure files, you'll begin to see the complexity and
expense of maintaining Excel BOM spreadsheets. Once you have
about 50 bill of materials spreadsheets, the risk of
expensive production errors begins to rise significantly.
10 challenges in using an Excel BOM spreadsheet
If you must use Excel spreadsheets for bill of materials
management, watch for these 10 common problems:
- Use caution when sorting. Since cells are not tied
together, sorting a list by, say, part Number or Type risks
mismatching adjacent cells. Any =SUM() that relies on rows
should also be checked immediately after the sorting
operation.
- Inspect each bill of material carefully after
every revision. You'll be entering the same data over and
over again, and any cell can be changed (even accidentally), So odds are good that a child part number or description on
one assembly may not match the same part on a different
assembly. Pasting the correct information into the wrong BOM
spreadsheet row (an "off by one" error) is
particularly common.
- Since changes to a part in one
spreadsheet don't get propagated to other BOM XLS files,
ensure you have access to every BOM spreadsheet when
revising a component. Each "where used" instance must be
discovered and updated. Take your time: searching
across spreadsheets to discover where a specific item is
used can be tedious, slow and error-prone (imagine updating the description
for a often-used screw or resistor).
- Keep track of
proprietary data, which may need to be cleaned before
sharing your BOM spreadsheet with your supply chain (e.g.,
do you want your new supplier to know what you pay your
current supplier?).
- Carefully verify that units of measure
are consistent (no mixing "each" and "gram") and calculated
to ensure that the cost roll-up is correct. If a wire length is
specified in millimeters, and purchased in meters, will
that conversion be noticed?
- When you decide to adopt a true
BOM database system, you'll find that spreadsheets with
little or no formatting will import easier than highly
formatted spreadsheets. This means that every step you take
to make the bill of material format more readable (and
easier to check) will work against you when the data is
moved into a real BOM database. (Our BOM spreadsheet
template may prove to be somewhat useful for importing into
an ERP or PLM database, but be careful about making
significant changes.)
- Avoid the temptation to adopt
so-called "intelligent" part numbers that allow
knowledgeable users to match a part to its description.
Intelligent part numbers generally require 8 to15
characters, and are a substantial burden on the people who
have to handle the part in high volumes. (Clerical error
rates increase roughly exponentially as part number length
increases.) Part and document number best practice says to
use short, purely numeric identifiers, which benefits
everyone except the person checking the accuracy of the bill
of material spreadsheet.
- Keep good records. There is no
audit trail to identify why or when a change was made, or
who authorized it. Unfortunately, adding change history to
the bill of material spreadsheet itself may make data import
into a ERP or PLM system more difficult.
- Review and enforce
business rules, such as item numbering and data revisioning,
change approval authority, and using approved suppliers.
- Verify each item's release status (pending, released,
canceled) and lifecycle (prototype, production, obsolete) to
ensure production assemblies aren't using prototype,
unreleased or canceled parts.
And, despite the utility of multi-level bills of
materials, don't try using Excel for the job.
Single-level BOMs offer enough challenges, but maintaining
an indented BOM in Excel requires substantially greater
clerical effort and is incredibly prone to errors (as well
as being just plain tedious).
Advantages of using the specialized bill of materials
capabilities in PDXpert PLM software
PDXpert PLM software is specifically designed avoid to
the limitations of BOM spreadsheets:
- Automatically assign part numbers using category prefix rules
- Precisely locate exact matches and possible
alternate parts using free-form text searches
- Make a snapshot duplicate of an existing bill of material for
accurate, consistent edits
- Drag and drop data-rich items onto the bill of material
- Automatically display each child
item's current revision on released assemblies
- For each item in your database, use built-in "where used" lists to
immediately examine what assemblies use it, where it appears
as a source, and what changes have affected it
- Print multi-level indented bills of materials, and roll-up costs
and materials into top-level product structures
- Know information instantly about each item's release status and
life cycle
- Create unlimited supplier sources with preferred rank
- Attach design, production and acceptance files directly
to database items, for immediate viewing by engineers,
quality personnel and supply chain managers
- Export part list data for your supply chain partners in Adobe Acrobat PDF
and Microsoft Excel XLS file formats
- Know that there's a full audit trail of all changes made to
the bill of materials
- Enjoy the advantages of professionally developed
and maintained PLM software: valuable features that go beyond simple bill of material
management; comprehensive, context-sensitive documentation;
professional configuration and operation advice; training
materials; on-going feature improvements
Watch how fast and easy bill of material management can be! Or, for more details and
a screen image, see PDXpert PLM's structure management features.
Learn more about how PDXpert PLM can replace your Excel
bill of material spreadsheets
PDXpert PLM has been specifically designed for smaller
organizations making the transition to automated product
structure management. It's easy for users to find their
items using free-form text search, then create complex
products by simple drag-and-drop BOM construction.
We invite you to: