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, although it can't
avoid the many limitations that are inherent in all BOM spreadsheets.) 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
(e.g., 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 spreadsheet must be separately
discovered and updated (imagine updating the description for a often-used screw
or resistor). Take your time: searching across spreadsheets to discover where a
specific item is used can be tedious, slow and error-prone.
- 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 is specified in millimeters, and
purchased in centimeters, 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
simply to 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 even
think about 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 according to your specified
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, yes, 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 a video that introduces
PLM and shows how simple it is to drag-and-drop parts onto a bill of
material markup. Or, for more details and a screen image, see
PDXpert PLM's
structure management features.
The benefits of PLM bill of material software include
increased sales revenue through faster product releases;
lower product costs by using more accurate BOMs; and
lower administrative overhead by providing important supporting
information (like cost and material roll-ups) automatically.
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 from ad hoc bill of material
management. It's easy for users to find their items using free-form text
search, then create complex products by simple drag-and-drop bill of
material
construction.
We invite you to:
Make professional bill of material management a priority today, and
reap the rewards of fast, accurate and reliable product information.
Download a free copy
of
PDXpert PLM
evaluation software now simply by registering
071104.2009