How does this template calculate inventory accuracy?

Location accuracy = counts that match / all counts. A count matches when |counted - system| / system is within the tolerance for the item's class. With the Inventory Location Accuracy calculator's example of 8 discrepant locations in 250 counted, accuracy is (250 - 8) / 250 = 96.8 percent, 1.8 points above a 95 percent target; the workbook gives the same figure when 8 of 250 rows read Match = No.

What tolerance should I set for each class?

It is a policy choice, not a standard. Many programmes use no tolerance for A items and a small one for low-value C items that are counted by weight or estimate. In the example, a B item counted at 245 against 250 in the system is 2.0 percent short and matches at a 2 percent tolerance, while a C item counted at 1,060 against 1,000 is 6 percent over and misses at 5 percent.

What if the first count does not match?

Recount before you log it, and enter the verified quantity. Otherwise a counting mistake is booked as a record error and adjusted into the system. Use Notes for the first count if you want to keep it; accuracy, variance and the adjustment value are all calculated from the verified count.

Why track net and gross adjustment value?

Because gains and losses cancel in the net figure. In the example the net write-down is 4,462, but the gross adjustments are 17,460: four times as much money in records that were wrong. Gross value shows how much the records are wrong; net value shows the effect on the balance sheet.

How many locations should I count each day?

Set count frequencies by class and divide by your working days. For example, 12 counts a year for 60 A items, 4 for 150 B items and 1 for 400 C items is 720 + 600 + 400 = 1,720 counts a year, about 7 a day over 250 working days. The ABC Inventory Analysis template works this out from your item list.

Can I use it without Excel?

Yes: a CSV with live formulas of the Count Log opens in any spreadsheet, with variance, tolerance, Match and adjustment values as formulas against the tolerances listed below the rows, and net and gross adjustment totals. The dashboard, charts, dropdowns and self-check are in the .xlsx.

Planning and Supply Chain

Cycle Count Tracker Excel Template

Log every cycle count against the system quantity, let the class tolerance decide whether it matched, and watch location accuracy, adjustment value and the reasons behind the misses week by week.

Overview

This cycle count tracker is a working Excel workbook for inventory control leads, stores and warehouse supervisors and materials managers who count a few locations every day instead of shutting down for an annual stocktake. It ships with example data for a fictional pump maker's stores, 150 counts in four zones over six weeks, so the dashboard, charts and tables are populated the moment you open it; replace the rows with your own counts and everything recalculates.

Each row compares the quantity the system shows with the verified count. The variance, in units, percent and value, is calculated for you, and the item's ABC class picks the tolerance you set on Setup: a count matches when the absolute variance divided by the system quantity is within it, and stock found where the system shows none is always a miss. Location accuracy is the share of counts that match, the same calculation as the Inventory Location Accuracy calculator on this site, where every miss is a discrepant location.

In the example, 132 of 150 counts match, a location accuracy of 88.0 percent against a 95 percent target, with 17,460 of gross adjustments and a net write-down of 4,462. The weekly chart shows the turn: 79 percent over the first three weeks and 97 percent over the last three, after scan-to-confirm picking started in week four, and the Pareto shows that stock put away or moved without a location transfer was the biggest single reason for misses.

What this template includes

Count Log table: date, location, zone, item, ABC class, unit cost, system quantity and verified counted quantity, one row per location counted
Per count: variance in units and percent, the tolerance for the item's class, a Match word (Yes/No), and the adjustment value and its absolute size
Your own accuracy target and count tolerances for A, B and C items on Setup (95% and 0%, 2%, 5% in the example), with store zones and miss reason codes as dropdown lists
Dashboard KPI tiles: location accuracy against target, counts logged, gross adjustment value and the biggest miss reason
Two native charts: location accuracy by week with the target line, and a Pareto of misses by reason with the cumulative percentage
Tables by week (counts, misses, accuracy, net and gross adjustment), by class and by zone
Check column that names problems in words: a miss without a reason, a zone or reason not on the list, the same location counted twice in a day, a missing value
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference, including the Inventory Location Accuracy calculator's example (Self-check: PASS)

Suggested use case

Use it to run a cycle-count programme without a WMS module: record each count, measure location accuracy against your target, see what the adjustments cost, and find the process (receiving, picking, putaway, issues to production) that keeps breaking the records.

How to use it

On Setup, enter your company name, currency label, accuracy target and the count tolerance for A, B and C items, and adjust the zones and reason codes.
On Count Log, add one row per location counted with the system quantity at the time of the count and the verified physical count.
For every row with Match = No, pick the reason the record was wrong; the Check column flags misses without a reason.
Open the Dashboard to see accuracy by week against the target, misses by reason, and accuracy by class and zone.
Work on the top reason in the Pareto, and use the weekly trend to show whether the fix held.

Frequently Asked Questions

How does this template calculate inventory accuracy?: Location accuracy = counts that match / all counts. A count matches when |counted - system| / system is within the tolerance for the item's class. With the Inventory Location Accuracy calculator's example of 8 discrepant locations in 250 counted, accuracy is (250 - 8) / 250 = 96.8 percent, 1.8 points above a 95 percent target; the workbook gives the same figure when 8 of 250 rows read Match = No.
What tolerance should I set for each class?: It is a policy choice, not a standard. Many programmes use no tolerance for A items and a small one for low-value C items that are counted by weight or estimate. In the example, a B item counted at 245 against 250 in the system is 2.0 percent short and matches at a 2 percent tolerance, while a C item counted at 1,060 against 1,000 is 6 percent over and misses at 5 percent.
What if the first count does not match?: Recount before you log it, and enter the verified quantity. Otherwise a counting mistake is booked as a record error and adjusted into the system. Use Notes for the first count if you want to keep it; accuracy, variance and the adjustment value are all calculated from the verified count.
Why track net and gross adjustment value?: Because gains and losses cancel in the net figure. In the example the net write-down is 4,462, but the gross adjustments are 17,460: four times as much money in records that were wrong. Gross value shows how much the records are wrong; net value shows the effect on the balance sheet.
How many locations should I count each day?: Set count frequencies by class and divide by your working days. For example, 12 counts a year for 60 A items, 4 for 150 B items and 1 for 400 C items is 720 + 600 + 400 = 1,720 counts a year, about 7 a day over 250 working days. The ABC Inventory Analysis template works this out from your item list.
Can I use it without Excel?: Yes: a CSV with live formulas of the Count Log opens in any spreadsheet, with variance, tolerance, Match and adjustment values as formulas against the tolerances listed below the rows, and net and gross adjustment totals. The dashboard, charts, dropdowns and self-check are in the .xlsx.