demand_forecasting.py

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from pathlib import Path

# input file path
INPUT_PATH = "data/inventory_sales_daily.xlsx"

# outputs
OUT_DIR = Path("outputs")
OUT_DIR.mkdir(parents=True, exist_ok=True)

# reading data
df = pd.read_excel(INPUT_PATH)
df["date"] = pd.to_datetime(df["date"])
df["qty"] = pd.to_numeric(df["qty"], errors="coerce").fillna(0)

# month
df["month"] = df["date"].dt.to_period("M").dt.to_timestamp()

# monthly
monthly = (
    df.groupby(["sku", "month"], as_index=False)["qty"]
      .sum()
      .rename(columns={"qty": "demand"})
)

# choosing sku4
TARGET_SKU = "SKU-004"
ts = monthly[monthly["sku"] == TARGET_SKU].sort_values("month")[["month", "demand"]].reset_index(drop=True)

# time series data storage
ts.to_excel(OUT_DIR / f"monthly_series_{TARGET_SKU}.xlsx", index=False)

# time series chart
plt.figure(figsize=(8,4.5))
plt.plot(ts["month"], ts["demand"], marker="o")
plt.title(f"Monthly Demand Series — {TARGET_SKU}")
plt.xlabel("Month")
plt.ylabel("Demand (units)")
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.savefig(OUT_DIR / f"monthly_series_{TARGET_SKU}.png", dpi=200)
plt.close()

print("Saved:", OUT_DIR / f"monthly_series_{TARGET_SKU}.xlsx")
print(ts)




# data division
h = 3
train = ts.iloc[:-h].copy()
test = ts.iloc[-h:].copy()

print("Train months:", len(train), "Test months:", len(test))
print("Test period:\n", test)



WINDOW = 3

def moving_average_forecast(train_series, horizon=3, window=3):
    history = list(train_series)
    forecasts = []
    for _ in range(horizon):
        fc = np.mean(history[-window:])
        forecasts.append(fc)
        history.append(fc)
    return np.array(forecasts)

ma_pred = moving_average_forecast(train["demand"].values, horizon=h, window=WINDOW)




ALPHA = 0.3

def simple_exp_smoothing_forecast(train_series, horizon=3, alpha=0.3):
    level = train_series[0]
    for x in train_series[1:]:
        level = alpha * x + (1 - alpha) * level
    return np.array([level] * horizon)

ses_pred = simple_exp_smoothing_forecast(train["demand"].values, horizon=h, alpha=ALPHA)





def mae(y_true, y_pred):
    y_true = np.array(y_true)
    y_pred = np.array(y_pred)
    return np.mean(np.abs(y_true - y_pred))

def mape(y_true, y_pred):
    y_true = np.array(y_true)
    y_pred = np.array(y_pred)
    eps = 1e-9
    return np.mean(np.abs((y_true - y_pred) / (y_true + eps))) * 100

y_test = test["demand"].values

results = pd.DataFrame({
    "model": ["Moving Average", "Exp Smoothing"],
    "MAE": [mae(y_test, ma_pred), mae(y_test, ses_pred)],
    "MAPE_%": [mape(y_test, ma_pred), mape(y_test, ses_pred)]
}).round(2)

print(results)

results.to_excel(OUT_DIR / f"model_comparison_{TARGET_SKU}.xlsx", index=False)






plt.figure(figsize=(9,4.8))

# the real ones
plt.plot(ts["month"], ts["demand"], marker="o", label="Actual")

# months of testing
test_months = test["month"].values

plt.plot(test_months, ma_pred, marker="o", label=f"Moving Avg (w={WINDOW})")
plt.plot(test_months, ses_pred, marker="o", label=f"Exp Smooth (α={ALPHA})")

plt.title(f"Forecast Comparison on Test — {TARGET_SKU}")
plt.xlabel("Month")
plt.ylabel("Demand (units)")
plt.xticks(rotation=45, ha="right")
plt.legend()
plt.tight_layout()
plt.savefig(OUT_DIR / f"forecast_test_comparison_{TARGET_SKU}.png", dpi=200)
plt.close()

print("Saved comparison chart.")






# choosing a better model based on MAE
best_model = results.sort_values("MAE").iloc[0]["model"]
print("Best model:", best_model)

H_FUTURE = 3

if best_model == "Moving Average":
    future_pred = moving_average_forecast(ts["demand"].values, horizon=H_FUTURE, window=WINDOW)
    model_tag = f"MA_w{WINDOW}"
else:
    future_pred = simple_exp_smoothing_forecast(ts["demand"].values, horizon=H_FUTURE, alpha=ALPHA)
    model_tag = f"SES_a{ALPHA}"

# future months
last_month = ts["month"].max()
future_months = pd.date_range(last_month + pd.offsets.MonthBegin(1), periods=H_FUTURE, freq="MS")

future = pd.DataFrame({
    "month": future_months,
    "forecast": np.round(future_pred, 0)
})

future.to_excel(OUT_DIR / f"future_forecast_{TARGET_SKU}_{model_tag}.xlsx", index=False)
print(future)