Product Inventory Analysis Report overview

Product > Reports > IC Reports > ICRII

Function acronym: ICRII

Use this report to identify non-productive stock. The report is sorted warehouse products by sales amount and the current stock level for each product is compared to the total inventory level. Use the report to compare the percent of total sales that are generated by a percent of total inventory. For example, 60% of your total sales may be generated by 20% of your inventory.

Even with proper inventory management, some inventory decisions result in dead stock or slow moving stock. The process of identifying your dead stock or slow moving stock is the first step in improving your inventory's productivity. Decisions must be made to remove, discontinue, discount for promotional sales, or keep the stock.

The products that are included depend on the ranges and options you choose. The Product Setup file is used to validate the products. Inactive and labor products are not included. Your Product Warehouse Product Setup records are reviewed, and products with a direct ship status are not included in calculations. However, order as needed products are included in your report if On Hand, Received, or Unavailable quantities are present in the Product Warehouse Product Setup-Costs record. If the balances are all zero, the order as needed product is not included on the report.

Usage is a key factor in the analysis. The usage recorded for periods 2 through 13 are used to arrive at Units Sold Past Year. If a product has less than 12 months of usage history, results may not accurately reflect the product's usage.

Product Administration Month End Processing Report should be performed before generating this report to ensure all usage data in Product Warehouse Product Setup is correct. The Average Cost per Unit is the total of Product Warehouse Product Setup-Ordering usage unit cost divided by the number of periods used. This is a transparent cost which is calculated when Product Administration Month End Processing Report is generated, based on the Post to GL By option setting in SA Administrator Options-Products-Costs.

Average Cost Per Unit=ICSWU Total Unit Cost / # of Periods Used

If the Product Warehouse Product Setup-Ordering unit cost is zero, the Post to GL By or Post to SM By cost in SA Administrator Options-Products-Costs is used to determine the unit cost for the calculation.

If a product's Product Setup record indicates it is a Special Costing Product, Product Administration Month End Processing Report uses the Special Costing Unit to obtain a correct Product Warehouse Product Setup-Ordering usage unit cost.

Multiplying the Average Cost per Unit by the Units Sold Past Year produces the Extension.

Units Sold Past Year * Avg Cost Per Unit = Extension

The Percent of Sales calculation is the extension divided by the sum of all extension amounts times 100. The Percent of Sales and Percent of Total Inventory are running totals. To determine the impact of a particular item, subtract the figures for the line from the preceding line.

(Extension/total of all Extension Amounts x 100 = % of Sales

The Units Now In Stock include the Product Warehouse Product Setup quantity on hand plus quantity unavailable plus quantity received. The Average Cost per Unit is multiplied by this sum to produce the Units x Unit Cost.

[(Units Now in Stock * Average Cost Per Unit) / Sum of (Units Now in Stock * Average Cost Per Unit)] * 100

Generate this report consistently on a quarterly or biannual basis to identify your non-productive stock.

Note: If the product is a special cost item set up in Product Setup, the cost is converted to a stocking unit factor before the cost is multiplied by the quantity.

Line Hits

This report also prints the total line hits for each product. Line hits reflect the number of times the product occurred on a sales order, warehouse transfer, or lost business transaction. Line hits are used to rank products in Product Administrator Classification Report. The product’s rank, both within the company and warehouse, are also reflected on the report. A product’s hits and ranking within the company or warehouse helps identify those that are slow moving.

Surplus

Surplus stock is another aspect of inventory management that must be managed carefully. This excess inventory could be made available to other locations or removed from inventory by a vendor return or price reduction. In the system, the surplus amount is calculated slightly differently for products using the Min/Max order method than for other order methods.

If the Order Method in Product Warehouse Product Setup is Min/Max, surplus stock is calculated as:

Surplus = On Hand – Reserved – Committed – Backordered – Demand – Received – Line Point

If the Order Method is EOQ, Class, Qty Break, Blanket, or Human, surplus stock is calculated as:

Surplus = On Hand – Reserved – Committed – Backordered – Demand + Received – Line Point + Order Quantity