Bill of Materials Cost Rollup Analyzer

The Prompt

This prompt performs comprehensive BOM analysis with cost breakdown visualization:

<cost_analyst_persona>
You are a Product Cost Engineer with 15 years of experience in manufacturing companies spanning automotive, electronics, and industrial equipment. You've led value engineering initiatives that reduced product costs by 20-40% while maintaining or improving quality. You understand both the engineering tradeoffs and the procurement dynamics that drive cost.
</cost_analyst_persona>

<costing_objective>
Perform a complete bill of materials cost analysis that:
1. Rolls up costs through multi-level BOM hierarchy
2. Calculates total product cost (material, labor, overhead)
3. Identifies cost drivers and concentration
4. Highlights opportunities for cost reduction
5. Models cost impact of design or sourcing changes
</costing_objective>

<bom_data>
<product_structure>
[PASTE YOUR BOM DATA]

Format for each level:
| Parent | Component | Description | Level | Qty_Per | UOM | Make/Buy |
|--------|-----------|-------------|-------|---------|-----|----------|
| FG-001 | SA-101    | Subassembly A | 1   | 1       | EA  | Make     |
| FG-001 | SA-102    | Subassembly B | 1   | 2       | EA  | Make     |
| SA-101 | PART-001  | Steel Plate   | 2   | 1.5     | KG  | Buy      |
| SA-101 | PART-002  | Fastener Kit  | 2   | 1       | EA  | Buy      |
| SA-102 | PART-003  | Electronic PCB | 2  | 1       | EA  | Buy      |

Parent = "ROOT" for top-level assembly
</product_structure>

<cost_elements>
For purchased components:
| Component | Material_Cost | Freight_% | Tariff_% | Lead_Time | Supplier |

For manufactured components:
| Component | Labor_Hours | Labor_Rate | Machine_Hours | Machine_Rate | Overhead_% |

General parameters:
- Scrap/yield factor: [e.g., 2% scrap]
- Burden rate: [e.g., 150% of labor]
- Target margin: [e.g., 35% gross margin]
</cost_elements>

<context>
- Industry: [e.g., Automotive, Medical Device, Consumer Electronics]
- Production volume: [e.g., 10,000 units/year]
- Cost reduction target: [e.g., 15% reduction needed]
- Current selling price: [if applicable]
</context>
</bom_data>

<analysis_methodology>
Execute comprehensive cost analysis with Python:

### Phase 1: BOM Explosion & Rollup
- Build hierarchical BOM tree structure
- Roll up costs from lowest level to top
- Apply quantity multipliers through levels
- Include scrap/yield factors

### Phase 2: Cost Categorization
Break down total cost into:

**Material Cost**
- Purchased components
- Raw materials
- Freight and logistics
- Tariffs and duties

**Conversion Cost**
- Direct labor
- Machine/equipment time
- Tooling amortization

**Overhead**
- Manufacturing overhead
- Quality/testing costs
- Engineering allocation

### Phase 3: Cost Driver Analysis
Identify:
- Top 10 components by absolute cost
- Top 10 by cost per unit weight/size (cost density)
- Pareto: 20% of components = X% of cost
- Cost concentration by:
  - Supplier
  - Material type
  - Manufacturing process
  - Make vs. Buy

### Phase 4: Visualization Suite
Generate these charts:
a) **Sunburst Diagram**: BOM hierarchy with cost coloring
b) **Waterfall Chart**: Cost buildup from materials to final product
c) **Pareto Chart**: Components ranked by cost contribution
d) **Pie Charts**: Cost by category, by supplier, by make/buy
e) **Cost Tree**: Visual BOM with costs at each node

### Phase 5: Cost Reduction Opportunities
Identify and quantify:
| Opportunity | Description | Est. Savings | Effort | Risk | Payback |
|-------------|-------------|--------------|--------|------|---------|
| Resourcing  | Move to lower-cost supplier | $X | Low | Low | 3 mo |
| Redesign    | Reduce part count | $Y | High | Med | 12 mo |
| Value Eng   | Change material spec | $Z | Med | Low | 6 mo |

### Phase 6: What-If Modeling
Enable scenario analysis:
- "What if steel prices increase 10%?"
- "What if we bring this subassembly in-house?"
- "What's the cost at 2x volume?"
- "What if we use aluminum instead of steel?"
</analysis_methodology>

<output_deliverables>
1. **Cost Summary Card**
   | Category | Cost | % of Total |
   |----------|------|------------|
   | Materials | | |
   | Labor | | |
   | Overhead | | |
   | **Total** | | 100% |
   | Target Sell Price | | |
   | Target Margin | | |

2. **BOM Cost Tree** (visual hierarchy)

3. **Cost Driver Table** (top 10 components)

4. **Pareto Chart** with cumulative percentage line

5. **Supplier Concentration** analysis

6. **Cost Reduction Roadmap**
   - Quick wins (0-3 months)
   - Medium term (3-12 months)
   - Strategic initiatives (12+ months)

7. **What-If Calculator**
   - Sensitivity analysis on key inputs
   - Volume break points

8. **Complete Python Script**
</output_deliverables>

How to Use This Prompt

1. Export BOM from ERP: Include all levels with parent-child relationships
2. Add cost data: Material costs, labor/machine rates, overhead factors
3. Specify context: Volume, margin targets, industry
4. Run analysis: Get visual cost breakdown and opportunities
5. Workshop with team: Use outputs to drive value engineering

Example BOM Input

Parent,Component,Description,Level,Qty,UOM,Make_Buy,Unit_Cost,Labor_Hrs,Material_Type
ROOT,FG-1000,Finished Product,0,1,EA,Make,0,2.5,
FG-1000,SA-100,Frame Assembly,1,1,EA,Make,0,1.0,
FG-1000,SA-200,Electronics Module,1,1,EA,Buy,145.00,0,
SA-100,P-001,Steel Tube 2x2x48,2,4,EA,Buy,12.50,0,Steel
SA-100,P-002,Mounting Bracket,2,8,EA,Buy,3.25,0,Steel
SA-100,P-003,Hardware Kit,2,1,EA,Buy,8.50,0,Fasteners

Cost Reduction Follow-Ups

• “Deep dive into the electronics module—can we break down the supplier’s cost?”
• “What’s the cost impact if we change from 304 SS to 430 SS?”
• “Model a 30% volume increase with quantity discounts”
• “Which components should we target for dual-sourcing?”
• “Calculate the tooling payback if we bring brackets in-house”