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.

1. Query Table - Stock In Hand
2. Query Table - Inventory Age Tier
3. 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,  2,  3, 4, 6 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,  2,  3,  4 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,  2,  3,  4,   6 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,  2,  3,  4,  6 ) 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.

1. Create a new pivot over the Inventory Age Tier query table.
2. 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.
  
  
  
  
3. The Click Here to Generate Pivot button allows you to generate the report.
4. Hide the totals by clicking Show/Hide for all Totals.
   
   
   
   
5. Click Sort > Custom Sort for Age Tier column to arrange the age tier.
   
   
   
   
6. Now apply the required Theme. 
   
   
   
   
7. Your final Inventory aging report is ready.
   
   
   

You can explore the solution by copying the workspace from the below link.

https://analytics.zoho.com/workspace/19601000018963001

• Topic Participants

• Janani Thakshayani

  

• Wayne Sobey | Amacron Golf & Turf

• Vaijayanthi V

• Sticky Posts

• Tip of the Week - Spot Risky Sales with Conditional Formatting

  In Zoho Analytics, small tweaks can lead to big insights. One such feature is Conditional formatting based on other columns, your key to instantly spotting where sales success is overshadowed by product returns. Our tip this week shows you how to apply

• Recent Topics

• MRP or Manufacturing Module for Zoho

  We have been searching for options for a production planning or MRP that will integrate with Zoho.   Zoho Creator is pushed as a platform that can have an MRP built from scratch but we would like to find more of an out of the box solution and modify it to fit our needs.  Are there any recommendations? Would Zoho consider creating a custom solution in Creator to support this need?

• encountering an error when attempting to associate an email with a Deal using the Zoho CRM extension in Zoho Mail.

  When I click "Yes, associate," the system displays an "Oops!! Something went wrong" error message. I have attached a screenshot of the issue for reference.

• Can 1 Zoho CRM instance sync with 2 Zoho Marketing Automation instances?

  Can 1 Zoho CRM instance sync with 2 Zoho Marketing Automation instances?

• Can I add Conditional merge tags on my Templates?

  Hi I was wondering if I can use Conditional Mail Merge tags inside my Email templates/Quotes etc within the CRM? In spanish and in our business we use gender and academic degree salutations , ie: Dr., Dra., Sr., Srta., so the beginning of an email / letter

• Zoho mail account ownership transfer

  We recently took over another company and have assumed responsibility for its Zoho account, including Zoho Mail and all related services. We would like to formally transfer ownership of this account to our organization. Could you please outline the complete

• Email Authentication is Failing

  I'm trying to setup gitlab with email authentication. I used the following configs picked up from: https://docs.gitlab.com/omnibus/settings/smtp/ gitlab_rails['smtp_enable'] = true gitlab_rails['smtp_address'] = "smtp.zoho.com" gitlab_rails['smtp_port']

• DMARC reports for mail I didn't send: how to deal with?

  I know the enthusiastic amateur's bare minimum about e-mail; am able to set up a Thunderbird account and know the basic acronyms. I have a Zoho Mail account connected to my domain, and have set up SPF, DMARC and DKIM successfully according to Zoho's instruction

• ms

  Email set up for communication

• WorkDrive issues with Windows Explorer Not Responding

  We are using WorkDrive to collaborate on editing video content. We have a lot of files and quite a few are a few gigs. Recently anytime I try and work with the files Explorer freezes for a couple minutes whether it's dragging the files into Premiere or

• Fix the speed

  It takes ages to load on every step even though my dataset is quite small.

• Capture Bank Charges for Invoice Payments

  We've added the Bank Charges field in the "Add Payments" page both in Zoho Invoice & Zoho Books. So all you need to do is to enter the bank charges when you record a payment for the invoices. This bank charge will be included to the amount paid for that invoice. Steps to add the bank charges while you record the payment:       1. Click the "Invoices" sub-tab under the "Money-In" tab.       2. Click the 'add payment' link for the invoice that you wish to record the payment for.       3. On the add

• Conditional Layouts On Multi Select Field

  How we can use Conditional Layouts On Multi Select Field field? Please help.

• Fill Mail Merge document up with subform fields of an Inventory module record being in the Related List

  Hi, I try to insert subform fields from an inventory module record being on the Related List of another inventory module record into a Mail Merge template without success. For example: we use ratecards in licensing and this ratecard items are available

• How can we add products using a Wizard?

  We want to create a Wizard to add products. Why is there no possibility to use the products module when creating a wizard?

• Image field in custom module

  Hi guy, Is there any hope of adding a custom image field in the custom module? We created a custom module to keep track of assets, and it would be helpful if we could attach an image to the record. Thanks Rudy

• Deluge Function to Update Custom Field

  I'm trying to get a Deluge function (which will run as part of a Schedule in Desk) that retrieves all tickets with the status "Recurring" and updates the custom field checkbox "cf_recurring" to "true". Here's what I have, which doesn't work: searchValue

• "View ticket" link is broken

  The "View ticket" link in our Zoho ticketing system confirmation emails is broken (please see attached). Impacts ability to update/review details, and, refresh recollection at a later date. Any help would be much appreciated.

• import data from Apollo.ai into zoho crm via zoho flow

  I might be asking this question in the wrong forum. We use Apollo.ai to find potential new leads for our business, there are around 10000 leads that we have initially found. We have an Apollo.ai account but just to do searches, we dont use it as a crm.

