datasets manually downloaded and consolidated
up to ~60 min.to perform a routine planning update
~6 monthsof typical forward visibility
1 personholding most of the planning knowledge
← Built Systems Alloy Procurement
01 / Material Planning
Alloy Procurement
Planner
An 18-month material planning and purchasing decision system for approximately $3M in on-hand raw material inventory.
System ArchitecturePlanning LogicRequirements DiscoveryValidationImplementation
20Alloys Planned
~18 mo.Planning Horizon
~5 min.Routine Refresh
~$3MOn-Hand Inventory Visibility
01
The Constraint
Critical material planning depended on fragmented data and one person’s experience.
Alloy planning required downloading four to five separate datasets, consolidating them manually in Excel, and refreshing a pivot-based view about every two weeks. Planning typically extended no farther than six months.
Purchasing rules, supplier timing, test-bar requirements, historical usage, safety-stock considerations, and special alloy behavior also lived largely as tribal knowledge. One person had maintained the process for roughly four years before teaching it to me as they left.
Alloy shortages occurred roughly two to three times per quarter. A shortage could stop affected production until replacement material arrived and completed a 2–8 week verification cycle.
Before / After
plus a maintained scheduled-receipts table
~5 min.to refresh routine planning inputs
up to ~18 monthsof selectable forward planning visibility
documentedmethodology, operating instructions, and constraints
02
System Architecture
From customer demand to purchasing action.
The workbook converts operational exports into material requirements, reconciles supply, applies purchasing constraints, and presents decision horizons tailored to different users.
Input 01Current InventoryOn-hand pounds by alloy
Input 02Customer Work OrdersPart · Quantity · Pour Weight · Due Date
Input 03Scheduled ReceiptsPlanned · Ordered · Confirmed · ETA
Planning Engine
WO / Part → Alloy Mapping
Quantity × Pour Weight → Material Demand
Aggregate Demand → Alloy Requirements
On Hand + Receipts − Demand → Running Balance
MOQ + Lead Time + Test-Bar Timing → Suggested Order
13 WeeksImmediate purchasing actions
6 MonthsOperational material readiness
1 YearLong-range planning
18 MonthsSupplier forecasting
Parts by AlloyScheduled ReceiptsPurchasing CalendarYoY Consumption
03
Design Decisions
Automate the calculation. Preserve the judgment.
Human judgment stays in the loop.
The planner suggests quantities and order timing while leaving room for customer expedites, overdue orders, business conditions, and current production priorities to shape the final decision.
Plan for plant capacity, not only supplier capacity.
The purchasing calendar staggers incoming material to avoid overloading test-bar verification or creating unnecessary mix-up risk on the production floor.
Different horizons answer different questions.
Purchasing needs the next quarter’s actions; operations needs six-month readiness; leadership and suppliers benefit from longer-range forecasting.
Convert tribal knowledge into explicit constraints.
MOQ rules, supplier lead time, test-bar timing, historical consumption, special-material behavior, and planning notes are documented instead of left in one person’s memory.
04
Validation & Iteration
A forecast is only useful if its timing reflects operational reality.
Early signalRisk in Q3 2027
Quarter-level logic could imply urgency from the start of the quarter.
→
Refined signalPrecise Need-By Date
The model now uses the next relevant customer due date to locate the actual material need inside the quarter.
I validated the planner by tracing discrepancies against operational expectations and refining formulas or business logic when recommendations did not reflect the real decision.
05
Current State
Built, validated, documented, and entering operational use.
The planner entered deployment after formula validation, stakeholder review, and functional refinement with purchasing and operational leadership.
Because deployment is recent, I do not yet attribute shortage reductions or inventory savings to the system. Long-term impact will be evaluated as it accumulates operating history.
Next Iteration
Automated Data Ingestion Action Alerts Supplier Capacity Inputs Reduced Manual Copy / Paste Potential Migration Beyond Excel 06
Working Artifact
The system, not just a screenshot.
A portfolio version will preserve the workbook’s planning architecture and decision logic using fictionalized alloy, part, demand, and supplier data.
Artifact slot reservedAlloy Procurement PlannerWorkbook screenshots + downloadable .xlsx coming next
Next System Workforce Training & Capability System→