Router Assembly Line Capacity Planning Dashboard

Router Assembly Line Capacity Planning Dashboard.

[Audio] Background & Context The Operational Challenge Assembly Lines Esbu (2), Xr, Xr60, Xp Every week, the Supply Chain team issues an updated monthly forecast from Jabil Vietnam covering 15 router product families across 4 assembly lines, spanning a 12-month horizon. Product Families The Operations team must then answer critical questions: MP70 to RX400 Can each assembly line meet the forecasted demand? Which months will be over capacity — and by how much? Month Horizon How many headcount are needed per line per month? Mar 2026 – Feb 2027 Where should we apply overtime or rebalance production? Weekly Forecast Cycle Previously, these simulations were done manually in Excel — a process that was time consuming, error prone, and difficult to repeat consistently each week. New file every week 2 / 12.

[Audio] The Manual Excel Process — Pain Points Download Forecast Copy Data to Template Map S-K-U's to Products Build Formulas & Simulate Check Results & Fix Errors → → → → 5 min 5 min 10 min 20 min 20 min Total manual effort: ~1 hours per forecast cycle × 52 weeks/year = ~52 plus hours/year spent on repetitive simulation work Formula Errors Data Mapping Mistakes Inconsistent Assumptions The P-C-A Forecast sheet contains combo rows that merge multiple products. Manual extraction easily miscounts XR60, XP, and other families. Different analysts may use different working days, shift hours, or HC parameters — producing conflicting results from the same forecast. Manual formulas across 15 products × 12 months create hundreds of cells where one wrong reference breaks the entire simulation. Version Control Issues No Automated Alerts Not Repeatable Each weekly update creates a new spreadsheet. Comparing changes across weeks or tracking which version is current becomes chaotic. When a new forecast arrives, the entire process restarts from scratch. There is no reusable framework — just a pile of spreadsheets. Overloaded months are only caught by visual inspection. A planner scanning hundreds of cells can easily miss a critical bottleneck. 3 / 12.

[Audio] How A-I Solves This before — Manual Excel AFTER — (A I ) Powered Dashboard ~2 hrs per forecast cycle <1 min upload → full dashboard Manual SKU-to product mapping Auto SKU parsing & classification Hidden formula errors in 180 plus cells Zero formula errors — logic is coded Static no alerts, visual scan only Live color coded alerts & HC flags Fragile breaks when format changes Robust auto detects column layout Smart Parsing Error Detection Precise Modeling Iterative Refinement A-I reads Excel structure, auto detects header rows and weekly vs monthly columns Identified P-C-A combo row issue that inflated XP counts by 7× and undercounted X-R-6-0 Built correct 2-shift HC model separating 10.5 hours worker hours from 21 hours line hours Dashboard improved over multiple cycles with corrections validated against source data 4 / 12.

[Audio] How the Dashboard Works → → → 1. Upload Forecast 2. Auto Parse & Map 3. View Dashboard 4. Act on Alerts Upload the Jabil Vietnam forecast Excel file (SKU Forecast tab) System detects columns, maps S-K-U's to 15 product families across 4 lines Interactive charts show utilization %, HC needs, and monthly breakdowns Red/yellow flags identify overloaded months for rebalancing or overtime 2 / 9.

[Audio] Line Overview — Utilization at a Glance Green (<80%) Yellow (80–100%) Red / Over (>100%) Comfortable capacity — no action needed Tight capacity — monitor closely Exceeds capacity — requires overtime or rebalancing 6 / 12.

[Audio] Product Detail — Demand against Capacity Key Insight Each product shows demand, production days needed, and U-P-D per month. Line totals (for example, ESBU: 29.5/36d in M-A-R--) reveal if a line is overbooked. Red days = demand exceeds working days. 7 / 12.

[Audio] Headcount Plan — Worker Hours Model HC Calculation HC Needed = (HC/shift × 21 hrs/day × Prod Days) ÷ (10.5 hrs/worker × Working Days). Product level shows 1 decimal; line totals use Math.ceil. HC Alert triggers when total exceeds peak crew capacity. 8 / 12.

[Audio] Parameters & U-P-D Reference Parameters Tab UPD Reference Tab Adjustable working days per month and number of assembly lines per configuration Editable U-P-H per product with auto calculated U-P-D and head count per assembly line 6 / 9.

[Audio] Key Benefits & Value Proactive Risk Detection Instant Analysis Precise HC Planning Forecast to dashboard in seconds. No manual spreadsheet work or formulas needed. Worker hours model separates worker shifts from line shifts for accurate staffing. Color coded alerts flag overloaded months before they become production issues. Weekly Refresh Cycle Data Driven Decisions Multi Line Visibility Upload new forecast each week; dashboard recalculates automatically. See all 4 assembly lines and 15 products in one view with drill down detail. Embedded business rules and assumptions ensure consistent, transparent analysis. Zero install, single file solution — portable across teams — works offline in any browser 10 / 12.

[Audio] Who Uses It & How Operations Manager Production Planner Supply Chain / N-P-I Review capacity outlook weekly; identify months needing overtime or rebalancing; validate headcount plan against budget. Generate dashboard after each forecast update; adjust working days and parameters; share alerts with production leads. Validate forecast feasibility before committing; flag bottleneck lines early in the planning cycle. Quick Start Guide 1. Open the H-T-M-L file in Chrome/Edge (keep semtech_logo.png in the same folder). 2. Click "Upload Forecast" and select the latest Jabil Vietnam forecast (.xlsx). 3. Click "Generate" — the dashboard auto parses the S-K-U Forecast tab and renders all tabs. 4. Review Line Overview, Product Detail, and Headcount Plan tabs. Adjust parameters as needed. 11 / 12.

[Audio] Project Summary & Achievements <1min Product Families Assembly Lines Month Horizon Forecast to Dashboard Tracked Monitored Mar 26 – Feb 27 Was ~1 hours Eliminated ~50 hours/year of manual spreadsheet simulation work A-I identified and corrected P-C-A combo row data error — missed by manual process Headcount model properly accounts for 2-shift operations (10.5 hrs/worker vs 21 hrs/line) Proactive alert system catches capacity bottlenecks that manual scans missed Single portable H-T-M-L file — no server, no installation, consistent results every time Khanh Dinh | [email protected] | Jabil Vietnam Operations 12 / 12.