AI Inventory Forecasting: From 18 Hours to 4 Hours

Outcome: 7,800+ SKUs, 18+ hours of weekly Excel planning → 4h 25m unattended overnight run producing a decision-ready CSV before anyone arrives. Maryana Group manages those SKUs across four branches in Oman and the UAE: rapid-trend cosmetics and steady skincare lines, plus seasonal personal care items that spike during Ramadan and Eid.

I wrote this as the technical log of how a Python + Prophet pipeline replaced the crashing spreadsheets. The full project record is the forecasting-tool case study.

---

1.8 GB of XML Excel Couldn’t Swallow

My client’s primary ERP is Tally Prime. It handles financial accounting well, but pulling granular, historical sales velocity for 7,800 individual items produces massive XML exports. One run parsed 1.8 GB at [12:04:11] in the work log.

The legacy manual workflow was:

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

The result: fast-moving A-class items stocked out while slow-moving C-class stock tied up purchasing budget. Branch managers were allocating capital on estimates, with no systematic forecast behind them.

---

The Pipeline: Python ETL + Prophet, No Vendor

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

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

---

Beating the GIL: One Process per SKU

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 8 workers (concurrent.futures.ProcessPoolExecutor, as detailed in the forecasting-tool case study), runtime dropped from ~18 hours sequential to 4h 25m (transcript [14:29:18]). The run completes unattended; the buyer opens a decision-ready CSV with Recommended_Reorder_Qty per SKU.

---

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, shifting backward by roughly 11 days every Gregorian year, so 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.

I learned the difference the expensive way: a forecast that predicts a Ramadan stockout two weeks in advance beats 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. The hardest engineering in the work entry is the ETL half: streaming 1.8 GB via lxml.etree.iterparse() (never loading it into RAM), normalizing mixed UTF-8/UTF-16, and skipping 14 damaged nodes without halting, all over a 782,904-row sales velocity frame.

Nobody applauds the ETL. The model gets the attention. The pipeline does the work.

Key ETL decisions:

• SKU standardization: enforced naming conventions across all four branches before any modeling. A product like L’Oréal Serum 30ml must be recorded as LOREAL-SERUM-30ML everywhere, or its time-series splits across multiple SKUs. See why data hygiene is foundational to every AI project.
• 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

---

4h 25m, Unattended

• Time: 18 hours of manual Excel work → 4h 25m unattended (work-entry transcript [14:29:18]), 8 workers at near-100% CPU
• Scale: 7,800 Prophet models fit over a 782,904-row velocity frame; 14 damaged XML nodes skipped, run continues
• Process: unattended overnight run from XML export to decision-ready CSV, with no human step in the loop
• Limitation (honest): the pipeline never backtested forecasts against actuals, so no accuracy figure is claimed here. Per-SKU error logging is the documented next step in the work entry

---

Key Engineering Takeaways

1. Data quality determines everything. The work entry’s two documented challenges are both ETL (malformed XML, streaming parse) before modeling is even mentioned. If the data is dirty, the AI is useless.

2. Prophet has minimum data requirements. At least 30 transactions to build a trend. New SKUs need a fallback strategy, and a simple moving average works fine for items with no history.

3. SQLite was the right choice for this job. An unattended ETL run over a 782,904-row frame on office desktop hardware: no server, no cloud bill, as documented in the forecasting-tool case study. SQLite’s indexed file store fits that shape; the tool matches the problem.

4. ProcessPoolExecutor over sequential Prophet fits. Python’s GIL serializes CPU-bound threads; separate processes bypass it. Measured here: ~18h sequential projection → 4h 25m on 8 workers (transcript [12:11:04]–[14:29:18]).

Who Should Steal This

I wrote this for procurement managers and branch owners. Without a systematic forecast, the failure mode is exactly what this post describes: A-class items stocking out during Ramadan while capital sits frozen in C-class overstock, week after week. Operations teams running Tally Prime face the same shape of problem wherever 1.8 GB XML exports meet Excel’s row limits. One caution from the limitation note above: an unbacktested forecast is a decision aid, not a guarantee, so log per-SKU error before trusting the reorder quantities blindly. The full project context is the work case study, and the stack pieces are in the Prophet and SQLite notes.