Python code: reorder point and economic order quantity (EOQ)

Python code: reorder point and economic order quantity (EOQ)

Ordering late empties the shelf and the sale goes to the competitor; ordering too much leaves cash asleep in the back room, paying rent, insurance and shrinkage on goods nobody touches. Between those two extremes sit two numbers every business holding stock should keep at hand: the reorder point, which answers “when do I order again?”, and the economic order quantity (EOQ), which answers “how much do I order each time?”. This package brings a Python program that works out both, plus the safety stock and the sensitivity table that shows how much the system costs once you drift away from the optimum.

You do not need to be a programmer: unzip the package, run the program and read the table. It is meant for the owner of a hardware store or a small depot, for the accountant who builds the purchasing budget and for the storekeeper who pays for every late delivery.

What the ZIP contains

What the program does

1. Reads the products from datos/consumo.csv and stops if it finds zero demand or zero holding cost, instead of carrying on with an invented number.
2. Works out the holding cost (H): unit cost times holding percentage, divided by one hundred. That is what it costs to keep one unit for a year: space, money tied up, insurance and shrinkage.
3. Works out the EOQ as the square root of two times annual demand times ordering cost, divided by H.
4. Rounds up with the mathematical ceiling, because nobody orders a fraction of a screw.
5. Counts orders per year and the annual cost of running the item, which is not the value of the goods you buy.
6. Spreads demand over a business year of 360 days to get daily demand.
7. Works out safety stock: 1.65 times the daily deviation times the square root of the lead time. The 1.65 stands for a service level of ninety-five per cent.
8. Works out the reorder point: what you consume during the lead time plus the safety cushion.
9. Builds the sensitivity table with six order sizes around the EOQ, from half to double.
10. Saves two CSV files, salida/reorden_eoq.csv and salida/sensibilidad.csv, ready to open in Excel.

How to run it

You need Python 3.11 or newer. If you do not have it, download it from python.org and install it with the default options. Unzip the package into a folder, open a terminal in that folder and type:

python punto_reorden_eoq.py

On Windows you can open a terminal in the folder by typing cmd in the address bar of File Explorer. There is nothing else to install: the program only uses csv, math, decimal and pathlib, which ship with Python. It needs no internet connection and no database. When it finishes you will see the tables on screen and an salida folder will appear with both CSV files inside.

The code, explained

The program is about two hundred and sixty lines long and is split into short functions: read, compute, print and save. These are the four pieces worth understanding, copied exactly as they come in the file inside the ZIP.

The tools and the constants

import csv                                        # # csv: to read the input file
import math                                       # # math: for the ceiling of the units (ceil) and nothing else
from decimal import Decimal, ROUND_HALF_UP         # # Decimal: money is never computed with binary decimals (float)
from pathlib import Path                           # # pathlib: paths that work on Windows, Linux and Mac

The program starts by importing four modules from the standard library. csv reads the product file and math is used only for the ceiling of the units. Decimal is the single most important decision in the program: money is not computed with binary decimals, because 0.1 has no exact form in binary and the cents start to wobble once you add thousands of operations. pathlib builds paths that work the same on Windows, Linux and Mac, and the base folder is derived from the file itself, so the program runs no matter where you call it from.

The heart of the calculation

    # H: lo que cuesta tener una unidad guardada un año (arriendo, dinero, mermas).
    h = unitario * mantener / 100
    if h <= 0 or demanda <= 0:
        raise ValueError(f"Invalid data in the file (demand or holding cost is zero): {producto['product']}")

    # Lote económico de pedido: el que hace mínimo el costo de pedir + el de mantener.
    # Sale de igualar las dos curvas: D*K/Q = Q*H/2  ->  EOQ = raíz(2*D*K/H).
    eoq = raiz(Decimal(2) * demanda * costo_pedido / h)
    cantidad = entero(eoq)

    # Pedidos al año y costo anual total (NO es el valor de la mercancía comprada).
    pedidos = demanda / Decimal(cantidad)
    costo_total = pedidos * costo_pedido + (Decimal(cantidad) / 2) * h

    # Demanda diaria con el año comercial de 360 días.
    diaria = demanda / DIAS_ANO

    # Stock de seguridad: 1,65 * desviación diaria * raíz del plazo de entrega.
    seguridad = entero(Z_SERVICIO * desviacion * raiz(plazo))

    # Punto de reorden: lo que se alcanza a consumir mientras llega el pedido + colchón.
    reorden = entero(diaria * plazo + Decimal(seguridad))

Everything in the model is in this block. H is the cost of keeping one unit for a year. The EOQ comes from setting the two cost curves against each other: the cost of placing orders is spread over more units when the order is large, and the cost of holding grows with the size of the order. Where the two curves cross, the sum is at its lowest, and that crossing gives an EOQ of the square root of two times demand times ordering cost, divided by H. After that the program rounds up, counts the orders per year, spreads demand over 360 days and builds the safety cushion from the daily deviation and the lead time.

The sensitivity table

def sensibilidad(datos: dict) -> list[dict]:
    """Tries six order sizes around the EOQ to show where the lowest cost sits."""
    filas = []
    for factor, etiqueta in FACTORES:
        cantidad = entero(factor * Decimal(datos["cantidad"]))
        pedir = (datos["demanda"] / Decimal(cantidad)) * datos["costo_pedido"]
        guardar = (Decimal(cantidad) / 2) * datos["h"]
        filas.append({
            "factor": factor, "etiqueta": etiqueta, "cantidad": cantidad,
            "pedir": pedir, "mantener": guardar, "total": pedir + guardar,
        })
    return filas

