Long-term surplus calculation

This formula is used to calculate a long-term surplus point:

  • Usage * Number of Months Considered Long Term

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 amounts are used when returning the long-term surplus value. This formula is used to calculate the available quantity for sale:

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

You can select Usage Rate or Actual Monthly Usage as the long-term Surplus Calculation usage method in Product Line Setup-Order or Product Warehouse Product Setup-Ordering.

  • Usage (Product Warehouse Product Setup Usage Rate)
  • Actual Monthly Usage (AMU = Total Usage (normal or override) for the last year divided by 12)

These options control the calculation method used when you view long-term surplus in Product Availability Inquiry or run the Product Surplus Stock Report to analyze long-term surplus inventory. They allow you to define a long term surplus calculation method, even at the product level.

Retain the default, Usage, to use this formula to calculate long-term usage:

Usage = Total Usage / Total # of Months

where:

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

If you select Actual Monthly Usage, the calculation uses a rolling actual monthly usage:

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

where:

  • Past 12 Months (periods 2-13) is the amount of the product sold in the last 12 months (typically periods 2-13, as calculated the last time you ran Product Administration Month End Processing Report and as displayed in the Usage Information window).
  • 12 represents a 12-month time period.
  • Number of Months for Long Term is one of two values: the default Long Term Surplus months specified at the company, warehouse, vendor, or product line level in Product Replenishment Setup-Ranking, or the time period set in the Product Surplus Stock Report option, Number of Months for Long Term Calc. The Product Replenishment Setup months are used when calculating long-term surplus in Product Availability Inquiry, Purchased Demand Center Entry, and Transfer Demand Center Entry.

Both methods impact only the long-term surplus calculation. The short-term surplus calculation is unchanged. You should review your product lines and products to determine the surplus calculation method. For instance, you could use the Product Line Setup-Order Surplus Calculation setting, Actual Monthly Usage, as a default. All product records created in Product Warehouse Product Setup-Ordering under that product line going forward also are set for Actual Monthly Usage. You can still, however, manually change the setting on a product level. How ever Surplus Calculation option is set in Product Warehouse Product Setup-Ordering, that same value is displayed in the other functions.

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

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.

Before you can determine your long-term surplus quantities, you must define the number of months you consider to be long term. The number of months can be defined for products one of two ways:

  • By setting a default number of months to be used in the calculation in the Long Term Surplus field in Product Replenishment Setup-Ranking. This allows you to capture the long-term surplus value of products at the company, warehouse, vendor, or product line level. The long-term surplus value calculated using the months from Product Replenishment Setup is displayed in Product Availability Inquiry on the Product and Warehouse Availability views. It is also displayed for replenishment reports in Purchase Demand Center Entry and Transfer Demand Center Entry.
  • By running the Product Surplus Stock Report. When you run this report, you specify the number of months to use in the Number of Months for Long Term Calc option. The long-term surplus value is printed on the report and is based on all report option selections.

The number of months to use in the long-term surplus calculation is usually 6-13 months. We recommend specifying no more than 25 months. The default setting in Product Replenishment Setup at the company level is 12 months.

Product conditions that contribute to surplus

The long-term surplus point is calculated differently for these product types. The Product Surplus Stock Report contains options to include them.

  • Nonstock products: Because the long-term surplus point is zero, the long-term surplus is equal to the available quantity for sale. These products are identified with the surplus type code of NS.
  • Order-as-needed and Do Not Reorder products: Because the long-term surplus point is zero, the long-term surplus is equal to the available quantity for sale. These products are identified with the surplus type code of LTO.
  • Frozen Permanently: The long-term surplus point is calculated as Line Point + Order Quantity. Products that are permanently frozen are identified with the long-term surplus type code of LTF.

New products are be considered for long-term surplus. Normal long-term surplus is identified on the report with a long-term surplus type of LTU or LTP. LTU is used if the available for sale quantity exceeds the user-defined number of months supply. LTP is used if the available for sale quantity also exceeds line point + order quantity, in addition to meeting the LTU criteria.