Product Surplus Stock Report ranges and options

Ranges

See the Report ranges topic for more information.

Vendor # (ARP)

Specify a beginning and ending range to limit surplus products to a specific vendor or range of vendors. The report uses the ARP vendor for the product in Product Warehouse Product Setup, if it is defined. If you are creating vendor CSV files, a separate file is created for each ARP vendor.

Whse From

Specify a beginning and ending range to limit surplus products to those within a specific warehouse. If you plan to transfer surplus stock to your distribution (ARP) warehouse, this is the warehouse in which the product currently exists and where it is transferred from. If you are creating warehouse CSV files, a separate file is created for each warehouse from/warehouse to combination.

Product

Specify a beginning and ending range of product numbers to limit the products for which you are calculating surplus.

Product Category

Specify a beginning and ending range to limit surplus products to a specific product category or range of categories. Product categories are set up in SA Table Code Values Setup and assigned to individual products in Product Setup.

Region

Specify a beginning and ending range to limit surplus products within a warehouse in a specific region or range of regions. Regions are assigned to warehouses in Product Warehouse Description Setup.

Class

Specify a beginning and ending range to limit surplus products to those within a specific class or range of classes. Class is assigned to warehouse products based on the Product Administration Inventory Classification and Rank Report and is stored in Product Warehouse Product Setup-Ordering.

Warehouse Rank

Specify a beginning and ending range to limit surplus products to those within a warehouse in a specific rank or range of rankings. Rank is assigned to warehouse products based on settings in Product Replenishment Setup and calculations made by the Product Administration Inventory Classification and Rank Report. Warehouse Rank is stored in Product Warehouse Product Setup-Ordering.

Options

Print Only Surplus > or = What %

You can print only those products that contain a substantial percentage of surplus stock. The surplus percentage is calculated by dividing the surplus quantity by the net available.

Specify the percentage as a whole number and not a decimal. All those products with a percentage equal to or greater than the percentage you specify are included on the report.

  • For 10%, specify 10.0, not 0.10.
Print Only Surplus > or = What $

You can print only those products with surplus stock over a particular value, instead of a percentage. The surplus value is determined by multiplying the surplus quantity by the cost selected for the Post to G/L By option in SA Admin Options-Products-Cost. The options are average, standard, replacement, last, FIFO and addons, if applicable.

Specify the value, and all products with a surplus value equal to or greater are included on the report.

Sort Order: (P)rod, Prod (C)at, (V)endor. (B)uyer, (A)mt or Prod (L)ine
Specify one of these values:

Specify P to print in product order.

Specify C to print in product category order.

Specify V to print in vendor order.

Specify B to print in buyer order.

Specify A to print in amount order. The report does not include subtotals for changes in the surplus cost values. Products appear in ascending sequence by the surplus cost value, and there is one total at the end of the report followed by the warehouse summary page.

Note:  If you select No to Show Detail, the report displays only the final total, no products.

Specify L to print in product line order.

(D)etail, (P)rod Totals, or (W)hse Totals

Specify D to print the full report, including detailed history transactions, product totals, and warehouse totals. This option displays two lines per product and warehouse with the surplus totals, as well as the ordering control information.

Specify P to print the product totals, including products by warehouse without the detailed transactions. This option displays one line per product and warehouse with only the product’s surplus totals.

Specify W to print only the warehouse summary page with no details or summary.

Print Surplus Stock Only?

Select Yes to print only those products with surplus stock.

Select No to print all products regardless of surplus stock.

Show Detail?

Select Yes to print product detail if the (D)etail, (P)rod Totals, or (W)hse Totals option is set to D or P.

Select No to print only the subtotals for the sort sequence. For example, if the sort order is C, then only a list of categories and their totals print. No products print.

(S)hort Term, (L)ong Term or (B)oth

This option allows you to choose the calculation to use for determining surplus inventory.

Specify S and the surplus is calculated using the following formula:

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

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

Surplus cost is determined by multiplying the surplus units by the cost in the Post to GL By option in SA Admin Options-Products-Costs.

Short-term surplus is identified with the surplus type code "st."

Specify L and you must also specify the number of months in the Number of Months for Long Term Calc option to use in the long-term calculation below. This number is multiplied by the average monthly usage for that period to determine a long-term surplus point:

Long-term Surplus Point = Average monthly usage * Number of Months Considered Long Term (user-defined value)

A second long-term surplus point is calculated to use as a comparison:

Line Point + Economic Order Quantity (EOQ)

Each surplus point is subtracted from the product’s available quantity for sale in separate calculations, and the larger of the result is shown on the report as long-term surplus.

If you specify L and set either Include Order As Needed Products or Include Do Not Re-order Products to Yes, products with a status of Order as Needed (OAN) or Do Not Reorder (DNR) that are not already committed are included on the report.

Specify B to print short-term and long-term surplus.

Number of Months for Long Term Calc

Use this option only if you specified L in the previous option.

Specify the number of months to be used in the calculation to determine long-term surplus, up to 25. The number of months you specify are used in this calculation:

Long-term Surplus Point = Average monthly usage * Number of Months Considered Long Term

In addition, a second long-term surplus point is calculated by adding Line Point and Economic Order Quantity (EOQ). Each surplus point is subtracted from the product’s available quantity for sale in separate calculations, and the larger of the results appears as long-term surplus. Available quantity for sale is calculated as:

On Hand – Reserved – Committed  –  Backordered – Demand + Received

Include Non-Stock Products?

Select Yes to include nonstock products on the report. Nonstock surplus appear with the surplus type code ns. The net available quantity for nonstock products is calculated using this formula: Net Available = Total Quantity - Tied Quantity

Include Order As Needed Products?

Select Yes to include order-as-needed products on the report. They appear with the surplus type code lto (long-term othe product class).

Include Do Not Re-Order Products?

Select Yes to include do not reorder products on the report. They appear with the surplus type code lto (long-term other product class).

Include Frozen Permanently Products?

Select Yes to include products that are frozen permanently (the Number of Months field in Product Warehouse Product Setup- Frozen is set to 99).

Usage Method Options Include:

This option controls the calculation method used to determine long-term surplus inventory. This works in conjunction with the Surplus Calc option in Product Warehouse Product Setup.

  • S)urp - uses each product’s Product Warehouse Product Setup-Ordering Surplus Calc selection. This is the default setting. If you have not changed the default Surplus Calc setting in Product Warehouse Product Setup, this selection will utilize the existing Usage Rate Method to calculate long-term surplus.
  • U)sg - overrides whatever Product Warehouse Product Setup-Ordering method is selected for all products and applies the Usage Rate.
  • A)MU - overrides with the AMU rate.
  • H)igh - uses the highest of the two methods.
  • L)ow - uses the lowest of the two methods.
  • B)oth H/L - uses both Usg and AMU, printing the results of both on the report.

Using this option allows you to calculate long term surplus using the method that produces the best result. You can run the report using each product's Surplus Calc selection, override whatever method is selected for all product with the Product Warehouse Product Setup Usage Rate, override with the AMU rate, use the highest of the two methods, use the lowest of the two methods or us both Usg and AMU, printing the results of both so that you can choose the value you want.

This option impact the long-term surplus calculation only; short-term surplus calculation is not changed by this setting.

Create C)SV Files, R)eport Only or B)oth

This option determines whether or not you create the report as a CSV (comma-separated value) file, report only, or both.

Select C to create CSV files only. A CSV file is created for each vendor or warehouse from/to combination. If a vendor or warehouse CSV file includes more than 999 lines, a second CSV file is created. The second file name is distinguished by a ‘2’ in the name.

Example

Vendor CSV file created for vendor 100, warehouse ‘main’: icris-vendno-100-whse-main-390393003.csv

Vendor CSV file created for lines exceeding 999: icris-vendno-100-whse-main-2-390393003.csv

Report output is still produced, but it contains no detail. Instead, the message “Report Data Sent to CSV Files” is printed.

Select R to print the report only. No CSV files are created.

Select B to print the report detail and create CSV files. The report is printed to the print device type you specified on the Report Name view. The CSV files are saved to the SA Setup Company Print Directory location. If you selected E-Mail as the Print Type on the Report Name view, both the report and the CSV files are e-mailed to the address you specify. The CSV files are saved to the Print Directory location.

Note:  Because the CSV files are uniquely named each time they are created, they are never overridden in the Print Directory. We recommend you periodically review and delete old files.
CSV By V)endor, W)hse, N)on ARP, or A)ll

This option is required if you select C or B for the option, Create C)SV Files, R)eport Only or B)oth. This option determines the type of data included in the CSV file.

Select V to produce a CSV files containing data that is grouped by vendor. Surplus products with an ARP Vendor in Product Warehouse Product Setup are included in each CSV file. A single CSV file is created for each vendor and assigned a unique file name containing the vendor ID and ship from warehouse. (Example: icris-vendno-101-whse-main-390393003.csv.) The file is stored in the Print Directory you defined in SA Company Setup. If you selected a Print Type of E-Mail on the Report Name view, the file is also e-mailed to the vendor. If there is no ARP Type of Vendor on the Product Warehouse Product Setup record, the product is not included in this file type.

Select W to produce a CSV file for each warehouse to and warehouse from combination. The ‘from’ warehouse is the warehouse(s) you defined in the Whse From ranges. The ‘to’ warehouse is the ARP Warehouse defined on each surplus product’s Product Warehouse Product Setup record. If there is no ARP Type of Warehouse or Central Warehouse on the Product Warehouse Product Setup record, the product is not included in this file type.

Select N to produce a CSV file for products that do not have an ARP vendor or warehouse defined in Product Warehouse Product Setup. This CSV file cannot be imported in Purchase Order Entry or Transfer Entry to create PO RMs or WTs. It is considered an internal branch report. It includes column labels and can be used to review all non-ARP surplus products in a spreadsheet application such as Microsoft Excel.

Select A to produce CSV files for all items that qualify as surplus, sorted by product and warehouse. Separate CSV files are still created for vendors and warehouses when there is an ARP vendor or warehouse in Product Warehouse Product Setup. If there is no ARP vendor or warehouse for the product, a Non ARP CSV file is also created.

CSV PO/RM Default Unavailable Reason

If you intend to import vendor CSV file(s) after receiving approval from your vendor, you must include the Reason Unavailable on each line or the import of the line will fail in Purchase Order Entry. Use this option to define a default unavailable reason that is stored on the PO RM line that is created during the import. Unavailable reasons must be set up in SA Table Code Values Setup-Reason Unavailable. If you select to create a CSV by Vendor and the unavailable reason is blank or invalid, the report is not processed.