This function is the one that really teaches: it tries six sizes around the EOQ —from half to double— and recomputes for each one the ordering cost, the holding cost and their sum. Next to the EOQ the curve is very flat, and that is good news for anyone who has to buy in boxes, pallets or full lots: rounding the order up to the pack size your supplier sells hardly moves the total cost.

The final check

    minimo = min(filas, key=lambda fila: fila["total"])
    cuadra = minimo["factor"] == Decimal("1")
    print()
    print(f"  Check (the lowest cost falls at the EOQ, factor 1): {'TIES' if cuadra else 'REVIEW'}")

The last function looks for the row with the lowest total cost and checks that it is the one with factor 1, the EOQ. It is a check the program runs on itself: if any number were out of place, the screen would print the word REVIEW instead of TIES. This kind of internal check is cheap to write and saves trouble once somebody starts touching the constants of the model.

What you will see on screen

With the four sample products, the first thing you get is the heading and how many products were read:

===================================================================================================================
  REORDER POINT AND EOQ
  Didactic Python code · Kardex Tauro · kardex-tauro.muisca.co
===================================================================================================================
Products read: 4
The EOQ is the size that balances the ordering cost against the holding cost.

Then comes the replenishment table, one row per product. Look at the cement row: the economic order is 573.00 units, it is ordered about fifteen times a year, running those orders costs 2,517,144.50 and the safety cushion is 45.00 units, so the reorder point lands on 545.00 units.

REPLENISHMENT PARAMETERS PER PRODUCT
-------------------------------------------------------------------------------------------------------------------
Product                        EOQ units      Orders/year    Annual cost Daily demand   Safety stock    Reorder pt.
-------------------------------------------------------------------------------------------------------------------
Gray cement 50 kg              573.00               15.71   2,517,144.50        25.00          45.00         545.00
THHN 12 AWG wire 100 m         425.00               14.12   1,272,794.12        16.67          32.00         282.00
Self-tapping screw 1 in        837.00                5.02     200,798.42        11.67           8.00          67.00
Anticorrosive paint 1 gal      156.00                9.62     673,498.46         4.17          73.00         198.00
-------------------------------------------------------------------------------------------------------------------

And this is the table that teaches the most, the sensitivity of the first product:

ORDER SIZE SENSITIVITY: Gray cement 50 kg
--------------------------------------------------------------------------
Factor   Quantity        Ordering cost       Holding cost       Total cost
--------------------------------------------------------------------------
0.5x     287.00           2,508,710.80         631,400.00     3,140,110.80
0.75x    430.00           1,674,418.60         946,000.00     2,620,418.60
1x       573.00           1,256,544.50       1,260,600.00     2,517,144.50
1.25x    717.00           1,004,184.10       1,577,400.00     2,581,584.10
1.5x     860.00             837,209.30       1,892,000.00     2,729,209.30
2x       1,146.00           628,272.25       2,521,200.00     3,149,472.25
--------------------------------------------------------------------------
Lowest total cost: 1x (2,517,144.50)

The factor 1 row is the EOQ: 573.00 units at a total cost of 2,517,144.50. If the business ordered half, 287.00 units, the total cost would rise to 3,140,110.80; if it ordered double, 1,146.00 units, it would rise to 3,149,472.25. Drifting away from the optimum costs roughly a quarter more, while being a quarter over or under is barely noticeable. That is the lesson: you do not have to chase the EOQ down to the last decimal, but you do have to respect its order of magnitude.

  Gray cement 50 kg
    Order            : 573.00 units
    Every            : 22.92 days between orders
    Reorder point    : 545.00 units
    Total annual cost: 2,517,144.50

  Check (the lowest cost falls at the EOQ, factor 1): TIES

Finally you get the plain-words summary with the two figures used every day —how often to order and at what balance to release the order— plus the program's own check:

Common mistakes and tips

• Confusing the annual cost with the value of the goods. The annual cost column is what it costs to run the orders and hold the stock, not what you pay the supplier.
• Ignoring the decimal separator of the data file. In datos/consumo.csv the numbers use a decimal point and no thousands separator. If you paste them from a sheet that uses commas, the program stops with an invalid-data warning.
• Leaving the CSV open in Excel. On Windows a file open in Excel is locked and the program cannot read it. Close the sheet and run it again.
• Changing the 1.65 constant without changing the service level. That value belongs to ninety-five per cent; at a lower service level the constant drops (around 1.28 for ninety per cent) and the cushion shrinks.
• Entering the lead time in months. The field expects days: if your supplier takes three weeks, type 21.
• Forgetting the business year. The program uses 360 days; if your business sells every day of the year, raise that constant and daily demand comes down a little.
• Believing the EOQ is a straitjacket. It is a reference: round it to the pack size your supplier sells, because the sensitivity table shows that a quarter over or under hardly changes the cost.

When this is not enough

This program is useful for deciding how much and when to order while the catalogue is short and the data lives in a spreadsheet: a few hundred items, a single warehouse and deliveries that arrive complete. It falls short when there are several warehouses, when the unit price changes with the quantity ordered, when orders arrive in part and, above all, when you need to know who moved what and when.

That is what Kardex Tauro is for: a free program to put the stock of a small business in order —receipts, issues, balance per item and reports, with no licence fee. The honest recommendation is this: use this script while your operation fits in a spreadsheet, and when volume, warehouses or internal control call for more, move to software that keeps the full stock ledger.

Download the package, replace the four sample products with your own and in one afternoon you will have your own replenishment table. Next time the supplier asks how much you want, the answer will be in a file instead of in your memory.