Data AnalysisChatGPTClaudeGemini

Inventory Demand Forecasting Prompt

Build an inventory demand forecasting model that predicts stock needs, prevents stockouts and overstock, and optimizes reorder points using historical sales data and seasonal patterns.

Prompt Template

You are a supply chain analytics expert. Help me build an inventory demand forecasting model and optimize my stock levels.

Business context:
- Business type: [e-commerce / retail / wholesale / manufacturing / D2C]
- Number of SKUs: [total product count]
- Sales history available: [months/years of historical data]
- Seasonality: [describe seasonal patterns — holiday spikes, summer/winter trends, back-to-school, etc.]
- Current inventory management: [manual / spreadsheet / ERP system / WMS — specify]
- Main inventory problems: [stockouts / overstock / dead stock / unpredictable demand / long lead times]
- Supplier lead times: [average days from order to delivery]
- Storage constraints: [warehouse capacity limitations, if any]
- Data available: [daily sales, weekly orders, returns, marketing spend, website traffic, etc.]

Please provide:

1. **Demand Forecasting Model** — A practical forecasting approach suited to my data:
   - Recommend the right method (moving average, exponential smoothing, ARIMA, or ensemble) based on data volume and seasonality
   - Step-by-step setup instructions with formulas or code
   - How to incorporate seasonality, trends, and promotional effects
   - How to handle new products with no history (cold start problem)

2. **Safety Stock Calculation** — Formula and worked example for calculating safety stock based on:
   - Demand variability
   - Lead time variability
   - Target service level (e.g., 95% = Z-score of 1.65)

3. **Reorder Point Optimization** — When to trigger purchase orders:
   - Reorder point formula: (Average daily demand × Lead time) + Safety stock
   - Worked example with real numbers
   - Economic Order Quantity (EOQ) calculation if applicable

4. **ABC-XYZ Analysis** — Classify inventory by value and demand predictability:
   - A/B/C: Revenue contribution (80/15/5 rule)
   - X/Y/Z: Demand variability (stable/variable/unpredictable)
   - Strategy recommendations for each combination (AX, AY, BZ, etc.)

5. **Forecast Accuracy Tracking** — How to measure and improve forecasts:
   - MAPE (Mean Absolute Percentage Error)
   - Bias detection (consistently over/under-forecasting)
   - Dashboard metrics and alert thresholds

6. **Actionable Dashboard** — Key metrics to monitor daily/weekly:
   - Days of stock remaining per SKU
   - Stockout risk alerts
   - Overstock flags
   - Forecast vs. actual variance

Example Output

# Inventory Demand Forecasting: D2C Skincare Brand (120 SKUs)

1. Demand Forecasting Model

Recommended: Exponential Smoothing with Seasonality (Holt-Winters)

**Why:** You have 24 months of daily sales data with clear seasonal patterns (holiday spike Nov-Dec, summer sunscreen surge). Holt-Winters handles trend + seasonality well and doesn't require the statistical complexity of ARIMA.

Setup (Python)

import pandas as pd

from statsmodels.tsa.holtwinters import ExponentialSmoothing

# Load and prepare data

df = pd.read_csv('daily_sales.csv', parse_dates=['date'])

df = df.set_index('date').resample('W').sum() # Weekly aggregation

# Fit Holt-Winters model

model = ExponentialSmoothing(

df['units_sold'],

trend='add',

seasonal='add',

seasonal_periods=52 # Weekly data, annual seasonality

).fit()

# Forecast next 12 weeks

forecast = model.forecast(12)

print(forecast)

Handling Promotions

Add a promotional uplift multiplier:

# Historical promotion impact

promo_uplift = {

'flash_sale': 2.3, # 130% increase

'influencer_post': 1.5, # 50% increase

'holiday_bundle': 1.8, # 80% increase

}

# Adjust forecast for planned promotions

forecast_week_8 = base_forecast * promo_uplift['flash_sale']

Cold Start (New Products)

For SKUs with < 8 weeks of history:

1. Use the sales trajectory of a similar existing product as a proxy

2. Start with manual estimates: Expected daily sales × 1.5 (buffer)

3. Switch to data-driven forecast after 8 weeks of actual sales

2. Safety Stock Calculation

Formula:

Safety Stock = Z × √(Lead Time × σ²_demand + Average Demand² × σ²_lead_time)

Worked Example: "Hydrating Serum 30ml" (top seller)

- Average daily demand: 45 units

- Demand standard deviation: 12 units/day

- Average lead time: 21 days

- Lead time standard deviation: 3 days

- Target service level: 95% (Z = 1.65)

Safety Stock = 1.65 × √(21 × 12² + 45² × 3²)

= 1.65 × √(21 × 144 + 2025 × 9)

= 1.65 × √(3,024 + 18,225)

= 1.65 × √21,249

= 1.65 × 145.8

= 241 units

3. Reorder Point

Reorder Point = (Average Daily Demand × Lead Time) + Safety Stock

= (45 × 21) + 241

= 945 + 241

= 1,186 units

→ When stock hits 1,186 units, trigger a purchase order.

4. ABC-XYZ Analysis

| Class | Revenue | Demand Pattern | Strategy | Example SKUs |

|-------|---------|---------------|----------|--------------|

| **AX** | Top 80% | Stable/predictable | Automated reordering, tight safety stock, JIT delivery | Hydrating Serum, SPF 50 |

| **AY** | Top 80% | Variable | Higher safety stock, monthly forecast review | Holiday Gift Sets, Limited Edition |

| **BX** | Next 15% | Stable | Standard reorder with moderate safety stock | Cleanser, Toner |

| **BY** | Next 15% | Variable | Quarterly review, flexible MOQs with supplier | New product lines |

| **CX** | Bottom 5% | Stable | Minimal stock, consider dropship | Accessories, travel sizes |

| **CZ** | Bottom 5% | Unpredictable | Evaluate discontinuation, make-to-order if possible | One-time collaborations |

5. Forecast Accuracy Tracking

# MAPE Calculation

mape = (abs(actual - forecast) / actual).mean() * 100

# Targets

# MAPE < 15%: Excellent

# MAPE 15-25%: Acceptable

# MAPE 25-40%: Needs improvement

# MAPE > 40%: Investigate and rebuild model

**Bias Check:** If forecast is consistently 10%+ higher than actual for 4+ weeks, your model is over-forecasting → reduce safety stock to avoid overstock.

Tips for Best Results

  • 💡Start with weekly aggregation, not daily — daily sales data is noisy and leads to less accurate forecasts for most consumer products.
  • 💡Run your ABC-XYZ analysis quarterly. Products shift categories as seasons change and new items launch.
  • 💡Track forecast accuracy by SKU category, not just overall. A 20% MAPE average might hide that your top seller is off by 40%.
  • 💡Factor in marketing calendar events (product launches, influencer campaigns, sales) as manual forecast adjustments — statistical models can't predict promotions you haven't run yet.

Try it with

ChatGPTClaudeGemini

Related Prompts

Data Analysis

Dataset Summary and Insights

Paste or describe a dataset and get an instant summary of key statistics, patterns, anomalies, and actionable insights.

ChatGPTClaudeGemini
Data Analysis

SQL Query Writer for Business Reports

Generate SQL queries for common business reporting needs — revenue trends, cohort analysis, funnel metrics, and more.

ChatGPTClaude
Data Analysis

Dashboard KPI Definition Framework

Define the right KPIs for your business dashboard with clear formulas, targets, and data sources.

ChatGPTClaudeGemini