Supply Chain & Inventory Optimization
Joint Replenishment Shared Major/Minor Setup Formula
Coordinate multi-SKU supplier shipments via the Joint Replenishment Problem (JRP) optimal cycle time T* = SQRT(2*(S_0 + SUM(s_i)) / SUM(D_i*H_i)) and quantify savings vs independent EOQ.
Stop paying full truckload / customs major setup S_0 separately on every single SKU!
When a distributor sources multiple SKUs from the SAME overseas factory or regional vendor, placing an order incurs one Shared Major Header / Freight Setup Cost S_0 PLUS small SKU-specific Minor Line-Item Setup Costs s_i! Ordering each SKU on its own uncoordinated EOQ pays S_0 + s_i per SKU order, summing to Independent Annual Cost SUM(SQRT(2 * (S_0 + s_i) * D_i * H_i)). Under the Common-Cycle Joint Replenishment Problem (JRP), pooling all SKUs onto one shared replenishment interval T* = SQRT((2 * (S_0 + SUM(s_i))) / SUM(D_i * H_i)) years (n* = 1 / T* orders/yr, SKU order size Q_i* = D_i * T*) slashes total annual ordering-plus-holding cost to TC_Joint* = SQRT(2 * (S_0 + SUM(s_i)) * SUM(D_i * H_i))!
Verify on exact integers with Shared_Major_Setup S_0 = 100.00 (H2) and 3 SKUs (Rows 2..4): Minor_Setups s_i = [30, 25, 25] (SUM = 80 -> Pooled Setup = 180.00), Annual_Demands D_i = [1,000, 600, 500], and Unit_Holding_Costs H_i = [2.00, 2.00, 1.60] (so D_i * H_i = [2,000, 1,200, 800], SUM = 4,000.00): Optimal Joint Cycle Time is T* = SQRT((2 * 180) / 4,000) = SQRT(0.09) = 0.3000 years (3.3333 consolidated shipments/yr; Q* = [300, 180, 150]), Joint Annual Cost is SQRT(2 * 180 * 4,000) = 1,200.00/yr, and Uncoordinated Independent EOQ Cost is 721.11 + 547.72 + 447.21 = 1,716.05/yr — saving 516.05/yr (30.07%)!
| JRP Multi-Item Metric (S_0 = 100, SUM(s) = 80, SUM(D*H) = 4,000) | Formula | Verified Output |
|---|---|---|
| Optimal Joint Order Cycle (T*) | SQRT(2 * (100 + 80) / 4,000) | 0.3000 years (3.33 orders/yr) |
| Joint Replenishment Total Cost (TC_JRP*) | SQRT(2 * 180 * 4,000) | 1,200.00 / yr |
| Uncoordinated Independent EOQ Sum | SUM(SQRT(2 * (S_0 + s_i) * D_i * H_i)) | 1,716.05 / yr |
| Annual Consolidation Savings ($) | TC_Indep - TC_JRP* | 516.05 / yr (30.07% Saved!) |
Optimal Common-Cycle Joint Replenishment Interval T* Formula
Validate S_0 >= 0, minor setups s_i >= 0, S_0 + SUM(s_i) > 0, and strictly positive demands and holding rates.
=LET(s_mj, H2, s_mn, SUM(B2:B4), dh_sm, SUMPRODUCT(C2:C4, D2:D4), IF(OR(s_mj < 0, MIN(B2:B4) < 0, s_mj + s_mn <= 0, MIN(C2:C4) <= 0, MIN(D2:D4) <= 0), "Invalid JRP Input", ROUND(SQRT((2 * (s_mj + s_mn)) / dh_sm), 4)))Joint Replenishment Annual Cost Savings vs Independent EOQ Formula
Use helper column E (Individual EOQ Cost E_i = SQRT(2 * ($H$2 + B_i) * C_i * D_i)) to audit exact SKU-by-SKU independent costs before subtracting consolidated TC_Joint*!
=LET(s_mj, H2, s_mn, SUM(B2:B4), dh_sm, SUMPRODUCT(C2:C4, D2:D4), IF(OR(s_mj < 0, MIN(B2:B4) < 0, s_mj + s_mn <= 0, MIN(C2:C4) <= 0, MIN(D2:D4) <= 0), "Invalid JRP Input", ROUND(SUM(E2:E4) - SQRT(2 * (s_mj + s_mn) * dh_sm), 2)))Try the example
Use the original values or change them to test your understanding.