Project Overview
A plastics manufacturer was performing daily material substitutions to maintain production flow, but had no systematic way to measure financial impact or identify operational patterns. Leadership needed to move from assumptions to data-driven decision-making.
I built a complete analytics solution from raw Excel files to interactive dashboards, revealing critical cost drivers and enabling strategic optimization.
The Challenge
- Multiple disparate Excel files with complex, inconsistent formatting
- Merged-cell headers breaking standard parsers
- Thousands of non-standardized text entries
- No system to track financial impact of substitutions
- Critical business questions unanswered
The Solution: Four-Phase Engineering Approach
Data Engineering
Built custom header parser and alignment engine to solve critical extraction bugs
Normalization
Developed regex-based cleaning system with validation checkpoints
Analysis & Strategy
Created categorization logic and aggregated insights across dimensions
BI & Deployment
Built interactive dashboards with drill-down capabilities
Technical Deep Dive
Key engineering challenges and solutions
Header Parsing
Problem: Multi-level merged-cell headers caused standard parsers to misalign data, creating silent corruption.
Solution: Engineered a custom parser that dynamically detects header rows, reads three independent tables, and programmatically enforces perfect alignment. This single innovation ensured data integrity across the entire pipeline.
Surgical Normalization
Challenge: Thousands of text entries with inconsistent whitespace, casing, and punctuation.
Solution: Built a regex-based cleaning engine that standardizes entries without altering business terminology. Included mid-pipeline validation checkpoints exporting cleaned values to .txt files for manual verification before proceeding.
Strategic Categorization
Innovation: Transformed raw blend changes into meaningful business strategies using logical hierarchies.
Examples: "Removed All Virgin," "Recycled to Virgin Dominant," "Pure Virgin Swap." This enabled leadership to analyze profitability by strategy type, not just by material codes.
Interactive Business Intelligence Suite
Developed seven specialized dashboards using Plotly with multi-level drill-down capabilities:
- Executive Summary: High-level financial overview and KPIs
- Machine 360°: Complete performance profile by machine with month-over-month analysis
- Part Number Profitability Analyzer: Identify which parts drive costs vs. savings
- Strategy Performance: Compare financial outcomes across substitution strategies
- Blend Analysis: Material composition impact on costs
Business Impact
Key Findings
- Quantified financial impact, proving substitution process was a net positive
- Achieved 80% "good" substitution rate across operations
- Identified optimal strategy: "Remove All Virgin" material for maximum savings
- Revealed 22.5% visibility gap due to missing cost data
Surgical Optimization
80/20 Analysis Revealed: Over 92% of machine-related cost increases came from just 3 machines.
This finding enabled the client to focus engineering resources on specific high-impact areas rather than broad, unfocused process improvements.
Tools & Technologies
Core Technologies
Python, Pandas, NumPy, Plotly
Data Engineering
ETL Pipelines, Regex, Openpyxl
Deployment
GitHub Pages, Git, LaTeX
Development
Jupyter Notebooks, VS Code