Lead Time Investigation — Knowledge Build Log
Running log of the data fusion effort to answer: “Why are PSI machine ship lead times increasing?”
Investigation Status: COMPLETE — See Lead Time Analysis Report
Started: 2026-02-26 Requested By: Management Core Question: Why are machine ship lead times increasing? What stages and departments are slipping?
Data Sources Examined
Round 1 — Discovery Phase (2026-02-26)
| # | Source | Status | Key Finding |
|---|---|---|---|
| 1 | PSI Wiki (all pages) | Reviewed | Mapped complete data ecosystem |
| 2 | Current State Map 8-5.pdf | Reviewed | Documents 8-stage build pipeline with cycle times |
| 3 | PSI Explorer codebase | Reviewed | Has LeadTimeView, project health, schedule deviation |
| 4 | PSI.UniData.API codebase | Reviewed | Schedule endpoints parse Excel files, LDS milestones |
| 5 | Redbook Dashboard | Reviewed | Quality metrics, resolution times, department tracking |
| 6 | Project Explorer | Reviewed | 25 years of OrderDate → ShipDate data, lead time calculator |
| 7 | Fabric ProgressiveDataSet | Reviewed | 33 tables, 9.6M rows — queryable via DAX |
| 8 | CSV files (\fs1) | Cataloged | ganttJOB, ganttWO, openwo, tslabor2, wiprouteline, etc. |
| 9 | MCP Server (37 tools) | Cataloged | Project info, WOs, schedule, BOM, inventory tools |
| 10 | Schedule Excel files | Documented | 7 dept schedules + per-project LDS Gantt files |
| 11 | Power BI Reports | Documented | Executive, Production, Capacity, ProjectDashBoard |
Round 2 — Data Analysis Phase (2026-02-26)
| # | Analysis | Status | Key Finding |
|---|---|---|---|
| 12 | ganttJOB.csv analysis | DONE | 81 active projects, 40% tasks past due, Purchasing=155d avg |
| 13 | openwo.csv WO cycle times | DONE | CC108 avg 87 days late, CC106 avg 70 days late |
| 14 | tslabor2 dept hour trends | DONE | Dept 108 hours/job +41% in 2023, Dept 106 total hours +54% |
| 15 | Project1287 lead time calc | DONE | Lead time doubled: 180d→328d starting 2022 |
| 16 | purchaseorders lead times | DONE | Vendor OTD dropped 81%→69%, Kendall Electric 73% late |
| 17 | Fabric DAX aggregations | DONE | Confirmed dept completion rates and WO lateness |
| 18 | Labor headcount analysis | DONE | Dept 108 flat headcount despite 41% more hours/job |
| 19 | Schedule deviation analysis | DONE | Controls Eng 79% late, Mech Eng 71.6% late |
| 20 | Cross-source synthesis | DONE | See lead-time-analysis-2026.md for full report |
Key Architectural Discoveries
The PSI Data Brain
Created comprehensive data map (content/data/data-brain.md) documenting:
- All data sources, locations, access methods
- Join keys between systems
- What questions each source answers
- Department-level data source mapping
Build Pipeline Documentation
Documented 8-stage pipeline (content/company/build-pipeline.md) from Current State Map:
- Order Entry / Proposal Handoff
- Mechanical Engineering (Design)
- Controls Engineering (Design)
- Purchasing / Procurement
- Machine Shop / Weld / Fabrication
- Mechanical Assembly
- Electrical Assembly
- MVI / Startup / Testing
Data Joins Required for Full Analysis
To answer “why are lead times increasing” requires fusing:
- Timeline data: ganttJOB.csv (dept-level), openwo.csv (WO-level), wiprouteline.csv (op-level)
- Hours data: PROJECT.HRS.1287 (budget vs actual), tslabor2.csv (detailed labor)
- Quality data: redbook.csv (issue volume, resolution time, dept flags)
- Supply chain: purchaseorders.csv (vendor delivery), partmaster.csv (standard lead times)
- Schedule data: Excel files (PMO priorities, deviation, hours-to-go)
- Project data: Project1287List.xml (order→ship dates, planned vs actual)
Hypotheses to Test
Based on initial data review, these are the leading hypotheses for lead time increases:
H1: Engineering Phase is Expanding
- Test: Compare MENG and CENG budget vs actual hours over time
- Data: PROJECT.HRS.1287, tslabor2.csv (Dept 110, 111)
- Indicator: Increasing actual-to-budget ratio
H2: Purchasing Lead Times Are Increasing
- Test: Track vendor Promise Date → actual receipt over time
- Data: purchaseorders.csv, pohist.csv
- Indicator: Growing gap between promise and delivery
H3: Quality Issues Are Causing Rework Delays
- Test: Track redbook count, resolution days, and late-stage detection
- Data: redbook.csv (Days Open, Ship Timing Category)
- Indicator: More issues found late, longer resolution times
H4: Assembly Phases Are Overrunning Hours
- Test: Compare hours overrun by department (102, 104, 106, 108)
- Data: wiplabor.csv (Routed vs Actual), PROJECT.HRS.1287
- Indicator: Systematic overruns in specific departments
H5: Scope Creep / ECN Volume Is Increasing
- Test: Track ECN count per project over time
- Data: ecn.csv, redbook.csv (ECN links)
- Indicator: More ECNs per project in recent years
H6: Floor Space / Capacity Constraints
- Test: Check bay utilization and WIP levels
- Data: Floor Space Capacity Chart.xlsm, capacity.csv, wc-capacity.csv
- Indicator: Full bays causing projects to wait for space
H7: Specific Machine Types Take Longer
- Test: Segment lead times by machine type
- Data: Project1287List.xml (MachineOneName), ganttJOB.csv
- Indicator: Certain machine types driving the average up
Findings Log
Finding #1: Data Infrastructure is Comprehensive
PSI has excellent data coverage across the entire build pipeline. The challenge is not data availability but data fusion — no single system tracks end-to-end stage-by-stage lead time. The data exists in fragments across:
- AFTEC ERP (project dates, WO dates)
- Excel schedules (PMO view of planned dates)
- LDS Gantt files (per-project milestones)
- CSV exports (aggregate analysis)
Finding #2: Project Explorer Already Calculates Lead Time
The project-explorer app at C:\git\project-explorer has a LeadTimeView.tsx component that:
- Calculates lead time = ShipDate - OrderDate (in days)
- Calculates slip = actual lead time - planned lead time
- Computes year-over-year trends
- Calculates on-time delivery rate
- Filters out retrofits (5-digit projects starting with 9)
- Filters unreasonable values (≤0 or >1500 days)
Finding #3: Multiple Schedule Views Exist
- PMO Excel view: Priority, deviation, hours-to-go (department level)
- LDS Gantt view: Per-project milestones with planned/actual dates (often unfilled)
- ganttJOB.csv: ERP-sourced schedule with department start/end + % complete
- PSI Explorer: Computes health status (on-track, at-risk, delayed) from dates + progress
Finding #4: Department Hours Tracking is Available
PROJECT.HRS.1287 tracks 8 departments with budget vs actual hours:
- 110M (Mech Eng), 110E (Controls Eng)
- 102 (Machine Shop), 104 (Weld)
- 106 (Mech Assembly), 108 (Elec Assembly)
- 122 (Startup/Runoff), 120 (Field Service)
This enables department-level overrun analysis.
Final Results
All analyses complete. See Lead Time Analysis Report 2026 for the full synthesis.
Top-Line Findings:
- Lead time doubled from ~180 to ~330 days starting in 2022
- Purchasing is the longest phase (155 days) with vendor OTD collapsing (81%→69%)
- Controls Engineering is 79% late across all active projects — upstream bottleneck
- Electrical Assembly WOs average 87 days late; hours/job jumped 41%
- Mechanical Assembly surged to 42k hours in 2023 (+54% from baseline)
- Volume is NOT the sole cause — similar volume years (2007, 2018) had far lower lead times
- Machine complexity is increasing — more hours/job in assembly departments
Related Pages
- PSI Data Brain — Complete data source map
- Build Pipeline — Process flow documentation
- Analytics Methodology — Metric calculations
- Dashboards — Existing analytics dashboards
Last updated: February 26, 2026