Material Shortage Monitoring
Which parts will stop the line, when, and will the material already on its way arrive in time?
Open full image ↗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.
Independent rebuild: the Shortage Monitor portal
After Atlas Honda I independently redesigned the idea as a full web application, informed by the planning principles I used at work and built on synthetic data. It contains no employer data, source files or proprietary systems. 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 independent portal rebuild, built with fully synthetic data. It contains no employer data, source files or proprietary systems.
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.