ERP Supply-Chain Planning & Inventory Control
MRP Gross-to-Net & Lot-Multiple Planned Order Formula
Calculate Joseph Orlicky’s MRP Unreplenished Projected Balance, Safety-Stock-Protected Net Requirement MAX(0, Gross + Safety_Stock - On_Hand - Scheduled_Receipts), Lot-Multiple Planned Order Receipt, and Ending On-Hand Inventory.
Protect Safety Stock inside MRP Gross-to-Net netting and round up to vendor pallet multiples!
Every ERP Material Requirements Planning (MRP) engine—codified by Joseph Orlicky in 1975—executes a strict four-line time-bucket calculation! Supply planners auditing spreadsheet MRP overrides often forget to ADD Safety Stock to Gross Requirements (allowing projected inventory to dip below the safety buffer without triggering a purchase order!) or forget vendor pallet Lot-Multiple rounding! Exact single-bucket MRP logic computes: (1) Unreplenished Balance = On_Hand + Scheduled_Receipts - Gross_Req; (2) Net Requirement = MAX(0, Gross_Req + Safety_Stock - On_Hand - Scheduled_Receipts); (3) Lot-Multiple Planned Order Receipt = IF(Net_Req = 0, 0, ROUNDUP(Net_Req / Lot_Multiple, 0) * Lot_Multiple); and (4) Ending Projected Available Balance = On_Hand + Scheduled_Receipts + Planned_Receipt - Gross_Req (always >= Safety_Stock)!
Verify on a Subassembly Bucket—Gross_Requirement = 1,200 units (B2), Beginning_On_Hand = 350 units (B3), Scheduled_Open_PO_Receipts = 200 units (B4), Safety_Stock_Floor = 150 units (B5), and Vendor_Pallet_Multiple = 250 units (B6): Unreplenished Balance is 350 + 200 - 1,200 = -650 units (E2); MRP Net Requirement (restoring the 150-unit buffer!) is 1,200 + 150 - 350 - 200 = 800 units (E3); Planned Order Receipt rounds up 800 / 250 = 3.2 -> 4 pallets = 1,000 units (E4); leaving Ending Projected On-Hand at 350 + 200 + 1,000 - 1,200 = 350 units (E5)!
| Orlicky MRP Bucket Metric (Gross=1,200, OH=350, Sched=200, SS=150, Lot=250) | Formula | Verified Output |
|---|---|---|
| Unreplenished Available Balance | On_Hand + Scheduled_Receipts - Gross_Req | -650 units (+200 @ OH=1,200) |
| Safety-Protected Net Requirement | MAX(0, Gross + SS - On_Hand - Sched) | 800 units (0 @ OH=1,200) |
| Planned Order Receipt (Lot Mult) | IF(Net=0, 0, ROUNDUP(Net/Lot,0)*Lot) | 1,000 units (4 Pallets; 0 @ OH=1,200) |
| Ending Projected On-Hand | On_Hand + Sched + Planned_Order - Gross | 350 units (>= 150 Safety Stock) |
MRP Lot-Multiple Planned Order Receipt Formula
Validate non-negative Gross_Req, On_Hand, Scheduled_Receipts, Safety_Stock, and strictly positive integer/real Lot_Multiple > 0!
=LET(gr_q, B2, oh_0, B3, sc_r, B4, ss_f, B5, lt_m, B6, IF(OR(gr_q < 0, oh_0 < 0, sc_r < 0, ss_f < 0, lt_m <= 0), "Invalid MRP Bucket Input", LET(nt_r, MAX(0, gr_q + ss_f - oh_0 - sc_r), IF(nt_r = 0, 0, ROUNDUP(nt_r / lt_m, 0) * lt_m))))Above-Safety-Stock Zero-Order Check (Set Beginning On-Hand = 1,200)
Raise Beginning_On_Hand B3 to 1,200 units: Unreplenished Balance is 1,200 + 200 - 1,200 = 200 units (>= 150 Safety Stock), so Net Requirement E3 = 0, Planned Order E4 = 0, and Ending On-Hand E5 = 200!
=IF(ISTEXT(E4), E4, B3 + B4 + E4 - B2)Try the example
Use the original values or change them to test your understanding.