Store-Level Demand Forecasting and Replenishment Planning for a Bengaluru Supermarket Chain

Store-Level Demand Forecasting and Replenishment Planning is a records-based business-analytics study built around Sahyadri Fresh Mart, a twelve-store neighbourhood supermarket chain in Bengaluru (name fictionalised; the operating pattern — festival spikes, inconsistent ordering, uneven availability across stores — mirrors real Indian organised grocery). The chain's store managers raise replenishment orders to the distribution centre largely from experience and last week's sales. That works in ordinary weeks but breaks down during Ugadi, Ganesha Chaturthi and Deepavali, when fast-moving SKUs run out in some stores while neighbouring stores sit on unsold stock.

For my VTU MBA project work (22MBAPR407), I extracted twenty-four months of point-of-sale billing records for forty fast-moving SKUs — staples, dairy, beverages, snacks and personal care — cleaned and aggregated them into a SKU-store-week panel in Excel, and analysed the series in Python (pandas, statsmodels, scikit-learn) and IBM SPSS Statistics. The study benchmarks a seasonal-naive baseline against Holt-Winters exponential smoothing and a regression model with festival and promotion dummies, validates every model on a held-out eight-week period using MAPE and RMSE, and converts the winning forecasts into a safety-stock and order-quantity policy. The report closes with a managerial-recommendations section written for the chain's operations head, not for an examiner.

The chain's replenishment decisions are made twelve times a week by twelve people using judgement alone. The visible symptoms are stock-outs of fast movers during festival weeks, overstock and near-expiry write-offs after over-ordered promotions, uneven availability of the same SKU across stores, and DC despatch loads that swing wildly. Because no forecast exists, promotions cannot be evaluated against a baseline, safety stock is guessed, and the operations head cannot quantify how much money poor ordering costs.

There is a need for a validated, store-level demand-forecasting method built on the chain's own billing records — one that accounts for Indian festival calendars and promotion activity, can be maintained in Excel and Python by a small team, and converts directly into weekly order quantities with safety stock, so that ordering stops being a guess and starts being a measured decision.

Abstract

Indian organised grocery is growing, but store-level replenishment in most regional chains is still driven by intuition. This study builds a quantitative demand-forecasting and replenishment framework for a twelve-store Bengaluru supermarket chain using two years of its own billing records. Weekly SKU-store demand is modelled with a seasonal-naive baseline, Holt-Winters exponential smoothing and regression with festival and promotion dummies; models are compared on a held-out eight-week window using MAPE and RMSE, and the best model feeds a safety-stock and ordering policy.

Introduction

Sahyadri Fresh Mart (fictionalised) operates twelve stores of 3,000–6,000 sq ft across South and East Bengaluru, supplied by one distribution centre (DC). Store managers order weekly. The operations head's complaint is familiar: stock-outs of atta, edible oil, milk and biscuits during festival weeks, alongside dead inventory of slow promotions. Nobody can say which stores will sell how much, so ordering guesses are multiplied by twelve.

Existing system and literature gap

• Orders are placed from experience and last week's bills; no forecast exists at SKU-store level.
• Festival demand (Ugadi, Ganesha Chaturthi, Deepavali, Ramadan) is handled by ad-hoc bulk orders, often too late or too large.
• Promotions are evaluated on total sales only, not on uplift against a baseline.
• Academic treatments of forecasting are usually demonstrated on textbook series (airline passengers, M3 data); store-level Indian grocery data with festival calendars is rarely used in MBA projects.
• Gap: a validated, reproducible SKU-store forecasting workflow on real Indian retail records, with a policy a chain of this size can actually run.

Proposed system / study design

• Build a clean SKU-store-week demand panel from POS records (24 months, 40 SKUs, 12 stores) with Excel Power Query.
• Quantify seasonality, festival spikes, promotion uplift and store heterogeneity through exploratory analysis.
• Model demand three ways: (1) seasonal-naive baseline, (2) Holt-Winters (statsmodels), (3) regression with festival, promotion and pay-week dummies (scikit-learn / SPSS).
• Validate on the final eight weeks, untouched during model building, using MAPE and RMSE; test whether regression beats the baseline significantly.
• Translate forecasts into a replenishment policy: order = forecast + safety stock − stock on hand, with safety stock derived from forecast error at a 95% service level.

