Build an Inventory Reorder Report with Python and Excel

Every week someone in operations looks at the stock list and decides what to order. Done by eye, it produces two failures: items that run out because nobody noticed they were low, and items ordered twice because nobody remembered the order already placed. A reorder report replaces the eyeballing with a rule: for each item, work out how much will be used before a new order could arrive, add a safety margin, and order when the stock on hand plus the stock on order falls below that. This page, part of Building Finance and Operations Reports, builds that report from a stock export, a sales history and a list of open purchase orders.

Prerequisites

pip install pandas numpy openpyxl xlsxwriter

stock.xlsx lists six items with their supplier, stock on hand, lead time, pack size and minimum order quantity. sales_history.xlsx has daily sales for the last 90 days. open_pos.xlsx lists purchase orders placed but not yet received.

Step 1 — Work out daily usage and its variability

Average daily usage comes from recent history. Divide by the number of days in the window, not the number of days with sales — days without a sale are days of zero demand:

import numpy as np
import pandas as pd

stock = pd.read_excel("stock.xlsx")
history = pd.read_excel("sales_history.xlsx", parse_dates=["Date"])
window = pd.date_range("2026-06-28", "2026-09-25")

daily = (history.pivot_table(index="Date", columns="SKU", values="Qty", aggfunc="sum")
         .reindex(index=window, columns=stock["SKU"], fill_value=0).fillna(0))
usage = pd.DataFrame({"Daily Usage": daily.mean(), "Usage SD": daily.std()})
print(len(window), "days")
print(usage.round(2))

reindex over every day in the window fills days without sales with zero, and over every SKU adds items that did not sell at all — the latch, LT-02, has no sales and a usage of zero. Averaging only the days with sales would overstate usage for slow movers, sometimes many times over. Fill Missing Dates in a Time Series with pandas explains why.

Step 2 — Calculate safety stock and the reorder point

Safety stock covers demand running above average during the lead time. The standard formula scales the day-to-day variation by the square root of the lead time and by a factor for the service level wanted — 1.65 for roughly 95% of cycles without a stock-out:

Z = 1.65
REVIEW_DAYS = 7

plan = stock.merge(usage, left_on="SKU", right_index=True, validate="one_to_one")
plan["Safety Stock"] = np.ceil(Z * plan["Usage SD"] * np.sqrt(plan["Lead Time Days"]))
plan["Reorder Point"] = np.ceil(plan["Daily Usage"] * plan["Lead Time Days"]) + plan["Safety Stock"]
plan["Target"] = plan["Reorder Point"] + np.ceil(plan["Daily Usage"] * REVIEW_DAYS)
print(plan[["SKU", "Daily Usage", "Lead Time Days", "Safety Stock", "Reorder Point", "Target"]].round(1).to_string(index=False))

The target — how high to fill up when ordering — is the reorder point plus a week of usage, because the report runs weekly and the stock has to last until the next review. Items with longer lead times, the panels and frames from Corwell, need proportionally more stock in reserve.

Step 3 — Count stock on order

An item already on order should not be ordered again. Add the quantity on open purchase orders to the stock on hand to get the inventory position, and compare that with the reorder point:

pos = pd.read_excel("open_pos.xlsx")
on_order = pos.groupby("SKU")["Qty"].sum()
plan["On Order"] = plan["SKU"].map(on_order).fillna(0).astype(int)
plan["Position"] = plan["On Hand"] + plan["On Order"]
plan["Reorder"] = plan["Position"] <= plan["Reorder Point"]
print(plan[["SKU", "On Hand", "On Order", "Position", "Reorder Point", "Reorder"]].to_string(index=False))

The large brackets, BR-20, are below their reorder point on hand alone, but the 100 already on order lift their position above it, so they are not reordered. Without that step the report would order them again every week until the delivery arrived — the double-ordering failure the report exists to prevent.

Step 4 — Size the orders

For items to reorder, the quantity is whatever lifts the position to the target, rounded up to whole packs and to at least the supplier's minimum order quantity:

def order_qty(row):
    if not row["Reorder"] or row["Daily Usage"] == 0:
        return 0
    need = max(row["Target"] - row["Position"], 0)
    packs = np.ceil(need / row["Pack Size"])
    qty = max(packs * row["Pack Size"], row["MOQ"])
    return int(np.ceil(qty / row["Pack Size"]) * row["Pack Size"])

plan["Order Qty"] = plan.apply(order_qty, axis=1)
plan["Days of Cover"] = (plan["Position"] / plan["Daily Usage"]).replace(np.inf, np.nan).round(1)
plan["Stock-out Risk"] = plan["Days of Cover"] < plan["Lead Time Days"]
print(plan[["SKU", "Position", "Target", "Pack Size", "MOQ", "Order Qty", "Days of Cover", "Stock-out Risk"]].to_string(index=False))

