Material Shortage Monitoring
Which parts will stop the line, when, and will the material already on its way arrive in time?
The problem
Planners needed to know, continuously, which components could stop planned motorcycle production. The information was spread across SAP, material data, inter-plant information and logistics updates. Combining it by hand took about an hour every day before any real analysis could start.
My role
I built an Excel/VBA shortage-monitoring model that consolidated five to six data sources into one structured planning view. It grew to cover about 1,100 actively monitored components, in a plant with more than 3,000 parts.
What the tool answered
- What is short?Current and potential shortages, pending material and newly added requirements, with SAP status for each.
- When does it hit production?Projected balances turned into the lot and time a shortage reaches the build sequence.
- When does the material arrive?Incoming quantities and expected arrival timing, phased in by ETA.
- Is that soon enough?If not, it flags the part for escalation or a faster logistics route.
Recovery options were judged against typical transport lead times: about 1 day by air, 3 days by fast-track logistics and 4 days by container.
Result
Preparation fell from around 60 minutes to 5–10 minutes, removing 10+ hours of manual work a week, and the report became part of PPC’s daily planning routine.
It shows what automation looks like when it’s built by someone who understands the planning problem, rather than separately from the manufacturing process.
What I learned
Automation works best when the person building it understands the operational decision behind the report.
Personal project: the Shortage Monitor portal
After Atlas Honda I rebuilt the same shortage logic as a full web application on synthetic data. It keeps three horizons strictly separate (short today, short tomorrow and the worst point in the next seven days) and adds a stock bridge, an inbound timeline that never overstates cover, chronic-shortage streaks, truck states that prevent double counting, part inquiry and a material master. It runs on Node/Express with sign-in, roles and an audit trail.
| What you need | In a workbook | In the portal |
|---|---|---|
| Know a part has been short for weeks | No memory; each morning starts blank | Daily state recorded and read back as a streak |
| Convert parent stock to child parts | Worked out by hand before entry | Automatic from the quantity-per-parent ratio |
| Separate today from next week | One shortage column | Three horizons held apart everywhere |
| Let two people work at once | One file, one person | Individual sign-in, roles, every change recorded |
The screenshot and PDF show the personal portal rebuild. All part codes, models, carriers and quantities in them are synthetic; no Atlas Honda data is shown.
Full product document
12 pages · the complete walkthrough with every screen.
Planning should turn complexity into action.
Open to Production Planner, Materials Planner, Production Controller and Supply Chain Planning roles in the UK.