NPI Supply Chain Craft

Practical Techniques for Holistically Taking Over Existing New Product Programs.

Part 4: Convert Manual BOM into a Daily Shortage & Status Tracker.

---

• Part 1: Right mindset & the foundational rules (Steps 1&2)
• Part 2: Program requirements and status (Step 3)
• Part 3: Manual BOM, Bill of Materials (Step 4)
• Part 4: BOM conversion to materials tracker (Step 5)
• Part 5: Priorities and execution (Steps 6&7)
• Part 6: Combo BOM Trackers (Step 8)

---

This is a continuation of my blog posts on this subject. Please proceed to Part 1 to read the full article from the start. Also, this particular step won’t make sense if you haven’t read the previous post yet (Part 3; Manual BOM creation).

---

Step 5 – Build up into a BOM Tracker with shortages and status.

Timing: Aim for a filled-in BOM Tracker in one week or less. You can start taking actions as you go along but the idea is to get a complete snapshot of shortages and status ASAP.

Here is the method I use to make a BOM Tracker. You may have better or faster methods; modify as you see fit. If using MS Excel, I highly recommend format setup that will enable easier sorting, filtering, or use of pivot tables.

• Create Prelim BOM – Option 1: Extract from ERP. If the BOM has been input into your ERP system but is not activated yet for MRP, then see if you can extract it in either report form or as editable data that you could load onto MS Excel or other software. This might be something that your Planner has to check. Better yet, check with your I.T. specialists to see if the this can be done or if an entire BOM Tracker format could be created and generated routinely in your ERP software. (Keep reading to see the recommended columns). Also, always remember that the BOM in this unreleased form is subject to change.

• Create Prelim BOM – Option 2: Request from Engineering. If the BOM can’t be extracted yet from your ERP system, ask the Planner (or Project or Design Engineer) if they can provide you a preliminary BOM. Ideally, it will be in an indented format and via MS Excel or other editable software. If they simply give you a parts list, ask to meet with them in order to expand it to an informal indented BOM. Plus, be sure that it has critical information (if known): hierarchy level, part number, part description, quantity per unit, and unit of measure.

• Create Prelim BOM – Option 3: Build it yourself. To do this, you have to know how to read an indented BOM. Most likely, you’d have to resort to this in an R&D or development prototype situation wherein neither a BOM nor assembly/component drawings will be released in ERP. (Maybe the final product will be built for internal company use only). If Engineering cannot provide you with a prelim BOM, you can build one yourself IF assembly drawings and/or parts lists are available. (Assembly drawings don’t have to be formally released but get them in PDF format). Copy and paste the parts lists of all assembly drawings into an Excel file or other editable software. Edit as necessary to put the BOM in the right hierarchal order. Ask the project team to review for any corrections.

• Validate Prelim BOM against Drawings. The prelim BOM part numbers should align with corresponding parts lists on all assembly drawings. Typically, released drawings are available through Document Control or archive files. For unreleased drawings, try asking the responsible Design Engineer. Then look at the parts lists on the drawings and validate the information against your prelim BOM. Ask Engineering to clarify any mismatches or anomalies.

• Review Prelim BOM Draft. At this point, you should have something that looks like an indented BOM. Do a line-by-line review for any mistakes and make sure all subassembly and component part numbers are the in the right hierarchal level. Ask the project team to review for any corrections.

• Add Procurement Columns. These columns will have information normally not provided by Engineering. They will reflect the supply chain details and status for each line item. Here are some of the column headers that I use; rename and reorder as you see fit.

Dwg Status	Status of drawing. Is it currently unreleased or released.
Total FP	Total Finished Product. Enter the total deliverable Finished Products to the customer (i.e. the sales order quantity). If using Excel, enter the number for the Finished Product part number and “fill down” the column.
Qty Req’d per Assy	Total Quantity Required per Finished Product. Multiply “Total FP” by “Qty Per”. This will be the total quantity needed of each BOM subassembly or component to make one finished product.
Qty Req’d per SO	Total Quantity Required per Sales Order. Multiply “Total FP” by “Qty Req’d per Assy”. This will be the total quantity needed of each BOM subassembly or component to make all the sales order finished product quantities.
Risk Stock Qty	This represents additional quantity to purchase specific parts for risk mitigation or attrition reasons. Because of the extra cost, the project team should collectively agree on specific numbers.
Cons Qty Req’d (Optional)	Consolidated Total Quantity Required per Sales Order. In this column, you could consolidate all the quantities of recurring part numbers into one part number only (I suggest the one closest to the top of the BOM). You can then enter “0” (zero) into the fields of the same part number where it reoccurs in the BOM.
Stock Qty	Available inventory stock that can be committed or reserved for the program use.
Purch Qty	The final purchase quantity that considers the total minimum needed, risk quantity, and stock quantity.
Make / Buy	Will be purchased or made in-house?
Lead Time	Use the ERP system, estimated, historical, or quoted lead time in days or weeks. For long lead or critical path items, strive for accuracy (like a recent quote). If using estimates, add a little buffer.
Target Cost	Budgeted cost per item. This may be derived from the original Cost Estimate BOM but, regardless, the Program Manager should know the materials budget and be able to provide the supportive document driving that figure.
Quote Price	Unit pricing quote based on the Purchase Quantity. Check for mismatch against the Target Cost.
Supplier Cand	The primary Supplier Candidate. May be subject to change.
Awaiting For PO (or WO)	In this column, you can list one word that explains why you can’t place a PO or start a WO (work order). Examples: “DWG” drawing release, “MDS” master data setup, “QUOTE” quote from suppliers, “BOM” BOM release, “PO U/N” contract undergoing negotiation with supplier, “PO APPR” management approval of purchase order, “WIP” purchase order process is in progress, etc.
RP	Responsible Person. The party or person who is responsible for completing the action that is preventing you from placing a purchase order. Note that this could be you (e.g., you can’t submit a purchase order because you’re evaluating quotes for supplier selection).
Commit Date	The date to which the RP has committed to completing the action that is preventing purchase order submittal.
PO Order Date	The date in which PO was sent to the supplier.
PO Number	The purchase order number.
Supplier	The supplier with whom the PO was placed.
PO Dock Date	Make sure that your PO terms and the supplier are clear as to definition of the PO’s “due date”. I like to show the date when PO is expected on dock.
MRP Need Date	The date in which the assembly or component is needed completed according to MRP (if it is available). “Completion” needs to be defined (i.e., received on dock, received in the ERP system, or moved to stock).
Project Need Date	The date in which the assembly or component is needed, according to the project schedule. “Completion” needs to be defined the same as the MRP Need Date. This column can be populated directly from correlating fields in the project schedule. In fact, if the PM supports it, I recommend linking the fields either way so that an updated field updates the other document. This is a great way of alerting schedule slippage through linked software. Also, this column enables viewers to instantly see if any MRP and Project Schedule need dates are not aligned.
Status	This column can be used to describe status in one word. (I use it for status of material orders). Examples: “HOLD” order on hold, “RISK” order at risk, “WIP” work in progress, “RCVD” order received, “LATE” order is running late at supplier, “RFQ” awaiting quotes, etc.

• Fill in Procurement Columns. In the previous step, you added additional columns to your BOM Tracker. Now you fill them in with two types of status information: that which is readily available and that which requires research (like RFQ’s, emails, consultations, etc.). IMPORTANT: Rush out a first cut BOM Tracker with the readily available information first and proceed to Step 5. Getting the other status details will be a day-by-day process. Following are typical sources in lieu of MRP reports.
  • Bill of Materials, released or preliminary
  • Drawings, released or unreleased
  • Sales Order Report
  • Open Purchase Order Report
  • Inventory Stock Report per Project Number
  • Program/Project Schedule
  • Quotes from the Cost Estimating Phase of each awarded Program
  • Quotes or feedback from Suppliers as a result of new RFQs
  • Manufacturer Websites (for lead times of COTS parts such as electronics)

• Restructure for Efficient Updating. If you create a BOM Tracker, you should design it in a way for quickest and most efficient updates in the future. It would be time consuming and cumbersome if you have to create a new BOM Tracker from scratch every time you update. Instead, format it so that you can do updates using merge, linking, and/or copy-and-paste functions from other reports (like MRP or Open Order reports) run in your ERP system. To this end, you may have to rename or move around some columns. Or consult with your I.T. Specialists to see if the ERP system can be configured to directly update your BOM Tracker.

• Finalize Formats for Presentations. The BOM Tracker is a good tool for status presentation during project meetings or executive leadership briefings. You might want to create a few formats for various purposes. You’ll probably want to display only the most news-worthy columns (while hiding the rest). Experiment with sorting, filtering, and/or pivot tables (if using Excel). It also helps to build in color coded conditional formatting so that if you enter particular info in columns (like “Status”), the field background will turn to the color red, yellow, or green. Bottom line: keep your “Presentation BOM Trackers” simple and with relevant info only.

---

Part 5 of this blog will cover prioritization and execution of your BOM Tracker items (steps 6 and 7).

To be continued. Stay tuned for Part 5!