Bill of Materials Template
If you are starting a small business that manufactures or assembles products, you are going to need to create and maintain a bill of materials (BoM) for your products. Just like a cook has a recipe and list of ingredients, if you are designing, selling, or servicing a product, you need some instructions and a list of materials. Although you may eventually need to use more advanced inventory control software or a full-fledged MRP system, you can get started using our free Bill of Materials template below and customize it as needed.
Bill of Materials Template
for Excel
Description
This bill of materials spreadsheet was designed originally to identify the parts needed for various custom LEGO® designs. But then I added a few other features, such as a revision history worksheet, to make the template more general.
Most people could easily create their own bill of materials spreadsheet from scratch, but hopefully this template will provide a useful example. It may not always be possible or desirable to include photos or images, but when applicable, pictures can be tremendously useful. See the example BoM below.
Google Sheets Version (Added 4/12/2019): This version implements the new Image in Cell feature. The template includes a Parts List worksheet and you can insert an image of each part. In the Bill of Materials worksheet, after you select the Part ID the rest of the info is displayed automatically, including the image, using formulas. Awesome!
Bill of Materials with Price List
for Excel
Download
⤓ Excel (.xlsx)Description:
This version lets you choose part numbers from a customizable price list and includes the new Image in Cell feature for Microsoft 365. It is similar to the Google Sheets version above, but for Excel.
What is a Bill of Material (BoM)?
Put simply, a bill of materials (BoM) is a listing of the materials and parts used to manufacturer some product. Each material in the list should include the quantity needed and a unique part number that can be used to identify the exact part or material to acquire from the supplier. Additional information (such as the cost, description, revision history, photos, and supplementary characteristics) may be included in a BoM, or that information may be available elsewhere (using the unique part number as the reference).
For complex products, some of the parts listed in a bill of materials may be sub-assemblies that each have their own BoM as well. For larger companies or more complex products, the BoM is a crucial part of a larger MRP System (Material Requirements Planning), and would generally be integrated into the MRP software. In these cases, the MRP system might be able to export the list to an Excel spreadsheet, but you would generally not be using the spreadsheet directly to make changes or revisions in the system.
Using a Spreadsheet-Based BoM in Practice
It's very easy to use the above template to create a bill of materials for a single assembly. But, as your product(s) become more complex, you'll quickly run into the following problems:
- Security and Access Control - Using spreadsheets as your primary data source for process definitions can lead to problems when it comes to security, giving people different access privileges (viewing vs. updating), etc.
- Change Management - What happens when an engineer makes a change to their bill of materials but the manufacturer is still using an old copy of the spreadsheet? These types of errors are difficult to avoid without using a central primary database for storing, viewing, and tracking product data. A BoM spreadsheet file is not much different than having a printed copy of the materials list: out-of-date lists could be floating around in cyberspace. If you are going to use a spreadsheet BoM system, make sure you also set up a system for managing changes and revisions to ensure that the information is accurately updated throughout the process.
Because of these limitations, I want to be clear that I'm not advocating the use of BoM spreadsheets for every circumstance. However, the bill of materials template can be very useful when you want a quick solution.
Bill of Material Example
The "Example" worksheet in the Bill of Materials template above shows the parts needed to make a custom mini Star Wars X-Wing® design with LEGO® bricks. I'm using this as an example because it shows how useful the parts list can be when images of each part are included.
LEGO® enthusiasts keep reading ...
The LEGO Digital Designer software allows you to create virtual assemblies of LEGO bricks and then export the bill of materials to a spreadsheet. That is where the images came from in the example. The software will also automatically produce the assembly instructions. The purpose of the BoM is to help you acquire the needed pieces.
If you are your kids enjoy designing custom LEGO creations, you probably know that the main limitation is not having the right pieces. Luckily, the lego.com website lets you purchase individual pieces using their Pick a Brick service, and although you can't get every possible piece in whatever color you want, it is pretty amazing how many pieces are available.
Resources / References
- Bill of Materials at wikipedia.org - Information about what a bill of materials is and the various types and uses.
- Definition of Bill of Materials at searchmanufacturingerp.techtarget.com - A good general definition.
- Using Excel for Bill of Materials at arenasolutions.com - Lists and explains the pros and cons for using a spreadsheet-based bill of materials (BOM). Includes an example Excel BOM download. They're obviously promoting their BOM system that is part of their PLM (product lifecycle management) tool, but the site still contains some really good information.