Inventory reconciliation, from SKU data to discrepancy review.
A transparent, practical inventory tracking exercise using 17 real Shopify SKU references from five demo-store products and fictional quantities to demonstrate variance formulas, investigation documentation and approval controls.
The Excel workbook version includes a dashboard, formula-driven tracker, investigation log, SOP, and provenance notes. These public CSV files can be opened and edited in Excel or Google Sheets.
RECRUITER QUICK REVIEW
Case study at a glance
- Task
Practice how to flag stock discrepancies without treating demo inventory as genuine stock.
- Actions
Combined 17 real SKU references with fictional counts, variance formulas, and six simulated exceptions.
- Evidence
Interactive local-only reconciliation tracker, investigation log, SOP, and CSV downloads.
- What I Learned
Never infer that zero on an untracked inventory record means sold out; obtain approval before adjustments.
All quantities, shortage/surplus calculations, and exceptions are simulations
The challenge and the approach
Challenge
A Shopify store may have organized SKUs but insufficient tracking to establish actual stock. An entry-level operations assistant needs a repeatable way to compare a book balance to a count, flag discrepancies, document possible causes, and request review without changing inventory prematurely.
Solution demonstrated
Use real catalog identity fields (product names, variants, SKUs, retail reference prices), add simulated expected/count quantities, calculate variances, categorize issues and prepare a discrepancy log. Real retail prices are not treated as stock cost.
Baseline Shopify product/variant snapshot: 22 October 2026. The original source names below predate some separate product-title edits in the product-listing case study. Counts and investigations were invented solely for this exercise.
Try the inventory variance tracker
Change the blue Book or Counted quantity fields. The variance, shortage/surplus result and dashboard above will update instantly inside this browser only. Nothing is sent to Shopify, saved to the server or used as real stock.
| Product · Variant | Real SKU | Book (sim) | Counted (sim) | Variance | Result |
|---|---|---|---|---|---|
| 3 in 1 Wireless Charging StationOnly Charger Holder / None | 1351072_TJLGMJQ | 0 | Match | ||
| 3 in 1 Wireless Charging StationBlack / With EU plug | 1351072_ZNEZ0SX | -2 | Shortage | ||
| 3 in 1 Wireless Charging StationBlack / With US plug | 1351072_AUUCSOY | 0 | Match | ||
| 3 in 1 Wireless Charging StationBlack / With UK plug | 1351072_MLQU81B | 1 | Surplus | ||
| 3 in 1 Wireless Charging StationNew Only Charger Holder / None | ABR5MRSUQ | 0 | Match | ||
| Bluetooth EarphonesWhite | 1217238_6ZIHGUT | -2 | Shortage | ||
| Bluetooth EarphonesBlack | 1217238_LOUW5Y7 | 0 | Match | ||
| Bluetooth SpeakerPink | MTTR711YZD | 0 | Match | ||
| Bluetooth SpeakerBlack | PZUOXS7XPQ | 0 | Match | ||
| Bluetooth SpeakerYellow | HZQJGPUG3O | -2 | Shortage | ||
| Gamer LED Mouse Pad WaterproofBlack / 250mm X 350mm | 1059275_DPHRLJQ | 0 | Match | ||
| Gamer LED Mouse Pad WaterproofBlack / 300mm X 800mm | 1059275_GWLSWWR | -1 | Shortage | ||
| Airpods Shield CaseBlack / 4.8*6.2 Centimeter | GEGZXR6WGY | 0 | Match | ||
| Airpods Shield CaseGold / 4.8*6.2 Centimeter | PFE679VKED | 0 | Match | ||
| Airpods Shield CaseRose Gold / 4.8*6.2 Centimeter | XTN7W7QO3D | 1 | Surplus | ||
| Airpods Shield CaseSilver / 4.8*6.2 Centimeter | YK47XLB5RM | 0 | Match | ||
| Airpods Shield CaseRed / 4.8*6.2 Centimeter | UZEYUH1PRQ | 0 | Match |
Simulation: 566 expected units; 561 counted units. The figures are fictional.
Six simulated exceptions to investigate
Potential causes are hypotheses, not verified incidents. The investigation log outlines what to check before any real adjustment could be approved.
| SKU | Variance | Possible cause (hypothesis) | Proposed next check | Review |
|---|---|---|---|---|
1351072_ZNEZ0SX | -2 | Possible picking/count discrepancy | Recount; compare receiving and dispatch logs | Open / simulated |
1351072_MLQU81B | 1 | Possible return not entered | Inspect returns and receiving logs | Open / simulated |
1217238_6ZIHGUT | -2 | Possible outbound movement not entered | Review movement log; recount | Open / simulated |
HZQJGPUG3O | -2 | Possible mis-pick or damaged unit | Recount and check storage locations | Open / simulated |
1059275_GWLSWWR | -1 | Possible stock movement not recorded | Review issuance and dispatch log | Open / simulated |
XTN7W7QO3D | 1 | Possible restock timing difference | Review receipts; recount | Open / simulated |
What is real, and what is simulated?
Actual Shopify-derived records
- Five product references and 17 variant SKUs from a dated Admin snapshot
- Variant names and retail prices as recorded on 22 October 2026
- An existing Admin screenshot showing inventory was not tracked at that time
Training-only records
- All expected and physical count figures
- The six discrepancies and nine absolute units of difference
- Suggested root causes, investigation steps and review statuses
No live inventory changes were made. This is a workflow example, not a real warehouse reconciliation.
How an inventory assistant should reconcile stock
- Confirm product, variant and SKU identity against the store's current item records.
- Obtain verified book balances from authorized inventory records, not from the zero value returned for untracked Shopify stock.
- Carry out or receive approved physical counts and record time, location and counter.
- Calculate Counted − Book, then categorize shortages, surpluses and matches.
- Recount exceptions, review receiving, returns and fulfillment logs, and document findings.
- Seek manager/client approval before posting any real stock corrections.
- Preserve an audit trail and report outcomes without changing prices or product content.