Building an Inventory Aging Report in pandas: Classifying Stock by Days on Hand, Flagging Slow Movers, and Exporting a Tiered Summary to Excel

Inventory aging is one of those reports that lives on every warehouse manager's desk, in every operations review deck, and on every CFO's quarterly checklist — and yet it's also one of the most time-consuming reports to maintain when it's built in Excel. You have a snapshot of current stock, a history of sales or consumption, and a business question that sounds simple: how long has this stuff been sitting here, and should we be worried about it?

The challenge is that the analysis has real moving parts. You need to calculate days on hand from sales velocity, classify items into aging tiers (Fresh, Watch, Slow, Critical), flag items that exceed thresholds, and then package all of that into a formatted Excel workbook that stakeholders can actually read without asking you twelve follow-up questions. In pandas, you can build this whole pipeline in a single script that runs in seconds, refreshes from any data source, and produces a consistent output every time.

By the end of this lesson, you'll have a complete, production-ready inventory aging report — from raw data through bucketed tiers to a multi-sheet formatted Excel file. You'll also understand why each calculation works the way it does, so you can adapt the logic to your own inventory structure.

What you'll learn:

• How to calculate average daily sales velocity and days on hand from transaction history
• How to classify items into aging tiers using pd.cut and custom bin logic
• How to flag slow movers and high-risk items with conditional column logic
• How to build a tiered summary table using groupby aggregations
• How to export a multi-sheet, formatted Excel workbook with openpyxl

---

Prerequisites

You should be comfortable loading data with pandas, working with dates, and writing basic aggregations. If you need a refresher on any of those, check out Working with Dates and Time Series in pandas and Grouping and Aggregating in pandas: groupby as the PivotTable Replacement before diving in. You'll also want pandas, numpy, and openpyxl installed in your environment — see Setting Up Python for Data Analysis if you need help there.

---

The Dataset: What We're Working With

We'll use two tables that mirror what you'd pull from almost any ERP or warehouse system:

1. inventory.csv — A snapshot of current stock on hand, one row per SKU
2. sales_history.csv — Line-level sales transactions over the past 90 days

Here's what those look like. Rather than loading from files you don't have, we'll build them directly in the script so you can run everything immediately:

import pandas as pd
import numpy as np
from datetime import date, timedelta
import random

random.seed(42)
np.random.seed(42)

# --- Build synthetic inventory snapshot ---
skus = [f"SKU-{str(i).zfill(4)}" for i in range(1, 51)]
categories = ["Electronics", "Apparel", "Home Goods", "Office Supplies", "Tools"]

inventory = pd.DataFrame({
    "sku": skus,
    "description": [f"Product {i}" for i in range(1, 51)],
    "category": np.random.choice(categories, size=50),
    "unit_cost": np.round(np.random.uniform(5, 250, size=50), 2),
    "qty_on_hand": np.random.randint(0, 500, size=50),
    "last_receipt_date": pd.to_datetime([
        date.today() - timedelta(days=int(d))
        for d in np.random.randint(1, 180, size=50)
    ])
})

# --- Build synthetic sales history (90 days) ---
sale_records = []
today = date.today()

for sku in skus:
    # Some SKUs sell frequently, some rarely, some not at all
    sale_frequency = np.random.choice(["fast", "medium", "slow", "dead"], 
                                       p=[0.25, 0.35, 0.25, 0.15])
    if sale_frequency == "fast":
        n_transactions = np.random.randint(20, 60)
    elif sale_frequency == "medium":
        n_transactions = np.random.randint(5, 20)
    elif sale_frequency == "slow":
        n_transactions = np.random.randint(1, 5)
    else:
        n_transactions = 0  # dead stock

    for _ in range(n_transactions):
        sale_date = today - timedelta(days=int(np.random.randint(1, 91)))
        qty_sold = int(np.random.randint(1, 15))
        sale_records.append({
            "sku": sku,
            "sale_date": pd.Timestamp(sale_date),
            "qty_sold": qty_sold
        })

sales = pd.DataFrame(sale_records)

print(f"Inventory rows: {len(inventory)}")
print(f"Sales rows: {len(sales)}")
print(inventory.head())
print(sales.head())

