Page |1

Capstone Project Report

Course: Applied Business Analy cs for Decision Making
Dura on: Week 7 (8 Days)

Project Title
Retail – Smart Inventory Op miza on in Retail Chains

Business Context & Problem Statement

 This capstone project is centred on enhancing inventory management practices within

a retail chain by leveraging advanced demand forecasting techniques. In many retail
environments, inaccurate demand prediction leads to two major challenges: stockouts,
which result in missed sales opportunities and customer dissatisfaction, and
overstocking, which ties up working capital and increases storage costs and product
wastage.
 To address these challenges, the project applies two powerful time series forecasting
models—ARIMA (Autoregressive Integrated Moving Average) and Prophet,
developed by Facebook. These models are implemented to analyse historical sales
data at a product category level and forecast future demand patterns with greater
accuracy. The ultimate goal is to provide data-driven insights that help the retail chain
maintain optimal inventory levels, reduce operational inefficiencies, and improve
product availability across stores.
 The analytical process combines multiple tools: Excel was used for initial data
cleaning and transformation; Python, along with libraries such as stats models
and prophet, was used to build and evaluate forecasting models; and Tableau was
utilized to create interactive visual dashboards for better business communication.
This integrated approach ensures that technical insights are translated into actionable
strategies that decision-makers can apply directly to inventory planning and supply
chain operations.
 What pa erns can be iden fied from historical sales and promo onal data?
 Which internal and external factors influence product sales?
 Can we build a model to accurately predict weekly or monthly sales for key product
categories?
 How can the findings guide strategic decisions in marke ng, logis cs, and
procurement?
 Page |2

Project Objec ves

The primary objec ves of this project are as follows:
Clean and preprocess data:
Build category-wise ARIMA & Prophet models:
Evaluate using MAE, RMSE, and MAPE:
The performance of each forecas ng model was evaluated using standard error metrics:
 MAE (Mean Absolute Error): Measures average absolute devia on between actual
and forecasted values.
 RMSE (Root Mean Squared Error): Penalizes larger errors more heavily, providing
insight into predic on accuracy.
 MAPE (Mean Absolute Percentage Error): Expresses error as a percentage, allowing
easy comparison across categories.
These metrics helped determine which model (ARIMA or Prophet) performed best
for each category.
Visualize insights in Tableau:
Recommend stock strategies:
Based on forecast results and observed trends, strategic recommenda ons were made to
op mize inventory management. These included se ng dynamic reorder points, adjus ng
procurement plans according to predicted peaks and troughs in demand, reducing safety
stock for slow-moving items, and leveraging dashboards for ongoing monitoring. The goal
was to improve service levels, reduce costs, and enhance opera onal efficiency.

Data Sources & Collec on:

h ps://www.kaggle.com/datasets/atomicd/retail-store-inventory-and-demand-
forecas ng
I have drawn the data set from Kaggle
This project u lizes structured data extracted from the retailer’s internal systems. The
dataset includes:
 Source: SQL-based sales databases and Excel extracts from promo onal planning
systems.
 Time Frame: Data spans over three fiscal years (2022–2024).
 Key Variables:
 Date – The date of the sales transaction
 Store_ID – Unique identifier for each store location
 Product_ID – Unique identifier for each product
 Category – Product category (e.g., Furniture, Technology, Office Supplies)
 Page |3

 Quantity – Number of units sold

 Sales – Total sales value (₹ or $)
 Demand – Customer demand (often same as quantity sold or adjusted)

Addi onal external datasets such as public holidays and weather condi ons were integrated
to enrich the analysis.

Tools & Technologies Used

To perform analysis and build insights, the following tools were employed:
 Excel: Used for ini al data inspec on, summary sta s cs, and cleaning.
 SQL: Used to extract and join data from the transac onal sales database.
 Power BI: Used for visual analy cs, KPI dashboards, and execu ve storytelling.
 Python: Used for data preprocessing, EDA, and predic ve modelling. Key libraries
include:
o pandas for data manipula on
o seaborn and matplotlib for data visualiza on
o scikit-learn for machine learning models
o stats models for sta s cal analysis

Project Workflow & Deliverables

Project Workflow
Day 1: Project Ini a on
• Conducted problem scoping based on frequent stockouts and overstocking
trends.
• Defined project goals: demand forecas ng and inventory op miza on.
• Iden fied key datasets: sales data, inventory logs, wastage records, and
product info.
• Clarified important variables like Date, Product_ID, Category, Quan ty,
Demand.
 Page |4

Day 2–3: Analysis Execu on

• Cleaned and integrated mul ple datasets into a structured master table using
Python and Excel.
• Performed me series aggrega on by category and date for forecas ng.
• Built ARIMA and Prophet models category-wise to predict future demand.
• Evaluated model accuracy using MAE, RMSE, and MAPE metrics.
• Developed Tableau dashboards for trend visualiza on and store-wise
filtering.
Day 4: Final Output
• Documented modeling results with plots and evalua on metrics.
• Interpreted seasonal and category-specific demand behavior.
• Delivered final execu ve insights and recommenda ons for demand-based
stock planning.
• Prepared Word report and Tableau dashboard for presenta on to
stakeholders.
Expected Deliverables
1. Final Report (3–5 pages)
• Execu ve summary of the project.
• Detailed background, business problem, and project objec ves.
• Methodology used including ARIMA and Prophet modeling.
• Data cleaning, transforma on steps, and insights from exploratory analysis.
• Evalua on metrics: MAE, RMSE, MAPE with interpreta on.
• Final conclusions and business recommenda ons.
2. Interac ve Dashboard in Tableau
• Visuals included:
- Forecasted demand trends by product category
- Inventory levels vs. predicted demand
 Page |5

- Store-wise stock movement and wastage analysis

• Filters for store loca on, me period, and category
• Key Performance Indicators (KPIs): stockouts, wastage %, forecast error rate
3. Execu ve Presenta on (6–8 slides)
• Problem defini on and business context
• Data overview and model methodology
• Visual summary of demand forecasts
• Strategic recommenda ons for inventory management
• Op onal: model performance metrics and benefits of implementa on
Regression Evalua on Metrix:

ARIMA, Prophet Evalua on Metrix: