Being the core of a prosperous venture, it heavily hinges upon inventory management to be handled effectively. And this goal can be easy to achieve using effective management that encompasses three crucial aspects:
- Expenses reduction through optimal resource allocation,
- Fulfilling customers’ requirements through adequate supply chain management,
- and streamlining processes through data analysis and key insights gleaned therefrom.
This article demonstrates pros and implementation processes in SAP Business One by examining two key inventory management strategies – FSA analysis and ABC Analysis.
Comparing FSN and ABC via parallel methods will reveal their differences initially.
Analysis |
FSN |
ABC |
Approach |
Movement dynamics/Categories (Fast-Slow-Non-Moving) |
Value and importance |
Objective |
Reduce holding costs and optimal stock levels and |
Informed decisions and Resource allocations and |
Categories |
Fast Moving (F), Slow Moving (S), Non-Moving (N) |
Category A, Category B, Category C |
Purpose |
Efficiently manage slow-moving items, prevent stockouts and manage replenishment, and address obsolete Inventory |
Control stock of critical items, Optimize storage costs for low-value items, and balance stock levels for moderate-value items, |
Benefits |
Avoid understocking and overstocking and minimize the carrying costs |
Maximize revenue from high-value items and efficiently allocate resources. |
The Query:
-- Define the AvgQuantityCTE to calculate various metrics for FSN Analysis
WITH AvgQuantityCTE AS (
SELECT
T1.[ItemCode],
T1.[ItemName],
T4.[FirmName],
T0.[WhsCode],
T0.ONHAND AS 'In Stock',
T0.[AvgPrice] * T0.[OnHand] AS "Stock Value",
MAX(T5.DocDate) AS 'Last Sales Date',
-- Calculate days since the last sale
DATEDIFF(
day,
CASE WHEN MAX(T5.DocDate) IS NOT NULL THEN MAX(T5.DocDate) ELSE T1.LASTPURDAT END,
GETDATE()
) AS days_since_last_sold,
T1.LASTPURDAT AS 'Last Purchase date',
-- Calculate the average quantity sold in the last 12 months
ROUND(ISNULL((
SELECT SUM(Total.Quantity) / 12
FROM (
-- Calculate the quantity sold
SELECT SUM(T5.Quantity) AS Quantity
FROM INV1 T5
WHERE T5.DocDate >= DATEADD(Month, -12, GETDATE()) AND T5.[ItemCode] = T1.[ItemCode]
UNION ALL
-- Calculate the quantity returned
SELECT -SUM(R5.Quantity) AS Quantity
FROM RIN1 R5
WHERE R5.DocDate >= DATEADD(Month, -12, GETDATE()) AND R5.[ItemCode] = T1.[ItemCode]
) AS Total
), 0), 1) AS 'Avg Quantity Sold Last 12 Months'
FROM OITW T0
INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE
INNER JOIN OITB T2 ON T1.ITMSGRPCOD = T2.ITMSGRPCOD
LEFT JOIN ibt1 t3 ON t3.itemcode = t0.itemcode AND t3.whscode = t0.whscode
INNER JOIN OMRC T4 ON T1.[FirmCode] = T4.[FirmCode]
LEFT JOIN INV1 T5 ON T1.[ItemCode] = T5.[ItemCode]
LEFT JOIN RIN1 R5 ON T1.[ItemCode] = R5.[ItemCode]
GROUP BY
T0.ITEMCODE, T1.ITEMCODE, T1.[ItemName], T4.[FirmName], T0.[WhsCode], T0.ONHAND, T0.[AvgPrice], T1.LASTPURDAT ),
-- Define the SKUStatsCTE to calculate the stock value and percentiles for ABC Analysis
SKUStatsCTE AS (
SELECT
[ItemCode],
[ItemName],
[FirmName],
[WhsCode],
[In Stock],
[Stock Value],
[Last Sales Date],
[days_since_last_sold],
[Last Purchase date],
[Avg Quantity Sold Last 12 Months],
-- Calculate the 75th percentile for FSN categorization
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY [Avg Quantity Sold Last 12 Months]) OVER () AS Top25Percentile,
-- Calculate the 25th percentile for FSN categorization
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY [Avg Quantity Sold Last 12 Months]) OVER () AS Bottom25Percentile
FROM AvgQuantityCTE
),
-- Define the ABCAnalysisCTE to calculate stock value percentage and ABC category
ABCAnalysisCTE AS (
SELECT
[ItemCode],
[ItemName],
[FirmName],
[WhsCode],
[In Stock],
[Stock Value],
[Last Sales Date],
[days_since_last_sold],
[Last Purchase date],
[Avg Quantity Sold Last 12 Months],
[Top25Percentile],
[Bottom25Percentile],
-- Calculate the stock value percentage for ABC categorization
StockValuePercentage,
-- Determine the ABC category based on cumulative percentages
CASE
WHEN CumulativePercentage <= 0.8 THEN 'A'
WHEN CumulativePercentage <= 0.95 THEN 'B'
ELSE 'C'
END AS 'ABC Category'
FROM (
SELECT *,
-- Calculate stock value percentage
[Stock Value] / TotalStockValue AS StockValuePercentage,
-- Calculate cumulative stock value percentage
SUM([Stock Value] / TotalStockValue) OVER (ORDER BY [Stock Value] DESC) AS CumulativePercentage
FROM (
SELECT *,
-- Calculate total stock value
SUM([Stock Value]) OVER () AS TotalStockValue
FROM SKUStatsCTE
) AS Subquery
) AS ABCSubquery
-- Final query to get ABC Analysis results along with inventory movement type
SELECT
[ItemCode],
[ItemName],
[FirmName],
[WhsCode],
[In Stock],
[Stock Value],
[Last Sales Date],
[days_since_last_sold],
[Last Purchase date],
[Avg Quantity Sold Last 12 Months],
[ABC Category],
-- Determine inventory movement type based on percentiles
CASE
WHEN [Avg Quantity Sold Last 12 Months] = 0 THEN 'Non-Moving'
WHEN [Avg Quantity Sold Last 12 Months] >= [Top25Percentile] THEN 'Fast Moving'
ELSE 'Slow Moving' END AS 'Inventory Movement Type'
FROM ABCAnalysisCTE
WHERE
[In Stock] > 0
ORDER BY [ABC Category] DESC, [Stock Value] DESC
Query Understanding:
Part 1 – Defining the Metrics.
This part will help us understand the calculations of various metrics and how our products are moving.
- Collection of information about each of our inventory items, such as item code, name, it’s location where the item is stored, in our case it’s warehouse code.
- Determining how many of each item we currently have in stock and how much these items are worth.
- Calculation of how quickly we are selling the items by calculating when our last item was sold.
- We are also interested in when we last purchased that item.
- Then finally to get the sense of how well we are selling our items, we calculate the average quantity sold in the last 12 months for each item.
.
Part 2 – Statistical Model
After defining the initial and basic information about our inventory, we want to calculate/define stats that will help us classify our inventory items.
In this section, we are trying to define the 75
th and 25
th Percentile for the above-calculated average quantity sold in the last 12 months. This section will categorize our items based on their performance into three groups.
- The top 25% will be classified as Fast-moving,
- The bottom 25% will be classified as Non-moving.
- And middle ones are our Slow-moving items.
Part 3 – ABC Analysis Categories
In this part, we are going to assign categories (A, B, C) to each of our inventory items based on their value and importance.
- Firstly, we calculate a percentage of how much each of our items contributes to the overall value of the stock.
- Then we use that percentage to determine whether an item falls into categories A, B, or C.
- “A” items are the most valuable but are normally sold in smaller quantities.
- “B” items have moderate values and may have moderate quantities sold as well.
- “C” items are less valuable but sold in larger quantities.
By producing different metrics for our analysis, this SQL query empowers us to make informed decisions based on our data results and compare those results with Industry standards. This can help us identify critical items such as obsolete or Non-Moving inventory efficiently.
Conclusion:
To keep our inventory at optimal levels, FSN and ABC analysis are vital tools for business management. FSN Analysis helps us to get a better understanding of the classification of items based on their movement dynamics and on the other hand, ABC analysis defines prioritizes based on the value of the items and their importance.
By adopting these tools and methodologies using this SQL query in SAP Business One, we can modernize our operations by reducing inventory carrying costs. We can achieve balanced growth through efficient Inventory controls.
