Skip to main content

Power BI Sales & Inventory Forecasting Project (SARIMA)

Project Overview

In this project, I built an end-to-end Business Analytics & Data Science solution using SQL, Power BI, and Python to:

  • Analyze historical sales, profit, discounts, and units sold

  • Build an Executive Summary Dashboard for leadership

  • Forecast next 3 months of Sales & Units Sold

  • Support inventory planning and business decision-making

This project simulates a real-world eCommerce / Retail analytics use case, combining ETL, BI reporting, and predictive modeling in a single workflow.

Business Objective

Primary Goals

  • Provide leadership with a single-source executive dashboard

  • Identify sales, profit, and regional performance trends

  • Predict future demand (Sales & Units Sold) for:

    • Inventory planning

    • Revenue forecasting

    • Procurement & supply-chain readiness

Key Questions Answered

  • How are sales and profits trending over time?

  • Which regions and segments drive the most value?

  • What will be the expected sales & unit demand for the next 3 months?

Architecture & Tools Used

LayerTools
Data SourceSQL Database (simulated enterprise retail DB)
ETL & TransformationSQL, Power Query
Analytics & BIPower BI
ForecastingPython (SARIMA – statsmodels)
VisualizationPower BI + Python visuals
Skills DemonstratedSQL, ETL, Time Series, BI, Forecasting

Step 1: Data Extraction (ETL using SQL)

I started by extracting structured sales data using SQL from the transactional database.

Sample SQL ETL Query

SELECT  
    OrderDate,
    Country,
    Segment,
    Product,
    Sales,
    Profit,
    Discounts,
    UnitsSold,
    COGS
FROM Financials
WHERE OrderDate IS NOT NULL;

ETL Objectives

  • Select only business-relevant fields

  • Ensure date integrity

  • Prepare data for analytics & forecasting

Step 2: Data Cleaning & Transformation

Cleaning Actions

  • Removed duplicate rows

  • Handled null values

  • Standardized column names

  • Converted Month text → Month Number

  • Ensured consistent date format

Created a Date Dimension Table

Essential for:

  • Time intelligence (YoY, MoM)

  • Filtering by Year / Month / Quarter

DateTable =
ADDCOLUMNS (
    CALENDAR (DATE(2013,1,1), DATE(2015,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "Month Number", MONTH([Date]),
    "Quarter", "Q" & FORMAT([Date], "Q")
)

Step 3: Executive Summary Dashboard (Power BI)

Dashboard Purpose

Designed for CXOs & Business Heads to get instant insights.

Key Visuals

  • 📈 Profit Trend by Date

  • 🌍 Profit by Country (Map)

  • 📊 Sales by Segment & Date

  • 🎛️ Interactive slicers (Year, Month)

Business Value

  • Quick performance monitoring

  • Region-wise profit analysis

  • Segment contribution clarity

Step 4: Predictive Analytics – Sales & Units Forecast

To support inventory and revenue planning, I built a 3-month forecast using SARIMA.

Why SARIMA?

SARIMA (Seasonal ARIMA) is ideal for retail/ecommerce data because:

  • Handles trend + seasonality

  • Works well with monthly sales patterns

  • Provides confidence intervals (risk visibility)

SARIMA Components

  • AR (p): Autoregression

  • I (d): Differencing (trend removal)

  • MA (q): Moving average

  • Seasonal (P, D, Q, s): Monthly seasonality (s = 12)

Step 5: Python SARIMA Forecast (Power BI Python Script)

Key Python Steps

  1. Convert Month text → numeric

  2. Create proper Date column

  3. Aggregate monthly sales

  4. Train SARIMA model

  5. Forecast next 3 months

  6. Plot actual + forecast + confidence band

Simplified SARIMA Code

from statsmodels.tsa.statespace.sarimax import SARIMAX

model = SARIMAX(
    monthly_sales['Units_Sold'],
    order=(1,1,1),
    seasonal_order=(1,1,1,12)
)

model_fit = model.fit()
forecast = model_fit.get_forecast(steps=3)
conf_int = forecast.conf_int()

Step 6: Predictive Visualization in Power BI

Forecast Dashboard Includes

  • Actual historical trend

  • Forecasted next 3 months

  • Upper & lower confidence limits

  • Separate visuals for:

    • Units Sold

    • Sales Value

Business Impact

  • Better inventory planning

  • Reduced stock-out risk

  • Data-backed procurement decisions

Insights & Outcomes

Key Insights

  • Clear seasonal demand patterns

  • Certain months show predictable spikes

  • Forecast confidence helps risk planning

Business Value Delivered

  • Executive-ready reporting

  • Predictive decision support

  • Scalable analytics framework

Skills Demonstrated

  • SQL & ETL pipelines

  • Power BI data modeling

  • DAX & Date intelligence

  • Time-series forecasting

  • Python scripting in Power BI

  • Business storytelling with data





Comments

Post a Comment

Popular posts from this blog

Data Analysis and Visualization with Matplotlib and Seaborn | TOP 10 code snippets for practice

Data visualization is an essential aspect of data analysis. It enables us to better understand the underlying patterns, trends, and insights within a dataset. Two of the most popular Python libraries for data visualization are Matplotlib and Seaborn . Both libraries are highly powerful, and they can be used to create a wide variety of plots to help researchers, analysts, and data scientists present data visually. In this article, we will discuss the basics of both libraries, followed by the top 10 most used code snippets for visualization. We'll also provide links to free resources and documentation to help you dive deeper into these libraries. Matplotlib and Seaborn: A Quick Overview Matplotlib Matplotlib is a low-level plotting library in Python. It allows you to create static, animated, and interactive plots. It provides a lot of flexibility but may require more code to create complex plots compared to Seaborn. Matplotlib is especially useful when you need full control ove...
Post a Comment
Read more

Guide to Performing ETL (Extract, Transform, Load) Using SQL in Oracle and Other Databases

  In the world of data engineering, ETL (Extract, Transform, Load) is a key process that allows you to efficiently extract data from various sources, transform it into a suitable format for analysis, and then load it into a target database or data warehouse. This blog will guide you through the ETL process using SQL, with code examples applicable to Oracle and other relational databases such as MySQL, PostgreSQL, and SQL Server. What is ETL? ETL stands for Extract, Transform, Load , which refers to the three key steps involved in moving data from one system to another, typically from source databases to a data warehouse. Here’s a breakdown: Extract : This step involves retrieving data from source systems such as relational databases, flat files, APIs, or cloud services. Transform : The extracted data often needs to be cleaned, formatted, aggregated, or enriched to meet the specific needs of the destination system or analytics process. Load : Finally, the transformed data is l...
Post a Comment
Read more

Stochastic Gradient Descent: A Cornerstone of Machine Learning and Data Science

In the world of machine learning and data science, optimizing models to make accurate predictions is crucial. One of the most important optimization algorithms used to train models is Stochastic Gradient Descent (SGD) . But what exactly is SGD, and why is it so widely used in machine learning tasks? Let’s dive into this powerful technique and explore its role in building more efficient and accurate models. What is Stochastic Gradient Descent (SGD)? At its core, Stochastic Gradient Descent is an optimization algorithm used to minimize a function, most commonly a loss function in machine learning models. The goal is to adjust the parameters of the model (like weights in a neural network) in order to reduce the error between the model's predictions and the actual outcomes (i.e., the ground truth). The "gradient" in SGD refers to the derivative of the loss function with respect to the parameters. It tells us the direction and rate of change needed to move towards the min...
Post a Comment
Read more