Cycle Count Template: AI Plans Counts by ABC Class

Rank every item by annual usage value, label the top items that make up about 80% of it as A, the next 15% as B and the rest as C, then count A items most often (say monthly), B quarterly and C twice a year. AI builds the ranking and the weekly list from your stock export.

The reason this beats counting everything once a year is simple: a handful of items hold most of the money, and an error there costs far more than an error in a box of cable ties. A small warehouse can run the whole programme with nine or ten count lines a day, and a single page of rules about variances and recounts.

Follow me on Instagram@sagnikteaches

The idea in one picture: where the money sits

ABC classification sorts items by how much value passes through them in a year, which is annual units used or sold multiplied by unit cost. It is not the same as how many items you hold, or how big they are. A pallet of low-cost packaging can be a C item, and a small box of controllers can be an A item.

Connect on LinkedInSagnik Bhattacharya

A common convention uses cumulative cut-offs: the items that together make up the first 80% of annual value are A, the next 15% are B, the final 5% are C. They are conventions rather than laws, and you can set 70/20/10 if your range is flatter. What matters is that you choose them once, write them down and keep using them.

Subscribe on YouTube@sagnikteaches

Say a small distributor of bicycle parts holds 600 stock-keeping units (SKUs, meaning one line per distinct item) with an annual usage value of $1,200,000. Illustrative figures after ranking:

Two thirds of the lines are C items worth 5% of the value. That is the whole case for not treating every shelf the same.

Step 1: get the data out of your system

You need one row per SKU with these columns. Most stock systems, shop platforms and accounting tools can export them as a CSV or spreadsheet; if yours cannot give you annual usage, sales history by item and a unit cost list will do.

• SKU and short description
• Location (bin, shelf or zone)
• Units issued or sold in the last 12 months
• Unit cost (what you paid, not the selling price)
• System quantity on hand
• Date of last count and last variance, if you have them (blank is fine)

Remove anything you will never count: discontinued items with zero stock and zero movement, services, and kits that are really two other SKUs. Keep items with stock but no sales, because that is exactly what a C class is for. If the data has duplicate SKUs or units in mixed sizes, tidy it first; cleaning messy data before analysis is worth ten minutes here.

Step 2: let AI classify, then check its sums

Upload the file to ChatGPT, Claude or whichever assistant you use, or paste it in if it is under a few hundred rows. Paste this prompt:

This is a stock export with columns: SKU, Description, Location, Units_12m, Unit_cost, Qty_on_hand.
1. Add a column Annual_value = Units_12m x Unit_cost.
2. Sort by Annual_value, highest first.
3. Add Cumulative_pct = running total of Annual_value divided by the grand total.
4. Class = A where Cumulative_pct is 80% or less, B up to 95%, C above that.
5. Show me a summary table: class, number of SKUs, share of SKUs, annual value, share of value.
6. List any SKU where Units_12m is zero but Qty_on_hand is above zero as "no movement".
Give the full table as a downloadable spreadsheet and show the grand total so I can check it.

Illustrative output: a summary table very like the one above, a spreadsheet, and a short list such as "14 SKUs with no movement, worth $3,180 at cost". Those 14 are your first candidates for review rather than counting.

Do not accept the totals on trust. Chat models can drop a row or mis-add a long column, and a wrong A class defeats the point of the exercise. Check the grand total against a sum in your own spreadsheet, and for a permanent version let the spreadsheet do the work with formulas. With the data in rows 2 to 601, sorted by annual value in column E, a cumulative share in column F and the class in G:

E2: =C2*D2
F2: =SUM($E$2:E2)/SUM($E$2:$E$601)
G2: =IF(F2<=0.8,"A",IF(F2<=0.95,"B","C"))

Copy those down, and re-sort whenever you refresh the data. One quirk: with these formulas the SKU that crosses the 80% line is classed B, not A. Some owners prefer the opposite, so add a note to the template saying which rule you chose. If you want more help building formulas this way, the guide to using ChatGPT with Excel covers the approach.

Step 3: choose the frequency for each class

Frequency is a trade-off between the cost of counting and the cost of an undetected error. These are common starting points, not rules:

