Finding the real bottleneck in a design-to-ship process
Custom equipment manufacturer | Power BI + on-prem SQL Server + SharePoint
All project numbers, stage names, and figures below are illustrative. Client data stays private.
The Problem
Projects were shipping late and nobody agreed on why. The prevailing assumption inside the company was that drawing approvals were the bottleneck, so that is where the improvement effort was going. Leadership wanted to review the full design and production process and find out which stage was actually causing the delays.
The Solution
We merged several data sources into a single schedule variance heat map. Actual dates came from the ERP system on an on-premise SQL Server, connected to the Power BI Service through a gateway. Budgeted dates, plus a few stages the ERP did not track, came in through SharePoint lists and Google Sheets. Every project shows the variance in days between the budgeted date and the actual date at each stage of the process, and the whole report filters by year, quarter, month, project manager, and project size.
Explore a sanitized version of the report
Stage averages (days vs. plan)
Green = days ahead of plan. Red = days behind plan. Blank = stage not tracked for that project.
| Project Name | Mech Design | Elec Design | Design Approval | Long-Lead Buy | Fab Release | Fabrication | Paint/Finish | Assembly | Functional Test | Final Inspection | Ship Prep | Ship |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| P0001 | -8 | 7 | 3 | 3 | 2 | 6 | -3 | 0 | -3 | -1 | ||
| P0002 | 5 | 13 | -7 | 7 | 2 | -16 | -10 | -6 | -4 | -15 | ||
| P0003 | -20 | 5 | -5 | -4 | 17 | -7 | -4 | -26 | -11 | -3 | -8 | -16 |
| P0004 | -14 | -3 | -2 | 8 | 4 | -3 | 1 | -1 | 1 | -2 | 7 | -2 |
| P0005 | -3 | 4 | 4 | 2 | 5 | 3 | 0 | -3 | -8 | -7 | 3 | -2 |
| P0006 | 7 | 10 | -4 | -7 | -7 | 6 | -3 | 4 | 4 | -7 | -8 | |
| P0007 | -5 | 0 | -10 | 1 | -2 | -15 | -26 | 1 | -16 | -4 | -1 | -9 |
| P0008 | -17 | 12 | -10 | 6 | 10 | 7 | -26 | -15 | -11 | 7 | -23 | |
| P0009 | -2 | 5 | -1 | -3 | 5 | -4 | -3 | 2 | -2 | 1 | -3 | |
| P0010 | 7 | 11 | -2 | 3 | 4 | -9 | -13 | -4 | 3 | -6 | -13 | |
| P0011 | 9 | -1 | -14 | 0 | -14 | -4 | 4 | -12 | -11 | 2 | -2 | -8 |
| P0012 | 14 | -11 | -7 | 6 | 4 | 4 | 2 | -3 | -3 | -3 | -6 | |
| P0013 | -6 | 1 | 1 | 6 | 5 | -2 | 4 | -10 | -5 | 12 | -5 | |
| P0014 | 2 | 4 | -6 | 5 | 11 | -6 | 4 | -2 | -6 | -1 | ||
| P0015 | 1 | 1 | -6 | 1 | -6 | -10 | 6 | -10 | -1 | 12 | 17 | -7 |
| P0016 | 5 | -6 | -18 | -1 | 2 | 1 | -16 | 16 | -9 | |||
| P0017 | -1 | -2 | 0 | 4 | 0 | -5 | -3 | -2 | -8 | |||
| P0018 | -4 | -17 | -12 | -14 | -18 | -5 | -18 | -26 | -8 | -21 | ||
| P0019 | -2 | 5 | 1 | 5 | 2 | 2 | 3 | -7 | 2 | |||
| P0020 | -2 | 5 | 4 | -2 | 5 | 4 | 1 | 1 | 2 | -1 | 4 | |
| P0021 | 8 | 6 | 31 | -3 | -2 | -26 | -23 | 6 | -1 | -21 | ||
| P0022 | -7 | 3 | -5 | 12 | 3 | 5 | -4 | 4 | 7 | |||
| P0023 | 1 | 7 | 2 | 6 | 0 | 1 | -6 | 3 | -4 | 0 | ||
| P0024 | -3 | -3 | -2 | 1 | 11 | -1 | 8 | -11 | -8 | -9 | -5 | -9 |
The Results
The heat map made the pattern hard to argue with. Drawing approvals, the assumed problem, were running close to schedule. The bottleneck actually started at assembly, and it got worse as projects got bigger: large projects were being under-estimated and took far longer than planned. The company stopped spending improvement effort on approvals and refocused on assembly estimating and capacity. Filter the demo above to Large projects and you will see the same thing they saw.