📌 Stage 6 · Count, Shrink, and Reorder · Inventory & COGS · Cycle counts · 30-40 min

Variance times unit cost, and the bridge back into stock

Learning goals

  • Run a cycle count: system quantity versus shelf quantity
  • Value the variance at current unit cost with a VLOOKUP
  • Let the count adjust stock through the SKUs shrink column

Concepts

Count a little, often

A cycle count checks one SKU at a time on a rolling schedule instead of shutting the studio for a full physical inventory. Each row records what the sheet believed (System Qty, snapshotted at count time) and what the shelf said (Counted Qty). The difference is the variance: negative is shrinkage, positive is found stock.

CNT-001 counted JG-01 on Sep 20: the sheet said 78, the shelf held 75. Three jars had cracked in a dropped tote. CNT-003 found one extra JG-04 gift set, a customer return restocked as resaleable.

=$G5-$F5

Variance: counted minus system. Negative means the shelf is short; zero means the sheet and the shelf agree.

=VLOOKUP($D5,SKUs!$B$5:$F$104,5,FALSE)

Unit cost for valuing the variance: the current BOM roll-up from SKUs column F, the fifth column of the range starting at B. JG-01 values at $3.69.

=$H5*$I5

Shrink value: variance times unit cost. The three cracked jars cost -$11.07. Positive found stock shows as a positive value.

💡 Tips:

  • The system quantity column is typed as a snapshot of the moment you counted. Reconcile soon after counting, before more orders move the number.

The variance flows home

The status column triages each count: Shrinkage, In sync, or Found. Meanwhile SKUs column K sums the variances per SKU (-3 for JG-01, +1 for JG-04), so on hand drops or rises the same hour.

Net across September, shrinkage cost $11.07 and the return recovered $8.52, a net -$2.55. Small, but now it is visible, valued, and reflected in stock; without counts, reorder points in the next lesson would steer from numbers the shelf contradicts.

Practice

Fixed case (Sep 2026). The exercise opens on Cycle Counts. Pale-green cells are formulas; you type the count number, date, SKU, system qty, and counted qty.

  1. Read the three counts — CNT-001 (JG-01, -3), CNT-002 (JG-03, 0, In sync), CNT-003 (JG-04, +1, Found). Check the notes for causes.
  2. Type the shrink value — Cell J5 is blank. Type =$H5*$I5 and press Enter. The cracked jars value at -$11.07. Fill down through J7.
  3. Follow the bridge — Open SKUs. K5 reads -3 and K8 reads +1; on hand for JG-01 is 47 and for JG-04 is 13, both already adjusted.
  4. Read the net — Net shrink across the month: -$11.07 + $0.00 + $8.52 = -$2.55. Two minutes of counting kept the sheet honest.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed =$H5*$I5 into Cycle Counts!J5 and got -$11.07
  • ☐ Confirmed the status column shows Shrinkage / In sync / Found for the three counts
  • ☐ Traced the -3 and +1 variances into SKUs columns K and L
  • ☐ Verified the baseline: JG-01 on hand 47 after shrink, net shrink value -$2.55