Run the sum for the bicycle-parts example: 78 A items counted 12 times is 936 count lines; 132 B items counted 4 times is 528; 390 C items counted twice is 780. That is 2,244 count lines a year. Over about 250 working days it is nine lines a day. At roughly three minutes a line including walking, that is under half an hour a day for one person. A full count of 600 lines, once a year, can take a team a whole weekend and stops trading.

Add event-triggered counts on top of the schedule, because some moments call for a count regardless of class:

• Zero or negative balance. When the system says nothing is left, count the location before you tell a customer it is out of stock. The system may be wrong in either direction.
• Receiving discrepancy. If a delivery's count did not match the paperwork, count that item at the next opportunity.
• Repeated picking errors. If an item keeps being picked wrong, count it and its neighbouring bins. Tracing pick errors back to their cause goes through how to separate a count problem from a labelling problem.
• High-theft or high-damage items. Small, valuable, portable things move up a frequency level whatever their class.

Step 4: have AI spread the counts across the calendar

The next job is dull but easy to get wrong: deciding which ones to count on which day, so no day is packed with 40 lines and none sits empty. Give the assistant the classified list and these rules:

Using the classified list, build a 52-week cycle count calendar.
Rules:
- A items counted once a month (12 times a year), B once a quarter, C twice a year.
- Spread so each weekday has the same number of lines, give or take two.
- Group items by Location so one person walks one aisle per day.
- Put items with a variance in their last count first in the queue.
- Never schedule the same SKU on two days in one week.
Output: a table with Week, Weekday, Aisle, SKU, Description, Class, Expected_qty. Then show total lines per week for the first 8 weeks so I can check the spread.

What you get back is a long table. Check three things before you print any of it: the lines-per-week figure sits near 43 (2,244 divided by 52), no SKU appears twice in a week, and each aisle is visited on a sensible day. The aisle grouping is the part most people forget, and it decides whether counting takes 25 minutes or an hour.

A filled-in daily count sheet

Staff do not need the calendar, only today's page. Here is the layout of one, with the expected quantity deliberately left off in the version handed to the counter (see the blind-count rule below):

A second sheet, kept by the supervisor, carries the system quantity beside each line and compares it after counting.

Variance thresholds: when a difference needs a recount

Variance is the counted quantity minus the system quantity. Express it in units, as a percentage and in dollars (units times unit cost). Then set a tolerance for each class. Anything inside the tolerance is adjusted and logged; anything outside triggers a recount.

These figures are illustrative; the right ones depend on your margins and how fast items move, and on what your accountant or lender expects. The principle is that the tolerance tightens as the value rises, and the owner sees any large write-off.

A quick sum shows how the rule bites. A bin holds 40 brake pad pairs (A item, $9 each), and the count finds 38. The variance is minus two units, 5% and $18. Five per cent exceeds the 2% limit, so the rule triggers a recount even though $18 is under the $25 cap, because the lower of the two limits applies. If the recount also gives 38, the system is adjusted and the variance is logged. If the recount gives 40, the first count is discarded and nothing is adjusted.

Recount rules that keep the numbers honest

1. Count blind. The counter writes down what is on the shelf without seeing the system figure. People who see the expected number tend to find it.
2. Recount by a different person. Same day if possible, before any further movement on that item.
3. Two matching counts win. If count one and count two agree, adjust the system to them. If they differ, a supervisor counts a third time and the location is frozen until it is settled.
4. Look for the cause before you adjust. Check open picks, receipts not yet booked, items put away in the wrong bin and returns not processed. Many variances vanish at this step.
5. Log it. Record the SKU, date, counted, system, variance, cause and who approved.
6. Escalate repeats. Two consecutive variances on the same SKU mean the problem is not the count: look at the process, the unit of measure or the labelling.

A filled-in variance log

That last row is a typical find: the "variance" was not missing stock at all. The system holds valve caps as singles at $0.50 each, the counter counted the packs of four on the shelf, and the apparent $45 loss vanished on the recount. Nothing went missing, but the same false variance will appear on every count until the bin label and the item record say which unit to count.

Having AI read the variance log