• Woocommerce Line Item Information

  I'd like to add each line item from a Woocommerce order to a zoho creator form record. The line items are found within the line items array, but I'm not sure how to get each item out of the array? Any help would be much appreciated.

• Synching changes to Stripe when changes are made in Zoho Billing

  We have a situation where we have merged customers in Zoho BIlling and then found out later that the payment in Stripe was not updated and still associated with the old customer record. The card gets updated and billed, but that payment is still associated

• How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

  How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

• Disable Smart Filters By Default

  The smart filters "feature" is causing confusion for Zoho Mail users. New emails should be delivered to the inbox unless they have specifically opted to redirect them somewhere else. People don't understand that new emails might be waiting in a random

• ME SALE ESTE ERROR: No fue posible enviar el mensaje;Motivo:554 5.1.8 Email Outgoing Blocked

  Ayuda!! Me sale este error al intentar enviar mensajes desde mi correo electronico de Zoho! Tampoco recibo correos pues cuando me envia rebotan. Ayuda, Me urge enviar unos correo importantes!! Quedo atenta MAGDA HERNANDEZ +5731120888408

• Client Script | Update - Client Script Support For Custom Buttons

  Hello everyone! We are excited to announce one of the most requested features - Client Script support for Custom Buttons. This enhancement lets you run custom logic on button actions, giving you greater flexibility and control over your user interactions.

• Zoho Mail not working

  Zoho Mail not working

• Product line search in quotes

  Is there a way to change the search setting when you add a product line to a quote. We have created a field in products called 'Part Number' and I would like when I'm adding a product line to the quote that I can search on that field. At the moment it only searches on product description. In products I can search on the 'Part Number' field Any help welcome.

• CRM x WorkDrive: File storage for new CRM signups is now powered by WorkDrive

  Availability Editions: All DCs: All Release plan: Released for new signups in all DCs. It will be enabled for existing users in a phased manner in the upcoming months. Help documentation: Documents in Zoho CRM Manage folders in Documents tab Manage files

• Queries on Project-Based Inventory Consumption and Proforma Invoice in Zoho ERP

  We would appreciate your clarification on how Zoho ERP plans to handle the following: Project-based inventory consumption without itemized sales orders Accurate project cost tracking along with inventory reduction Proforma Invoice usage We look forward

• I can't add a new customer in Zoho invoice? Anyone had this issue before?

  Ive been using Zoho invoice for over 6 years. Today I wanted to add a new customer to send an invoice to but it doesn't save when I try to access it from the pulldown menu when you go to send a new invoice.

• Spreadsheet View click & focus issue in Arabic (RTL) localization

  Hello Zoho Support Team, I am facing an issue in Zoho Creator Spreadsheet View when using Arabic localization (RTL). Scenario: My app supports English (LTR) and Arabic (RTL). I created a Spreadsheet View for a form. In English, everything works correctly.

• Customer address in Zoho Bookings

  Hello,  Is it possible to add customer address information to the Zoho bookings appointment screen? Or have it pull that information automatically from the CRM? We are wanting to use this as a field management software but it is difficult to pull the address from multiple sources when it would be ideal to have a clickable address on the appointment screen that opens up the user's maps.  It would also be advantageous for the "list view" to show appointment times instead of just duration and booking

• Ghost email notification on a form

  Hello, We have recently encountered an error where I can not see a email notification set up for a form which I am currently the owner, although neither the form nor the notification were created by me. However, neither can the Super Admin access the

• bulk edit records and run internal logic

  hi there is few logics in manner "it this than that" logics work well when i edit entry openning it one by one (via workflow "on add/edit - on success" , for custom field "on update/on user input") but when i try bulk edit records - logic does not work.  how can i turn on logic to work as programmed - for mass editing records via bulk edit?

• Dheeraj Sudan and Meenu Hinduja-How do I customize Zoho apps to suit my needs?

  Hi Everyone, I'm Meenu Hinduja and my husband Dheeraj Sudan, run a business. I’m looking to tweak a few things to fit my needs, and I’d love to hear what customizations others have done. Any tips or examples would be super helpful! Regards Dheeraj Sudan

• Bulk bank rule creatioin

  Hi team, I am exploring Option to create a multiple bank rule. Could please suggest the option to implement this?

• Limitations on editing a message in Cliq

  Hi I've checked the documentations and there's no mention of how many times a message can be edited. When trying with code, I get various numbers such as ~1000 edits or so. Please mention if there's a limit on how many times one can change a message via

• Orphan email alias blocking user creation – backend cleanup required

  Hello Zoho Mail Support, I´m unable to assign or create the address xx@iezzimatica.ar in my organization. Current situation: Alias cannot be assigned to any user (system says it is already in use) New user with this address cannot be created Address does

• email address autocomplete

  Is there a way to eliminate certain addresses from showing up in auto complete when entering an address? Many old and unused addresses currently show up, many of which I would like to get rid of. Thanks

• How to use MAIL without Dashboard?

  Whenever I open Mail, it opens Dashboard. This makes Mail area very small and also I cannot manage Folders (like delete/rename) etc. I want to know if there is any way to open only Mail apps and not the Dashboard.

• Introducing Radio Buttons and Numeric Range Sliders in Zoho CRM

  Release update: Currently out for CN, JP, AU and CA DCs (Free and standard editions). For other DCs, this will be released by mid-March. Hello everyone, We are pleased to share with you that Zoho CRM's Layout Editor now includes two new field formats—

• Next Page