How Do I Create a Bill Of Materials In Excel?

Diagrams and graphs make it easy to navigate the project details and understand which materials are needed for production. However, they don’t allow hiding some points or use the “Subtotal” function, so the BOM comes in handy instead. It is often used in electronics manufacturing, construction, engineering, hardware development, and other fields. There is special software for working with BOM, but it is expensive. Hence, a more convenient alternative is to use Excel to create and edit bills of materials. Also, there are ready-made templates that you can edit to meet the company’s current needs.

Define BOM

A BOM is a comprehensive list of all materials, components, parts, and assemblies required to manufacture or create a product. It contains information such as part numbers, descriptions, quantities, and other specifications to identify and purchase materials and components necessary for production.

How to create a BOM in Excel?

Here’s a step-by-step guide to creating a BOM using Excel. You can also go the easy way and choose a ready-made bill of materials template when you need it. Unlike creating a new workbook in Excel, the template is already filled in the fields: Final result, SKU, Category, Name, Unit price, etc. When you update these cells, you should also edit the field headings on the Bill of Materials tab.

Editing a template is easier than creating everything from scratch. So it is best to be aware of both options and use one or the other depending on your needs.

Creating a BOM in Excel

Open a new Excel spreadsheet and create a table with the following column headings: Part Number, Description, Quantity, Unit Cost, and Total Cost. You can do this both in Excel 2003 and in Excel 2007. Only the location of the “New Document” button differs in these versions.

• List all the components needed to build your product in the first column, using a unique part number to identify each. If you are working with a ready-made template, you need to select the menu “Templates” – “Go” – “Enter.” Now, you can load a ready-made template from the ones you have, and it will open as a new sheet. Enter the desired name for this sheet and click “Save the file.”
• Let’s go back to creating the template yourself. To do this, briefly describe each component in the second column.
• In the 3rd column, enter the quantity of each component needed to create one unit of the product.
• Enter the unit cost of each component in the 4th column, and in the 5th one, use the formula to calculate the total cost of each element by multiplying the quantity by the unit cost. For example, if the amount is in cell C2 and the unit cost is in cell D2, you can enter the formula =C2×D2 in cell E2.

At the bottom of the table, use the formula to calculate the total cost of all components by summing the values in the Total Cost column. For example, if the Total Cost column goes from row 2 to row 10, you can enter the formula =SUM(E2:E10) in the cell below the Total Cost column.

You can also add columns, such as supplier information or lead times, to help you manage your inventory. Save the Excel spreadsheet and update it whenever you add or change components in your product.

When is a BOM used?

A specification helps ensure that all the necessary parts and materials are available when they are needed and that the finished product meets the required specifications. It also serves as a reference document for engineers, designers, and other stakeholders throughout product development.

BOMs can be simple or complex, depending on the product being manufactured, and they can be created manually or with software that automates the process. Specifications are commonly used in the electronics, automotive, aerospace, and construction industries.

Benefits of specification templates

A specification template provides a standardized format for listing the components required to build a product. It makes it easier for different teams and departments to communicate and collaborate on a project because everyone uses the same terminology and format. It is also essential for budgeting, pricing, and profitability analysis.

Additionally, a BOM template can help you streamline your manufacturing processes by providing a clear list of all the components needed to build a product. It can help reduce errors, minimize waste, and optimize production schedules.

You can customize BOM templates to suit the specific needs of your business or industry. In particular, you can add or remove columns, change formatting, and customize calculations. To make it easier to track changes, you can also add a version number or date to the BOM template to help you track changes.

Remember that each team member must have access to the current version of the BOM, so we recommend enabling real-time collaboration, i.e., sharing this document in Microsoft Teams or opening access to it in Google Sheets. In addition, the BOM template can be integrated with other software systems, such as inventory management or enterprise resource planning (ERP) software. Thus, specification templates are pretty easy to adapt to the specific needs of a particular business and, with their help, optimize and simplify production processes and accounting.