Menu Close

Bill of Materials in Excel

A bill of materials is an ingredient list used for the creation and manufacture of products. It is important for both engineering and manufacturing.

It helps with inventory management and assists in production planning. You can quickly manage and calculate BOMs using Excel’s spreadsheet features. Sorting and filtering options make it easier to examine BOM components. You can also enable outlining so that Excel displays a tally next to each subassembly.

1. Part Numbers

A bill of materials (BOM) is basically an instruction pamphlet that explains what raw materials and sub-assemblies are required to produce a product. It includes the part numbers, descriptions and unit costs for each of these items. Ideally, the BOM should be as detailed as possible to avoid any mix-ups during production.

A well-organized BOM should have a unique part number for each item or component, and it should also include a short description of each one to prevent any confusion or mix-ups. Having an organized BOM is essential for accurate production planning and inventory management.

In addition, the BOM should contain information about each item’s raw material suppliers or vendors and their contact details. This helps ensure that the correct materials are ordered on time and delivered in good condition.

To ensure consistency in the BOM structure, it’s best to use a standard naming convention for all parts and to keep all the columns visible. The column names can be defined in Excel by matching them with a custom property established in SOLIDWORKS or by using a custom formula.

2. Quantity

A bill of materials is more than just a list of parts; it’s also a tool to avoid production delays and waste. It’s used by professionals in engineering, design, operational planning, and manufacturing. It’s often part of enterprise resource planning (ERP) systems and material requirement planning (MRP).

The quantity of parts in a bill of materials is an important piece of information to include, especially when working with distributed teams or suppliers. It’s a crucial part of the product development process and helps reduce product defects, production errors, and shipping delays.

Excel is a popular choice for creating a bill of materials because it offers accessibility, flexibility, calculating abilities, and collaborative tools. But managing a bill of materials in Excel has unique challenges. When a BOM is shared between departments, individuals, and outside contractors, it can be difficult to keep track of versions and determine who’s responsible for changes. For this reason, many companies are moving away from antiquated spreadsheet-based bills of materials and exploring specialized software that helps streamline inventory management. Katana, for example, is a bill of materials manager that makes it easy to update and share a BOM with multiple users.

3. Description

Just like a recipe and its list of ingredients, a bill of materials serves as a guide for creating a product. This is a useful tool for those who manufacture or assemble products because it helps with inventory, making sure that the right quantities of raw materials are always on hand to keep production running smoothly and avoid delays or extra warehousing costs.

Several parties often depend on or interact with a BOM, including internal departments, industry experts, project or product stakeholders, and external vendors or suppliers. This is why it’s important that the BOM document is comprehensive, accurate, and up-to-date.

Many companies use Excel spreadsheets to manage their product bills of materials. However, spreadsheets are often modified and emailed to different people inside and outside of the company, making it difficult to know who has the latest version. This can lead to errors, delays, or even scrap or rework during new product introduction (NPI). Katana is specialized software that helps eliminate these problems by centralizing and managing all of your BOM information in a single place.

4. Pricing

A bill of materials is much more than a list of parts; it also contains costs, quantity information and procurement details. It helps ensure that the right quantity of raw materials are available for production, preventing inventory shortages and over-stocking that lead to storage and delivery delays. This is especially important when working with multiple suppliers.

A BOM also provides insight into how to best organize your components and products. For example, if you use the same part number for several different products and naming conventions are in place, it’s easier to search for these common parts and reuse them rather than creating duplicate parts.

While small companies can get by using spreadsheets to manage their bills of materials for the first release of a new product, it becomes challenging when they scale. If employees are using different formats and styles to create their own bills of materials, it can be difficult to read or interpret these lists. This can lead to costly errors that affect a company’s productivity and supply chain operations. BOMs need to be consistent and easily accessible by anyone who needs them.

5. Shipping

A bill of materials is much more than a listing of materials; it’s a tool to help avoid production delays and waste. It also helps with inventory management by showing which materials you need and when. This reduces delays and extra warehousing costs.

This is especially important if you contract with external vendors or suppliers to supply the raw materials that make up a product. A BOM template can help you gather their contact information and keep track of the orders that they ship to you. It can even include a field for comments or notes to communicate any special requirements that you have for their products.

Managing a BOM spreadsheet can be difficult, especially when multiple people are editing it. Specialized software like Katana can make it easier to manage changes and create a BOM that can be easily shared. It can also save the BOM as an Excel file, so it’s easy to use with other applications. This feature makes it easier for engineers, designers, operation planners and manufacturers to collaborate on the same data.