Free Bill of Materials Template for Excel 2007 – 2016

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.

With this free bill of materials template, you’ll have a very organized set of documents in which you can record your different type of bill of materials and use it an inventory control sheet.

Bill of Materials Template

Use for Excel, OpenOffice, and Google Sheets

DOWNLOAD

Description:

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.

Put simply, a bill of materials (BoM) is a listing of the materials and parts used to manufacture 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.

We will be happy to hear your thoughts

Leave a reply