Python code: ABC classification and inventory turnover

Python code: ABC classification and inventory turnover

Not every item on a warehouse shelf is worth the same. A handful of products carry most of the money tied up in stock while the rest just take up space. ABC classification sorts them into three families: the ones to watch almost daily, the ones to review every week and the ones that only need a look now and then. Turnover answers the other everyday question: how often a product is renewed and how many days of stock are sitting idle. This article brings a Python program, free and with no dependencies, that does that job with your own inventory: it reads a CSV file, sorts the products by annual consumption value, splits them into A, B and C, works out turnover and days of stock, and leaves you an ordered file you can open in Excel. It is written for the owner of a hardware store or a small distributor, for the accountant checking how much capital is parked in the warehouse, and for the storekeeper who needs to know which product deserves attention first.

What the program does

1. Reads the inventory from datos/inventario.csv: item name, units consumed in the year, unit cost and the units kept on average in the warehouse.
2. Works out the annual consumption value of each item by multiplying units consumed by unit cost. That is not what sits on the shelf today: it is the money the item moves in a year, and that figure is what orders the whole list.
3. Sorts from the highest down and calculates the share of each item over the total and the running total, which is the sum that grows as you read down the list.
4. Assigns the class: A up to eighty per cent of the running total, B up to ninety-five per cent and C the rest. There is a fine detail most people miss: the item that crosses a cut already falls in the next class, so class A ends up a little below eighty per cent. That is normal and far more useful than forcing a round number.
5. Calculates turnover by dividing annual consumption by average stock. A turnover of 5 means the item was renewed five times in the year; a turnover of 1 means goods bought a year ago are still on the shelf.
6. Works out days of stock: the 360 days of the business year divided by turnover. That is the answer to the warehouse manager's question about how long the stock will last.
7. Prints the ordered table, the summary by class with its percentages and two checks, and saves salida/clasificacion_abc.csv and salida/resumen_abc.csv.

How to run it

All you need is Python 3.11 or newer, installed with the box that adds Python to the PATH ticked. There is nothing else to install: the program uses the standard library only, so you never touch a package manager or a virtual environment.

1. Download the ZIP with the green button and unpack it into any folder.
2. Open a terminal (on Windows cmd, on Linux or Mac any terminal) and move into that folder with the cd command.
3. Run python clasificacion_abc.py.

The program finds the folder where it lives, so you can call it from anywhere: it always looks for the data and writes the results next to itself. If you see encoding errors, check that your CSV is stored as UTF-8. With the sample inventory the whole run takes a second, offline and with nothing else installed.

The code, explained

Four real fragments, copied exactly from the program inside the ZIP, with no edits at all.

One: the table is defined as data, not as one giant print. Each column declares its internal name, its width and the heading you see on screen; from that list the program works out how wide the banner and every separator must be:

# Fixed width per column: that is why the table never drifts
COLUMNAS = [
    ("producto", 30, "Product"),
    ("valor", 17, "Consumption value"),
    ("participacion", 14, "Share"),
    ("acumulado", 12, "Running"),
    ("clase", 7, "Class"),
    ("rotacion", 10, "Turnover"),
    ("dias", 9, "Days"),
]
# How wide the banner and every table separator is
ANCHO_LINEA = sum(ancho for _, ancho, _ in COLUMNAS) + len(COLUMNAS) - 1

Look at the last line: the table width is computed from the columns. Add a column tomorrow and the separators stretch on their own, so the table stays aligned.

Two: money is never computed with binary decimals. The whole program works with Decimal, and these three helpers format amounts with a thousands separator and a decimal point, as well as the percentages:

def miles(valor: Decimal) -> str:
    """Formats an amount with thousands separator and decimal point (local format)."""
    entero, decimales = f"{valor:,.2f}".split(".")
    return entero.replace(",", ",") + "." + decimales


def numero(valor: Decimal, decimales: int) -> str:
    """Formats a number with the requested decimals and the local format."""
    entero, cola = f"{valor:,.{decimales}f}".split(".")
    return entero.replace(",", ",") + "." + cola


def porcentaje(valor: Decimal) -> str:
    """Formats a percentage with two decimals and the per cent sign."""
    return numero(valor, 2) + " %"

Rounding uses ROUND_HALF_UP, which is what anyone checking an invoice by hand expects: the cents go up when the third figure is five or more. Never use floating point numbers for money, because the cents drift on their own.

Three: the core of the calculation. Everything this article promises is here: consumption value, turnover, days, order, running total and class:

