Math & Trig
Round order quantity up to case-pack / MOQ bundle size (CEILING & excess units)
Enforce supplier Minimum Order Quantity (MOQ) and case-pack multiples, calculate sealed cases to order, and track overage buffer units.
Procurement reality: suppliers ship sealed 24-unit cases with a 96-unit MOQ!
A supplier may require both a minimum order and whole cases. First choose the larger of net demand and MOQ, then round that result up to a case multiple. MAX(MOQ,CEILING(Demand,Pack)) is not equivalent when the MOQ is not a pack multiple: an MOQ of 100 and pack of 24 must become 120, not 100.
This example uses nonnegative whole-unit demand and MOQ, and a positive whole-unit case size. Zero demand means no order. The model covers these two quantity constraints only; supplier acceptance, price tiers and stock availability are separate.
| Net Demand (A2) | Pack=24, MOQ=96 Order Qty | Sealed Cases to PO | Excess Buffer Stock |
|---|---|---|---|
| 130 units | 144 units | 6 cases (144 / 24) | +14 units |
| 30 units (< MOQ) | 96 units (MOQ floor!) | 4 cases | +66 units |
| 0 units (No need) | 0 units (Zero-guarded!) | 0 cases | 0 units |
MOQ-aware case-pack order quantity, case count, and overage units
Guarding A2 <= 0 prevents ordering a full 96-unit MOQ when net demand is zero!
=IF(OR(COUNT(A2:C2)<>3,A2<0,A2<>INT(A2),B2<=0,B2<>INT(B2),C2<0,C2<>INT(C2)),"Invalid inputs",IF(A2=0,0,CEILING(MAX(A2,C2),B2)))Number of sealed master cases to list on the Purchase Order
Divides compliant order units by PackSize B2 to produce an exact integer case count.
=IF(OR(COUNT(A2:C2)<>3,A2<0,A2<>INT(A2),B2<=0,B2<>INT(B2),C2<0,C2<>INT(C2)),"Invalid inputs",IF(A2=0,0,CEILING(MAX(A2,C2),B2)/B2))Try the example
Use the original values or change them to test your understanding.