The small brackets, BR-10, need 191 units to reach their target, which rounds up to four packs of 50 — 200, which also meets the supplier's minimum. Rounding up the MOQ to a pack handles suppliers whose minimum is not a multiple of their pack size. Items with no usage are never ordered automatically, even with zero stock: the latch has sold nothing in 90 days, and whether to restock a dead item is a decision for a person, not a formula. Days of cover — how long the stock on hand and on order lasts at the average rate — is the figure buyers read first. A cover shorter than the lead time means the item will probably run out before a new order arrives, whatever is ordered today, so it needs expediting or a faster supplier rather than just a purchase order. Counting stock on order here too keeps an item with a delivery due next week from being flagged needlessly.

Step 5 — Write a purchase list per supplier

Buyers place orders per supplier, so group the lines to order by supplier, with a sheet each and a summary of everything that needs attention:

cols = ["SKU", "Description", "On Hand", "On Order", "Reorder Point", "Order Qty", "Days of Cover"]
to_order = plan[plan["Order Qty"] > 0]
with pd.ExcelWriter("reorder_2026-09-28.xlsx", engine="xlsxwriter") as writer:
    wb = writer.book
    warn = wb.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})
    plan[["Supplier"] + cols + ["Stock-out Risk"]].to_excel(writer, sheet_name="All items", index=False)
    ws = writer.sheets["All items"]
    ws.set_column("A:C", 14)
    ws.conditional_format(1, 0, len(plan), 8, {"type": "formula", "criteria": "=$I2=TRUE", "format": warn})
    for supplier, lines in to_order.groupby("Supplier"):
        lines[cols].to_excel(writer, sheet_name=supplier, index=False)
        writer.sheets[supplier].set_column("A:B", 14)
        print(supplier, lines[["SKU", "Order Qty"]].values.tolist())
print("stock-out risk:", plan.loc[plan["Stock-out Risk"], "SKU"].tolist())
print("no usage, review manually:", plan.loc[plan["Daily Usage"] == 0, "SKU"].tolist())

The per-supplier sheets are ready to paste into a purchase order, or to feed a script that raises the orders in the purchasing system. The "All items" sheet highlights stock-out risks, so the buyer knows which orders to chase or expedite.

Common pitfalls

• Averaging only days with sales. Reindex over every day in the window, filling zeros.
• Ignoring stock on order. Compare the reorder point with on hand plus on order.
• Ordering dead stock. Zero-usage items need a human decision, not an automatic order.
• Forgetting pack sizes and MOQs. Round up to packs, then to the minimum.
• Stale lead times. Supplier lead times drift; keep them in the stock file where buyers update them.
• One window for everything. Seasonal items need a window that reflects the coming season, not the last 90 days.

Performance notes

The calculations are vectorised apart from the order sizing, which is fine for thousands of items; for hundreds of thousands, replace the apply with np.ceil on whole columns and np.maximum for the MOQ. The pivot of daily sales is the largest object — SKUs × days — so for very large catalogues aggregate usage with groupby instead.

Conclusion

Compute average daily usage and its variation over every day in a recent window, including zero-sale days. Set safety stock from the variation, lead time and service level, and the reorder point as lead-time demand plus safety stock. Compare the reorder point with on hand plus on order, size orders up to a target in whole packs and at least the MOQ, flag items whose cover is shorter than their lead time, and write the purchase list grouped by supplier.

Frequently asked questions

How do I calculate a reorder point in Python? Reorder point = average daily usage × lead time in days + safety stock. Compute daily usage from recent sales history with pandas, join the lead times, and compare each item's inventory position — on hand plus on order — with its reorder point.

Why include stock already on order? Stock on an open purchase order will arrive within the lead time. Ignoring it makes the report order the same items again every week until the delivery lands. Compare the reorder point with on hand plus on order, not on hand alone.

How do I round order quantities to pack sizes and minimum order quantities? Round the required quantity up to a whole number of packs with ceil(required / pack) × pack, then take the larger of that and the MOQ, rounded up to a pack again if the MOQ is not a multiple of the pack size.

How much safety stock should I hold? A common formula is z × standard deviation of daily demand × √lead time, where z sets the service level — about 1.65 for 95%. It grows with how variable demand is and how long the lead time is.

Related

• Up one level: Building Finance and Operations Reports — the shared report pipeline.
• Calculate Moving Averages with pandas rolling — smoothing usage over time.
• Run a Pareto Analysis on Excel Data with pandas — ranking items by importance.
• Split One Excel Sheet into Multiple Files by Value — one file per supplier.