Inventory ABC Analysis

Inventory ABC Analysis

This screen ranks every active QuickBooks inventory item by how much money moves through it, and puts each item in an A, B or C class. A items are the small handful of parts that carry most of the value — the ones worth counting often, watching min/max on, and never running out of. C items are the long tail.

Open it from the Inventory ABC Analysis tile on the Logistics Main Menu.

The report reads QuickBooks live. A run takes about three minutes — leave the page open until it finishes or you lose the run and have to start over.

The choices before you run it

• ABC thresholds (A / B %) — how the cumulative value is split. The default is 80 / 15 / 5: items are sorted biggest value first, and the ones making up the first 80% of value are A, the next 15% B, the rest C. The C number fills in by itself; A and B each have to be above zero and add to less than 100.
• Include on-hand qty in the ABC basis? — No (the default) ranks items on what was actually issued in the window: what you use. Yes ranks on issued plus what is sitting on the shelf, which pushes slow, expensive stock up the list. Pick one and stay with it, or the classes will not compare month to month.
• Movement window — how far back to look at movement: 90, 180 (the default) or 365 days.
• Update the ABC code on IM2 items? — only admins see this. No is report-only. Yes writes each item's new A/B/C code onto the item in IM2, and every one of those writes shows on the Change Log with the source abc-report. Archived items are skipped.

Then click Run report. You will see "Reading QuickBooks…", then "Building the spreadsheet…", then a one-line summary and a Download the spreadsheet button. If two runs are already going, the screen tells you to wait.

What you get

An Excel file named Inventory_ABC_Analysis_<date>_issuedOnly.xlsx (or _inclOnHand), with two tabs:

Summary — the window, the basis and the thresholds you used; a table of A/B/C with item counts, share of items and share of value; a breakdown of where each item's cost came from; and a Notes block with the totals, how many items have no cost basis at all, and the standing cautions.

Item Detail — one row per active inventory item, biggest ABC value first:

Several of the middle columns arrive hidden to keep the sheet readable — unhide them in Excel if you want them.

Where the cost comes from

The report tries, in this order, and tells you which one it used:

1. FIFO weighted-average cost of the purchase layers still on hand (blended with the QB item cost if the layers do not cover the whole quantity)
2. The QuickBooks item cost
3. The average receipt cost inside the window
4. The last paid purchase order
5. An estimate borrowed from a closely-named item that does have a cost

If none of those exist the item shows Uncosted — no basis in QB, and the count of those items is called out in the Notes. Items flagged in IM2 as no cost expected (used, surplus, seed stock) are deliberately valued at zero.

Things to know before you act on it

• The item that crosses a threshold stays in the higher class — standard Pareto.
• An item with zero value in the window is always C.
• Qty On Hand is current, so it will not tie out to the movement columns.
• Inventory adjustments are not counted as movement.
• Bulk and wire SKUs can show an inflated cost if bills were entered per spool but stock is kept per foot — check the unit of measure before believing the number.

Common mistakes

• Closing the tab during the run. Three minutes, page open.
• Changing the window and the basis at the same time, then comparing to last month's file. Change one thing at a time.
• Turning on Update IM2 to "see what it would do" — it does it. Run report-only first, read the file, then re-run with the write turned on.