Challenge Scenario
Warehouse operations conduct periodic physical inventory audits (cycle counts) to reconcile actual shelf stock against software ERP records. Counting variances reveal misplaced stock (overage) or lost/stolen inventory (shortage/shrinkage).
As a Warehouse Operations Auditor, you are reconciling four stock lines:
- Quantity Variance (Column D): Subtract System Qty from Counted Qty:
=C2-B2 (negative = shortage, positive = overage).
- Variance Dollar Value (Column F): Multiply quantity variance by unit cost:
=D2*E2.
- Audit Tag (Column G): If variance is 0, assign
"BALANCED"; if less than 0, mark as "SHORTAGE"; otherwise mark as "OVERAGE" using =IF(D2=0, "BALANCED", IF(D2<0, "SHORTAGE", "OVERAGE")).
- Total Net Variance Value (Cell F7): Sum the dollar impact across all items using
SUM(F2:F5).
- Total Shortage Items (Cell G7): Count items experiencing shortage using
COUNTIF(G2:G5, "SHORTAGE").
Your Sheet Layout
Understanding the stocktake reconciliation schedule:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Product, System, Counted Qty |
Physical Stocktake |
Recorded ERP quantities vs physical counts |
Read-only stock count entries. |
D2:D5 |
Variance |
Unit Difference |
=C2-B2 |
Physical count minus recorded system stock. |
E2:E5 |
Unit Cost ($) |
Costing Reference |
Replacement wholesale cost per unit |
Read-only unit cost values. |
F2:F5 |
Variance ($) |
Financial Impact |
=D2*E2 |
Total monetary gain/loss from discrepancy. |
G2:G5 |
Audit Tag |
Classification |
=IF(D2=0, "BALANCED", IF(D2<0, "SHORTAGE", "OVERAGE")) |
Inventory flag (BALANCED, SHORTAGE, OVERAGE). |
F7 |
Net Variance Value |
Summary |
=SUM(F2:F5) |
Net inventory dollar impact (-$55.00). |
G7 |
Shortage Items |
Summary |
=COUNTIF(G2:G5, "SHORTAGE") |
Products suffering from missing stock (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells D2:D5, calculate unit variance (= Counted Qty - System Qty).
2
In cells F2:F5, calculate dollar variance value (= Variance * Unit Cost).
3
In cells G2:G5, assign audit tag ("BALANCED" if 0, "SHORTAGE" if <0, otherwise "OVERAGE").
4
In cell F7, sum net variance value, and in G7 count total items with "SHORTAGE".
Solution & Formula Breakdown
Let's review the physical cycle count audit calculations:
1. Unit Quantity Variance (D2:D5)
=C2-B2
- Earbuds: 96 counted - 100 system = -4 units (shortage).
- Headset: 50 counted - 50 system = 0 units (perfect match).
- Cable Pack: 78 counted - 75 system = +3 units (overage).
- Speaker: 115 counted - 120 system = -5 units (shortage).
2. Discrepancy Dollar Value (F2:F5)
=D2*E2
- Earbuds: -4 units × $25.00 cost = -$100.00.
- Headset: 0 units × $80.00 cost = $0.00.
- Cable Pack: +3 units × $40.00 cost = +$120.00.
- Speaker: -5 units × $15.00 cost = -$75.00.
3. Audit Classification (G2:G5)
=IF(D2=0, "BALANCED", IF(D2<0, "SHORTAGE", "OVERAGE"))
- Earbuds (-4): < 0 → SHORTAGE.
- Headset (0): = 0 → BALANCED.
- Cable Pack (+3): > 0 → OVERAGE.
- Speaker (-5): < 0 → SHORTAGE.
4. Audit Summary Metrics (F7 & G7)
- Cell F7 (Net Impact):
=SUM(F2:F5) → -$55.00 net shrinkage.
- Cell G7 (Shortage Count):
=COUNTIF(G2:G5, "SHORTAGE") → 2 items.
In physical warehousing, always subtract system numbers from physical counts (Count - System). That way, negative numbers represent missing stock and positive numbers represent found stock!