Why Your BOM Excel Template is Going the Way of the Dinosaur

August 6, 2019 Zachariah Peterson

Graphs and data in an Excel document
It’s time to ditch your BOM Excel template

The saying goes in software, “great coders use great code”. The same idea applies to Excel templates. I like to use Excel templates for a number of business tasks. I use invoice templates, finance calculators, and an accounting template. There’s no need to reinvent the wheel, so why not use a great template when someone makes it available for free?

The usefulness of an Excel template depends on the data you are tracking. An Excel template that is used as a calculator or to compile important lists can save you a significant amount of time. When it comes to a bill of material for your PCB, a BOM Excel template quickly becomes unwieldy when you start considering sourcing information and changes in PCB design data.

 

Using a BOM Excel Template

If you’re working on a budget, then an Excel template for preparing a BOM and organizing design data can be useful. It allows you to prepare a BOM in a standard format that manufacturers can understand. Excel includes plenty of built-in functions, and it’s an easy program to learn. It can also help you with budgeting as you can quickly view component costs for your design and get an idea of lead times.

Although a BOM Excel template can be useful in a number of applications, it is less useful in PCB design. With the rate technology changes and new components are released, you’ll be forced to manually add new sourcing and design data to a BOM template in order to ensure your data remains current. Unless you are a VBA developer, you won’t be able to incorporate new data into a template automatically, and you’ll still be stuck manually searching distributor websites for sourcing updates.

Electronic components on a black PCB

 

You Can Do Better Than a BOM Excel Template

Using Excel to prepare a BOM is really only useful when sourcing information is not affected by a volatile supply chain, shortages, or obsolescence. When changes in the supply chain for your product occur suddenly, you’ll have to manually update the data in your BOM with new sourcing data. If you have a precompiled list of reliable sources for your components, then this isn’t such a major problem. However, this becomes a serious problem in PCB design as you will need to update your schematic symbols and PCB footprints in your layout to match the updates in your BOM.

Some PCB design teams rely on a small number of component suppliers and keep precompiled databases of sourcing information and component data. The industry’s leading PCB design platforms can interface with standard database formats, including Excel formats, allowing you to view and update this data as you locate updates. While this makes it easy to keep your bill of materials and your component information database consistent, it may not solve the problem of synchronicity between your design libraries, design documents, manufacturing deliverables, and component databases.

 

Integrating Sourcing and Design Data in Your BOM

Given the workflow problems that come with using a BOM Excel template, you need a solution that can quickly place design and component sourcing data into a central database. If you use PCB design software that separates your design and data management functions into different programs, your management tools won’t pass design and sourcing data updates into your PCB schematic and layout, or into your bill of materials. At best, your design tools will only update individual components and component libraries.

Library on a laptop screen
Excel templates make it difficult to keep your design libraries updates

You also can’t be sure your data remains current if your data management tool does not grab data updates directly from major component distributors and manufacturers. If your sourcing data goes stale, you risk longer lead times and even including unsourceable or obsolete components in your designs. This can trigger a redesign and can greatly increase your lead time from days to months, ultimately affecting your time to market and making you less competitive.

This is where design teams need a single data management solution that provides updates to sourcing data and component data directly in their design software. These data updates should be importable directly into your design documents, including your BOM and assembly documents.

This helps you anticipate problems like component obsolescence and helps you manage costs when it comes time to prepare for manufacturing. This also allows you to source components directly from verified distributors and remain confident that your lead times will remain reasonable.

You can overcome the problems that come with a BOM Excel template when you use a data management solution that provides sourcing data and design data updates directly within your PCB design software. The world-class PCB design tools in Altium Designer now integrate with the data management and collaboration features in Altium Concord Pro, giving you a complete PCB design and management solution in a single program. You’ll also have a platform that synchronizes ECAD/MCAD co-design.

Contact us or download a free trial of Altium Designer® and Altium Concord Pro. You’ll have access to the industry’s best layout, MCAD collaboration, and data management tools in a single platform. Talk to an Altium expert today to learn more.

About the Author

Zachariah Peterson


Zachariah Peterson has an extensive technical background in academia and industry. Prior to working in the PCB industry, he taught at Portland State University. He conducted his Physics M.S. research on chemisorptive gas sensors and his Applied Physics Ph.D. research on random laser theory and stability.

His background in scientific research spans topics in nanoparticle lasers, electronic and optoelectronic semiconductor devices, environmental systems, and financial analytics. His work has been published in several peer-reviewed journals and conference proceedings, and he has written hundreds of technical blogs on PCB design for a number of companies.

More Content by Zachariah Peterson
Previous Article
What is an Engineering Bill of Materials in Agile PCB Design?
What is an Engineering Bill of Materials in Agile PCB Design?

Your engineering bill of materials is one of many documents that can help you verify requirements and manag...

Next Article
Optical Transceiver Layout for Gigabit and Faster Ethernet
Optical Transceiver Layout for Gigabit and Faster Ethernet

There are plenty of design aspects to account for in a PCB for an optical transceiver. Here’s are some high...