def calcular(filas: list[dict]) -> tuple[Decimal, list[dict]]:
    """Computes value, share, running total, class, turnover and days for each item."""
    inventario = []
    for fila in filas:
        consumo = Decimal(fila["annual_consumption"])          # Units consumed in the year
        costo = Decimal(fila["unit_cost"])             # What one unit costs
        promedio = Decimal(fila["average_inventory"])     # Units kept on average in the warehouse

        # Consumption value: what everything used in the year is worth
        valor = redondear(consumo * costo)
        # Turnover: how many times the stock is renewed in the year
        rotacion = redondear(consumo / promedio) if promedio else Decimal("0.00")
        # Days of stock: how many days the stock lasts at that pace
        dias = redondear(DIAS_ANO / rotacion) if rotacion else Decimal("0.00")
        inventario.append({"producto": fila["product"], "valor": valor,
                           "rotacion": rotacion, "dias": dias})

    # From the highest consumption value down: that is what makes the running total work
    inventario.sort(key=lambda item: (-item["valor"], item["producto"]))

    total = sum((item["valor"] for item in inventario), Decimal("0"))
    acumulado = Decimal("0")
    for item in inventario:
        acumulado += item["valor"]
        item["participacion"] = redondear(item["valor"] * CIEN / total)
        item["acumulado"] = redondear(acumulado * CIEN / total)
        # Class by running total: A up to 80 per cent, B up to 95 per cent and C the rest
        if item["acumulado"] <= CORTE_A:
            item["clase"] = "A"
        elif item["acumulado"] <= CORTE_B:
            item["clase"] = "B"
        else:
            item["clase"] = "C"
    # That is why the item that crosses a cut already falls in the next class
    return total, inventario

Three things deserve a careful look. Turnover is consumption divided by average stock, and days of stock come from dividing the 360-day business year by that turnover: the faster an item moves, the less stock you need to keep. Sorting by value from the top down is what gives the running total its meaning; if the list is not sorted, the running total says nothing. And the class is decided by the running total and not by each item's own share, which is exactly the difference between a sound classification and one that merely looks pretty.

Four: the output you open in Excel. The program writes two CSV files, and the item-by-item one carries the product name next to its calculated figures:

def escribir_abc(inventario: list[dict]) -> None:
    """Saves the item-by-item classification to a CSV file."""
    SALIDA_ABC.parent.mkdir(parents=True, exist_ok=True)
    columnas = ["product", "consumption_value", "share",
                "running_total", "class", "turnover", "days"]
    with open(SALIDA_ABC, "w", encoding="utf-8", newline="") as archivo:
        escritor = csv.writer(archivo)
        escritor.writerow(columnas)
        for item in inventario:
            escritor.writerow([item["producto"], item["valor"], item["participacion"],
                               item["acumulado"], item["clase"], item["rotacion"], item["dias"]])

In the CSV files the figures use a decimal point, the format Excel understands with no setup, while on screen the program shows them with the local separator. That way you keep working in the spreadsheet: filter the class column, sort by days of stock and your priority list is done.

What you will see on screen

This is the real output with the sample inventory shipped inside the ZIP. First the banner and the count of items read:

=========================================================================================================
  ABC CLASSIFICATION AND INVENTORY TURNOVER
=========================================================================================================
Items read: 10

The table sorted by annual consumption value. The sample closes with a total annual consumption value of 332,270,000.00, and gray cement alone accounts for 110,000,000.00, that is a third of the total. The percentages in this table are written here in words and without their sign, so they do not clash with the blog editor; on screen the program prints them with the sign and the CSV keeps them as plain numbers:

And this is the summary by class, the part you take into the meeting with the business owner:

The closing lines show the two checks and the two files left in the output folder:

Total annual consumption value: 332,270,000.00

Check (sum of the summary values = grand total): 332,270,000.00 = 332,270,000.00 -> TIES

File written: salida/clasificacion_abc.csv
File written: salida/resumen_abc.csv

The checks are the part accountants like best: the first one verifies that the running total closes at exactly one hundred per cent, and the second one that the summary values add up to the grand total. If either said REVIEW instead of TIES, a figure in the CSV is wrong and the program is warning you before you make decisions on shaky numbers.

Common mistakes and tips

• Data file not found. The program expects datos/inventario.csv next to the program file. If you unpacked the ZIP and then moved only the .py file, rebuild the folder including its datos subfolder.
• Figures that do not add up. Check that annual consumption is in units and unit cost is money per unit. Mix boxes with single units and turnover goes through the roof while the classes come out upside down.
• Average stock at zero. You cannot divide by a zero average: the program leaves it at zero so it does not crash, but that figure has to be fixed at the source.
• Decimal separator. The sample data uses a decimal point. If your system exports a decimal comma, adjust the data before running the program.
• Too few items. With a very short list ABC loses its point, because almost everything lands in class A. Run it on the full inventory of the business.
• A single period. Consumption value is a snapshot of the last year. If the business is seasonal, run the program for each quarter and compare.

When this is not enough

This program is free and it is meant to order your inventory and help you read the numbers: copy it, change it and use it with your own data. What it does not do is run the day to day, because it does not record receipts and issues, does not reduce the balance when someone sells and does not warn you when an item runs out. Once a business goes past a few movements a day, the file and the script fall short and it is time to think about a system. Kardex Tauro exists for exactly that moment, for when spreadsheets and scripts are no longer enough: it keeps the stock ledger for every item, works out the balance and leaves the inventory in order. But before you look at any system, run this program: with the classification in hand you know which items justify the software and which do not.

Download the ZIP, replace the sample inventory with your own and run the program. In less time than a coffee break you will know which products hold your business up and which ones are eating your capital.

Teaching material about internal control, written for this blog's code workshop. It is not accounting, tax or legal advice: check your own figures and local rules before making decisions with these numbers.