Product Surplus Stock Report overview

Short-term Surplus

Short-term surplus is stock that is available to sell in excess of line point plus order quantity. This formula is used to calculate short-term surplus:

[On Hand – Reserved – Committed – Backordered – Demand + Received – (Line Point + Order Quantity)] 

If you are using the Min/Max order method set up in Product Warehouse Products Setup-Ordering, the order quantity is not included in this formula.

The result of this calculation is then multiplied by the cost you have selected in SA Admin Options-Products-Costs.

You can run the Product Surplus Stock Report to print only short-term stock, or both short- and long-term stock. Generally, the report is most beneficial for reviewing long-term stock, as you can also view short-term surplus amounts in the Purchase Entry Buyers Control Center when processing PO RRARs.

Long-term surplus

Long-term surplus is defined when you run the Product Surplus Stock Report. Specify the number of months to be considered as long term (usually 6-13 months, but up to 25 months can be specified). To be considered long-term surplus, the product’s net available must exceed the number of months supply defined in the option as long term.

You can also view long-term surplus for products in Product Availability Inquiry. This value is calculated based on the number of months considered long term defined in the Long Term Surplus field in Product Replenishment Setup-Ranking.

Other surplus stock

Products with certain status types qualify for a closer look when reviewing surplus. These include order-as-needed, nonstock, do not reorder, and frozen permanently. Options exist on the Product Surplus Stock Report to include these classes.

The long-term surplus point is calculated differently for these classes:

• Frozen Permanently. Line Point + Order Quantity. These are indicated on the report using the long-term surplus type code of "LTF."
• Order-as-needed, Do Not Reorder products. The long-term surplus point equals zero and any available quantity is considered long-term surplus. These are indicated using the surplus type code of "LTO."
• Nonstock products. The long-term surplus point equals zero and any available quantity is considered long-term surplus. These are indicated using the surplus type code of "NS."

New products are not considered for long-term surplus. Normal long-term surplus is indicated on the report using a long-term surplus type of "LTU" if the long-term surplus point calculated using the user-defined number of months is applied. Otherwise, the surplus type code of "LTP" (where the surplus point is Line Point + Order Quantity) is shown.

If the product's Acquired Date in Product Warehouse Product Setup-Frozen is blank, the product is not included in surplus reporting. Products with a blank Acquired Date usually represent new product records that have been created, but where no product has been received into the warehouse. The Acquired Date can also be manually overridden even if stock exists for the product, although this is not recommended. It is updated automatically when product is first received into the system as a product receipt, return, unavailable product, or stock adjustment. Access to this field should be controlled using the option, Allow Access to Acquired Date, in SA Operator Setup-System Controls.

Report output

You can group the Product Surplus Stock Report by product, product category, vendor, or buyer. You can also print a fully detailed report (including non-surplus products) or only totals. Products with surplus stock are identified by warehouse, and the report offers an easy way to spot those areas where there is an abundance of surplus stock. If you are reviewing long-term surplus for other product classes, you can choose to print order-as-needed, do not reorder, or frozen permanently products.

The Product Surplus Stock Report is divided into three sections:

Product/Warehouse Detailed Surplus Lines – For each product/warehouse record, a detailed transaction for the product will print. The order method is shown under the Type column (next to the Ord Qty column): 

Surplus Cost is determined by multiplying the surplus units by the cost identified in the Post to GL By option in SA Admin Options-Products-Costs. This would be average, standard, replacement, last of FIFO cost, and addons, if specified.

When a product’s authorized replenishment path (ARP) is another warehouse, the transfer unit of measure prints on the report when one has been specified on the Product Warehouse Product Setup record. The buying unit of measure prints on the report when products are obtained from other sources.

Product Totals – The product totals print each time the product number changes. The product totals consist of totals for surplus units, price, and cost.

Warehouse Totals – The warehouse totals print at the end of the report. The surplus units and amounts for all products on the report are added together and shown as a total for each warehouse.

A legend is printed at the bottom of each page of the report describing the surplus type codes. These are:

Surplus Type Legend: st = Short Term; lto = Long Term (OAN, DNR); ltf = Long Term (Frozen); ltp = Long Term (Using LP + EOQ); ltu = Long Term (Using # Months * Usage); lta = Long Term (AMU); ns = Non Stock; Blank = no surplus

Exporting to a CSV file

In addition to the report - or, in place of - you can create output files that contain a list of the products that qualify as surplus. When you specify C or B for the option, Create C)SV Files, R)eport Only or B)oth, separate files are created in a CSV (comma-separated values) file for each ARP vendor or warehouse defined for the qualifying products in Product Warehouse Product Setup. These files can be immediately e-mailed to the vendor or warehouse manager by selecting a Print Type of E-Mail and specifying their e-mail address on the Printing Information view before you complete the report ranges and options.

When you receive the list of products approved for return or transfer, you can edit the CSV file to delete the products that were not approved, and then import the file in Purchase Order Entry or Transfer Entry (depending on the CSV file type). The CSV files are initially created in the exact layout that is required for a PO or WT import; you should only make changes to delete unapproved lines.

Use the option, CSV By V)endor, W)hse, N)on ARP, or A)ll, to control the type of CSV file created.

CSV files are saved in the Print Directory defined in SA Company Setup. The file name is based on the report options you select. For example, if you select to create the CSV by vendor, the vendor number and warehouse are part of the file name: icris-vendno-100-whse-01-58560334.csv. When you create a vendor CSV, you must also specify an unavailable reason code that is stored on each line. The Purchase Order Entry Import will fail if you do not specify an unavailable reason code for each line. The unavailable reason is validated by the Product Surplus Stock Report. Ensure you are using a valid reason set up in SA Table Code Values Setup-Reason Unavailable.

If you create CSV files for transfer, a separate file is created for each warehouse from/warehouse to combination and the from and to warehouse numbers are part of the file name. Example: icris-whsefm-04-whseto-02-55649250.csv.

If a CSV file contains more than 999 lines, a second report is created with a ‘2’ in the file name: icris-whsefm-04-whseto-02-2-55649250.csv.

A third option is to create a CSV file for non-ARP surplus products. The file name for these CSV files includes ‘nonarp’ in the name: icris-nonarp-55649250.csv. The non-ARP CSV files cannot be imported. They are formatted for viewing in a spreadsheet program such as Microsoft Excel and include column headings.

All CSV files created by a single Product Surplus Stock Report run have the same report number in the file name. This helps you identify all reports created by one run. CSV files are not removed or overwritten by the system or subsequent runs of the report. We recommend you purge these files periodically to free up system space.