Identifies possible stale inventory.
Jump to: Overview | Samples | Variables | Alert Functionality | OnDemand Report Filters | Best Practices & Tips | Related Alerts
Overview
Overview
This report is meant to identify inventory items that were not used on Service Calls, Sales Orders, Purchase Orders, etc. in the last Variable Y number of days specified and only show items with an average cost exceeding Variable X. The report sends automatically monthly and can also be run on-demand via your CEO Juice SSRS Reports.
This report should be run manually each month for all items/warehouses so you can write-off (devalue) those dead items over your fiscal year instead of taking a big hit at the end of the year. This alert/SSRS Report looks for usage from the specified warehouses selected only (the alert runs for all warehouses by default).
Usage is determined as any of the following transaction types: Sales Invoice, Sales Credit/Debit Memo, Service Invoice, Service Credit Memo, Purchase Order Receipt/Return, Sales Fulfillment, Inventory Assembly or Inventory Refurbishment.
Run Schedule: Monthly on 24th
Type of Output: Email and On-Demand SSRS Report
* * *
Sample
Sample
Document Map/Worksheet1:
Provides hyperlinks to Item Number in the results listed in Worksheet2/Stale Inventory Results
Stale Inventory Results/Worksheet2:
Shows applied filters at top:
Results:
* * *
Variables
Variables
Variable W: REQUIRED - Enter the Item Category Code(s) to consider (Separate list with commas) OR leave blank for ALL
**BE SURE TO ENTER THE CATEGORY, NOT DESCRIPTION (for example, PRT highlighted below)**
Variable X: REQUIRED - Enter Minimum Cost of items to consider (whole number only - i.e. $5.00 = 5)
Variable Y: REQUIRED - Enter number of days back for no usage
Variable Z: REQUIRED - Select Service Code Category(ies) from multi select pick list
Variable 1: Enter in Warehouse Number(s) for QTY on hand in (separated by comma, leave blank for ALL)
Variable 2: Enter in Warehouse Number(s) to check for Usage History from (separated by comma, leave blank for ALL). ("Usage" means invoiced from this Warehouse Number)
Variable 3: Branch Number(s), based on warehouse, to consider (separated by comma, leave blank for ALL)
Variable 4: Ignore items created in last XX days (Leave blank or set to 0 to ignore)
Variable 5: Enter in Bin Number(s) for QTY on hand in (separated by comma, leave blank for ALL)
Variable 6: Enter in Bin Number(s) to check for Usage History from (separated by comma, leave blank for ALL)
Variable 7: Enter in Bin Number(s) to exclude from report (separated by comma, leave blank for ALL)
Variable 8: Exclude Serialized Items (Yes/No), leave Blank for Yes
* * *
Alert Functionality
Alert Functionality
1. . If you see this error on your report results, you need to review your VariableW and VariableZ (see #1 above):
2. Alert can be cloned so you can set with different variables:
3. Top of report shows all of your filter settings. Emailed report also indicates which subscription it is, and will show 75A for ID75A clone subscription, 75B for ID75B, etc.
* * *
OnDemand Report Filters
OnDemand Report Filters
**Please note, depending on your company's SSRS Report version, you may need to select {DO NOT APPLY FILTER} rather than leave filter blank:
Filters Explained:
**DO NOT set 'Select All' for each filter option please. That will be too much data (especially considering the number of warehouses and bins many companies have built in their EA database.
Set usage dates via:
Items Last Used on Call Before: set date last used on Service Call
Items Last Used on Order Before: set date last used on Sales Order
Items Last Received on PO Before: set date last used on Purchase Order
Exclude Items Created In Last XX Days: Enables you to exclude newly created Item Records
Exclude if Active Contract Equip >X Machines: can look at how many active contract machines you have so as to consider your usage of parts for machines under contract. Designate a threshold for quantity of machines under contact via this filter. By setting this value to zero you are telling the report to list ONLY parts which cannot be consumed by ANY Equipment on an Active Contract (which is determined with a simple set of logic to see whether an Eqp of that Model has ever consumed that Item across a Service Call before). If you want a list of all Stale parts irrespective of how many machines in your Contracted fleet can consume the part you would set this value to 9999 or similar.
Show only Items Where AVGCOST >X: Set minimum average cost to consider
Item Category(s)
Per Item Record here:
Service Code Category(s)
Per Item Record here:
Branches: Perhaps you only want to view usage for one branch. Or if you want to use report to determine if you should move inventory from one branch to another, then consider usage of all your warehouses.
Usage Warehouse(s): enter warehouses to consider for usage
On Hand Bin(s): enter bins to consider for items on hand
Bins to Exclude: enter bins to exclude from consideration
Show Warehouse Details: shows warehouse number & name
Show Bin Details: shows bin number & name
Exclude Serialized Items: exclude any/all serialized items from consideration
CSV Export Mode: select YES if format for CSV/Excel needed. You can then save as an excel file for needed sorting/filtering:
* * *
Best Practices & Tips
Best Practices & Tips
1. This report is not intended to be an overstock report. It is best used to identify dead inventory and to manage end of life inventory.
2. Be careful to pay attention to your warehouse selection when running the report.
3. See this link for an overview of best practices on handling dead inventory.
* * *
Related Alerts
Related Alerts
ID72 - Tech Car Stock, Stale Highlighted
ID335 - Stale Inventory for Overstock Parts Network
ID500 - Items at risk of becoming obsolete
* * *
0 Comments