After eight to twelve weeks you hold enough rows to look for patterns, and this is where AI earns its keep. Paste or upload the log and ask:

Here is the cycle count variance log for the last 12 weeks. Group variances by cause code, by aisle and by class. Show total absolute value per group. Flag any SKU with two or more variances. Suggest, for each flagged SKU, the most likely process cause and one check I can make on the floor. Do not recommend adjustments to the system; only suggest where to look.

Illustrative output: "Aisle D accounts for 11 of 27 variances. Seven of the eleven carry the cause code 'Mispick' and involve near-identical SKUs (D-12 and D-13). Check whether the two bins are labelled clearly and whether the similar items could be separated." The AI is good at this sort of grouping. It does not know your floor, so treat its suggestions as places to look and make the decision yourself. Where variances come from, in more depth, is covered in finding why your count and your system disagree.

A rule for changing classes

Classes drift. A product that had a good year becomes A; a discontinued line sinks to C. Once a quarter, rerun the classification on fresh data and apply two simple rules:

• Reclassify on the new ranking, but keep an item at the higher count frequency until it has had two clean counts in a row at its new class.
• Any item with a variance above tolerance on its last two counts is counted one level more often (a C item monthly, say) until it has three clean counts.

This keeps the programme tied to what the counts actually find, rather than to the ranking alone.

The count that was wrong because the warehouse was busy

A small online seller started cycle counting and found, in its first month, that A items showed variances in almost every count, often a few units short. The staff were counting during the afternoon pick wave. Orders were being picked and packed while the count sheet was being filled in, so the count was correct at the moment it was taken, and the system was correct a few minutes later.

The fix was procedural: count A items before the first pick wave, or in a window when picking is paused for that aisle. After the change, variances on A items dropped to the occasional real error. The lesson is that a variance is a question rather than a verdict. Before blaming theft or carelessness, check whether stock was moving while you counted. Nobody needed software to see this, but a log with a "time counted" column made it obvious within a week.

The same method in a parts room or a back store

You do not need a warehouse. An HVAC contractor with a van and a small parts room can class parts by annual spend: compressors and control boards as A, contactors and capacitors as B, fittings, tape and screws as C. A tidy rule for a business that size is to count A items on the first Monday of each month, B items on the first Monday of each quarter, and C items at the start of spring and autumn. The list takes one afternoon to build, and the total counting time is likely to be an hour a month. For the van stock itself, setting reorder levels for HVAC and electrical vans pairs well with it.

A café does the same with its stockroom, but ranks by weekly spend: coffee beans, milk alternatives and pastry bases are A, syrups and cups B, and cleaning supplies C. Perishables also need expiry checks at each count, which a plain A, B and C ranking by value will not prompt.

Counting by aisle or counting by item

There are two ways to organise a day's count, and the right one depends on how your stock is laid out. Counting by item takes the day's SKUs from the calendar and walks to each. It follows the ABC rules exactly but can send a person across the building for a single bin. Counting by location takes one aisle or zone a day and counts everything in it, whatever its class. It is quicker on the floor and finds misplaced stock, but gives A items the same attention as C items unless you add a rule.

A workable compromise for a small space: build the calendar by item as above, but ask the AI to group each week's lines by aisle and to add any A item in the same aisle that is due within the next two weeks. That pulls counts forward a little, which costs nothing, and cuts walking. In a warehouse with one narrow aisle layout, you may find that counting by location, with A items counted twice a month by a separate short list, is simpler to run. Try one for a quarter and compare the minutes per line in your log; the log decides, not the theory.

Adjustments need an approver

Posting an adjustment changes the quantity your customers and your accounts rely on, so give it an owner. For a team of a few people, a good rule is that the counter never posts their own adjustment. The supervisor posts anything inside tolerance, and the owner approves anything above the sign-off figure in the threshold table. Keep the adjustment reason in the log. If an auditor or lender ever asks how your stock figure is supported, a dated log of counts, recounts and approvals is the answer they want to see.

How to tell it is working

After one quarter you should be able to answer these from the log:

