Minimum Stock Level Calculation¶

This document describes the automated minimum stock level calculation system.

Overview¶

The system calculates recommended minimum stock levels for products based on historical sales data. Minimum stock levels are stored per-business on the Product model:

ssl_minimum_stock_level: For SSL (UK) business
sse_minimum_stock_level: For SSE (EU) business

This means the same minimum applies whether viewing stock at Loanhead Picking, Loanhead Warehouse, or any other location belonging to the same business.

Logic¶

Data Source: Analyzes shipped sales orders from the last 12 months (configurable)
Calculation: min_quantity = monthly average sales × months of stock buffer
Default Buffer: 2 months worth of stock
Zero Sales: Products with no sales in the lookback period have their min_quantity set to 0
Business Grouping: Sales are grouped by Order.business_id to calculate separately for SSL and SSE

Data Model¶

Field	Location	Description
`ssl_minimum_stock_level`	Product	Minimum for SSL (UK) business
`sse_minimum_stock_level`	Product	Minimum for SSE (EU) business

Business IDs¶

Business	ID	Field
SSL (UK)	1	`ssl_minimum_stock_level`
SSE (EU)	2	`sse_minimum_stock_level`

Command Line Usage¶

Dry Run (Preview Changes)¶

python manage.py calculate_min_stock_levels

Shows what would change without modifying the database.

Apply Changes¶

python manage.py calculate_min_stock_levels --apply

Actually updates the minimum stock level fields on Products.

Options¶

Option	Default	Description
`--apply`	False	Apply changes to database (default is dry-run)
`--months-lookback`	12	Number of months to look back for sales data
`--months-of-stock`	2	Months of stock to maintain as minimum
`--verbose`	False	Show detailed output including zero resets
`--export FILENAME`	None	Export results to a CSV file
`--product-id ID`	None	Calculate for a specific product only

Examples¶

# Preview all changes
python manage.py calculate_min_stock_levels

# Apply changes with verbose output
python manage.py calculate_min_stock_levels --apply --verbose

# Use 6 months lookback and 3 months buffer
python manage.py calculate_min_stock_levels --months-lookback 6 --months-of-stock 3

# Export results to CSV for review
python manage.py calculate_min_stock_levels --export min_stock_review.csv

# Calculate for a single product
python manage.py calculate_min_stock_levels --product-id 123

Frontend API¶

The calculation can also be triggered from the frontend via API endpoints.

Endpoints¶

Calculate for a specific product/business: /stock/calculate-min-level/<product_id>/?business=ssl|sse

Preview Calculation (GET)¶

GET /stock/calculate-min-level/123/?business=ssl
GET /stock/calculate-min-level/123/?business=sse&months_lookback=6&months_of_stock=3

Returns JSON with calculation results without modifying the database. The business parameter determines which business (ssl or sse) to calculate for.

Response Format¶

{
    "product_id": 123,
    "product_sku": "SENSEK0001",
    "product_name": "Temperature Sensor",
    "business": "ssl",
    "business_id": 1,
    "business_name": "SSL (UK)",
    "business_field": "ssl_minimum_stock_level",
    "months_lookback": 12,
    "months_of_stock": 2,
    "total_sold": 48,
    "months_with_sales": 10,
    "monthly_average": 4.8,
    "monthly_average_full_period": 4.0,
    "monthly_breakdown": [
        {"month": "Jan 2025", "quantity": 5},
        {"month": "Feb 2025", "quantity": 3}
    ],
    "suggested_min_quantity": 10,
    "suggested_min_conservative": 8,
    "current_min_quantity": 5,
    "is_lumpy_sales": false,
    "calculation_explanation": "Sold 48 units over 10 month(s)..."
}

Bulk Calculation Response¶

{
    "updates": [
        {
            "product_id": 123,
            "product_sku": "SENSEK0001",
            "product_name": "Temperature Sensor",
            "business_id": 1,
            "business_name": "SSL (UK)",
            "field_name": "ssl_minimum_stock_level",
            "total_sold_12m": 48,
            "months_with_sales": 10,
            "monthly_average": 4.8,
            "current_min_quantity": 0,
            "calculated_min_quantity": 10,
            "change": 10
        }
    ],
    "zero_updates": [
        {
            "product_id": 456,
            "product_sku": "ACCTEK0001",
            "product_name": "Old Accessory",
            "business_id": 1,
            "business_name": "SSL (UK)",
            "field_name": "ssl_minimum_stock_level",
            "current_min_quantity": 5,
            "calculated_min_quantity": 0,
            "reason": "No sales in last 12 months"
        }
    ],
    "summary": {
        "products_with_sales": 150,
        "products_without_sales": 200,
        "total_updates": 150,
        "total_zero_resets": 25,
        "dry_run": true,
        "months_lookback": 12,
        "months_of_stock": 2
    }
}

Using in Code¶

The calculation logic is available as a reusable service:

from catalogue.stock_calculations import (
    calculate_min_stock_levels,
    calculate_min_stock_for_single_product,
    calculate_min_stock_for_business,
)

# Full calculation (dry run)
results = calculate_min_stock_levels(
    months_lookback=12,
    months_of_stock=2,
    dry_run=True
)

# Apply changes
results = calculate_min_stock_levels(dry_run=False)

# Single product calculation
results = calculate_min_stock_for_single_product(product_id=123)

# Single product for specific business (for frontend calculator)
results = calculate_min_stock_for_business(
    product_id=123,
    business='ssl'  # or 'sse'
)

These environment variables affect the calculation:

SSL_BUSINESS_ID=1
SSE_BUSINESS_ID=2

Product.ssl_minimum_stock_level - Minimum stock for SSL (UK) business
Product.sse_minimum_stock_level - Minimum stock for SSE (EU) business
StockLocation.business - FK to Business, determines which min level to use
Order.business_id - Source of sales data grouping