Supply Chain Replenishment Model Builder
Build a constraint-based replenishment model from Excel input data. The model calculates optimal purchase quantities per SKU across multiple suppliers under budget limits and turnover constraints, using a two-phase allocation algorithm.
Workflow
Step 1: Understand Requirements
Collect:
1. Budget per supplier - how much each supplier can receive
2. Turnover constraints - max days of stock per SKU and per category
3. Data columns - what columns exist in the Excel
4. Special rules - expiration, box size, location-based restrictions
Step 2: Design Rules
Implement constraints in this priority order:
| Priority | Rule | Content |
|---|---|---|
| 1 | Filter | Skip zero-sales SKUs, location="stop"/"clear" |
| 2 | Rule 3 | BGM/CGM/POCT expiration filter |
| 3 | Rule 4 | Replenishment <= available stock + in-transit |
| 4 | Rule 1 | Post-replenishment turnover <= max turnover |
| 5 | Rule 5 | Box size integer multiple |
| 6 | Rule A | Supplier budget cap (two-phase allocation) |
| 7 | Final check | Total <= budget (reduce by box if over) |
Step 3: Two-Phase Allocation Algorithm
Phase 1 - 70% Target Allocation:
1. Compute weighted daily average sales
2. Calculate target stock = category turnover × 70% × daily average
3. Compute ideal replenishment = target stock - current stock
4. Apply Rule 4 and Rule 1 caps
5. Scale all SKUs proportionally to fit within supplier budget
6. Round down to box size (skip if < 1 box)
Phase 2 - Additional Allocation (spend remaining budget):
1. Allocate remaining budget to all SKUs (including at-target) per Rule 1 cap
2. Round up to box size (if < 1 box, round to 1 box)
3. This phase can exceed the 70% target to use up budget
Phase 3 - Final Validation:
- Sum total by supplier; if over budget, reduce largest items by 1 box until compliant
Step 4: Weighted Daily Average
weighted_daily = monthly_avg / 30 × 0.7 + current_month_sales / current_days × 0.3
Step 5: Excel Output
Reserve 3 columns for results (V/W/X in current mapping):
- Col V: Replenishment quantity
- Col W: Replenishment amount (qty × price)
- Col X: Post-replenishment turnover days OR skip reason
For non-replenished SKUs, write "-" in V/W and skip reason in X.
Step 6: Implementation
See scripts/build_model.py for the complete reference implementation.
Key Design Decisions
- 70% ceiling: Reduces turnover gap within categories
- Weighted average: Ensures Rule 1 calculation matches actual post-replenishment turnover
- Box constraint: Round down in Phase 1 (conservative), round up in Phase 2 (aggressive)
- Two-phase vs one-phase: One-phase leaves budget unused; two-phase ensures "money must be spent" priority
- Gift SKUs: No Rule 1 cap (turnover unlimited)
Resources
- Algorithm: See scripts/build_model.py - complete reference implementation
- Rules reference: See references/rules.md - detailed rule specs with edge cases
- Excel template: See references/excel_template.md - required column structure
- Chinese triggers: See references/chinese_triggers.md - Chinese keyword triggers