Feasibility

• Technical: the chain's billing software exports standard CSVs; Excel, Python and SPSS are already available to me. No new infrastructure is required.
• Economic: the study costs nothing but time; the proposed policy needs only a spreadsheet template at the DC.
• Operational: store managers keep final say through a simple override rule, which reduces resistance. The pilot recommendation applies to the top 40 SKUs, roughly 60% of billed volume — small enough to manage, large enough to matter.

In scope

• 40 fast-moving SKUs in five categories (staples, dairy, beverages, snacks, personal care) across all 12 stores, weekly grain, 24 months of history plus an 8-week holdout.
• Demand forecasting, model comparison and validation; festival and promotion effects; safety stock and order-quantity policy at the DC-store interface.
• Semi-structured interviews with 4 store managers and the DC in-charge to interpret results and design the override rule.

Out of scope

• Fresh produce and perishables (shrinkage-dominated; needs a different model).
• New products with no sales history (no analogue method attempted).
• Daily or basket-level forecasting; supplier lead-time renegotiation; price-optimisation; ERP or POS software changes.

The study is a quantitative, records-based study with an applied predictive-analytics design: describe the demand process, model it, validate honestly, and convert the result into an operational policy. Data cleaning and panel construction happen in Excel Power Query; exploratory analysis in Excel PivotTables and Python (pandas); forecasting in Python (statsmodels, scikit-learn); hypothesis tests on error differences and promotion uplift in IBM SPSS Statistics.

Ethics and data care: records are used under a written permission letter from the chain; customer identifiers are removed at export; SKU-level sales are commercial confidential and reported only in aggregated form. No government ID numbers are stored anywhere in the dataset.

The study is a pipeline: each stage consumes the previous stage's output, and the validation stage gates everything downstream. Nothing in the policy stage is allowed to use holdout-week information.

flowchart TD
  A["POS billing CSV exports<br/>12 stores x 104 weeks"] --> B["Excel Power Query:<br/>clean, de-duplicate, aggregate to SKU-store-week"]
  B --> C["Exploratory analysis:<br/>seasonality, festival spikes, promotion uplift"]
  C --> D{"Model building"}
  D --> E["Model 1: seasonal-naive baseline"]
  D --> F["Model 2: Holt-Winters<br/>per SKU-store series"]
  D --> G["Model 3: regression with<br/>festival, promo and pay-week dummies"]
  E --> H["Holdout validation: last 8 weeks,<br/>MAPE and RMSE"]
  F --> H
  G --> H
  H --> I["Paired error comparison<br/>in SPSS (valid or not?)"]
  I --> J["Replenishment policy:<br/>forecast + safety stock - on hand"]
  J --> K["Managerial recommendations<br/>for the operations head"]

The operational policy the study hands over is deliberately simple enough to run in a spreadsheet:

flowchart LR
  F["Weekly SKU-store forecast"] --> S["Safety stock from forecast<br/>error at 95% service level"]
  S --> O["Order = forecast + safety stock<br/>- stock on hand"]
  O --> C{"Store manager<br/>override?"}
  C -->|"Yes — reason logged"| P["Order goes to DC"]
  C -->|"No"| P

Two design choices are worth defending in the viva. First, the holdout window is the last eight consecutive weeks and is never opened during model building — this is what makes the MAPE numbers comparable across models. Second, the override log is part of the design, not an afterthought: it lets the chain measure where managerial judgement still adds value, which is exactly the conversation an examiner will want to see a managerial-recommendations section address.

• A gradient-boosted tree model with weather and local-event features, benchmarked against the regression to test whether complexity pays at this data volume.
• Hierarchical reconciliation of forecasts across SKU, category and store so store totals and chain totals agree.
• Price-elasticity estimation from the chain's own discount history to move from forecasting to pricing decisions.
• Extending the policy to perishables with shelf-life constraints, where order quantities and markdowns must be decided together.
• A Power BI or Streamlit dashboard that refreshes weekly and surfaces the override log to the operations head.