Skip to main content

Inventory analysis in Python with pandas — DIO, ABC and slow movers

To analyse inventory in Python, load the monthly stock summary for each item into pandas, add up the month-end values, compare what was issued with the average stock held, and rank the items by what moves through the warehouse. The 47 lines of pandas below do this on a DATA DARE inventory practice set — a retailer's distribution centres with 640 items — and the results agree with the answers in the set's Question Book.

The sets live now

The steps

  1. Load the items, the monthly stock summary, the stock adjustments and the stock plan — four sheets of one Excel workbook.
  2. Take the stock at a month-end and compare it with the plan for that month.
  3. Work out turnover and days of stock (DIO): what was issued in the year at cost, against the average of the twelve month-end values.
  4. Rank the items by what was issued. The A class is the smallest group of items that makes up 80% of the total.
  5. Find the slow movers — items holding more than 180 days of their recent issues — and measure shrinkage from the stock counts.

The code

Download the L1 inventory set (DD-INV-001) and run this in the folder that holds its Excel workbook. It needs pandas and openpyxl (pip install pandas openpyxl).

Python (pandas)
import pandas as pd

pd.options.display.float_format = "{:,.2f}".format
workbook = "DD-INV-001_Inventory.xlsx"

items = pd.read_excel(workbook, sheet_name="DimItem")
stock = pd.read_excel(workbook, sheet_name="FactStockSummary")
adjust = pd.read_excel(workbook, sheet_name="FactStockAdjustment")
plan = pd.read_excel(workbook, sheet_name="FactStockPlan")

# 1. One row per item per month-end. Add the category, keep 2025.
stock = stock.merge(items[["ItemCode", "Category"]], on="ItemCode")
y25 = stock[stock["PeriodKey"] // 100 == 2025]
dec = y25[y25["PeriodKey"] == 202512]
on_hand = dec["ClosingValue"].sum()
gap = on_hand - plan.loc[plan["PeriodKey"] == 202512, "PlanValue"].sum()
print(f"Stock at 31 Dec 2025: {on_hand:,.2f}   against plan: {gap:,.2f}")

# 2. Turnover and days of stock (DIO): issues at cost against the average of the 12 month-ends.
average = y25.groupby("PeriodKey")["ClosingValue"].sum().mean()
issued = y25["IssuedValue"].sum()
print(f"Turnover 2025: {issued / average:.2f} times   DIO: {average / issued * 365:.2f} days\n")

# 3. DIO by category: where stock sits longest.
cat = y25.groupby(["Category", "PeriodKey"])["ClosingValue"].sum().groupby("Category").mean()
dio = (cat / y25.groupby("Category")["IssuedValue"].sum() * 365).sort_values(ascending=False)
print("Slowest categories (DIO, days):\n" + dio.head(3).to_string(), "\n")

# 4. ABC: rank items by 2025 issues; the A class is the fewest items that make up 80%.
by_item = y25.groupby("ItemCode")["IssuedValue"].sum().sort_values(ascending=False)
share = by_item.cumsum() / by_item.sum()
a_items = (share < 0.80).sum() + 1
print(f"A items: {a_items} of {len(by_item)} make up 80% of what was issued")

# 5. Slow movers: stock at the last month-end worth more than 180 days of recent issues.
last = stock[stock["PeriodKey"] == 202609].set_index("ItemCode")["ClosingValue"]
six_months = stock[stock["PeriodKey"].between(202604, 202609)]
recent = six_months.groupby("ItemCode")["IssuedValue"].sum()
days = (pd.Timestamp("2026-09-30") - pd.Timestamp("2026-03-31")).days
slow = last[last > recent / days * 180]
print(f"Slow movers at 30 Sep 2026: {len(slow)} items worth {slow.sum():,.2f}")

# 6. Shrinkage: stock lost at the counts, as a share of what was issued.
counts = adjust[(adjust["AdjustmentType"] == "Count variance")
                & (adjust["AdjustmentDate"].dt.year == 2025)]
lost = counts["Value"].sum()
print(f"Count losses 2025: {lost:,.2f}   shrinkage: {-lost / issued:.2%}")

What it prints

Exactly as the script prints it:

Output
Stock at 31 Dec 2025: 16,179,668.66   against plan: -7,018,031.34
Turnover 2025: 11.99 times   DIO: 30.45 days

Slowest categories (DIO, days):
Category
Seasonal and gifts        210.27
Electronics accessories    86.46
Home and kitchen           74.08

A items: 382 of 640 make up 80% of what was issued
Slow movers at 30 Sep 2026: 18 items worth 1,117,807.65
Count losses 2025: -233,461.30   shrinkage: 0.11%

How to read the result

  • Stock was 16,179,668.66 at the end of 2025, 7,018,031.34 below the plan for that month-end.
  • Stock turned 11.99 times in 2025: 30.45 days of stock on average. Seasonal and gifts sits for 210.27 days, about seven times the average, so it is the first place to look.
  • 382 of the 640 items make up 80% of what was issued. These A items deserve the tightest counts and reorder rules.
  • 18 items hold more than 180 days of stock at 30 September 2026, worth 1,117,807.65 — candidates for a write-down review.
  • The stock counts lost 233,461.30 in 2025, 0.11% of what was issued.
  • Stock at the year-end, the gap to plan, turnover, DIO, the slowest category, the A class, the slow movers, the count losses and shrinkage are all questions in the set, so each figure can be checked in its Question Book.

Mistakes this data will catch

  • Taking one month-end as the average stock. Use the twelve month-ends; stock builds up before Ramadan and the gift season.
  • Adding closing values across months as if they were a total. Stock is a balance: add across items at one date, then average across dates.
  • Measuring days of stock against sales value. Stock is held at cost, so compare it with what was issued at cost.
  • Forgetting the sign of the count adjustments. A negative value means stock was lost; flip it to get shrinkage.

The next levels

The L2 set (DD-INV-002) comes as CSV, JSON, Excel and Parquet files and adds every stock movement, purchase price history, suppliers and locations, the stock counts and a stock budget. The same steps work from the movements instead of a monthly summary.

Python or Power BI

Python suits the monthly stock review: the same ABC and slow-mover lists saved to Excel for the buyers. Power BI suits the dashboard, where category, item and month are slicers.

The course

  • Basic Python Course Free recording Basic Python takes you from never having written a line of code to scripts that do real work on your own files. You will cover the language itself, then move quickly to the part th...
  • Python for Finance — Automation & Controls Coming soon Python for Finance — Automation & Controls is about the monthly tasks that eat a week and shouldn't. You will automate reconciliations, build controls that check data instead of tr...
  • Python for Finance — Advanced Analytics & Reporting Coming soon Python for Finance — Advanced Analytics & Reporting is where the scripts stop being utilities and start producing analysis. You will work with datasets larger than Excel will hold,...
  • Python + AI for Finance Workflows Coming soon Python + AI for Finance Workflows is about putting a language model inside a process, not chatting with one. You will build workflows that read documents, extract what matters, che...
  • Data Transformation in Python — At Scale Free recording One export of 6.18 million dirty rows, cleaned in Python and reconciled to a total you can defend. For Power Query users who have never written Python. Amounts stored as text, thre...

Data Drill

Updated 16 Nov 2026 · findi360