Understanding the Relationship Between Inventory Levels and Sales Using SQL-Correlation Coefficient

[shahmed]

What is the Correlation Coefficient?

It is a statistical measure that can be used to measure the strength and direction of a linear relationship that exists between two variables which include the inventory levels and the sales quantities we are interested in. The correlation coefficient is a measure that ranges between -1 and +1:

1 indicates a perfect positive linear relationship.

-1 indicates a perfect negative linear relationship.

0 Indicates no linear relationship.

When there's more stock, more products are sold. If on the other hand, there’s more stock, sales decrease.

Important why Question? Why do we Calculate the Correlation Coefficient?

Optimizing Inventory level:

To maximize the benefits from sales, companies have to consider disposing of their inventories wisely since correlation helps in determining the validity of inventory volumes. Findings about this are listed below: Optimal stock quantities: It can assist in determining optimal stock quantities thereby minimizing holding costs

Demand Forecasting:

Forecasting demand is useful for retailers since they rely on stocks as an indicator of impending sales; however, trying to forecast demand has been proven as always being a complex subject. For this forecasting to be possible, retailers will first need to segment their product categories.

Resource Allocation:

It would be better if businesses managed their resources wisely so that they always had what was most needed by their customers among other considerations.

Strategic Planning:

Using correlation analysis can help to make strategic decisions like when there are going to be promotions in terms of pricing, advertising and other adjustments in the supply chain.

Code Magic

--Setting up the Environment
DECLARE @Whse nvarchar(10)
DECLARE @FromDate datetime
DECLARE @ToDate datetime

SET @Whse = '100' --try to restrict to specific WH instead of overall for better results.
SET @ToDate = GETDATE()
SET @FromDate = DATEADD(MONTH, -12, @ToDate)-- For the Last 12 months

--Gathering Inventory and Sales Data

;WITH InventoryData AS (
    SELECT
        T0.ITEMCODE,
        T5.DocDate AS TransactionDate,
        T0.ONHAND AS InventoryLevel
    FROM OITW T0
    INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE
    LEFT JOIN INV1 T5 ON T1.[ItemCode] = T5.[ItemCode]  AND T5.[WhsCode] = @Whse
    WHERE  T0.[WhsCode] = @Whse AND T5.DocDate >= @FromDate AND T5.DocDate <= @ToDate
),

--Sales Data CTE

SalesData AS (
    SELECT
        T0.ITEMCODE,
        T5.DocDate AS TransactionDate,
        SUM(T5.Quantity) AS SalesQuantity
    FROM OITW T0
    INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE
    LEFT JOIN INV1 T5 ON T1.[ItemCode] = T5.[ItemCode] AND T5.[WhsCode] = @Whse 
    WHERE  T0.[WhsCode] = @Whse AND T5.DocDate >= @FromDate AND T5.DocDate <= @ToDate
    GROUP BY T0.ITEMCODE, T5.DocDate
)

--Combining Inventory and Sales Data

SELECT
    I.ITEMCODE,
    I.TransactionDate,
    I.InventoryLevel,
    S.SalesQuantity
INTO #CorrelationData
FROM InventoryData I
LEFT JOIN SalesData S ON I.ITEMCODE = S.ITEMCODE AND I.TransactionDate = S.TransactionDate

-- Calculate correlation  Pearson's R

DECLARE @N float, @SumX float, @SumY float, @SumXY float, @SumXSquare float, @SumYSquare float
SELECT
    @N = COUNT(*) * 1.0,
    @SumX = SUM(InventoryLevel),
    @SumY = SUM(SalesQuantity),
    @SumXY = SUM(InventoryLevel * SalesQuantity),
    @SumXSquare = SUM(InventoryLevel * InventoryLevel),
    @SumYSquare = SUM(SalesQuantity * SalesQuantity)
FROM #CorrelationData

-- Calculate the Pearson's correlation coefficient

DECLARE @Correlation float
SELECT @Correlation = (@N * @SumXY - @SumX * @SumY) / SQRT((@N * @SumXSquare - @SumX * @SumX) * (@N * @SumYSquare - @SumY * @SumY))

-- Display the correlation

SELECT @Correlation AS Correlation

Conclusion

Inventory levels correlate with sales, this can help a lot in managing inventory better. The use of SQL can be so useful when it comes to processing and analyzing big datasets quickly. Not only does the query we just discussed show how data analysis can be done with SQL but it also suggests a way businesses can change their stock levels based on how much they have sold before.

For readers who want to delve deeper into the Pearson correlation coefficient and its mathematical foundations, I recommend visiting https://en.wikipedia.org/wiki/Pearson_correlation_coefficient. This comprehensive resource provides detailed explanations, historical context, and various applications of the Pearson correlation coefficient, making it an excellent supplementary read to enhance your understanding of this statistical measure.