How to Make a Bill of Materials in Excel (2 Types)

An effective and practical way to handle the materials, parts, and components needed for manufacturing or assembly operations is to create a Bill of Materials (BOM) in Excel. You can quickly manage, calculate, and update your BOM using Excel’s robust spreadsheet features. This manual will show you how to make a Bill of Materials in Excel so you can track inventory, evaluate expenses, and organize your production planning. Excel can be a helpful tool to build and maintain your BOM efficiently.

What Is a Bill of Materials in Excel?

A complete description of the parts, pieces, raw materials, and sub-assemblies needed to produce or assemble a product is known as a bill of materials (BOM). Each item’s specifics are covered in full, including part numbers, descriptions, quantities, and occasionally other specs.

You can generate a BOM using Excel by arranging the pertinent data in a systematic manner. The columns of a table often contain information such as the part number, description, quantity, unit of measurement, cost, and supplier information. In contrast, each row normally represents a single item or component.

When dealing with a BOM, Excel has a number of features that might be helpful, including sorting and filtering options, formulae for calculations (such as adding up numbers or figuring out costs), and conditional formatting to draw attention to particular items or values.

You can easily manage and update the list as necessary by keeping a BOM in Excel. It enables you to keep track of inventories, evaluate expenses, schedule production, and communicate with other parties engaged in the manufacturing process.

Small and medium-sized firms can easily build and manage their BOMs using Excel. Enterprise resource planning (ERP) or specialized product lifecycle management (PLM) software may be better appropriate for larger-scale operations or when more sophisticated functionality is needed.

Download Practice Workbook

You can download the practice workbook from here.

How to Make a Bill of Materials in Excel: 2 Types

In this segment, we will discuss 2 types of BOM calculation. One will deal with a single selected product, the other one for multiple selection of products. We have created a drop-down list in cell B5, with data from our “BOM” worksheet. Follow the link if you don’t know that already.

Type 1: Making Bill of Materials for a Single Product

We will discuss evaluating the BOM for a single product first. We will create a drop-down list, and use different formulas of Excel to create the BOM. Let’s see the procedures.

Step 1. Create Basic Dataset for Making Bill of Materials

• Primarily, we need a dataset. We have taken a dataset where there are some product lists with the components needed to make the product. we‘ll utilize these data to make a BOM. observe our dataset in the BOM worksheet.

Step 2. Prepare Helping Table

• Now, let’s create the helper columns to create the BOM for a single product.
• Let’s compile the components for the products in a column. Use the following formula to do that. Copy the formula in the following cells with the Fill Handle.

In our dataset, the Component Quantity is the total number of components used for a selected product whereas the Component with Quantity is the measurement of the no. of individual components of the product.

In the code, the OFFSET function in Excel is used to reference a range of cells based on a starting point and specified rows and columns. This formula references the range starting from cell BOM!$C$4 and adjusts the row offset based on the current row minus 8. The range referenced will have the same number of rows as the current row and extend horizontally from the starting cell.

• Then, apply the following formula in Cell F8 to find the required components for a product. Use Fill Handle to copy the formula in the following cells.

• Afterward, count the number of components for a product. Use the given formulas.

In cell G8.

In cell G9.

• Copy the second formula in the following cells using the Fill Handle.

Step 3. Find Component and Component Quantity for Single Selected Item

• Later on, use the following formula in cell B8 to get the required components for a selected product from list. Use Fill HAndle to copy the formula.

• Next, find the Component Quantity by using the following formula. Copy the formula in the following cells with the Fill Handle.

Step 4. Calculate Total Price of Product

• Finally, use the following formula to calculate the total price of the product, we assumed the unit price was $25.

Type 2: Making Bill of Materials for Multiple Products

So far, we have shown the procedure to make BOM for a single selected product. Now, we will show the same procedures but for multiple selected products. Let’s see the procedures.

Step 1. Modify for Finding Component and Component Quantity for Multiple Selected Products (Helper Table)

