Sales Inventory Value Report

Reports Menu -> Inventory -> Inventory Value Reports -> Sales Inventory Value Report

This program can be used to generate the inventory value reports for sales items.
Separate output is generated for Serialized Sales inventory and for Non-Serialized Sales parts.
The reports can be based on the company-wide On Hand quantities or by the quantity at each location, and the product value can be calculated at the class level or for the individual products.

Exceptions:

Also any Serialized sales part quantities used on a current Work Order, are not included on the inventory value report.
This report respects the Divisional Restricted Views assigned to the operator, so the inventory value reported will only be reflected for this operator's divisions.

Inventory Value Formula:

The report calculation uses the Average Cost Each from the Sales Inventory, multiplied by the Location Quantity to calculate the Total Inventory Value.
If your firm uses the Sales Item Average Cost by Location feature as activated in the Support Application Parameters and the output combines multiple locations, then the operator will be prompted for the location to be used to determine the cost in the calculation.

This report lists the current inventory value, unless the option to Print By Date is selected.

This Date option reconstructs the Inventory quantity as of the selected date in the past, using the Inventory Adjustment records, Warehouse Receipts and the Detailed Revenue History (Sales History INSH).
The total value in a Print By Date report will not necessarily be accurate, as it still uses the current average cost in the product file, and the average cost has probably changed as it is updated each time a product is purchased and receipted.

Note: If there is no value for an item in the output, check the cost type in Sales Inventory.
If the cost type is Zero Cost the assumption is that the stock has already been expensed upon purchase, therefore no inventory value for reporting purposes is required.

Margin Calculation:

The Margin percent formula:

(LIST PRICE - PROD AVG COST) / LIST PRICE * 100

The reported Margin percent value is useful on the individual products, but less useful as a Class Margin Total or for the Report Margin Total.
This is because the Margin is calculated on each transaction before totalling. Then that total is divided by the number of transactions to provide the Class/Report Total Margins.
This formula for the Total Margin does not consider the difference in the value of the products so is not always an accurate margin value overall, and may not calculate consistently between reports.

The prompts to generate the report are:

FROM PRODUCT CLASS

Leave this field blank to include all sales classes, or enter the desired starting Product Class for the reporting range, or select one from the Product Class Search window.

TO PRODUCT CLASS

Leave this field blank to include all sales classes, or enter the desired ending Product Class for the range.

Note: When balancing the Inventory Value from this report to the General Ledger, use the Product Class filter and include all classes that post to the same GL account assigned in the Sales Product Classes for Inventory.

PRINT PRODUCT DETAIL

Uncheck this box to just print the summary for each Product Class.

Check this box to include the details for each product in the selection range.

FROM PRODUCT #

This field is enabled if the Print Product Detail option has been selected.
Leave this field blank to include all sales products, or type in a beginning product number, or select one from the Inventory Search window.
This product number needs to fall within the Product Class range selected above.

TO PRODUCT #

Leave this field blank to include all sales products, or type in an ending product number.
This product number must also fall within the Product Class range selected above.

CALCULATE QUANTITIES BY LOCATION

Uncheck this box to generate a single inventory value record per product or class combining all the locations included in the Location, ALL, or SEL filter below.
Useful output can be generated as a document or to a spreadsheet.

Check this box to generate a separate product/class inventory value record for each location selected in the Location, ALL, or SEL filter, based on the quantities at that location.
With this option it is recommended to output the resulting report information to a spreadsheet which includes a location column for each product record, however if document format is requested then the Sort By selection should be by 'Location' for easier interpretation of the data.

Note: This option is automatically checked if the Sort By selection is by "Location".

PRINT BY DATE

Uncheck this box to print an inventory list using the current costs and current quantities on hand, as stated in Sales Inventory.

Check this box to calculate the inventory value according to the quantities as of a prior date, and the with current cost.

DATE

This field is enabled for the Print By Date option.
Enter the date to use to reconstruct the product quantities, using the Inventory Adjustment records, Warehouse Receipts and the Detailed Revenue History (Sales History INSH).

Note: The utility Inventory Transaction History can be used to run an audit on rental product history.

PRINT SERIALIZED ITEMS

Uncheck this box to skip the serialized sales item report.

Check this box to list the serialized sale items individually on a separate report from the Non-Serialized inventory.
The serialized products are defined in Sales Inventory in the Serial# field.

COST TO USE

This field is enabled for the Print Serialized Items option.
Select one of the following cost options for the serialized products:

