Calculate Weighted Average Cost Inventory Valuation

Many businesses value their stock at weighted average cost: every unit of an item on the shelf carries the same cost, the average of what was paid for the units currently held. It is simpler than FIFO — no cost layers to track — and it smooths out price swings in the cost of goods sold. In its perpetual or moving form, the average is recalculated every time stock is received, and each sale is costed at the average at that moment. Spreadsheets struggle with it, because each row depends on the running quantity and value from the row above, and small rounding choices leave stock that is all sold but still worth 0.01. This page calculates moving weighted average cost from a stock movements export with pandas, builds a stock card for each item, deals with returns, late freight charges, the last unit and negative stock, and reconciles the result. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The input is a stock movements export for September 2026: one row per movement with date, item (SKU), type, reference, quantity and — for openings and receipts — the unit cost. Issues are sales or consumption; a return is a customer sending units back; an adjustment is a value-only change, here a freight invoice that arrived after the goods it belongs to. Two items are used: a bolt bought at rising prices and a valve that receives a late freight charge and briefly runs out.

Step 1 — Order the movements

The average depends on the order of movements, so sort deterministically: by item, by date, and within a day receipts before issues — goods received in the morning can be sold in the afternoon, and costing a same-day sale before its receipt would wrongly report negative stock:

import pandas as pd

mv = pd.read_excel("stock_movements_2026-09.xlsx").rename(columns={"Unit cost": "Cost"})
ORDER = {"opening": 0, "receipt": 1, "adjustment": 2, "return": 3, "issue": 4}
mv = (mv.assign(_o=mv["Type"].map(ORDER)).sort_values(["SKU", "Date", "_o"], kind="stable")
        .drop(columns="_o").reset_index(drop=True))
print(mv.groupby("SKU")["Type"].value_counts().unstack(fill_value=0).to_string())

kind="stable" keeps the export's own order for movements of the same type on the same day, which is usually the order they were posted. If your system records a time or a sequence number, sort by that instead.

Step 2 — Walk each item's movements and keep a running average

A loop is the clearest way to express a running calculation where each row depends on the previous state. Track the quantity and the value on hand — not the average itself — and derive the average from them:

def stock_card(group):
    qty, value, rows = 0.0, 0.0, []
    for m in group.itertuples(index=False):
        avg = value / qty if qty > 0 else (rows[-1]["Avg cost"] if rows else 0.0)
        flag = ""
        if m.Type in ("opening", "receipt"):
            q, v = m.Qty, round(m.Qty * m.Cost, 2)
        elif m.Type == "adjustment":
            q, v = 0, round(m.Cost, 2)
            if qty <= 0:
                flag = "adjustment with no stock on hand"
        elif m.Type == "return":
            q, v = m.Qty, round(m.Qty * avg, 2)
        else:                                               # issue
            q = -m.Qty
            if m.Qty > qty:
                flag = f"issue exceeds stock by {m.Qty - qty:g}"
            v = -value if m.Qty == qty else -round(m.Qty * avg, 2)
        qty, value = qty + q, round(value + v, 2)
        rows.append({"Date": m.Date, "Type": m.Type, "Ref": m.Ref, "Qty": q, "Value": v,
                     "Qty on hand": qty, "Value on hand": value, "Avg cost": value / qty if qty else avg, "Flag": flag})
    return pd.DataFrame(rows)

cards = {sku: stock_card(g) for sku, g in mv.groupby("SKU")}
pd.set_option("display.width", 160)
print(cards["BOLT-M8"].drop(columns="Flag").round({"Avg cost": 4}).to_string(index=False))

On a receipt the value on hand rises by quantity × purchase price, so the average moves towards the new price; on an issue the value falls by quantity × the current average, so the average does not change. Tracking value rather than average avoids accumulating rounding error: each movement's value is rounded to the penny once, and the average is always value ÷ quantity of what is actually there. The bolt starts at 0.42; the receipt of 1,000 at 0.47 lifts the average to 0.4635 (533.00 for 1,150 units); the customer return of 50 comes back at that average; and the receipt of 800 at 0.51 lifts it to 0.4901 — while every sale between receipts is costed at the average current at the time. The sale of 600 on 9 September costs 278.09 — 600 × 533.00 ÷ 1,150 — rather than the 278.10 that the displayed average of 0.4635 would give: the calculation uses the unrounded average and rounds each movement's value once.

Step 3 — Freight, the last unit and negative stock

The valve shows the awkward cases:

print(cards["VALVE-22"].round({"Avg cost": 4}).to_string(index=False))

The freight invoice for 96.00 arrives on 11 September, after the goods it relates to. As a value-only adjustment it is spread over the 58 valves on hand, raising their average from 20.2241 to 21.8793 — the remaining units carry their share of the landed cost, and the units sold later are costed with it. On 24 September the issue of 23 takes the last units in stock, and instead of 23 × average, which might leave a penny or two of value with no units behind it, it takes exactly the value remaining — so an empty bin is worth exactly zero. Two days later an issue of 4 arrives with nothing in stock: it is costed at the last known average, 87.52, and flagged, and the item shows negative quantity until the next receipt. Negative stock usually means a receipt was posted late; the flag sends someone to find it rather than letting the report hide it.

Step 4 — Reconcile and summarise

The valuation must reconcile: opening value plus receipts, adjustments and returns, less the cost of issues, equals closing value. Check it per item, then summarise:

summary = []
for sku, card in cards.items():
    by_type = card.groupby("Type")["Value"].sum()
    closing = card["Value on hand"].iloc[-1]
    row = {"SKU": sku, "Opening": by_type.get("opening", 0), "Receipts": by_type.get("receipt", 0),
           "Adjustments": by_type.get("adjustment", 0), "Returns": by_type.get("return", 0),
           "COGS": -by_type.get("issue", 0), "Closing value": closing,
           "Closing qty": card["Qty on hand"].iloc[-1], "Closing avg": round(card["Avg cost"].iloc[-1], 4),
           "Flags": int((card["Flag"] != "").sum())}
    check = row["Opening"] + row["Receipts"] + row["Adjustments"] + row["Returns"] - row["COGS"]
    assert abs(check - closing) < 0.005, f"{sku} does not reconcile"
    summary.append(row)
summary = pd.DataFrame(summary)
print(summary.to_string(index=False))

with pd.ExcelWriter("wac_valuation_2026-09.xlsx", engine="openpyxl", date_format="dd mmm yyyy", datetime_format="dd mmm yyyy") as xw:
    summary.to_excel(xw, sheet_name="Summary", index=False)
    for sku, card in cards.items():
        card.to_excel(xw, sheet_name=sku[:31], index=False)

Both items reconcile to the penny: the bolt's cost of goods sold for the month is 882.95 with 380 units worth 186.22 left, and the valve's is 1,578.52 with 21 units worth 447.48 — after the receipt on 29 September covered the four units issued without stock. The summary gives the figures for the month-end journal — cost of goods sold and closing stock value per item — and each item's sheet is its stock card, which is what an auditor asks to see when testing a valuation. Because issues are costed at the average at the moment they happen, the bolt's cost of goods sold sits between the old and new purchase prices; compare FIFO, which charges sales at the oldest prices first, in Calculate FIFO Inventory Valuation with Python, whose comparison section also shows the simpler periodic weighted average that uses one average for the whole month.

Common pitfalls

• Storing the average instead of the value. Track quantity and value; derive the average.
• Issues before same-day receipts. Sort receipts first within a day.
• Residual pennies on empty bins. Let the issue that empties stock take the remaining value.
• Ignoring late freight. Add value-only adjustments to the stock on hand.
• Hiding negative stock. Flag it and find the missing receipt.

Conclusion

Moving weighted average cost is a running calculation: for each item, in date order with receipts first, add receipts and adjustments to the value on hand, cost issues and returns at the current average of value over quantity, and let an issue that empties the bin take exactly the value left. Flag negative stock, reconcile opening plus movements to closing for every item, and write a summary and a stock card per item to Excel.

Frequently asked questions

How do I calculate moving weighted average cost in Python? For each item in date order, keep the quantity and value on hand; add receipts at their cost, cost issues at value divided by quantity, and recompute the average after each receipt.

What is the difference between perpetual and periodic weighted average? Perpetual recalculates the average after every receipt and costs each issue at the average at that moment; periodic uses one average for the whole period.

How should freight that arrives after the goods be handled? Add it as a value-only adjustment to the item's stock on hand, which raises the average cost of the units still held.

Why is there a penny left when all stock is sold? Rounding each issue to the penny leaves residue; let the issue that takes the last units remove the entire remaining value.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Calculate FIFO Inventory Valuation with Python — the layer-based alternative.
• Reconcile a Stock Count Against System Inventory — checking the quantities.
• Build an Inventory Reorder Report — deciding what to buy next.