• Firstly, use the following formula in Cell B6 to enlist the component quantity for each component for multiple selected products. Copy the formula in the following cells with the Fill Handle.

• Next, calculate the total requirement of a component for all selected products. Use the following formula in Cell B5.

Step 2. Determining Component Name and Component Quantity

• Then, insert the component names in cell range K5:K12.

• Afterward, insert the following formula in cell L5 to get the component quantity required. Copy the formula in the following cells with the Fill Handle.

• In the formula, The HLOOKUP function searches for the value in cell K5 within the range of rows 4 to 5 ($4:$5). It returns the value from the second row of the matched column. The final argument of 0 specifies an exact match.

Step 3. Determine Total Price of Product

• Simply, apply the following formula to get the total price of products.

In the formula, we multiplied Unit Price with Quantity to get the Total Price.

The Advantages of Making a Bill of Materials in Excel

Managing and organizing your manufacturing or assembly operations can be made easier by creating a Bill of Materials (BOM) in Excel. These benefits include:

• Accessibility: Excel is extensively used and available, making it simple for both people and companies to utilize. The majority of computers come preinstalled with Excel, enabling effortless collaboration and sharing of BOMs between teams or suppliers.
• Flexibility: Excel offers a framework that allows you to design your BOM in accordance with your unique requirements. You are free to arrange and format the BOM as you see fit, adding or removing columns, using calculations, and emphasizing key information with conditional formatting.
• Calculation and Analysis: You may carry out calculations and analysis on your BOM using the built-in formulae and functions of Excel. Quantities, prices, sums, and other crucial parameters can all be calculated automatically. This makes accurate cost estimation, inventory management, and budgeting possible.
• Sorting and Filtering: Excel’s sorting and filtering features make it simpler to arrange and examine your BOM. Components can be arranged alphabetically, sorted by quantity or price, and you can exclude certain parts based on criteria like supplier or part number.
• Updates and revisions: You might need to update your BOM when your manufacturing process changes. Excel offers a simple method for editing and revising documents. You may alter prices, add new components, change quantities, and swiftly disseminate changes across the entire BOM.

Frequently Asked Questions

• Can I create reports or summaries in Excel using the BOM?

Ans: Definitely. Excel has powerful reporting features. You can use the BOM data can to create detailed reports and summaries using formulas, pivot tables, and charts. This enables you to assess costs, monitor inventory levels, spot patterns, and come to wise judgments based on the available information.

• Is it feasible to input data into Excel for the BOM from other programs or systems?

Ans: Excel supports Data import from a variety of sources. Excel allows you to import data from databases, other software programs, and CSV files. This improves the accuracy and effectiveness of your BOM management by enabling you to incorporate data from supplier databases, inventory management systems, or other pertinent sources.

• Exist any restrictions or difficulties while using Excel for a BOM?

Ans: Excel has some restrictions even though it is a flexible tool. For improved functionality and scalability, large-scale operations with vast BOMs and complex production processes may need specialist software. Manual data entering and updating can also be time-consuming and error-prone. To minimize potential problems, it’s crucial to set up reliable data management procedures and make sure that you make backups frequently.

Although Excel can be a useful tool for organizing BOMs, it’s important to evaluate your unique requirements and take into account whether other software solutions would be more appropriate for your purposes.

Things to Remember

• Be careful regarding the Cell reference used in the formulas.
• Use the Fill Handle to avoid the repetition of writing the same formula.

Conclusion

For effective administration, a Bill of Materials (BOM) created in Excel offers accessibility, flexibility, calculating abilities, and collaborative tools. Excel is a useful tool for small to medium-sized enterprises to streamline inventories, costs, and production planning even though it may have limitations for complex procedures. For larger operations, you can solve issues by analyzing unique demands and taking specialized software into account. Hopefully, this article will help you to make a bill of materials easily. Feel free to comment if you have queries or suggestions.