Inventory supplier merge
Quarry Lane Hardware (fictional)
Consolidate a multi-sheet stock count workbook, compare it with the reorder policy and pick the best supplier for everything that needs ordering.
- Level
- advanced
- Time
- ~70 min
- Deliverables
- 2
- Rubric
- v1
Access
Pro. This brief is public. The data pack, workspace, hints, grading and solution explanations need Pro, Lifetime Learning or a seat in an organization. See plans.
Each deliverable's solution explanation unlocks when it passes. You can also unlock it early once you have used every hint for that deliverable (this needs an account so the help is recorded, and the deliverable then counts as completed with help).
Business goal
Give the purchasing lead a reorder list that says how many of each item to buy and from whom, refreshed every time the warehouse saves its count workbook.
Quarry Lane's warehouse team counts stock into a workbook with one sheet per area. Each sheet ends with a total row. Last year's counts are kept on a hidden sheet. Purchasing keeps a reorder policy (reorder point and target stock per item) and a catalogue of supplier offers, where several suppliers can sell the same item. Purchasing wants every item below its reorder point, the quantity to bring it back to target, and the cheapest supplier (ties go to the shortest lead time, then supplier name).
Graded deliverables
1. Stock On Hand
40 pointsTotal counted stock per SKU across every visible sheet of the workbook.
- Columns: SKU, OnHand, Bins
- One row per SKU; hidden archive sheet excluded
- No Total or empty rows
- OnHand is a whole number
Expected output schema for Stock On Hand Column Type SKU text OnHand whole number Bins whole number - Grain:
- One row per SKU counted on any visible sheet.
- Order:
- Not graded.
- Nulls:
- No nulls.
2. Reorder List
60 pointsEvery policy item below its reorder point, the quantity to order and the best supplier.
Builds on: Stock On Hand
- Columns: SKU, OnHand, ReorderPoint, ReorderQty, Supplier, UnitCost, LeadTimeDays, OrderValue
- Includes policy items that were not counted (OnHand 0)
- Exactly one supplier per item, chosen by cost, then lead time, then name
- Sorted by SKU
Expected output schema for Reorder List Column Type SKU text OnHand whole number ReorderPoint whole number ReorderQty whole number Supplier text UnitCost decimal or null LeadTimeDays whole number or null OrderValue decimal or null - Grain:
- One row per policy item whose stock is below its reorder point.
- Order:
- Graded: SKU ascending.
- Nulls:
- UnitCost, LeadTimeDays and OrderValue are null only when Supplier is NO SUPPLIER.