Report of the Week - Warehouse wise Inventory Aging Report over Finance Data

Slow moving stocks may lead to issues like expiry (in case of food items), obsolescence, and high holding costs. That's why you need the Inventory Aging Report. It helps you maintain a healthy business by tracking how quickly your inventory moves. It identifies the slow and fast moving inventory, and also lets you plan your warehouse capacity.

This report is done over a SQL query based on Advanced Analytics for Zoho Finance data. You can modify the query to suit your data structure.

Follow the below steps to do this.

Query Table - Stock In Hand
Query Table - Inventory Age Tier
Pivot View - Inventory Aging Report

Query Table - Stock In Hand

This tracks the stock in hand by joining different tables.

SELECT

/*sum of Quantity On Hand from below query*/

T1.*

SUM (T1. Quantity ) OVER( PARTITION BY T1. "Product ID" , T1. "Warehouse ID" ) AS 'Quantity On Hand',

/*calculate cumulative sum of items count in reverse order until it's equal or more that Quantity On Hand*/

sum(if ( T1 . "In/Out" = 'In' , T1 . Quantity , 0 )) OVER(PARTITION BY T1 . "Product ID" , T1. "Warehouse ID" ORDER BY T1. "Transaction Date" DESC ) "Reverse Cumulative Total"

/*stock in hand from Stock In Flow*/

FROM (SELECT

'In' "In/Out",

"Transaction Date",

"Warehouse ID",

"Product ID",

sum ( "Quantity In") "Quantity",

"Price (BCY)" "Purchase Price"

FROM "Stock In Flow Table"

WHERE "Stock In Flow Table" . "EntityType" != 'transfer_order'

GROUP BY 1,

UNION ALL

/*stock in hand from Stock Out Flow*/

SELECT

'Out',

"Transaction Date",

"Warehouse ID",

"Product ID",

-1 * sum ( "Quantity Out" ),

null

FROM "Stock Out Flow Table"

WHERE "Stock Out Flow Table" . "EntityType" != 'transfer_order'

GROUP BY 1,

UNION ALL

/*stock in hand from Transfer Order*/

SELECT

' In',

"Date",

"Warehouse ID",

"Product ID",

sum(if( "Transfer Order". "Status" = 'transferred' , "Transferred Quantity" , 0 )),

if ( "Transfer Order" . "Status" = 'transferred' , "Cost Price" , null )

FROM "Transfer Order"

LEFT JOIN "Transfer Order Items" ON "Transfer Order" . "Transfer Order ID" = "Transfer Order Items" . "Transfer Order ID"

WHERE

"Status" not in ( 'draft' )

GROUP BY 1,

UNION ALL

/*stock in hand from Transfer Order Item*/

SELECT

'Out',

"Date",

"Warehouse ID",

"Product ID",

sum ( "Transferred Quantity"),

"Cost Price"

FROM "Transfer Order"

LEFT JOIN "Transfer Order Items" ON "Transfer Order" . "Transfer Order ID" = "Transfer Order Items". "Transfer Order ID"

WHERE "Status" not in ( 'draft' )

GROUP BY 1,

) AS T1

Query Table - Inventory Age Tier

The below query tracks the age of each inventory and bucket them into the following groups.

0-5 days old
6-10 days old
11-15 days old
16-20 days old
older than 20 days

SELECT

"In/Out",

"Product ID",

"Quantity",

"Quantity On Hand",

"Reverse Cumulative Total",

"Transaction Date",

"Warehouse ID",

"Purchase Price",

/*identify the Transaction Date by comparing Reverse Cumulative Total and Quantity On Hand */

if(min(if( "Reverse Cumulative Total" - "Quantity On Hand" >= 0 , "Reverse Cumulative Total" -"Quantity On Hand " , 99999999999 )) OVER ( PARTITION BY "Product ID" , "Warehouse ID" ORDER BY "Transaction Date" DESC ) != 99999999999 , ( "Quantity On Hand" -("Reverse Cumulative Total" -"Quantity" )), "Quantity" ) "Final Qty" ,

if(min(if(

"Reverse Cumulative Total" - "Quantity On Hand" >= 0 , "Reverse Cumulative Total" -"Quantity On Hand" , 99999999999 )) OVER ( PARTITION BY

"Product ID" , "Warehouse ID" ORDER BY

"Transaction Date"

DESC ) != 99999999999 , ( "Quantity On Hand" -("Reverse Cumulative Total" -"Quantity" )) * "Purchase Price" , "Quantity" * "Purchase Price" ) 'Inventory valuation',

/*classify into different age tier*/

if ( Datediff ( current_date(), "Transaction Date" ) >= 0

AND

Datediff ( current_date() , "Transaction Date" ) <= 5 , '0-5 days', if( Datediff ( current_date() , "Transaction Date" ) >= 6

AND

Datediff ( current_date() , "Transaction Date" ) <= 10 , ' 6-10 days', if ( Datediff ( current_date() , "Transaction Date" ) >= 11

AND

Datediff ( current_date() , "Transaction Date" ) <= 15 , ' 11- 15 days', if( Datediff ( current_date() , "Transaction Date" ) >= 16

AND

Datediff (current_date(), "Transaction Date" ) <= 21 , '16-20 days', 'greater than 20 days')))) 'Age tier'

FROM "Stock In Hand"

WHERE "In/Out" = 'In'

AND

"Quantity On Hand" - ("Reverse Cumulative Total" -"Quantity" ) > 0

Join Data using Lookup Column

Join the Inventory Aging Query table with the Warehouse and Items tables using the following lookup columns.

Warehouse ID from Inventory Age Tier query table - Warehouse ID from Warehouse table
Items ID from Inventory Age Tier query table - Items ID from Items table

Pivot View - Inventory Aging Report

Now you can create the inventory aging pivot over the Inventory Age Tier query table.

Follow the below steps to do so.

Create a new pivot over the Inventory Age Tier query table.
Drop the columns as follows.

Column - Age tier from Inventory Age Tier query table with Actual.
Row - Warehouse Name from Warehouse table and Item Name from Items table with Actual.
Data - Purchased Quantity from Inventory Age Tier query table with Sum.