Stocktake Analysis: Reconcile Counted and Recorded Inventory

Stocktake analysis compares counted inventory with book quantities by SKU and location, then values the differences using the approved inventory cost. Show both the signed net adjustment and absolute discrepancies so opposing errors remain visible.

Start with a common inventory cutoff

Save the inventory-book export for the count cutoff. Record the count date and time, warehouse, bin, SKU, unit of measure and counter. Keep the original count and any recount separately so the final figure has an audit trail.

The IRS guidance on perpetual or book inventory says physical inventory must be taken at reasonable intervals and book inventory adjusted to agree with actual inventory. That does not make an initial count ready for posting: timing, ownership and count errors still need review.

Use one comparison row for each SKU, site, bin and inventory status included in the count scope. Aggregate count sheets to that grain before joining them to the book export. A lot-controlled operation may also need lot identifiers. Repeated book balances on individual count tickets would multiply recorded inventory.

Prepare the count and book exports

Keep these fields in the reconciliation workbook:

List unmatched rows from both exports. A counted SKU missing from the book needs an exception row, and a book SKU missing from the count needs investigation. Neither should disappear because a matching operation found no partner.

For the wider review of inventory balances and movement, see inventory analysis. The count comparison here establishes whether recorded stock matches physical evidence.

Work the quantities and values

An illustrative US distributor counts four SKU-site combinations on October 23, 2026. Finance supplies one approved unit cost per row; the example has no cost-layer complications, and all amounts are exact without rounding.

Put this input table in columns A through E, with the headings in row 1:

Unit variance = Counted units - Book units

Value variance = Unit variance × Approved unit cost

Add output columns F through J for unit variance, signed value variance, absolute value variance, book value and counted value. Enter these formulas in row 2 and fill down through row 5:

F2 =D2-C2
G2 =F2*E2
H2 =ABS(G2)
I2 =C2*E2
J2 =D2*E2

The resulting values are:

For A100, the count is four units below the book: (96 - 100) × $12.50 = -$50. The net discrepancy is -$20, but the absolute discrepancies total $140. Reviewing only the net would conceal most of the value needing explanation.

Reconcile before investigating causes

The counted value minus book value must equal the signed variance: $6,230 - $6,250 = -$20. This workbook check must return zero:

=SUM(J2:J5)-SUM(I2:I5)-SUM(G2:G5)

Then tie the book value to the inventory subledger for the same scope. A partial warehouse count will not equal the entire company's inventory balance; identify the excluded sites and categories explicitly. Finance should reconcile the full subledger to the inventory control account separately.

These values use supplied costs to isolate quantity differences. OpenStax's inventory valuation guidance explains that cost-flow assumptions differ from the physical flow of goods. If the actual ledger uses layers, ask finance to translate confirmed shortages into the appropriate accounting values. The weighted-average inventory cost guide covers that specific costing approach.

Turn discrepancies into an approved correction list

Sort exceptions by absolute value and check the largest ones first, while retaining any sensitive or controlled items regardless of value. Recount the bin without showing the expected book quantity where practical. Check neighboring bins, receiving areas, unposted shipments and transfer records.

Give each exception a status, evidence, reason, owner and proposed correction. Distinguish a verified physical shortage from an unposted movement: posting both a missing shipment and a shortage adjustment for the same units would duplicate the correction. Use a stock-movement review when receipts, shipments or transfers explain the gap.

Warehouse operations owns the physical evidence; inventory control owns movement completeness; finance owns costing and approval of ledger adjustments. Counted stock can still be obsolete or damaged. Treat that separate question through inventory aging by site, rather than assuming a quantity match proves recoverable value.

Where it goes wrong

• Mixed cutoffs. A shipment between the book export and the physical count looks like a shortage unless rolled into the comparison.
• Cases compared with units. A unit conversion error creates a discrepancy that a recount alone cannot resolve.
• Missing counts treated as zero. Unfinished work becomes an invented shortage. Keep a count-complete flag and resolve blanks.
• Opposing differences netted early. Site transfers and bin errors disappear from a signed total. Retain both the net and absolute figures.
• Costs changed during the analysis. Updating only some rows mixes quantity errors with valuation changes and breaks the original reconciliation.

Reconcile your own count in Covirage

Bring your stock-count file and the corresponding inventory-book export with approved cost fields; Covirage maps the columns and its tools compute and check the quantity and value differences. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data; creating the account takes about a minute.

See Covirage for distributors for the operating context.