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

• Does the ability exist to make tax on the customer profile mandatory?

  I am reaching out to inquire about the possibility of making the "Customer Tax" field mandatory when creating a new customer in Zoho. We want to ensure that all customers have their tax information recorded to maintain compliance with our internal processes.

• email association with CRM

  Why is it 2024 (almost 2025) and Zoho has not figured out how to integrate email with CRM? It is so inconsistent at associating emails within CRM. I am an attorney. I have clients and work with other attorneys. Attorney John Doe is associated with multiple

• Credit Note for Shipped and Fatoora pushed invoices

  We have shipped a Sales Order and created an Invoice. The Invoice is also pushed to Fatoora Now we need to create a credit note for the invoice When we try it, it says we need to create a Sales Return in the Zoho Books, we have already created a Sales

• FSM - Timesheet entires for Internal Work

  Hi FSM Team, Several of my clients have asked how they can manage internal timesheets within Zoho FSM. Since their technicians already spend most of their day working in FSM, it would be ideal if they could log all working hours directly in the FSM app.

• Add a way of clearing fields values in Flow actions

  It would be great if there was an option to set a field as Null when creating flows. I had an instance today where I just wanted to clear a long integer field in the CRM based on an action in Projects but I had to write a custom function. It would be

• Role Management

  I am creating an analytics dashboard for a company that will be utilized by its various departments such as Finance, Marketing, and HR. My goal is to design the dashboard with separate tabs for each department. Additionally, I plan to implement role-based

• Highlight a candidate who is "off limits"

  Hello: Is there a way to highlight a candidate who is "off limits"?  I would like to have the ability to make certain candidate and / or Client records highlighted in RED or something like that.   This would be used for example when we may have placed a candidate somewhere and we want everyone in our company to quickly and easily see that they are off limits.  The same would apply when we want to put a client or former client off limits so no one recruits out of there. How can this be done? Cheers,

• Announcing new features in Trident for Windows (v.1.37.5.0)

  Hello Community! Trident for Windows just received a major update, with a range of capabilities that strengthen email security and enhance communication. This update focuses on making your mailbox safer and your overall email experience more reliable.

• Early Payment Discount customize Text

  Hi, I’m currently using Zoho Books and am trying to customize the standard “Early Payment Discount” message that appears in the PDF invoice template. I’ve reviewed the documentation here: https://www.zoho.com/books/help/invoice/early-payment-discount.html

• Deprecation of SMS-based multi-factor authentication (MFA) mode

  Overview of SMS-based OTP MFA mode The SMS-based OTP MFA method involves the delivery of a one-time password to a user's mobile phone via SMS. The user receives the OTP on their mobile phone and enters it to sign into their account. SMS-based OTPs offer

• DKIM Now Mandatory - Changes to Zoho Forms Email Policies

  Hello Zoho Forms Users, This post is to inform you about an important update regarding the authentication of all email domains in your Zoho Forms account. This year, we are doubling down on our commitment to deliver a secure, seamless, and empowering

• Call description in notes

  When completing a call, we type in the result of the call in the description. However, that does not show up under the notes history on the contact. We want to be able to see all the calls that have taken place for a contact wihtout having to go into

• Email Address for Contact not Populating

  When I click "Send Mail" from a Contact's page, their email address does not auto populate the "To" field. How do I make this happen?

• New in CRM: Dynamic filters for lookup fields

  Last modified on Oct 28, 2024: This feature was initially available only through Early Access upon request. It is now available to all users across all data centers, except for the IN DC. Users in the IN DC can temporarily request access using this form

• Why hybrid project management might be the best fit for you?

  Project management techniques are designed to equip teams with proven methods for easy and efficient project execution. While management teams may have apprehensions about adopting the hybrid method of project management, we’ve compiled the top reasons

• Allow all Company Users to view all projects, but only owner/admins can change projects

  I was wondering if there was a permission setting I could adjust to allow all our company users to see all projects created. Then, only the project owners and admins with the change permission. Thanks

• Fail to send Email by deluge

  Hi， today I gonna update some email include details in deluge, while this msg pops up and restrict me to save but my rules has run for one year. can you tell me how to use one of our admin account or super admin account to send the email? I tried to update

• Seeking help to be able to search on all custom functions that are defined

  Hello I have a lot of custom functions defined (around 200) and i would like to search some specific strings in the content of those. Is there a way to accomplish that? If not, is there a way to download all existing custom functions in some files locally

• Totals for Sales Tax Report

  On the sales tax report, the column totals aren't shown for any column other than Total Tax. I can't think of a good reason that they shouldn't be included for the other columns, as well. It would help me with my returns, for sure. It seems ludicrous

• Add Bulk Section / Grid Layout Duplicate Feature in Zoho Forms Builder

  Currently in Zoho Forms, users can only duplicate individual fields. There is no option to duplicate an entire section or two-column/grid layout with all internal fields. This becomes inefficient when building structured forms such as Family Details,

• Leistungsdatum in Rechnungen (Zoho Books)

  Hallo, ist es irgendwie möglich den Leistungszeitraum in der Rechnung aufzuführen? Beste Grüße Aleks

• Zoho Trident Windows - Streams Not Visible

  Namaste We’re having an issue with Streams not being visible in Trident (Windows), which is important for us as we share many emails internally. It appears that the feature to show Streams above the Inbox folder, as seen in the default mailbox view, is

