Reconcile on-hand inventory: source events, reservations and a checked CSV summary

Fix the question: physical stock, not a sales promise

The example has one SKU, one warehouse and one reporting window. All quantities are nonnegative unit counts; event types determine their direction. A reservation records a commitment in this case, without a physical removal or shipment.

A real system may display on-hand, available, reserved and in-transit measures with its own definitions. The 15 units here are not automatically a sellable quantity, and this exercise is not an accounting audit. State the metric before comparing answers that may be about different things.

The source is one opening balance and four events

These are original teaching records, not a customer warehouse or a model response. The opening 10 units are the balance at the start, not another receipt. E1 through E4 occur in the stated order within the window; SKU and warehouse remain unchanged. Real cross-warehouse, time-zone or packaging differences need explicit definitions first.

The quantity column uses positive numbers. Do not convert shipment to −5 and subtract it again during summation. Preserve the reservation row and explain its zero physical effect instead of silently dropping an inconvenient record.

Opening balance: SKU-A, 10 units
event_id,sku,type,quantity
E1,SKU-A,receipt,8
E2,SKU-A,shipment,5
E3,SKU-A,reservation,3
E4,SKU-A,physical_return,2

Keep source quantities apart from signed movement

The exercise ZIP supplies opening.csv, events.csv, classified.csv, wrong-reservation.csv and a reference ledger. All are authored teaching materials. events.csv is the original four-event input; classified.csv is an editorial interpretation to review, not a second source or an automatically verified AI reply.

For E2, quantity 5 remains positive in the source while physical_delta becomes −5 once. For E3, quantity 3 remains in the record while physical_delta is 0. That zero describes the stated event meaning; it is not a replacement for missing evidence.

event_id,sku,type,quantity,physical_delta
E1,SKU-A,receipt,8,8
E2,SKU-A,shipment,5,-5
E3,SKU-A,reservation,3,0
E4,SKU-A,physical_return,2,2

Request classification before the total

Ask AI to retain identifiers, explain whether each event changes physical stock and show the running balance. A final number alone can hide a duplicated opening balance or a reservation treated as a shipment.

The reference answer is editorially derived from the stated rules; no third-party model was run. If you execute the request, preserve the original response and compare each event with the table before adopting its narrative.

Review fictional stock records without changing any system. The target is physical on-hand units only. Opening is 10 and is not a receipt. Every quantity is nonnegative. receipt and physical_return increase on-hand; shipment reduces it; reservation has no physical effect in this case. Retain E1–E4 and return classification, signed effect, running balance, closing on-hand and unresolved facts. Do not invent a count adjustment.
Opening balance: SKU-A, 10 units
event_id,sku,type,quantity
E1,SKU-A,receipt,8
E2,SKU-A,shipment,5
E3,SKU-A,reservation,3
E4,SKU-A,physical_return,2

Run the summary and reconcile the downloaded rows

Open Data summary. The tool sums a numeric column; it does not understand receipts, shipments or reservations and does not automatically add an opening balance. Complete the classification review first.

1. 1
   Import only classified.csv. Verify four current working rows. Keep opening.csv outside this import so it is not appended as an event.
2. 2
   In Group summary (current working data), choose sku as Grouping field and physical_delta as Sum / numeric validation field, then run Group summary.
3. 3
   Download CSV and reopen it. Expect Group=SKU-A, Count=4, Total=5. This 5 is a net change, not the closing balance. Keep the localized result headers distinct from source fields.
4. 4
   Add the separately preserved opening 10 to net 5 for closing 15. Check the running balances 18,13,13,15 against E1–E4, not only the last number. Keep E3 and its original quantity 3 traceable.

Twelve may reflect a different metric

An intentionally incorrect editorial draft calls 10 + 8 − 5 − 3 + 2 = 12 physical on-hand units. Its arithmetic is correct but its treatment of the reservation violates this task. Physical stock is 15. If another system-defined available measure is needed, give it a separate name and rule instead of keeping the on-hand label.

In a spreadsheet, derive physical_delta as 8, −5, 0 and 2. With columns A:E defined as event_id, sku, type, quantity and physical_delta, use 10 + SUMIFS(E2:E5,B2:B5,"SKU-A") to obtain 15. Microsoft documents conditional summation with SUMIFS; the source and business definition still determine whether each classification is valid.

A four-row summary can pass arithmetic and fail meaning

In a fresh run import wrong-reservation.csv. Only E3 physical_delta changes from 0 to −3. The same summary now returns Count 4 and Total 2. Adding opening 10 produces 12, with every source ID still present. This is a constructed bad classification, not an observed model error rate.

Reject that result by comparing E3 with the declared physical-event rule. Restore its physical_delta to 0 from that evidence, retain quantity 3 and rerun the reviewed classified.csv. Do not delete the reservation row or use the physical count 16 as the closing reference answer.

If a physical count is 16, retain the difference

Suppose a separate count at the same closing point reports 16. It differs from the ledger’s 15 by +1. Nothing in the four events explains that unit. Report an unresolved difference rather than inventing a missing return of one; that would promote a guess into a source fact.

Check cutoff, omitted events, SKU, warehouse and unit. Make any adjustment only with supporting evidence and the appropriate process. Related checks include reconciliation after a join multiplies rows and separating currencies before totaling. Neither substitutes for defining this stock metric.