Long-term surplus calculation

Long-term surplus is determined when you run the Product Surplus Stock Report. The Rate in Product Warehouse Product Setup-Ordering is multiplied the Months. This is calculated and updated each time you run the Product Administration Month End Processing Report.

Typically, Forward or Backward is selected in the Method field in Product Warehouse Product Setup-Ordering. Both methods use the Month value to determine the usage rate. Generally, this is set to 6 months. If the Trend % or Exponential Smoothing methods are used, surplus calculations may be skewed.

For example, your usage rate calculation may use 6 or less months of usage, but you want to calculate how much surplus you have that is greater than a year’s supply. You may also see inflated surplus amounts if you perform the calculation during a time that is out of season for a product. For these types of products, you can use a longer period of time, up to 12 months to determine actual monthly usage.

Surplus Calculation

You can calculate actual monthly usage in Product Warehouse Product Setup-Ordering and Product Line Setup-Order. In the Surplus Calculation field, select Usage or Actual Monthly Usage.

These options control the calculation method that is used when you run the Product Surplus Stock Report to analyze long-term surplus inventory. They enable you to define a long term surplus calculation method, even at the product level.

Select Usage to calculate long-term usage. This is the formula:

  • Usage = Total Usage / Total # of Months

These values are used in the calculation:

  • Total Usage is the amount of the product sold in the time period that is specified in Months field.
  • Total # of Months is the time period that is specified in the Months field.

Select Actual Monthly Usage to calculate a rolling actual monthly usage. This is the formula:

  • AMU = Past 12 Months (periods 2-13) / 12 x Number of Months for Long Term Calc

These values are used in the calculation:

  • Past 12 Months (periods 2-13) is the amount of product that was sold in the last 12 months. This value was calculated the last time that you ran the Product Administration Month End Processing Report. This value is displayed in the Usage Information window in Product Warehouse Product Setup-Ordering. You can click Show Usage to access the Usage Information window.
  • 12 represents a 12-month time period
  • Number of Months for Long Term Calc is the time period set in the Product Surplus Stock Report

Both methods affect only the long-term surplus calculation. Short-term surplus calculation is unchanged. Review product lines and products to determine which surplus calculation method should be used. The value in the Surplus Type setting on a Product Lines Setup record is the default value for the Surplus Calculation field on Product Warehouse Product Setup records with that product line. You can override the default setting on a product record. You can use the Mass Update and Manual Product List functions in Product Exception Center Administration to update the Surplus Calculation field on multiple products records.