Bill of Materials (BOM) export in PCB Design - Real Business Scenario

Scenario Mode

👤 Your Role: You are a production planner at an electronics manufacturing company.

📋 Request: Your manager wants a clear and organized Bill of Materials (BOM) export report for the latest printed circuit board (PCB) design. The report should summarize all components needed, their quantities, and suppliers to help the purchasing team order parts efficiently.

📊 Data: You have a dataset listing each component used in the PCB design. The data includes Component ID, Component Name, Quantity per PCB, Supplier Name, and Unit Cost.

🎯 Deliverable: Create a BOM export report that groups components by supplier, sums total quantities needed for a batch of 100 PCBs, and calculates total cost per component and overall.

Sample Data

Component ID	Component Name	Quantity per PCB	Supplier Name	Unit Cost ($)
R1	Resistor 10kΩ	4	Supplier A	0.05
C1	Capacitor 100nF	6	Supplier B	0.10
U1	Microcontroller	1	Supplier C	3.50
D1	LED Green	2	Supplier A	0.20
Q1	Transistor NPN	3	Supplier B	0.15
R2	Resistor 1kΩ	5	Supplier A	0.05
C2	Capacitor 10uF	2	Supplier B	0.12
U2	Voltage Regulator	1	Supplier C	1.80

1

Step 1: Calculate total quantity needed for each component for a batch of 100 PCBs.

Total Quantity = Quantity per PCB * 100

Expected Result

For example, Resistor 10kΩ total quantity = 4 * 100 = 400

2

Step 2: Calculate total cost per component by multiplying total quantity by unit cost.

Total Cost = Total Quantity * Unit Cost

Expected Result

For example, Resistor 10kΩ total cost = 400 * 0.05 = $20.00

3

Step 3: Group components by Supplier Name and list each component with its total quantity and total cost.

Group by Supplier Name; show Component ID, Component Name, Total Quantity, Total Cost

Expected Result

Supplier A group includes R1, D1, R2 with their calculated totals.

4

Step 4: Calculate the overall total cost for all components across all suppliers.

Sum of all Total Cost values

Expected Result

Overall total cost = $20.00 + $60.00 + $350.00 + $40.00 + $45.00 + $25.00 + $24.00 + $180.00 = $744.00

5

Step 5: Create a clear BOM export report table with columns: Supplier Name, Component ID, Component Name, Total Quantity, Unit Cost, Total Cost.

Configure report table with the grouped and calculated data

Expected Result

A table showing all components grouped by supplier with quantities and costs for ordering.

Final Result

BILL OF MATERIALS (BOM) EXPORT REPORT

Supplier A:
  R1 - Resistor 10kΩ: Qty 400, Unit $0.05, Total $20.00
  D1 - LED Green: Qty 200, Unit $0.20, Total $40.00
  R2 - Resistor 1kΩ: Qty 500, Unit $0.05, Total $25.00

Supplier B:
  C1 - Capacitor 100nF: Qty 600, Unit $0.10, Total $60.00
  Q1 - Transistor NPN: Qty 300, Unit $0.15, Total $45.00
  C2 - Capacitor 10uF: Qty 200, Unit $0.12, Total $24.00

Supplier C:
  U1 - Microcontroller: Qty 100, Unit $3.50, Total $350.00
  U2 - Voltage Regulator: Qty 100, Unit $1.80, Total $180.00

OVERALL TOTAL COST: $744.00

✓Supplier C supplies the most expensive components, making up over 70% of the total cost.

✓Supplier A provides many small components with low unit cost but high quantities.

✓The overall BOM cost for 100 PCBs is $744.00, useful for budgeting and purchasing.

Bonus Challenge

Add a column to the BOM report that flags components with total cost above $100 as 'High Cost' for priority review.

Show Hint

Use a conditional formula: If Total Cost > 100 then 'High Cost' else blank.