• Select Average Cost to use the general product average cost for the serialized items.
• Select Serial Number Cost as assigned to each serial number for the product in the Serial Numbers window.

INCL. TIME-OF-SALE SERIALIZED ITEMS

This field is only enabled for the Serialized report.
Some serialized sales parts are not assigned the serial numbers until the specific product is sold, as identified in Sales Inventory by the Assign at Time of Sale flag.

Uncheck this box to exclude the Time-of-Sale serialized sale items as they often do not have serial numbers yet.

Check this box to include all serialized sale items whether or not they have serial numbers.
The text TIME OF SALE ITEMS prints on the report in place of the serial number for items with no serial numbers.

PRINT NON-SERIALIZED ITEMS

Check this box to include bulk inventory that is not serialized, on a separate report from the Serialized Inventory.
Uncheck this box to exclude inventory that is not flagged as serialized, skipping this report.

SORT BY

Select one of the following sort options to apply to the product records:

• Select Class to sort the product records within the Sales Product Classes.
  If this option is selected, the option to generate a New Page per Class is enabled.

• Select Product to sort the product records by product number regardless of class.
  If this option is selected, the Print Product Detail is automatically checked.

• Select Location to sort the product records by location.
  If this option is selected, the formula to Calculate Quantities by Location is automatically applied and the option to generate a New Page per Location is enabled.

LOCATION OR 'ALL'

This prompt controls the location quantities (RSIL) reflected in the values reported on the output.
The option to Calculate Quantities by Location controls whether all locations are included in a single inventory value record per product, or whether a record is output per location.

Select one of the following location options:

• Type ALL to include quantities for all locations in the report.
• To generate the report with quantities for a single location, leave this field blank for the Head Office, or enter a specific location code, or select a location from the drop-down list.
• Type SEL to select a range of locations or locations by accounting region to include in the output, as outlined in Division Select By Region.

Note: The location selection is restricted to the Divisional Restricted Views assigned to the operator, so the inventory value reported will only be reflected for this operator's divisions.
The location selection is NOT restricted to just locations currently flagged as Inventory in Divisions as this could distorted the report if transactions were omitted from divisions that may previously have qualified as inventory locations.

COST LOCATION

This prompt appears only if Sales Item Average Cost by Location processing has been activated in the Support Application Parameters and the Calculate Quantities by Location is not selected so that products from multiple locations are to be included on one report.
Enter the specific location from which the costs should print, or select the cost location from the drop-down list.

If Cost by Location processing has not been activated, the average cost is used automatically.

Location Summary: When the output does not Calculate Quantities by Location, a summary "Location" column is included on the spreadsheet output listing all the RSIL location codes for each product, regardless of the selected reporting locations, regardless of quantities at those locations, and regardless of any restricted views.
If an RSIL record exists for more than one location, the locations will all be included in the same column separated by commas, allowing 16 locations per column with a maximum of 10 Location columns.

NEW PAGE PER CLASS

This option is only enabled if the Sort By option selected is "Class", and only applies to the product detailed output.

Check this box to start a new page for each new Product Class with a sub-total for each Product Class.
If the Excel option was selected for the output, then the information for each class will be on a separate tab on the spreadsheet.

Uncheck this box to continue printing all Product Classes together with a sub-total for each Product Class on the report output.

NEW PAGE PER LOCATION

This option is only enabled if the Sort By option selected is "Location".

Check this box to start a new page for each new Location with a sub-total for each Location.
If the Excel option was selected for the output, then the information for each Location will be on a separate tab on the spreadsheet.

Uncheck this box to continue printing all Locations together with a sub-total for each Location on the report output.

REPORT OPTIONS

This value defaults to the operator preference as defined in Operators.
Select one of the output options to Print a report or export the data to Excel as outlined in Report Options.

On the spreadsheet output, a separate tab is generated for each Product Class on the two reports.

Finished?

Click the ACCEPT button to begin printing the report, or CANCEL to abort.

Warning re: GRAND TOTAL
Two separate reports will print: one for Serialized Sales inventory and one for Non-Serialized Sales inventory.
To get the GRAND TOTAL of both types of inventory, print the report for all classes and manually add the two reports together.

Note: Because they do not affect inventory, any received (INWR) quantities for re-rentals or for services through associated parts, that were ordered using the P.O./Contract link feature, are not reflected in this report.
Refer to Contract/Purchase Order Link Overview for details on this processing.