Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Calculate FIFO Inventory Valuation with Python

Value stock and cost of goods sold first in, first out from purchase and sales ledgers: consume the oldest cost layers per SKU with a deque, cost each sale across several layers, value the layers left at the period end, stop on sales that exceed stock, compare with weighted average cost, and write the results to Excel with a reconciliation.
Report layouts & templatespandas

FIFO — first in, first out — assumes the oldest stock is sold first, so each sale is costed at the price of the earliest purchases still on hand, and the stock left at the end is valued at the most recent prices. It is simple to state and awkward in a spreadsheet: one sale can use up the tail of one purchase and part of the next, and every later cost depends on what earlier sales consumed. In Python it is a queue per product. This page is part of Building Finance and Operations Reports.

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

stock_moves_july.xlsx lists the stock movements for July 2026 for two products: the opening stock with its cost, purchases with their unit cost, and sales with quantities only — the cost of a sale is what FIFO has to work out. In practice the purchases come from the purchase ledger and the sales from the sales or shipping system; combine them into one table of dated movements first.

Cost layers for P-100, oldest at the bottom Three layers of stock for product P-100. The opening stock of 120 at 4.10 is at the bottom and is consumed first, then the 6 July purchase of 200 at 4.35, then the 20 July purchase of 150 at 4.60 on top. Sales take units from the bottom layer upwards. 20 Jul: 150 @ 4.60 consumed last 6 Jul: 200 @ 4.35 consumed second Opening: 120 @ 4.10 consumed first

Step 1 — Order the movements

FIFO depends on sequence, so sort by date, and within a day put receipts before sales, so that goods received in the morning are available to an afternoon sale. That is a policy choice; stating it in code makes it visible:

Python
import pandas as pd

moves = pd.read_excel("stock_moves_july.xlsx", parse_dates=["Date"])
order = {"Opening": 0, "Purchase": 1, "Sale": 2}
moves = moves.sort_values(["Date", "Type"], key=lambda s: s.map(order) if s.name == "Type" else s,
                          kind="stable").reset_index(drop=True)
print(moves.head(5).to_string(index=False))

A stable sort keeps the original order of movements with the same date and type, which matters when two sales happen on one day.

Step 2 — Consume layers with a queue

Each SKU gets a deque of cost layers, [quantity, unit_cost]. A receipt appends a layer at the back; a sale takes units from the front until its quantity is covered, splitting a layer if it only needs part of it. Each sale's cost is the sum over the layers it touched:

Python
from collections import deque, defaultdict

class NegativeStock(Exception):
    pass

def fifo(moves):
    layers = defaultdict(deque)
    costed = []
    for m in moves.itertuples():
        stack = layers[m.SKU]
        if m.Type in ("Opening", "Purchase"):
            stack.append([m.Qty, m.UnitCost])
            continue
        need, cost, used = m.Qty, 0.0, []
        while need > 0:
            if not stack:
                raise NegativeStock(f"{m.Date:%Y-%m-%d} {m.SKU}: sale of {m.Qty} exceeds stock by {need}")
            qty, unit = stack[0]
            take = min(qty, need)
            cost += take * unit
            used.append(f"{take}@{unit:.2f}")
            need -= take
            if take == qty:
                stack.popleft()
            else:
                stack[0][0] -= take
        costed.append({"Date": m.Date, "SKU": m.SKU, "Qty": m.Qty, "COGS": round(cost, 2),
                       "UnitCOGS": round(cost / m.Qty, 4), "Layers used": " + ".join(used)})
    return pd.DataFrame(costed), layers

sales, layers = fifo(moves)
print(sales.to_string(index=False))
One sale, two layers On the left, the P-100 layers before the 10 July sale, 50 units left at 4.10 and 200 at 4.35. The sale of 90 takes the 50 at 4.10 and 40 at 4.35. On the right, the layers after the sale, 160 at 4.35, with the sale costed at 379.00. Layer Qty Cost Opening 50 4.10 6 Jul 200 4.35 Layer Qty Cost 6 Jul 160 4.35 Sale COGS 90 379.00 before 10 Jul after 10 Jul sale of 90

Look at the P-100 sale on 10 July: 90 units, of which 50 came from the last of the opening stock at 4.10 and 40 from the 6 July purchase at 4.35, so its unit cost is between the two. That split is the whole point of FIFO, and the "Layers used" column makes it auditable.

Step 3 — Value the closing stock

Whatever layers are left at the end of the period are the closing stock, valued at their own costs, which under FIFO are the most recent ones:

Python
closing = pd.DataFrame(
    [(sku, qty, unit, round(qty * unit, 2)) for sku, stack in layers.items() for qty, unit in stack],
    columns=["SKU", "Qty", "UnitCost", "Value"],
)
print(closing.to_string(index=False))
print(closing.groupby("SKU")[["Qty", "Value"]].sum().to_string())

Step 4 — Reconcile

Cost is conserved: everything that came in, opening stock plus purchases, either went out as cost of goods sold or is still on the shelf. Check it per SKU, and check quantities the same way:

Python
receipts = moves[moves["Type"] != "Sale"].assign(Value=lambda d: d["Qty"] * d["UnitCost"])
check = pd.DataFrame({
    "In value": receipts.groupby("SKU")["Value"].sum(),
    "COGS": sales.groupby("SKU")["COGS"].sum(),
    "Closing value": closing.groupby("SKU")["Value"].sum(),
})
check["Difference"] = (check["In value"] - check["COGS"] - check["Closing value"]).round(2)
qty_in = receipts.groupby("SKU")["Qty"].sum()
qty_out = sales.groupby("SKU")["Qty"].sum()
check["Qty check"] = qty_in - qty_out == closing.groupby("SKU")["Qty"].sum()
print(check.round(2).to_string())

