Inventory balances and cycle count variance in a sheet
Roll stock from opening to closing quantities, calculate a manual reorder quantity, and compare book stock with physical counts without canceling discrepancies.
Closing stock equals opening stock plus receipts less issues. A cycle count then compares that book quantity with the quantity physically found. Keep the stock roll-forward and count variance distinct: one explains recorded movements, while the other identifies discrepancies to investigate.
How do you calculate a closing inventory balance?
For a fictional SKU with 80 opening units, 20 received units, and 65 issued units, closing stock is 80+20-65, or 35 units. With those inputs in B11, C11, and D11, use =B11+C11-D11.
Every quantity in the row must use the same unit. If receipts are cartons and issues are pieces, convert them using a documented pack factor before combining them. A formula cannot infer that a carton contains twelve pieces.
The inventory stock tracker keeps opening, receipts, issues, minimum, target, and closing units visible. It leaves negative closing balances visible so an impossible recorded position can be investigated.
How do minimum and target stock affect reorder quantity?
Suppose minimum stock is 40 units and target stock is 100. A closing balance of 35 is below the minimum, so a target-based reorder is 100-35, or 65 units. At 85 units, no reorder is triggered by this policy even though the balance is below the target.
If closing stock is G11, minimum is E11, and target is F11, the formula is =IF(G11<E11,MAX(0,F11-G11),0).
These thresholds are manual policies. They do not calculate an economic order quantity, service level, or safety stock. Account separately for outstanding purchase orders if your operational definition requires inventory position rather than physical stock alone.
How do you calculate cycle count variance?
Book stock of 100 units and a physical count of 97 gives a quantity variance of 97-100, or -3 units. At $12 per unit, the cost variance is -$36.
| SKU | Book units | Counted units | Unit cost | Cost variance |
|---|---|---|---|---|
| SKU-001 | 100 | 97 | $12 | -$36 |
| SKU-002 | 50 | 53 | $20 | $60 |
| SKU-003 | 75 | 75 | $8 | $0 |
The net cost variance is $24, but the absolute cost variance is $96: ABS(-36)+ABS(60)+ABS(0). The net figure alone understates the amount of discrepancy needing attention. The cycle count workbook shows both measures.
What should you check before adjusting stock records?
Confirm the count cutoff, unit of measure, storage location, unposted receipts, unposted issues, and duplicate SKU entries. A difference can come from timing or location rather than actual loss. Record the investigation and approval before posting an adjustment in the source system.
The workbook applies one entered unit cost per SKU. It does not reconstruct FIFO layers or determine the accounting value of an adjustment. Keep the quantity investigation and the costing decision traceable to their separate sources.