Building an AI Inventory Forecasting System: From 18 Hours to 4 Hours

Maryana Group manages 7,800+ SKUs across four branches in Oman and the UAE. Our product range is highly volatile — rapid-trend cosmetics, steady skincare lines, and seasonal personal care items that spike during Ramadan and Eid.

The procurement team was spending 18+ hours every week manually planning inventory using Excel spreadsheets. By the time I finished with this project, that process was fully automated and ran overnight, producing a decision-ready procurement list before anyone arrived at the office.

This is the technical log of how that happened.

The Breaking Point

Our primary ERP is Tally Prime. It’s a robust system for financial accounting, but extracting granular, historical sales velocity for 7,800 individual items forces it to generate massive XML files — routinely exceeding 1.5GB.

The legacy manual workflow was:

Exporting: Wait hours for branch-wise sales reports to export as raw XML from Tally
Cleaning: Fix encoding issues, filter out returns and damages. Excel would routinely freeze when attempting to load three years of history
Forecasting: Apply basic arithmetic averages — a moving average that treated Ramadan the same as an average February
Guesswork: Manually estimate seasonal swings based on “gut feeling” rather than data

The result: millions of dollars in capital trapped in dead stock. Stockouts during peak seasons. Overstock of slow-moving items. Procurement decisions made on gut instinct because the data was too unwieldy to trust.

The Solution: A Python ETL + Forecasting Pipeline

I built a custom ETL and forecasting pipeline from scratch. No enterprise software, no vendor lock-in, no per-prediction pricing.

Technical stack:

Core Engine: Python 3.11
Data Manipulation: Pandas + NumPy
Forecasting AI: Prophet (Meta’s open-source time-series library)
Data Persistence: SQLite (file-based, zero maintenance, perfect for offline cron jobs)
Parallelization: concurrent.futures.ProcessPoolExecutor

The Core Challenge: Parallelization

Prophet’s strength is that it builds a unique statistical model for each individual time series. For 7,800 SKUs, that’s 7,800 independent models. Executed sequentially, this takes over 18 hours on standard hardware.

The problem is Python’s Global Interpreter Lock (GIL). Threads in Python can’t run Python code in parallel — only one thread executes Python bytecode at a time. For CPU-bound work like Prophet training, threads don’t help.

The solution: ProcessPoolExecutor, which spawns separate Python processes that each have their own GIL. The work gets distributed across all physical CPU cores simultaneously.

import pandas as pd
from prophet import Prophet
from concurrent.futures import ProcessPoolExecutor, as_completed
import logging

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(processName)s - %(message)s')

def forecast_single_item(item_id: str, history_df: pd.DataFrame) -> dict:
 """
 Isolated worker function to train a Prophet model for a single SKU.
 Must be fully self-contained for safe multiprocessing.
 """
 try:
 # Isolate the specific SKU's history
 df = history_df[history_df['item_id'] == item_id][['date', 'quantity']]
 df.columns = ['ds', 'y'] # Prophet requires these column names

 # Skip items with insufficient history
 if len(df) < 30:
 return {'item_id': item_id, 'status': 'insufficient_data', 'forecast': 0}

 # Initialize model with seasonality
 model = Prophet(
 yearly_seasonality=True,
 weekly_seasonality=True,
 daily_seasonality=False
 )
 
 # Add Oman holidays (Eid, National Day, Ramadan adjacent periods)
 model.add_country_holidays(country_name='OM')

 # Train and predict
 model.fit(df)
 future = model.make_future_dataframe(periods=30)
 forecast = model.predict(future)

 predicted_demand = forecast[['ds', 'yhat']].iloc[-1]['yhat']

 return {
 'item_id': item_id,
 'status': 'success',
 'forecast': round(max(0, predicted_demand))
 }

 except Exception as e:
 return {'item_id': item_id, 'status': f'error: {str(e)}', 'forecast': 0}

def execute_parallel_pipeline(all_item_ids: list, global_sales_data: pd.DataFrame):
 """
 Distributes workload across all available CPU cores.
 max_workers=None = number of processors on the machine.
 """
 results = []

 with ProcessPoolExecutor(max_workers=None) as executor:
 futures = {
 executor.submit(forecast_single_item, item_id, global_sales_data): item_id
 for item_id in all_item_ids
 }

 for future in as_completed(futures):
 results.append(future.result())
 if len(results) % 500 == 0:
 logging.info(f"Processed {len(results)} / {len(all_item_ids)} SKUs—")

 return pd.DataFrame(results)

By distributing across all CPU cores, execution time dropped from 18+ hours to approximately 4.5 hours. The process runs overnight as a cron job. By 7 AM, the procurement manager has a filtered, decision-ready CSV telling them exactly what to reorder and how many units.

Why Prophet Handles Middle East Seasonality Correctly

A standard moving average algorithm fails spectacularly in the Middle East. Ramadan and Eid trace the Hijri lunar calendar — they shift backward by roughly 11 days every Gregorian year. A moving average sees the spike and treats it as noise because it can’t connect this year’s spike to last year’s spike across the calendar boundary.

Prophet handles this natively. By adding Oman holidays via model.add_country_holidays(country_name='OM'), the AI identifies these floating holidays in the historical dataset and maps demand multipliers to the correct future dates automatically.

This is the difference between a forecast that predicts a Ramadan stockout two weeks in advance versus one that sees the spike as an anomaly and orders normal stock.

Handling the ETL Layer

The Prophet model is only as good as the data it receives. Approximately 60% of the project engineering time went into writing resilient XML parsers to handle the corrupted strings generated by Tally’s legacy export system.

This is the unglamorous part of AI projects. The model gets all the attention. The data pipeline does all the work.

Key ETL decisions:

SKU standardization — enforced naming conventions across all four branches before any modeling. A “L’Oréal Serum 30ml” must be “LOREAL-SERUM-30ML” everywhere or the time-series gets fragmented across multiple SKUs — I wrote about why data hygiene is foundational to every AI project here
Return/damage filtering — excluded from historical data to avoid training on noise
Zero-history fallback — new SKUs with fewer than 30 transactions fall back to a simple moving average. The system doesn’t pretend Prophet can forecast from nothing

The Results

Time reduced: 18 hours of manual Excel work → 4 hours of automated backend processing (overnight cron)
Forecast accuracy: 22% improvement versus the legacy moving average method (measured against actual sales over 6 months)
Process: 100% automated from XML export to final procurement list
Dead stock reduction: Measured 15% reduction in capital trapped in slow-moving inventory over two quarters

Key Engineering Takeaways

Data quality determines everything. The ETL layer is 60% of the work. If the data is dirty, the AI is useless. Invest in data hygiene before modeling.
Prophet has minimum data requirements. At least 30 transactions to build a trend. New SKUs need a fallback strategy — a simple moving average works fine for items with no history.
SQLite was the right choice for this job. An offline, cron-scheduled ETL pipeline processing ~1 million rows doesn’t need Postgres. SQLite has zero network overhead, requires zero maintenance, and is faster for sequential reads. The tool should match the problem.
ProcessPoolExecutor over ThreadPoolExecutor for CPU-bound work. Python’s GIL means threads don’t parallelize CPU work. Separate processes bypass the GIL entirely. The syntax is nearly identical — the performance difference is an order of magnitude.