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