• Sales IQ Chat Widget is Only Displaying Last Name

  Can anyone suggest why the widget is only displaying "last name"?! We have the latest version of the wordpress plugin installed. Thanks Thanks!

• Shopify - Item sync from Zoho Inventory

  Hi team, We’ve connected Shopify with Zoho Inventory. We want that when an item is created in Zoho Inventory, it must create a product in Shopify. But currently, new items created in Zoho Inventory are not getting created in Shopify even after clicking

• Bulk upload image option in Zoho Commerce

  I dont know if I am not looking into it properly but is there no option to bulk upload images along with the products? Like after you upload the products, I will have to upload images one by one again? Can someone help me out here? And what should I enter

• Is it possible to setup bin locations WITHOUT mandating batch tracking?

  Hi fellow zoho users, I'm wondering if anyone else has a similar issue to me? I only have some products batch tracked (items with shelf life expiry dates) but I am trying to setup bin locations for my entire inventory so we can do stock counting easier.

• Kill zoho meeting

  Saying the quiet part out loud. Can zoho please just give up on the idea that they can make a meeting platform and just make our workplace licenses cheaper when you remove it so people can switch to zoom or teams. Tired of the excuses, you guys cant make

• Utilisation de Zoho en conformité avec l’article 286 du Code général des impôts (CGI)

  Cher(e) client(e), Conformément à l’article 286 du Code général des impôts (CGI) impose aux entreprises assujetties à la TVA d’utiliser des systèmes de caisse ou de gestion commerciale certifiés lorsqu’elles enregistrent des ventes à des particuliers.

• Unable to Create Task as a Support Administrator

  Hello! I want to ask for help regarding creating tasks within the tickets. I am by default the Support Admin. I should be able to create tasks or activities right? But there's a prompt that I need to contact the Administrator. See photos for reference.

• Introducing Forms in Zoho Sheet

  We hereby bring you the power of ​forms in Zoho Sheet. ​Now, build and create your own customized forms using Zoho Sheet. Be it compiling a questionnaire or rolling out a survey, Zoho Sheet can do it all for you. Forms is an excellent feature that helps you collect information in the simplest of ways and having it in Zoho Sheet takes it a notch higher. Build Simple yet Powerful forms Building forms using Zoho Sheet is fairly simple. The exclusive 'Form' tab lets you create one quickly. Whether you

• Layout one survey question in a time & redirect next Page based on previous response

  I have doubt while, I am scripting survey on the Zoho where I redirecting to next page based on my previous response but didn’t get success on this. Please help me on this and tell me how I layout one survey questions in a time when I submit response

• Zoho Bookings form pre-filled with Zoho Forms in

  Hi, I've got a contact page on my website and I'd like to have the option to book an appointment (redirected to zoho bookings page) after an option is submitted on the contact form. how would I go about doing this? thanks

• Support “Other” Option with Free Text in Dropdown Fields

  Hello Zoho Bookings Team, Greetings, We would like to request an enhancement to the registration form fields in Zoho Bookings, specifically for dropdown fields. Current Limitation: At the moment, dropdown fields do not support an “Other” option that allows

• Detailed Balance Sheet for tax preparer

  I'm using the free edition of Zoho Books. My tax preparer is asking for "detailed" Profit & Loss and Balance Sheet reports which include all the activity and transactions within the various categories. The default reports do not include these details.

• Sending automated messages that appear in the ticket's conversation thread

  Good morning, esteemed Zoho Desk community, warm greetings Today I am here to raise the following problem, seeking a solution that I can implement: I need to implement an automation that allows me to send reminder messages to customers when I am waiting

• Payment Card or Identity form-fill from Vault?

  Hello! I'm working on replacing Bitwarden with Vault and one issue I've run into is that I can't find any option to fill address and payment forms from Payment Card or Identity info that has been saved in Vault. Is there a way to do this? Is it a planned

• Ability to add VAT to Retainer Invoices

  Hello, I've had a telephone conversation a month ago with Dinesh on this topic and my request to allow for the addition of VAT on Retainer Invoices.  It's currently not possible to add VAT to Retainer Invoices and it was mutually agreed that there is absolutely no reason why there shouldn't be, especially as TAX LAW makes VAT mandatory on each invoice in Europe!   So basically, what i'm saying is that if you don't allow us to add VAT to Retainer Invoices, than the whole Retainer Invoices becomes

• Time Log Reminder

  Tracking the time spent on tasks and issues is one of the most important functions of a timesheet. However, users may forget to update the time logs because they have their own goals to achieve. But, time logs must be updated at regular intervals to keep

• [Early-access] Introducing Zoho's CommandCenter - Cross-Zoho business process automation

    Resources to help Webinar recording | Documentation  Feature Restrictions Currently available on early-access only for US data center accounts Features Role CommandCenter as a Service uses signals across Zoho services to propel the movement of records

• Tip #58- Accessibility Controls in Zoho Assist: Learning- 'Insider Insights'

  Learning should be clear and interruption-free for everyone. Timely feedback plays an important role in helping users understand actions as they happen, without breaking their focus. In this post, we’ll explore the final section of Accessibility: Learning.

• Next Page