This gives you 50 SKUs with varying sales patterns — fast movers, slow movers, and dead stock — which is exactly the distribution you'll encounter in a real warehouse. Notice that some SKUs have zero sales transactions, which will create NaN values in the join. We'll handle that intentionally.

---

Step 1: Calculate Sales Velocity per SKU

Days on hand is meaningless without a denominator. The denominator is average daily sales velocity — how many units of a given SKU typically sell per day. We calculate this from the sales history.

# Define the reporting window
lookback_days = 90
report_date = pd.Timestamp(date.today())
window_start = report_date - pd.Timedelta(days=lookback_days)

# Filter to the lookback window (defensive — data should already be 90 days)
sales_window = sales[sales["sale_date"] >= window_start].copy()

# Total units sold per SKU over the window
velocity = (
    sales_window
    .groupby("sku", as_index=False)
    .agg(total_units_sold=("qty_sold", "sum"),
         transaction_count=("qty_sold", "count"))
)

# Daily velocity = total units / number of days in window
velocity["avg_daily_sales"] = velocity["total_units_sold"] / lookback_days

print(velocity.sort_values("avg_daily_sales", ascending=False).head(10))

This gives you a clean per-SKU velocity table. The key design decision here: we're dividing by the number of days in the window, not the number of days on which a sale occurred. That's intentional. A SKU that sold 90 units in one giant transaction has the same velocity as one that sold 1 unit every day — both average 1 unit/day over 90 days. What we're measuring is throughput, not consistency. If you need to analyze selling consistency separately, transaction_count is there for that.

---

Step 2: Join Velocity onto the Inventory Snapshot

Now we merge the velocity data onto the inventory snapshot. This is a left join — every SKU in inventory stays in the result, and SKUs with no sales get NaN velocity values, which we'll convert to zero.

# Left join: keep all inventory rows
inv = inventory.merge(velocity, on="sku", how="left")

# SKUs with no sales in the window get zero velocity
inv["total_units_sold"] = inv["total_units_sold"].fillna(0).astype(int)
inv["transaction_count"] = inv["transaction_count"].fillna(0).astype(int)
inv["avg_daily_sales"] = inv["avg_daily_sales"].fillna(0.0)

print(f"Rows after merge: {len(inv)}")
print(inv[inv["avg_daily_sales"] == 0][["sku", "qty_on_hand", "avg_daily_sales"]].head())

---

Step 3: Calculate Days on Hand

Days on hand (DOH) is the core metric: given current stock and current sell rate, how many days until we run out?

# Avoid division by zero: items with zero velocity get a sentinel value
# 9999 means "effectively infinite" — stock will never sell at current rate
inv["days_on_hand"] = np.where(
    inv["avg_daily_sales"] > 0,
    inv["qty_on_hand"] / inv["avg_daily_sales"],
    np.where(inv["qty_on_hand"] > 0, 9999, 0)  # zero qty + zero sales = 0
)

inv["days_on_hand"] = inv["days_on_hand"].round(1)

# Also calculate inventory value
inv["inventory_value"] = inv["qty_on_hand"] * inv["unit_cost"]

print(inv[["sku", "qty_on_hand", "avg_daily_sales", "days_on_hand", "inventory_value"]].head(10))

The np.where logic handles three cases cleanly:

