Product Return On Investment Report overview

Product > Reports > IC Reports > ICRIR

Function acronym: ICRIR

Use this report to determine ROI to evaluate performance and profitability, and recognize trends.

This formula expresses ROI in general terms:

  • ROI = (Margin / Sales) * (Sales / Total Investment)

ROI, or return on investment, is the general term to describe a ratio of net income or gross margin to investment such as your inventory.

An increase in sales does not increase your ROI because the sales cancel each other out. If other factors are held constant, a decrease in investment or an increase in gross margin increases ROI.

Use ROI to compare the profitability of relative divisions, such as different warehouses. As with any analysis data, consistency is one of the most important factors in producing data and identifying trends. One ROI report cannot provide a valid assessment of your performance. Monthly reports show ups and downs, which is normal.

This basic formula is used to calculate ROI:

  • ROI = Average Margin Expressed as Amount / (Average Inventory Value + Average Carrying Cost)

This expanded formula is used to calculate ROI using the report:

  • ROI = (Sales Cost of Goods Sold / # Months Accumulated from stock only) / {(Average Inventory Value / # Months Accumulated) + [(Average Inventory Value / # Months Accumulated) * Carrying Cost]}

The SM Setup Warehouse/Product Totals totals for sales and cost of goods sold are used to calculate the margin. You must select Sales by Product, by Whse (SMSW) in SA Administrator Options-Sales History-Levels. The ROI can be calculated for up to 24 months. You specify the number of months on the report. If you operate under a 13-period fiscal year, the calculation does not include more than 12 months of sales history.

The average inventory value is computed for each product at month end by the Product Administration Month End Processing Report. The monthly values are displayed on the Usage Information page that is accessed from Product Inquiry-Replenishment. These values are used to compute the average inventory value:

  • (Beginning inventory value + Inventory values for the number of months) / Number of months + 1

The beginning inventory value is the previous month's ending average inventory value.

For example, to calculate the average inventory value for 1/19 through 12/19, this calculation is performed for the 12 month period:
  • (12/18 + 1/19 + 2/19 + . . .+ 12/19) / 12 + 1

The carrying cost in Product Warehouse Description Setup is reflective of the current year. It is averaged by dividing the total by the number of months of history that is specified on the report. If carrying costs have varied dramatically from year to year, the analysis can be skewed. Product Inquiry-Replenishment, which is the source for inventory value, includes 24 months of history. Analysis beyond that limitation generates a value of zero.

Caution: 
Ensure that the Carrying Cost field in Product Warehouse Description Setup contains a valid, realistic cost before you run this report.

Inventory items that have a zero investment cost have 100 percent ROI, if the average margin is greater than zero. If the investment cost is zero and the margin is less than zero, the ROI is -100 percent. Any calculations that use "0" as the denominator are replaced with a "z", because division by zero is not possible.

Seasonal products are calculated in much the same way. Low sales and increasing inventory build-up in the months before or after the season are considered. Seasonal products are indicated with an "s" on the ROI report. The Product Seasonal Product Analysis Report is a report that concentrates on your seasonal product activity.

Inactive, labor, and build-on-demand kit products are excluded from the ROI calculation.

The last merge date in Product Inquiry-Replenishment is set at month end processing by the Product Administration Usage Rate Analysis Report and determines what data is relevant. This date must be available. If it is blank, this message is displayed:
ICSWU Last Merge Date is blank, Cannot Process

The ranges and options that you specify may produce confusing results. Experiment with the ranges and options until you feel comfortable with the report.