Recalculate Reorder Fields

Application Overview

 The Recalculate Reorder Fields application should be run once every forecasting period.  Each time it is run it recalculates the average periodic usage for each item.  It also recalculates what the reorder level, amount of safety stock, and the recommended minimum order amount should be for the item, based on the actual usage for the period.

 Many of the fields in the Item File are used when the recalculation is performed.  Below is a list of those fields and their names.

* Field #4 is in the I/M Setup application. 

 Below is a step-by-step explanation of how the application recalculates the Reorder Level for each item.  Some of the equations use values calculated in earlier steps so be sure to understand each one before proceeding to the next step.  The numbers in parentheses represent field numbers in the Item File.

Calculations

 Step 1

 Smoothed Period Usage

 Used in calculating the Sum of Errors (#72), Average Error (#71), and Average Usage (#57).

 Calculated as follows:

 If Usage Filter (#61) = 0 or Average Usage (#57) = 0 then Smoothed Period Usage = Usage Period-To-Date (#54)

 Else if Usage Filter (#61) * Average Usage (#57)< Usage Period-To-Date (#54)

   then Smoothed Period Usage = Usage Filter (#61) * Average Usage (#57)

   else Smoothed Period Usage = Usage Period-To-Date (#54)

 The Smoothed Period Usage is mainly controlled by the Usage Filter (#61).  It is a usage value calculated to reduce the impact of large one-period surges in item demand.  See the explanation of Usage Filter (#62) below for more information.

Step 2

 Sum Of Errors (#72)

 This field should be left blank before the initial running of this application.

 Calculated as follows:

 Sum Of Errors (new #72) = Average Usage (#57) - Smoothed Period Usage (from above) + Sum of Errors (old #72)

 This is a summation of all forecast errors made by the package for the item.  It is used as an index of whether the forecasts have been too high or too low, on the average.  For example, if the forecasted usage was 3 high one month, 6 low the next, 10 low the next, 18 high the next, and 9 low the next, the Sum of Errors for this item would be:

       3 + (‑6) + (‑10) + 18 + (‑9) = ‑4

 In this example the forecasts for this item have been a little low over this time period.

Step 3

 Average Forecast Error (#71)

 This field should be left at 0 before the initial running of this application.

 Used in calculating Safety Stock (#59).

 Calculated as follows:

 Abs-Value-Of-Average-Error = Average Usage (#57) - Smoothed Period Usage (from above)

If Abs-Value-Of-Average-Error < 0 then change sign of Abs-Value-Of-Average-Error

Average Error (new #71) = [Abs-Value-Of-Average-Error * Usage Weight Factor (#58)] + [Average Error (old #71) * [1 - Usage Weight Factor (#58)]]

 This is the average amount that the computer's forecasted usage was off from the actual usage.  If the forecast was off by 8 one period and off by 4 the next period, the average forecast error would be 6, regardless of whether the forecasts were too low or too high.  This field gives you an index of predictability for the usage of this item.  It is used by the package in calculating the amount of safety stock that will be required for this item.  If the usage for a particular item is very predictable (i.e., is constant, steadily rising, or steadily falling ‑ no big dips or bumps) the average usage error will be low and not much safety stock will be necessary.  If the usage for an item is bumpy and unpredictable, it will cause the average forecast error to be much higher and more safety stock will be required to ensure that there is extra stock to handle any sudden demands.

Step 4

 Average Usage (#57)

 Also referred to as Forecasted Usage.

 Used in calculating Smoothed Period Usage, Sum of Errors (#72), and Recommended Minimum Order (#44).

Calculated as follows:

 Average Usage (new #57) = Smoothed Period Usage * Usage Weight Factor (#58) + [Average Usage (old #57) * [1 - Usage Weight Factor (#58)]]

 This is a weighted moving average usage for the item.  When this is recalculated at the end of a period, it becomes the forecast for the next period.  It is a weighted average, because the usage of the item in recent periods is given more weight (importance) than usages for periods long past. 

 It is a moving average because it is always an average of the last few periods, so it moves along in time, rather than being an average of every period from the beginning of time up to now.

Step 5

 Safety Stock (#59)

 This field should be left at 0 before the initial running of this application.

 Used in calculating Reorder Level (#41).

 Calculated as follows:

 Safety Stock (#59) = Safety Factor (#60) * Average Forecast Error (#71)

 This is the amount of extra stock that is kept in addition to what is predicted will be needed.  This gives you a cushion in case the pre­diction was wrong.  When an item's usage is predictable, less safety stock is required.  When the item's usage is unpredictable, more safety stock is required.

Step 6

 Recommended Minimum Order (#44)

 This field should be left at 0 before the initial running of this application.

 Used in calculating Reorder Level (#41).

 Rec. Minimum Order (#44) = Average Usage (#57) * [Lead Time (#45) / Number of Days in Forecast Period (#4 in I/M Setup)]

 This is the smallest order that should normally be placed for any item.  It is based on the idea that if you normally use 10 of these items per period and it normally takes 3 periods to get the item into stock once ordered, it would be inefficient to order less than 30 of this item, because by the time the order had arrived, you would have used up 30 more, and you would need to reorder again.

Step 7

 Reorder Level (#41)

 This field should be left at 0 before the initial running of this application.

 Calculated as follows:

 Reorder Level (#41) = Safety Stock (#59) + Rec. Minimum Order (#44)

 The reorder level is calculated by adding the amount of stock you want to keep on hand for sudden emergency demands (safety stock) to the amount of stock you expect to use during the time it takes to receive a new supply from the vendor or from the manufacturing facility.

 When the quantity on hand of the item falls below the reorder level, an order should be placed for the item.  This could be performed via the Create Unreleased Purchase Order's By Vendor application, if you wish.

The following fields are not calculated by this application but are important to it functioning correctly.  They are either entered in the Item File manually or updated by another application.

 Usage Period-To-Date (#54)

 Used in calculating Smoothed Period Usage.

This field represents the quantity of the item, which has been used so far this period.  This usage would have come about either as the result of shipping a quantity of the item to a customer, or by consuming the item as a component of some manufactu­ring process.  The field is updated either after the invoicing of orders in the Customer Order Processing package, by Inventory Transaction Processing, or by producing work orders in Bill Of Material Processor.

You must clear the Period-to-Date fields at the end of your accounting period using the Clear Item Accumulators application in order to run the Item History Report. If you are using Customer Order Processing, the COP Sales Analysis reports must be printed BEFORE you clear the Period-to-Date fields.

Usage Filter (#61)

 This field is manually entered in Item File Maintenance.

Used in calculating the Sum of Errors (#72), Average Error (#71), and Average Usage (#57).

This is a value used to control the effect of large one-period increases in demand.  If there was a sudden surge in demand, it could cause the forecasted usage to be raised too high, resulting in over-ordering.  When calculating the new Average Usage, the Usage Filter is multiplied by the old Average Usage.  The new Average Usage is not allowed to be higher than the product of these two.  For example, if the old Average Usage was 1000, the Usage Filter was 5, and the Actual usage for the period was 8000; the new forecast (Smoothed Period Usage) would not be allowed to exceed 5000, even though the actual amount used for the period was higher.

Usage Weight Factor (#58)

This field is manually entered in Item File Maintenance.

Used in calculating Average Forecast Error (#71), and Average Usage (#57).

This is the alpha factor used in forecasting the next period's usage of the item, based on the actual usage for the previous period, and the forecast for that period.  It determines the number of periods of sales history to use in forecasting next period's usage.  Based on experience in the manufacturing industry, a table of alpha factors for periods of dif­ferent lengths has been drawn up for your use.

In the calculation of a new forecast, as you use more periods of sales history, the importance placed on the previous period's actual usage decreases.  Thus a sudden change upward or downward will not produce a widely out of line forecast for next period.

If this field is left at zero, the forecast for the next period will never change, regardless of how high or low the actual usage goes.

Safety Factor (#60)

 This field is manually entered in Item File Maintenance.

Used in calculating Safety Stock (#59).

In general, the higher the safety factor is set, the higher the safety stock will be.  Thus the number of out‑of‑stock situations will decrease.

The following table of safety factors has been drawn up from experience in the manufacturing industry.  It is based on the customer service level you want to maintain.  For example, if you want to provide a customer service level of 85%, meaning that 85 out of 100 times a customer orders the item it will be in stock, you would specify the safety factor at 1.3.

If this field is left at zero, there will not be any safety stock, so about half the time you will be out of stock.

Lead Time (#45)

This field is manually entered in Item File Maintenance.

Used in calculating Recommended Minimum Order (#44).

This is the number of calendar days, which pass between the ordering of a new supply of the item and the actual receipt of the item into stock.

If this field is left at zero, the Recommended Minimum Order field will always be calculated as zero by this application.

Average Number Of Days In Forecast Period (#4 in I/M Setup)

This field is manually entered in the I/M Setup application.

 Used in calculating Recommended Minimum Order (#44).

 This is the average number of days in your forecast period.  For companies with a 12 period calendar, this will be 30.44 days.

Run Instructions

 Select Recalculate Reorder Fields from the pull down I/M Processing window.  The following screen will then be displayed:

Recalculate Reorder Fields Entry Screen

 The following options are available:

To return to the menu bar, press the ESC or F10 key. To leave this application, press X for EXIT when positioned at the menu bar.

Screens

Recalculate Reorder Fields