• Normal items: divide on-hand qty by daily velocity
• Dead stock with quantity: assign 9999 (they have stock but aren't selling — this is the most dangerous category)
• Zero qty, zero sales: assign 0 (nothing on hand, nothing sold — these are inactive SKUs you probably don't need in this report)

---

Step 4: Classify Items into Aging Tiers

This is where the analysis starts talking to the business. Aging tiers translate the raw days-on-hand number into actionable categories. The standard tiers most operations teams use are:

Tier	Days on Hand	Meaning
Fresh	0–30 days	Healthy, selling well
Watch	31–60 days	Slightly elevated, monitor
Slow	61–90 days	Underperforming, consider action
Critical	91–180 days	High write-down risk
Dead Stock	181+ days (or 9999)	No recent sales, urgent attention

We'll use pd.cut to do the bucketing, with a manual bin for the 9999 sentinel:

# Define tier bins and labels
# Note: 10000 as the upper bound catches our 9999 sentinel values
tier_bins = [0, 30, 60, 90, 180, 10000]
tier_labels = ["Fresh", "Watch", "Slow", "Critical", "Dead Stock"]

inv["aging_tier"] = pd.cut(
    inv["days_on_hand"],
    bins=tier_bins,
    labels=tier_labels,
    right=True,       # intervals are (left, right]
    include_lowest=True
)

# Convert to string for easier downstream handling
inv["aging_tier"] = inv["aging_tier"].astype(str)

# Verify the distribution
print(inv["aging_tier"].value_counts())

Let's also check our work visually by looking at a few rows from each tier:

for tier in tier_labels:
    sample = inv[inv["aging_tier"] == tier][["sku", "category", "qty_on_hand", "avg_daily_sales", "days_on_hand"]].head(2)
    print(f"\n--- {tier} ---")
    print(sample.to_string())

---

Step 5: Flag Slow Movers and High-Risk Items

Tiers classify everything. Flags highlight the items that need immediate attention. These are the rows that would be colored red in an Excel report or included in a weekly exception email to the warehouse team.

We'll create two flag columns:

1. slow_mover_flag: Items in the Slow, Critical, or Dead Stock tiers with meaningful inventory value (not worth flagging a $3 item)
2. high_risk_flag: Dead stock or Critical items where inventory value exceeds a dollar threshold

# Slow mover: Slow/Critical/Dead Stock tier AND value > $500
inv["slow_mover_flag"] = (
    inv["aging_tier"].isin(["Slow", "Critical", "Dead Stock"]) &
    (inv["inventory_value"] >= 500)
)

# High risk: Critical or Dead Stock AND value > $2,000
inv["high_risk_flag"] = (
    inv["aging_tier"].isin(["Critical", "Dead Stock"]) &
    (inv["inventory_value"] >= 2000)
)

# Summary counts
print(f"Slow movers flagged: {inv['slow_mover_flag'].sum()}")
print(f"High risk items: {inv['high_risk_flag'].sum()}")

# Dollar exposure
slow_mover_value = inv.loc[inv["slow_mover_flag"], "inventory_value"].sum()
high_risk_value = inv.loc[inv["high_risk_flag"], "inventory_value"].sum()

print(f"Slow mover inventory value: ${slow_mover_value:,.0f}")
print(f"High risk inventory value: ${high_risk_value:,.0f}")

The dollar thresholds ($500 and $2,000) are configurable — in a real deployment, you'd pull these from a config file or parameters dict so the operations team can adjust them without touching the code. That's a key principle of building reusable report pipelines.

---

Step 6: Build the Tiered Summary Table

Now we aggregate the item-level data into the summary table that goes on the first tab of the Excel report. The operations director doesn't want to scroll through 10,000 rows — they want to see: by tier, by category, how much stock and value is sitting there.

# Tier-level summary
tier_summary = (
    inv.groupby("aging_tier", as_index=False)
    .agg(
        sku_count=("sku", "count"),
        total_qty=("qty_on_hand", "sum"),
        total_value=("inventory_value", "sum"),
        avg_days_on_hand=("days_on_hand", lambda x: x[x < 9999].mean()),  # exclude sentinel
        slow_mover_count=("slow_mover_flag", "sum"),
        high_risk_count=("high_risk_flag", "sum")
    )
)

# Add percent of total value
total_value = tier_summary["total_value"].sum()
tier_summary["pct_of_total_value"] = (tier_summary["total_value"] / total_value * 100).round(1)

# Sort by logical tier order
tier_order = ["Fresh", "Watch", "Slow", "Critical", "Dead Stock"]
tier_summary["aging_tier"] = pd.Categorical(tier_summary["aging_tier"], categories=tier_order, ordered=True)
tier_summary = tier_summary.sort_values("aging_tier").reset_index(drop=True)

# Round financial columns
tier_summary["total_value"] = tier_summary["total_value"].round(2)
tier_summary["avg_days_on_hand"] = tier_summary["avg_days_on_hand"].round(1)

print(tier_summary.to_string())

Notice the lambda in the avg_days_on_hand aggregation: lambda x: x[x < 9999].mean(). That excludes our dead stock sentinel values from the average, which would otherwise distort the number significantly. Dead stock items aren't "9999 days old" — they're items with unknown aging that we're treating as a special category.

We also want a category-level breakdown for the second Excel tab:

# Category × Tier cross-tab
category_summary = (
    inv.groupby(["category", "aging_tier"], as_index=False)
    .agg(
        sku_count=("sku", "count"),
        total_value=("inventory_value", "sum"),
        slow_mover_count=("slow_mover_flag", "sum"),
        high_risk_count=("high_risk_flag", "sum")
    )
)

category_summary["aging_tier"] = pd.Categorical(
    category_summary["aging_tier"], categories=tier_order, ordered=True
)
category_summary = category_summary.sort_values(["category", "aging_tier"]).reset_index(drop=True)
category_summary["total_value"] = category_summary["total_value"].round(2)

print(category_summary.head(15).to_string())

For a deeper look at building cross-tabulations like this one, see Summarizing and Cross-Tabulating Categorical Data in pandas.

---

Step 7: Build the High-Risk Detail Tab

The third tab of the report is what the finance team actually cares about: a prioritized list of every flagged item, sorted by inventory value descending, with enough context to take action.

# Select and order columns for the detail tab
detail_cols = [
    "sku", "description", "category", "unit_cost",
    "qty_on_hand", "inventory_value", "avg_daily_sales",
    "days_on_hand", "aging_tier", "slow_mover_flag", "high_risk_flag",
    "last_receipt_date"
]

flagged_detail = (
    inv[inv["slow_mover_flag"] | inv["high_risk_flag"]][detail_cols]
    .sort_values("inventory_value", ascending=False)
    .reset_index(drop=True)
)

# Human-readable flag columns
flagged_detail["slow_mover_flag"] = flagged_detail["slow_mover_flag"].map({True: "Yes", False: ""})
flagged_detail["high_risk_flag"] = flagged_detail["high_risk_flag"].map({True: "YES - Review", False: ""})

# Cap days_on_hand display at 999 for readability (dead stock shows as 9999 otherwise)
flagged_detail["days_on_hand"] = flagged_detail["days_on_hand"].clip(upper=999)

print(f"Flagged items in detail tab: {len(flagged_detail)}")
print(flagged_detail.head(5).to_string())

---

Step 8: Export to a Formatted Multi-Sheet Excel Workbook

This is where the report goes from a Python DataFrame to something you can email to the warehouse director. We'll use openpyxl through pandas' ExcelWriter to create three sheets with column widths, number formatting, and header styling.

from openpyxl.styles import Font, PatternFill, Alignment, numbers
from openpyxl.utils import get_column_letter

output_path = "inventory_aging_report.xlsx"

with pd.ExcelWriter(output_path, engine="openpyxl") as writer:

    # --- Sheet 1: Tier Summary ---
    tier_summary.to_excel(writer, sheet_name="Tier Summary", index=False)

    # --- Sheet 2: Category Breakdown ---
    category_summary.to_excel(writer, sheet_name="Category Breakdown", index=False)

    # --- Sheet 3: Flagged Items Detail ---
    flagged_detail.to_excel(writer, sheet_name="Flagged Items", index=False)

    # --- Now apply formatting ---
    wb = writer.book

    # Helper: auto-fit column widths
    def autofit_columns(ws, min_width=10, max_width=40):
        for col in ws.columns:
            max_len = max(
                (len(str(cell.value)) if cell.value is not None else 0)
                for cell in col
            )
            col_letter = get_column_letter(col[0].column)
            ws.column_dimensions[col_letter].width = min(max(max_len + 2, min_width), max_width)

    # Helper: style header row
    def style_header(ws, fill_color="2F5496"):
        header_fill = PatternFill(start_color=fill_color, end_color=fill_color, fill_type="solid")
        header_font = Font(bold=True, color="FFFFFF", size=11)
        for cell in ws[1]:
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = Alignment(horizontal="center", wrap_text=True)

    # Helper: apply currency format to specified columns
    def format_currency_columns(ws, col_indices):
        currency_fmt = '#,##0.00'
        for row in ws.iter_rows(min_row=2):
            for idx in col_indices:
                row[idx].number_format = currency_fmt

    # --- Format Sheet 1: Tier Summary ---
    ws1 = wb["Tier Summary"]
    style_header(ws1)
    autofit_columns(ws1)
    # total_value is column index 3 (0-based), pct_of_total_value is 7
    format_currency_columns(ws1, [2, 3])  # total_qty at 2 is int, total_value at 3
    for row in ws1.iter_rows(min_row=2):
        # Highlight Critical and Dead Stock rows
        tier_val = row[0].value
        if tier_val in ("Critical", "Dead Stock"):
            red_fill = PatternFill(start_color="FFE0E0", end_color="FFE0E0", fill_type="solid")
            for cell in row:
                cell.fill = red_fill
        elif tier_val == "Slow":
            orange_fill = PatternFill(start_color="FFF3CD", end_color="FFF3CD", fill_type="solid")
            for cell in row:
                cell.fill = orange_fill

    # --- Format Sheet 2: Category Breakdown ---
    ws2 = wb["Category Breakdown"]
    style_header(ws2)
    autofit_columns(ws2)
    format_currency_columns(ws2, [3])  # total_value

    # --- Format Sheet 3: Flagged Items ---
    ws3 = wb["Flagged Items"]
    style_header(ws3, fill_color="8B0000")  # Dark red header for urgency
    autofit_columns(ws3)
    format_currency_columns(ws3, [3, 4, 5])  # unit_cost, qty_on_hand, inventory_value

    # Highlight the high_risk_flag column where it says "YES - Review"
    high_risk_col_idx = flagged_detail.columns.get_loc("high_risk_flag")
    for row in ws3.iter_rows(min_row=2):
        if row[high_risk_col_idx].value == "YES - Review":
            alert_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
            alert_font = Font(bold=True, color="FFFFFF")
            row[high_risk_col_idx].fill = alert_fill
            row[high_risk_col_idx].font = alert_font

print(f"Report written to: {output_path}")

When you open inventory_aging_report.xlsx, you'll see:

• Tier Summary: A single-glance overview with red rows for Critical/Dead Stock and yellow for Slow
• Category Breakdown: The same metrics cut by category so you can see which product lines have the worst aging
• Flagged Items: A prioritized action list with high-risk items highlighted in red

For more on openpyxl formatting patterns, see Automating Excel Reports with pandas and openpyxl.

---

Putting It All Together: The Complete Script

Here's the complete pipeline in one clean block, ready to drop into a .py file or Jupyter notebook:

import pandas as pd
import numpy as np
from datetime import date, timedelta
import random
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

# ============================================================
# CONFIG
# ============================================================
LOOKBACK_DAYS = 90
SLOW_MOVER_VALUE_THRESHOLD = 500
HIGH_RISK_VALUE_THRESHOLD = 2000
TIER_BINS = [0, 30, 60, 90, 180, 10000]
TIER_LABELS = ["Fresh", "Watch", "Slow", "Critical", "Dead Stock"]
OUTPUT_PATH = "inventory_aging_report.xlsx"

# ============================================================
# DATA GENERATION (replace with your actual data loading)
# ============================================================
random.seed(42)
np.random.seed(42)

skus = [f"SKU-{str(i).zfill(4)}" for i in range(1, 51)]
categories = ["Electronics", "Apparel", "Home Goods", "Office Supplies", "Tools"]

inventory = pd.DataFrame({
    "sku": skus,
    "description": [f"Product {i}" for i in range(1, 51)],
    "category": np.random.choice(categories, size=50),
    "unit_cost": np.round(np.random.uniform(5, 250, size=50), 2),
    "qty_on_hand": np.random.randint(0, 500, size=50),
    "last_receipt_date": pd.to_datetime([
        date.today() - timedelta(days=int(d))
        for d in np.random.randint(1, 180, size=50)
    ])
})

sale_records = []
today = date.today()
for sku in skus:
    sale_frequency = np.random.choice(
        ["fast", "medium", "slow", "dead"], p=[0.25, 0.35, 0.25, 0.15]
    )
    n_transactions = {"fast": np.random.randint(20, 60),
                      "medium": np.random.randint(5, 20),
                      "slow": np.random.randint(1, 5),
                      "dead": 0}[sale_frequency]
    for _ in range(n_transactions):
        sale_records.append({
            "sku": sku,
            "sale_date": pd.Timestamp(today - timedelta(days=int(np.random.randint(1, 91)))),
            "qty_sold": int(np.random.randint(1, 15))
        })

sales = pd.DataFrame(sale_records)

# ============================================================
# VELOCITY CALCULATION
# ============================================================
report_date = pd.Timestamp(date.today())
window_start = report_date - pd.Timedelta(days=LOOKBACK_DAYS)

velocity = (
    sales[sales["sale_date"] >= window_start]
    .groupby("sku", as_index=False)
    .agg(total_units_sold=("qty_sold", "sum"),
         transaction_count=("qty_sold", "count"))
)
velocity["avg_daily_sales"] = velocity["total_units_sold"] / LOOKBACK_DAYS

# ============================================================
# MERGE AND CALCULATE DAYS ON HAND
# ============================================================
inv = inventory.merge(velocity, on="sku", how="left")
inv["total_units_sold"] = inv["total_units_sold"].fillna(0).astype(int)
inv["transaction_count"] = inv["transaction_count"].fillna(0).astype(int)
inv["avg_daily_sales"] = inv["avg_daily_sales"].fillna(0.0)

inv["days_on_hand"] = np.where(
    inv["avg_daily_sales"] > 0,
    inv["qty_on_hand"] / inv["avg_daily_sales"],
    np.where(inv["qty_on_hand"] > 0, 9999, 0)
).round(1)

inv["inventory_value"] = (inv["qty_on_hand"] * inv["unit_cost"]).round(2)

# ============================================================
# TIER CLASSIFICATION AND FLAGS
# ============================================================
inv["aging_tier"] = pd.cut(
    inv["days_on_hand"],
    bins=TIER_BINS,
    labels=TIER_LABELS,
    right=True,
    include_lowest=True
).astype(str)

inv["slow_mover_flag"] = (
    inv["aging_tier"].isin(["Slow", "Critical", "Dead Stock"]) &
    (inv["inventory_value"] >= SLOW_MOVER_VALUE_THRESHOLD)
)
inv["high_risk_flag"] = (
    inv["aging_tier"].isin(["Critical", "Dead Stock"]) &
    (inv["inventory_value"] >= HIGH_RISK_VALUE_THRESHOLD)
)

# ============================================================
# SUMMARY TABLES
# ============================================================
tier_summary = (
    inv.groupby("aging_tier", as_index=False)
    .agg(
        sku_count=("sku", "count"),
        total_qty=("qty_on_hand", "sum"),
        total_value=("inventory_value", "sum"),
        avg_days_on_hand=("days_on_hand", lambda x: x[x < 9999].mean()),
        slow_mover_count=("slow_mover_flag", "sum"),
        high_risk_count=("high_risk_flag", "sum")
    )
)
total_value = tier_summary["total_value"].sum()
tier_summary["pct_of_total_value"] = (tier_summary["total_value"] / total_value * 100).round(1)
tier_summary["aging_tier"] = pd.Categorical(tier_summary["aging_tier"], categories=TIER_LABELS, ordered=True)
tier_summary = tier_summary.sort_values("aging_tier").reset_index(drop=True)
tier_summary["total_value"] = tier_summary["total_value"].round(2)
tier_summary["avg_days_on_hand"] = tier_summary["avg_days_on_hand"].round(1)

category_summary = (
    inv.groupby(["category", "aging_tier"], as_index=False)
    .agg(
        sku_count=("sku", "count"),
        total_value=("inventory_value", "sum"),
        slow_mover_count=("slow_mover_flag", "sum"),
        high_risk_count=("high_risk_flag", "sum")
    )
)
category_summary["aging_tier"] = pd.Categorical(
    category_summary["aging_tier"], categories=TIER_LABELS, ordered=True
)
category_summary = category_summary.sort_values(["category", "aging_tier"]).reset_index(drop=True)
category_summary["total_value"] = category_summary["total_value"].round(2)

detail_cols = [
    "sku", "description", "category", "unit_cost",
    "qty_on_hand", "inventory_value", "avg_daily_sales",
    "days_on_hand", "aging_tier", "slow_mover_flag", "high_risk_flag",
    "last_receipt_date"
]
flagged_detail = (
    inv[inv["slow_mover_flag"] | inv["high_risk_flag"]][detail_cols]
    .sort_values("inventory_value", ascending=False)
    .reset_index(drop=True)
)
flagged_detail["slow_mover_flag"] = flagged_detail["slow_mover_flag"].map({True: "Yes", False: ""})
flagged_detail["high_risk_flag"] = flagged_detail["high_risk_flag"].map({True: "YES - Review", False: ""})
flagged_detail["days_on_hand"] = flagged_detail["days_on_hand"].clip(upper=999)

# ============================================================
# EXCEL EXPORT
# ============================================================
def autofit_columns(ws, min_width=10, max_width=40):
    for col in ws.columns:
        max_len = max(
            (len(str(cell.value)) if cell.value is not None else 0)
            for cell in col
        )
        ws.column_dimensions[get_column_letter(col[0].column)].width = min(
            max(max_len + 2, min_width), max_width
        )

def style_header(ws, fill_color="2F5496"):
    hdr_fill = PatternFill(start_color=fill_color, end_color=fill_color, fill_type="solid")
    hdr_font = Font(bold=True, color="FFFFFF", size=11)
    for cell in ws[1]:
        cell.fill = hdr_fill
        cell.font = hdr_font
        cell.alignment = Alignment(horizontal="center", wrap_text=True)

def format_currency_columns(ws, col_indices):
    for row in ws.iter_rows(min_row=2):
        for idx in col_indices:
            row[idx].number_format = '#,##0.00'

with pd.ExcelWriter(OUTPUT_PATH, engine="openpyxl") as writer:
    tier_summary.to_excel(writer, sheet_name="Tier Summary", index=False)
    category_summary.to_excel(writer, sheet_name="Category Breakdown", index=False)
    flagged_detail.to_excel(writer, sheet_name="Flagged Items", index=False)

    wb = writer.book

    ws1 = wb["Tier Summary"]
    style_header(ws1)
    autofit_columns(ws1)
    format_currency_columns(ws1, [3])
    for row in ws1.iter_rows(min_row=2):
        tier_val = row[0].value
        if tier_val in ("Critical", "Dead Stock"):
            fill = PatternFill(start_color="FFE0E0", end_color="FFE0E0", fill_type="solid")
        elif tier_val == "Slow":
            fill = PatternFill(start_color="FFF3CD", end_color="FFF3CD", fill_type="solid")
        else:
            fill = None
        if fill:
            for cell in row:
                cell.fill = fill

    ws2 = wb["Category Breakdown"]
    style_header(ws2)
    autofit_columns(ws2)
    format_currency_columns(ws2, [3])

    ws3 = wb["Flagged Items"]
    style_header(ws3, fill_color="8B0000")
    autofit_columns(ws3)
    format_currency_columns(ws3, [3, 5])
    high_risk_col_idx = flagged_detail.columns.get_loc("high_risk_flag")
    for row in ws3.iter_rows(min_row=2):
        if row[high_risk_col_idx].value == "YES - Review":
            row[high_risk_col_idx].fill = PatternFill(
                start_color="FF0000", end_color="FF0000", fill_type="solid"
            )
            row[high_risk_col_idx].font = Font(bold=True, color="FFFFFF")

print(f"✓ Report written to {OUTPUT_PATH}")
print(f"  Tier Summary: {len(tier_summary)} tiers")
print(f"  Category Breakdown: {len(category_summary)} rows")
print(f"  Flagged Items: {len(flagged_detail)} items")

---

Hands-On Exercise

Take the script above and extend it with these modifications:

Exercise 1 — Add a reorder flag. Add a column reorder_flag that marks items where days_on_hand < 14 AND qty_on_hand > 0. These are fast-moving items that are running low. Add this count to the Tier Summary sheet as an additional column.

Exercise 2 — Parameterize the lookback window. Right now LOOKBACK_DAYS = 90 is hardcoded. Add a 30-day velocity column (avg_daily_sales_30d) alongside the 90-day one, and use the lower of the two in your days-on-hand calculation. This gives a more conservative aging estimate during periods of declining sales.

Exercise 3 — Add a "Days Since Last Sale" column. Use the sales history to find the most recent sale date for each SKU, then calculate (report_date - last_sale_date).dt.days. Items with no sales get a large number (or NaN). Add this to the flagged detail tab — it's often more intuitive to a non-analyst than "9999 days on hand."

Exercise 4 — Schedule it. Add a date stamp to the output filename: f"inventory_aging_{date.today().strftime('%Y%m%d')}.xlsx" so each run produces a dated archive. Then look at Building and Automating Recurring Reports with pandas to set this up as a scheduled daily job.

---

Common Mistakes & Troubleshooting

Problem: Dead stock items show NaN in the aging tier instead of "Dead Stock"

This happens when days_on_hand is 9999 but your TIER_BINS upper bound is lower. Make sure your highest bin boundary (we used 10000) exceeds your sentinel value. Also check that include_lowest=True is set on pd.cut.

Problem: Merge produces more rows than the original inventory table

You have duplicate SKU keys in your sales history aggregation — possibly because the groupby didn't run correctly or the data has duplicate SKU records. Run velocity['sku'].duplicated().sum() to check. You might also have encoding differences between SKU fields in the two tables (e.g., "SKU-001" vs "SKU-0001"). See Detecting and Resolving Data Quality Issues Across Merged DataFrames for a full diagnostic approach.

Problem: The avg_days_on_hand in the tier summary looks wrong

If your Dead Stock items are bleeding into the average, the lambda filter isn't working as expected. Test it in isolation: inv.loc[inv['aging_tier'] == 'Fresh', 'days_on_hand'].mean() versus the grouped result. Also verify that after pd.cut, the tier column is a string (not a Categorical with categories that contain "Dead Stock" as a label that doesn't match literally in x < 9999).

Problem: Excel file opens but formatting is missing

This almost always means the with pd.ExcelWriter(...) as writer: block ran but the openpyxl formatting code raised a silent exception before writing. Add import traceback and wrap the formatting section in a try/except to surface the error. The most common cause is an index mismatch — col[0].column changed behavior between openpyxl versions. Use get_column_letter(col[0].column) consistently and you'll avoid it.

Problem: pd.cut returns NaN for some items even though the values are in range

Values that fall exactly on bin boundaries can behave unexpectedly depending on the right parameter. With right=True (the default), bins are (left, right] — so a value of exactly 30 falls in the first bin (0–30], not the second. With include_lowest=True, a value of exactly 0 is included in the first bin. If you're seeing unexpected NaNs, print inv['days_on_hand'].describe() and check for values below 0 or above your maximum bin edge.

---

Summary & Next Steps

You've built a complete inventory aging pipeline that takes raw stock and sales data, computes days on hand with proper handling of zero-velocity SKUs, classifies everything into actionable tiers, flags items above value thresholds, and exports a formatted three-sheet Excel workbook ready for a business review.

The patterns here — sentinel values, left joins with fillna, pd.cut bucketing, conditional flags, tiered aggregation, and openpyxl formatting loops — apply to dozens of other operational reports you'll encounter: accounts receivable aging, project milestone tracking, maintenance backlog analysis, and more.

Where to go next:

• If your data lives in a database rather than CSV files, Reading from SQL Databases into pandas with SQLAlchemy covers how to pull the inventory and sales tables directly with a SQL query
• To make your tier thresholds and lookback windows configurable by business users (without them touching Python), Building a Dynamic pandas Report Pipeline with Parameters, Filters, and Excel Output shows how to externalize config
• If your dataset is large enough that the merge and groupby are slow, Writing Fast pandas Code: Vectorization Instead of apply and Loops will help you identify and fix the bottlenecks
• For adding subtotals and grand totals to the Excel summary in a boardroom-ready format, see Calculating Running Totals, Subtotals, and Grand Totals in pandas for Boardroom-Ready Summary Tables