Bill of Materials and Supplier Plan

While development teams typically have access to a formal Bill of Materials system (PLM/MRP software), most use spreadsheets to develop prototype Bills of Materials because the production systems have checks and balances that are too constraining for early BOM development.  PD-Trak's Bill of Materials tool is optimized for developing BOMs during product development.  Key features:

  • Simple data entry format for adding a mix of new and existing parts
  • Automatically calculates indent levels and a fully indented BOM structure based on item and parent part number relationships
  • Allows parts to be coded as Make, Make/Tooled, Buy or Standard.  Make/Tooled and Buy parts are automatically exported to a Supplier Plan worksheet where sourcing activities can be be tracked
  • Allows target costs to be compared with product cost roll ups based on quotation data entered on the Supplier Plan worksheet and standard material and labor costs entered on the Bill of Materials
  • Automatically updates information on parts that are used in multiple locations (on all Bills of Materials in workbook) when any part is modified
  • The Supplier Plan worksheet provides a workspace for the Sourcing or Purchasing function. The worksheet lists all new purchased and tooled parts that need to be sourced.  The worksheet has fields to capture quoted prices, delivery estimates, tooling costs, tooling development information and more. 
  • Capable of handling large Bills of Materials with thousands of parts
  • Multiple Bills of Materials can be added in a single workbook to capture different product models and to capture prototype and production BOMs
  • Existing Bills of Materials can easily be pasted into the PD-Trak Bill of Materials tool
  • Completed BOMs can be exported into PLM/MRP systems by developing a simple export/import map


The Bill of Materials and Supplier Plan tool in PD-Trak can be purchased as part of the Extended Toolkit for use with the PD-Trak Project and Portfolio Management system or can be purchased seperately for use on a stand-alone basis.  When purchased seperately the tool comes with an interface file that allows new Bill of Materials files to be created based on license codes that can be purchased.   The stand-alone system price is $1,000 which includes ten (10) Bill of Materials files.  Additional files can be created by purchasing license codes for $1,000 for ten (10) files.  Typically, one BOM file is used per project - multiple BOMs can be created within each file with one consolidated supplier plan.

Bill of Materials Worksheet Layout

The Bill of Materials worksheet layout is shown below in two sections.  The table at the top left captures the top level information: model number, product description, BOM type (prototype or production), item part number for the top level assembly, and the expected build volume (used on the Supplier Plan worksheet to support quotations).

Yellow cells on the worksheet are data entry cells. The contents of gray cells are either linked or calculated by the VBA code - these cells are protected and cannot be manually changed.

The top row of the Bill of Materials worksheet represents the top level (completed) assembly.

Indentation levels are automatically calculated based on the relationships between the item and parent part numbers.

Parts can be coded as Make, Make/Tooled, Buy (a new part that needs to be sourced) or Standard (an existing part in the production parts database). 

The costing section allows target costs to be captured and compared to current estimates for rolled up material and labor costs.  While this can be used to track prototype costs, these fields are used more frequently for predicting production cost estimates during the development process.  A prototype Bill of Materials can be copied and refined as a production Bill of Materials for tracking target vs. predicted product cost.

Additional columns can be exposed in the material cost section by clicking the checkbox above the table.  These columns can be used to capture equipment, fixturing and gauging costs that apply to fabricated items.  Tooling costs are automatically imported from the Supplier Plan worksheet.

Bill of Materials Controls

The Bill of Materials controls are accessed by clicking the Show BOM Controls button which brings up the following floating menu:

Supplier Plan Worksheet

Items on a Bill of Materials that are coded as Make/Tooled or Buy are exported to the Supplier Plan worksheet:

The Bill of Materials workbook may have multiple BOMs all of which feed line items to the Supplier Plan worksheet.  The Sourcing or Purchasing team members use this worksheet to manage their tasks related to part and supplier identification and management for the project.  Data entered on this worksheet by the Sourcing function can be used to automatically update the costing section of the Bills of Materials.

Rows can be added for multiple suppliers to a given part, but one supplier must be selected as the primary source (see checkmarks in Sel column).

There are two sections to the Supplier Plan, one for sourcing prototype parts and another for sourcing Production parts.

The Bill of Materials system comes with an integrated PowerPoint training file, explaining how to use the tool with example exercises.  On site or web-based training is also available.  Contact us for pricing on these training options.