Retail Inventory Optimization

Capstone Project 11: Retail Inventory Optimization

C11.1 Project Overview

This project analyzes product-level inventory data to flag overstocked and understocked SKUs, and studies weekly demand patterns to support smarter reordering decisions.

C11.2 Business Problem

A retail store is tying up cash in slow-moving stock while occasionally running out of fast-moving items. They want a data-driven way to flag which SKUs need attention.

C11.3 Dataset Description

Column	Description
sku	Unique product/stock-keeping unit code
current_stock	Units currently in stock
avg_daily_sales	Average units sold per day
days_of_stock	current_stock / avg_daily_sales

Sample scope: 6 representative SKUs plus 200 weeks of overall demand data.

C11.4 Objectives

Calculate days-of-stock remaining for each SKU
Flag SKUs that are overstocked or at risk of stockout
Understand the overall pattern of weekly demand
Recommend reorder and clearance actions per SKU

C11.5 Step-by-Step Solution

Step 1: Load the inventory CSV into Pandas
Step 2: Calculate days_of_stock = current_stock / avg_daily_sales for each SKU
Step 3: Set thresholds — flag <15 days as stockout risk, >90 days as overstocked
Step 4: Visualize days-of-stock per SKU against the target range
Step 5: Analyze weekly demand distribution to understand normal variability
Step 6: Summarize SKU-level action recommendations

C11.6 Complete Python Code

► inventory_optimization.py

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv("inventory.csv")

df['days_of_stock'] = df['current_stock'] / df['avg_daily_sales']

def flag_status(days):
    if days < 15:
        return 'Stockout Risk'
    elif days > 90:
        return 'Overstocked'
    else:
        return 'Healthy'

df['status'] = df['days_of_stock'].apply(flag_status)
print(df[["sku","days_of_stock","status"]])

plt.bar(df["sku"], df["days_of_stock"])
plt.axhline(60, linestyle="--")
plt.savefig("stock_days.png")

C11.7 Expected Output

Output

sku     days_of_stock    status
SKU-101  45          Healthy
SKU-102  12          Stockout Risk
SKU-104  5           Stockout Risk
SKU-106  95          Overstocked

C11.8 Visualizations

Figure C11.1 — Days of inventory on hand per SKU, against a 60-day target line.

Figure C11.2 — Distribution of weekly product demand.

C11.9 Key Insights

SKU-104 and SKU-102 are at serious stockout risk (5 and 12 days of stock respectively) — these need urgent reordering.
SKU-106 is significantly overstocked at 95 days — cash is sitting idle in slow-moving stock.
Weekly demand is fairly normally distributed around 50 units, meaning simple average-based reorder points should work well for most SKUs without complex forecasting.

C11.10 Business Recommendations

Place immediate reorders for SKU-104 and SKU-102 before they hit a full stockout and lose sales.
Run a clearance promotion on SKU-106 to free up cash and warehouse space tied up in overstocked inventory.
Set up an automated weekly days-of-stock report (reusing this exact script) so at-risk SKUs are caught before they become urgent.