Proposal For Inventory Aging Report

Proposal Summary 

Bloom & Grow would like to create new report to retrieve from item receipts with getting details from the Inventory Item that are still available on date recall report. They would also like to know if the business follows the FIFO method, the item from whom purchased is left in the warehouse. In case an item have an multiple item receipts from different vendors on the selected date, the sum of quantity will be displayed in respective line.    

Requirement 

New report ‘Inventory Aging report’ to retrieve data from item receipts and with getting details from Inventory Item that are still available on the date recall report. The report will be showing the details of the Inventory available from which vendor it has been procured and is left in the warehouse. There will be multiple lines for same item record, which will be differentiated according to the vendor from whom it has been procured.  

Deliverables

The requirement can be achieved by creating a new report by adding a custom suitelet page using saved search. And this search will  retrieve data from the Item receipts with getting details from the Inventory items that are still available on the date recall report. In case a vendor have different item receipts within the date range, the total quantity will be displayed as a hyperlink to the item receipts saved search.  

The navigation of the custom report can be added from the classic center. The user can navigate to Reports à Custom Reportsà Inventory Aging report. Then upon clicking the “Inventory Aging Report”, the user will be redirected to a report page.  

The report will first display a detailed report based on the default filters, such as subsidiary  as “All”, and the As at date filter as today, then show the details like corresponding vendor, quantity received, sold and all the inventory adjustments and the return authorisations.   

The transactions from the first day of the year up to the as at date given in the criteria filed will be considered as period of the report.   

Sample 

The following fields will be displayed on the header section of the report. 

Criteria
#	Field	Description	Details
1	Subsidiary	Select subsidiary for report	Name of Subsidiary
2	As at Date	Show date as at	Select the as at date
3	Page Index	Select the page for report	Page Number
4	Vendor	Select the vendor for report	Name of the Vendor
Report Details
#	Field	Description	Details
1	Subsidiary Name	Show Subsidiary name that selected for report	Name of the subsidiary
2	Vendor	Show vendor name that select for report	Name of the Vendor
3	As at Date	Show As at Date	Based on date selected in the criteria.
Refer to the table provided below for the columns included in the detailed “Inventory Aging” report.
Report Columns
#	Field	Description	Fields in NetSuite
1	Item	Item	Item from Item receipt
2	Description	Item Description	Description of the item from the item receipt line.
3	Vendor	Vendor Name	Vendor Name from item receipt line
4	Quantity Received	Quantity Received in Item Receipt	Quantity from Item receipt
5	Adj & Returns	Adjustments & Item Receipt from Return Authorizations	Quantity from Positive Inventory Adjustments, Bill and Item Receipt from Return Authorizations
6	Quantity Sold	Quantity Sold from Transactions	Quantity from Item Fulfilment, Negative Inventory Adjustment, Invoice/ Cash sale and Item fulfilment from Vendor Returns
7	Quantity Remaining	Quantity Remaining	Quantity remaining for each vendor. The quantity will reduced as FIFO method from each transaction as per the date of the transactions.
8	Age	Compare date of item receipt and report date	Calculate date different from item receipt  with report date
9	30 days	Compare date of item receipt and report date >= 30 days	Calculate date different from item receipt  with report date >= 30 days
10	60 days	Compare date of item receipt and report date >= 60 days	Calculate date different from item receipt  with report date >= 60 days
11	90 days	Compare date of item receipt and report date >= 90 days	Calculate date different from item receipt  with report date >= 90 days
12	180 days	Compare date of item receipt and report date >= 180 days	Calculate date different from item receipt  with report date >= 180 days
13	360 days	Compare date of item receipt and report date >= 360 days	Calculate date different from item receipt  with report date >= 36 0 days

• The Quantity Received column will include the stock data that came from the Item receipts created from the Purchase Order 
• The Adjustments and Return Authorization column will include the Positive Inventory adjustments of the corresponding item and return authorizations, Bill credit and Bill records too.  
• The quantity sold column will include the stock data from the Item fulfilments created from the sales orders, negative Inventory adjustments, Item fulfilments created from the vendor return authorizations, standalone cash sale, standalone Invoices. 
• The “Quantity Received” column will indicate the quantity and will appear as a clickable link to view the item receipts of the respective vendors, items and selected vendors in the header section.  

• The “Adj & Return” column will also display a numerical value along with the hyperlink to  the search to inventory adjustments and return authorisations of corresponding item.  
• The “Quantity Sold” column will also include a saved search for the transactions that resulted in the item’s outflow. 
• However, when a vendor filter is applied on the page, columns like “Adj & Return ” and “Quantity Sold” will not be visible because displaying this information would not make sense with the vendor filter applied to the transactions in the report page.  

Assumptions 

• Each page of the report can contain up to 1000 lines of data. If the report exceeds this limit, the additional data will appear on the next page within the same suite let page. The top section of the report will display the page index accordingly. 
• The BGG will utilize the FIFO (First-In, First-Out) method to handle the flow of inventory both in and out. 
• The positive Inventory adjustment is the quantity is added to the stock in the warehouse through Inventory adjustment record. 
• The negative Inventory adjustment is the quantity that is removed from the stock in warehouse through Inventory adjustment record.  

Risks 

• The  standard inventory aging reports are not currently available and customization of reports may not fully address the specific needs of the business. This is why a customized solution is being considered to meet the inventory reporting requirements. Thus the results of the report can be confirmed in the development phase. 
• Any additional requirements that arise during the development phase will be treated as change requests. 

• We have considered the highest number of transactions that could potentially impact the inventory within the business. If there is anything that we may have overlooked or missed, please feel free to suggest it.