• What percentage of counts matched the system exactly? Expect low numbers at first; you want to see it rising.
• Which three causes account for most of the variance value?
• How many stock-outs happened on items the system said were in stock? This should fall.
• How long does a day's count take, and does it stay under the half hour you planned?

If the exact-match rate is not improving after three months, the cause is rarely the schedule. Look at receiving, put-away and unit-of-measure problems first. And if the rest of your stock data is a mess, a dedicated inventory template with reorder alerts keeps the system quantities usable between counts.

Cycle counting questions from small warehouses

How often should A, B and C items be counted?

A common starting point is monthly for A items, quarterly for B and twice a year for C, then adjust by what the counts reveal. Items that keep showing variances move up a class for counting, and items with three clean counts in a row can move down.

Can I cycle count while the warehouse is picking orders?

Yes, but count a location only when no picks or receipts are in flight for it, or freeze the location for ten minutes. Most false variances in a live warehouse come from counting a bin while stock is moving in or out.

Does AI do the counting?

No. Staff count the stock. AI ranks the items, builds the schedule and flags patterns in the variances afterwards. Always check its totals against your own spreadsheet formulas, because chat models can slip on long columns of numbers.

What variance is acceptable?

There is no universal figure. Set a tolerance per class in units and in dollars, tighter for A items than for C items, and recount anything above it. The table in this tutorial gives a worked starting point to adjust.

Do I still need a full annual stocktake?

That depends on your accountant, auditor or lender. Some accept a well-run cycle count programme in place of a full count, others do not. Ask before you stop doing the annual count.

Further reads

• How to Run a Stocktake Faster With AI and a Phone Scanner — Speed up the counting itself with a phone scanner.
• How to Use AI to Set Reorder Points and Prevent Stockouts — Use the cleaned counts to set better reorder points.
• How to Find Dead Stock and Slow Movers With AI — Your C-class list is where dead stock hides.
• AI Tools for Warehousing: Which Suit a Small Warehouse or 3PL? — Tools that can run counts inside a warehouse system.
• AI for Ecommerce Inventory Management: Reorder Points and Stockouts — Connect counted stock to online store reorders.
• 3PL Billing: AI Reconciles Storage and Pick Fees Against WMS Data — How a small 3PL checks every storage and pick charge against its WMS export before invoicing, and what to do when a client disputes one.
• 3PL Client KPI Reports: AI Builds Weekly Reports From WMS Exports — A weekly one-page report per 3PL client, built from WMS exports: three KPIs defined exactly, charts that do not mislead, and AI commentary a person signs off.
• 3PL Client Onboarding Checklist: AI-Drafted SOPs and SLAs — The 3PL's own onboarding checklist for a new client: what to collect, how AI drafts the client SOP and SLA, and who signs what before the first pallet arrives.
• AI for Warehouse Loading: Plan Pallets and Trucks From Orders — A practical order-to-load method with pallet arithmetic, a sample manifest and physical safety checks.
• Advance Shipping Notice: Prepare an ASN From Pick Lists With AI — Build a draft ASN from your pick list and carton data, run the mismatch checks against the PO, and send only after the customer's guide is satisfied.
• Dock Scheduling for a Small Warehouse: Slot Rules and AI Reminders — Slot lengths by load type, buffers, a late-arrival rule and AI-drafted confirmations, run from one shared calendar.
• Forklift Safety Checklist: AI-Built Daily Inspection Forms — Draft a daily forklift inspection form per truck type with AI, plus the defect-reporting flow and the checks a competent person signs off.
• Walmart OTIF Fines: Build a Dispute Pack From Delivery Records — Which Walmart OTIF fines are worth disputing, the five records to gather, and how AI assembles one evidence pack per PO.
• Warehouse Labour Planning: AI Forecasts Shifts From Order Volumes — Turn orders per week into hours, forecast the seasonal peaks with AI, and decide in advance when to book agency staff.
• Warehouse SOPs: AI Training Cards for Picking and Packing — One task per card: photograph the best operator, let AI draft the steps, translate the card, and have a supervisor test it on a new starter.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: standard inventory-control practice for ABC classification and cycle counting; the thresholds in the tables are illustrative starting points, not vendor or regulator figures.