Challenge Scenario
In warehouse and supply chain management, running out of stock halts manufacturing and loses customer sales. To prevent stockouts, operations managers calculate the Reorder Point (ROP) using the standard formula:
ROP = (Lead Time × Daily Sales) + Safety Buffer.
When Current On-Hand Stock drops to or below the ROP (E2 <= F2), a purchase order must be placed immediately.
As a Warehouse Inventory Controller, you are auditing five key hardware SKUs:
- Reorder Point ROP (Column F): Calculate ROP using
=(B2*C2)+D2.
- Action Required (Column G): If On Hand Qty is less than or equal to ROP (
E2 <= F2), assign "REORDER NOW"; otherwise assign "SUFFICIENT".
- Total On-Hand Stock (Cell E8): Sum all inventory units on hand using
SUM(E2:E6).
- Total Reorder Triggers (Cell G8): Count how many SKUs require ordering using
COUNTIF(G2:G6, "REORDER NOW").
Your Sheet Layout
Understanding the inventory control schedule:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:E6 |
SKU, Lead Time, Demand, Buffer, On Hand |
Warehouse Stock |
Supplier turnaround, consumption rate, and stock count |
Read-only inventory parameters. |
F2:F6 |
Reorder Point (ROP) |
Threshold Zone |
=(B2*C2)+D2 |
Calculates minimum inventory threshold. |
G2:G6 |
Action Required |
Replenishment Alert |
=IF(E2<=F2, "REORDER NOW", "SUFFICIENT") |
Triggers purchase order alert flag. |
E8 |
Total On-Hand Units |
Summary |
=SUM(E2:E6) |
Combined warehouse stock count (935 units). |
G8 |
Reorder Trigger Count |
Summary |
=COUNTIF(G2:G6, "REORDER NOW") |
SKUs requiring replenishment (3). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells F2:F6, calculate Reorder Point ROP (= (Lead Time * Daily Sales) + Safety Buffer).
2
In cells G2:G6, assign action ("REORDER NOW" if On Hand <= ROP, otherwise "SUFFICIENT").
3
In cell E8, sum total on-hand inventory units across all items.
4
In cell G8, count total items flagged as "REORDER NOW".
Solution & Formula Breakdown
Let's review the ROP replenishment calculations:
1. Calculating Reorder Point ROP (F2:F6)
=(B2*C2)+D2
- SKU-BOLT-100: (5 days × 20 units) + 40 buffer = 100 + 40 = 140 ROP.
- SKU-VALVE-200: (10 days × 15 units) + 50 buffer = 150 + 50 = 200 ROP.
- SKU-PUMP-300: (7 days × 30 units) + 60 buffer = 210 + 60 = 270 ROP.
- SKU-SEAL-400: (4 days × 12 units) + 20 buffer = 48 + 20 = 68 ROP.
- SKU-PIPE-500: (8 days × 25 units) + 50 buffer = 200 + 50 = 250 ROP.
2. Action Trigger with IF (G2:G6)
=IF(E2<=F2, "REORDER NOW", "SUFFICIENT")
- SKU-BOLT-100: 110 on hand <= 140 ROP → REORDER NOW.
- SKU-VALVE-200: 260 on hand > 200 ROP → SUFFICIENT.
- SKU-PUMP-300: 250 on hand <= 270 ROP → REORDER NOW.
- SKU-SEAL-400: 95 on hand > 68 ROP → SUFFICIENT.
- SKU-PIPE-500: 220 on hand <= 250 ROP → REORDER NOW.
3. Summary Totals (E8 & G8)
- Cell E8 (Total Stock):
=SUM(E2:E6) → 935 units.
- Cell G8 (Reorder Triggers):
=COUNTIF(G2:G6, "REORDER NOW") → 3 SKUs.
Safety buffer stock protects against sudden supplier delivery delays. Always add parentheses (B2*C2)+D2 to make your order of operations crystal clear to teammates reviewing your model!