A difference other than rounding means a movement was skipped or double counted. Rounding each sale's cost to cents can leave a cent or two; if the ledger must match exactly, keep full precision and round only for display.

Cost in equals cost out plus stock Opening stock value plus purchases equals cost of goods sold plus closing stock value. The reconciliation checks this per SKU; any difference beyond rounding means a missing or duplicated movement. Opening value layers at start + Purchases new layers − COGS layers consumed = Closing value layers left

Step 5 — When sales exceed stock

In real data a sale sometimes appears before the receipt that covered it: a goods-received note keyed a day late, or a return processed as a negative purchase. FIFO cannot cost units that are not there, so the function stops and names the movement. Try it by moving a purchase after the sale that needs it:

Python
broken = moves.copy()
broken.loc[(broken["SKU"] == "P-200") & (broken["Date"] == "2026-07-14"), "Date"] = pd.Timestamp("2026-07-25")
broken = broken.sort_values(["Date", "Type"], key=lambda s: s.map(order) if s.name == "Type" else s, kind="stable")
try:
    fifo(broken)
except NegativeStock as e:
    print("stopped:", e)

Stopping is deliberate. Guessing a cost for missing units, or letting the layer go negative, produces a valuation that looks fine and is wrong. Fix the source data, or record the policy explicitly, for example costing the shortfall at the last known cost and flagging it for review.

Step 6 — Compare with weighted average cost

Many businesses report on weighted average cost instead, and the difference between the two methods is often asked for. In a periodic weighted average, every unit available in the period costs the same: total value in divided by total units in:

Python
avg_cost = (receipts.groupby("SKU")["Value"].sum() / receipts.groupby("SKU")["Qty"].sum())
compare = pd.DataFrame({
    "FIFO COGS": sales.groupby("SKU")["COGS"].sum(),
    "Avg cost COGS": qty_out * avg_cost,
    "FIFO closing": closing.groupby("SKU")["Value"].sum(),
    "Avg cost closing": (qty_in - qty_out) * avg_cost,
}).round(2)
print(compare.to_string())

With prices rising through the month, FIFO charges the older, cheaper costs to sales and leaves the newer, dearer ones in stock, so its cost of goods sold is lower and its closing stock higher than weighted average. When prices fall, the difference reverses.

Step 7 — Write the report

Three sheets: sales with their FIFO cost and the layers used, the closing layers, and the reconciliation:

Python
with pd.ExcelWriter("fifo_valuation_2026-07.xlsx", engine="xlsxwriter") as xw:
    sales.to_excel(xw, sheet_name="Sales at FIFO cost", index=False)
    closing.to_excel(xw, sheet_name="Closing layers", index=False)
    check.join(compare).to_excel(xw, sheet_name="Reconciliation")
    money = xw.book.add_format({"num_format": "#,##0.00"})
    xw.sheets["Sales at FIFO cost"].set_column("A:A", 18, xw.book.add_format({"num_format": "yyyy-mm-dd"}))
    xw.sheets["Sales at FIFO cost"].set_column("D:E", 11, money)
    xw.sheets["Sales at FIFO cost"].set_column("F:F", 30)
    xw.sheets["Closing layers"].set_column("C:D", 11, money)
    xw.sheets["Reconciliation"].set_column("A:I", 15, money)
print("written")

Scaling up

The loop processes one movement at a time, which is fine for tens of thousands of rows per month. For millions of movements, process each SKU independently — they never interact — and carry the closing layers forward as next month's opening layers, stored as a small table of SKU, Qty, UnitCost rows, so each month only processes its own movements. That table is also what the auditors will ask for.

Common pitfalls

  • Unsorted movements. FIFO is sequence dependent; sort by date and a documented same-day rule.
  • Averaging across layers. A sale that spans two layers is costed at each layer's price for the units taken from it, not at an average.
  • Negative stock. Stop and fix the data rather than costing units that do not exist.
  • Returns. A customer return re-enters stock; decide whether at its original FIFO cost or as a new layer, and apply it consistently.
  • Rounding each step. Round for display only, or the reconciliation drifts by cents.

Conclusion

Sort movements by date with receipts before sales on the same day, keep a deque of [qty, unit_cost] layers per SKU, append receipts, and consume sales from the front, splitting layers as needed and recording which layers each sale used. Value the remaining layers as closing stock, reconcile value in against COGS plus closing stock, stop on sales that exceed stock, and compare with weighted average cost when asked.

Frequently asked questions

How do I calculate FIFO cost of goods sold in Python? Keep a queue of purchase layers per product. For each sale, take units from the oldest layer first, moving to the next when it is used up, and add up quantity times unit cost for the units taken. The sum is the sale's cost of goods sold.

How is closing inventory valued under FIFO? At the cost of the layers still in the queue after all sales, which are the most recent purchases. Multiply each remaining layer's quantity by its unit cost and add them up.

What is the difference between FIFO and weighted average cost? FIFO costs sales at the oldest prices and leaves recent prices in stock; weighted average uses one blended cost per unit for both. When prices rise, FIFO gives lower cost of goods sold and higher closing stock.

What should happen when a sale exceeds the stock on hand? Treat it as a data problem: usually a receipt was recorded late. Stop the calculation and report the movement, rather than inventing a cost.

More on report layouts & templates

More on report layouts & templates →