Calculate a reorder with reservations and late inbound stock

A buyer needs a replenishment quantity for three items. Work from usable stock at the cutoff, then round the shortage to full supplier packs.

SOURCE DATA
"item","on_hand","reserved","inbound_units","inbound_before_cutoff","target","pack"
"A","10","4","5","yes","18","6"
"B","8","2","20","no","12","5"
"C","20","1","0","yes","12","4"

RULES
Usable stock = on_hand − reserved + inbound that arrives before the cutoff. Late inbound does not help this order.
Shortage = max(0, target − usable stock). Purchase complete packs only.
Round each item separately upward to a pack multiple; do not round the combined shortage.

MY FIGURES
Order item A (units): __________
Order item B (units): __________
Order item C (units): __________
Total units ordered: __________

MY REVIEW NOTE
________________________

METHOD
Calculate A as 10 − 4 + 5 = 11 usable units. Its seven-unit shortage needs two packs of six.
Exclude B’s 20 inbound units because they miss the cutoff. Six usable units leave a six-unit shortage, needing two packs of five.
C already has 19 usable units against a target of 12. A nonpositive shortage needs no purchase.

REFERENCE ANSWERS — try first
Order item A (units): 12. ceil((18 − 11) / 6) × 6 = 12.
Order item B (units): 10. ceil((12 − 6) / 5) × 5 = 10.
Order item C (units): 0. Usable stock exceeds the target.
Total units ordered: 22. 12 + 10 + 0 = 22; the three units of rounding surplus remain in stock.

Including late inbound makes item B look sufficiently stocked. Availability depends on time as well as quantity.

When extending the sheet, put the cutoff and inbound arrival date in explicit columns, and retain the rounding surplus for the next planning run.

Occupational context: O*NET 31.0 Database, USDOL/ETA, Task 11368, occupation 43-5061.00. https://www.onetcenter.org/dl_files/database/db_31_0_csv/task_statements.csv
CC BY 4.0: https://creativecommons.org/licenses/by/4.0/
Original practice material and adaptations by Skill Workbench. USDOL/ETA has not approved, endorsed or tested these additions. O*NET is a trademark of USDOL/ETA.