Inventory Cycle Count in Excel (Free Template + Variance Tracker)

What a cycle count does

A cycle count is a physical check of selected inventory items on a recurring schedule. Instead of waiting for one annual stocktake, you count smaller groups, compare what you find with the inventory record, and investigate differences by SKU and location.

This example uses eight items in three preassigned ABC classes. It does not calculate ABC classes automatically: set those classes using your own inventory value, risk, and counting capacity. The sample schedule uses 30 days for A items, 60 for B, and 90 for C as editable starting intervals. Those intervals are planning choices, not a universal accounting rule.

If a count also reveals that an item may fall below its reorder point, use the inventory reorder tracker in Excel to plan replenishment. For a broader formula review before sharing a workbook, see the Excel workbook audit checklist.

Step 1: Set up the items and count date

1. Open Count Log. In B3, enter the date the physical count is performed. The sample uses Oct 14, 2026.
2. For each item, enter a unique SKU, item name, storage location, ABC class, system quantity, unit cost, and a count date. In the sample, the first item is USB-C Dock, SKU A-101, in P1-A01.
3. Keep one row per SKU and location. If the same item sits in two locations, count and reconcile each location separately so an overage in one place does not hide a shortage in another.
4. Replace the sample rows with your own inventory, or copy the workbook and clear only the sample inputs before use. Keep the formula columns H, I, and K intact.

Step 2: Record a blind physical count

A counter can be influenced by seeing the expected quantity. For an independent count, save a copy of the workbook, temporarily hide the System qty column E, and print or share the SKU, item, location, Count 1, and Recount fields. In Excel, select column E, right-click its heading, and choose Hide. Restore the column after the count is entered.

Enter the observed quantity in Count 1 (column F). Do not overwrite the system quantity with what you saw on the shelf. The difference between the two is the control this worksheet is meant to reveal.

Step 3: Recount differences before approving them

Do a second count when your policy requires one or the first result looks unusual. Enter the confirmed quantity in Recount (column G); leave G blank when no recount is needed. A recorded zero is a valid count and must not be left blank.

In H8, the template chooses the recount when it exists and otherwise uses Count 1. The formula is copied through H15:

=IF(G8<>"",G8,IF(F8<>"",F8,""))

The outer IF checks whether G8 is populated. If so, G8 becomes the approved quantity. Otherwise, the formula falls back to F8. In the sample, the webcam’s first count is 39 and the recount is 40, so H12 returns 40.

Step 4: Calculate unit and value variances

In I8, subtract the system quantity in E8 from the approved physical quantity in H8. Copy the formula down to I15:

=IF(H8="","",H8-E8)

A positive result is an overage; a negative result is a shortage. In K8, multiply the unit variance by the unit cost in J8, then copy down to K15:

=IF(I8="","",I8*J8)

For C-310, the approved count is 250 and the system quantity is 240, so the unit variance is 10. At a unit cost of $4.25, the value variance is $42.50. A negative value means the counted stock is below the recorded quantity at the costs entered; it is not, by itself, an approved accounting adjustment.

Step 5: Record the cause and approval

Use column L to document what you checked and what supports the result. Depending on the item, compare receiving records, sales and issue slips, transfers, returns, damaged-stock reports, or the last approved count. Keep the note tied to the SKU and location.

The sample notes that the webcam recount was confirmed at 40 and that the cable overage needs a returns review. These are investigation notes, not proof that an inventory journal entry has been approved. Have the responsible person review and sign off according to your business policy before changing the inventory record or posting an adjustment.

Step 6: Plan the next count and verify the workbook

Open Item Schedule. The template links each item’s class and last count date from Count Log, assigns a starter interval, and calculates a next count date. In G6, the pattern is:

=IF(E6="","",E6+F6)

For the sample count date of Oct 14, 2026, the A items are due for their next planned count on Nov 13, B items on Dec 13, and C items on Jan 12, 2027. Adjust the frequencies in column F to match your actual policy.

Back on Count Log, check H3. It should show Ties, confirming that the value variance total agrees with the line-by-line calculation. Then test that the workbook responds: change F13, the C-310 first count, from 250 to 248. The row variance should become 8, net unit variance should become 3, and net value variance should become -$103.00. H3 should still say Ties. Restore F13 to 250 after the check.

Common mistakes and fixes

Frequently asked questions

Final checklist

• Each count row identifies one SKU and one location.
• Blind counts omit the system quantity from the counter’s copy.
• Recounts are entered in column G and confirmed before approval.
• Each variance has evidence, an owner, and a documented next action.
• Unit costs and quantities use the same unit of measure.
• H3 shows Ties, and the count schedule reflects your policy.
• Inventory adjustments are approved and reconciled separately.