IM1300 Inventory Management Recalculate Reorder Fields

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 Number

Name

41

Reorder Level

42

Last Reorder Lvl

43

Order Up To

44

Recommended Minimum Order

45

Lead Time

54

Usage Period-To-Date

57

Average Usage (also Forecasted Usage)

58

Usage Weight Factor

59

Safety Stock

60

Safety Factor

61

Usage Filter

71

Average Error

72

Sum Of Errors

*4

Average Number of Days in Period

 

* 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).

    Calculated as follows:

 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.

  

Periods of Sales History

Usage Weighing Factor

3

.50

6

.29

9

.20

12

.15

18

.11

 

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.

  

Customer Service Safety Level

Factor

50%

0.0

75%

0.8

80%

1.1

85%

1.3

90%

1.6

95%

2.1

97%

2.4

99%

2.9

99.5%

3.2

99.8%

3.6

99.9%

3.9

99.99%

5.0

 

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:

    • Select the desired mode from the Recalculate Reorder Fields menu bar
    • Enter the data requested on the screen

    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

     

     

     

     

    Inventory Management

    1. Long-Term vs. Short-Term Inventory Quantity Available and Inventory with Time Line
    2. Introduction to Available to Promise (ATP)
    3. Inventory Aging Report by Location Sequence Produces Different Result Than by Other Sequence
    4. Inventory Aging Report Shows Different Age Between Single and All Locations
    5. Multiple Users Entering Physical Count Tag at the Same Time Issue
    6. Expand Item Description 1 and 2 Character Limit
    7. How Different is an IM Kit from a BOMP Kit?
    8. Explanation on Component Availability Inquiry
    9. How Is Shortcut Item Different from Kit Item?
    10. No Privilege to View Location History
    11. How to Calculate EOQ (Economic Order Quantity)
    12. Item Quantity Allocation Is Incorrect
    13. Reorder Advice Custom CSV Export Shows Zero Items
    14. Elliott Physical Count Processing
    15. Feature - Add-On Item Drill Down From Item Search
    16. Feature - Utility to Reset Item PTD Fields from Location History
    17. Feature - Inventory Snapshot
    18. Feature - ATP Regen to Consider Order Multiple and Economic Order Qty
    19. Feature - Item Master Activity Sub-Code
    20. Feature - Add Support for the Update of “Mostly Mfg Flag” in Change Existing Item Import
    21. Feature - Auto Delete BOMP Product Structure When Item Obsolete
    22. Feature - Item Linkage Report/Processing
    23. Feature - Added the Ability to Organize Product Categories by a New Product Category Group
    24. Procedure to Correct Bad Serial Number with Special Symbol
    25. Feature - Additional SY12MONS Updates
    26. Feature - Additional Activity Codes for Item Linkage CSV Report and Item Linkage Processing
    27. Feature - Item Number Wild Card Support for Starting Item Number
    28. Feature - Obsolete Kit Parent When First Component is Obsolete
    29. Feature - Show Items with Qty Variance on Tag Variance Reports
    30. IM01S3 Inventory Management Iten Audit Trail Report
    31. IM04S2 Inventory Management Inventory Transaction Audit Report
    32. IM02S2 Inventory Management Inventory Location Audit Trail Report
    33. IM03S1 Inventory Management Usage Exception Reports
    34. IM06SCN Inventory Management Stock Status Report
    35. IM08S1 Inventory Management Print Cycle Count Worksheet
    36. IM14S1 Inventory Management Item History Report
    37. IM19S1 Inventory Management Serial/Lot Stock Status Report
    38. IM19S2 Inventory Management Serial/Lot Issue History Report
    39. IM20S1 Inventory Management I/M Distribution to G/L Report
    40. IM26SCN Inventory Management Frozen Stock Status Report
    41. IM30S1 Inventory Management Kit Where-Used Report
    42. IM31S1 Create Unreleased POs by Vendor
    43. IM32S1 Inventory Management Kit Gross Requirements Report
    44. IM0201 Inventory Management Location Control File Maintenance
    45. IM0405 Inventory Management Available To Promise Inquiry
    46. IM0500 Inventory Management Stock Status Inquiry
    47. IM0500Q Inventory Management Stock Status Inquiry
    48. IM0700 Inventory Management ABC Analysis Reports
    49. IM0900 Inventory Management Reordering Advice Reports
    50. IM1000 Inventory Management Physical Count Processing
    51. Feature - Utility to Reset Item PTD/YTD Fields from Location History
    52. IM1100 Inventory Management Utilities Setup
    53. IM1201 Inventory Management Location File Maintenance
    54. IM1300 Inventory Management Recalculate Reorder Fields
    55. IM1400 Inventory Management Clear Item Accumulators
    56. IM1501 Inventory Management Product Category File Maintenance
    57. IM1601 Inventory Management Material Cost Type File Maintenance
    58. IM1701 Inventory Management Material Cost Type/Loc File Maintenance
    59. IM1801 Inventory Management Buyer/Analyst Code File Maintenance
    60. IM1901 Inventory Management Initialize Lifo/Fifo File
    61. IM1902 Inventory Management Adjust Item File to Lifo/Fifo
    62. IM1903 Inventory Management Serial/Lot Processing/Multi-bin Utilities
    63. IM1904 Inventory Management Serial/Lot Stock Status Inquiry
    64. Feature - Add Additional Record Types to Reorder Advise User-Defined CSV Export
    65. IM2500 Inventory Management Reset Allocated Quantities
    66. IM2700 Inventory Management Set Trx Audit File Beginning Balances
    67. IM2800 Inventory Management Freeze Inventory
    68. IM2900 Inventory Management Kit File Maintenance
    69. IM3301 Inventory Management Job Code File Maintenance
    70. IM3400 Inventory Management Job Analysis Report
    71. IMACTMNT Inventory Management I/M Account File Maintenance
    72. IMAGESCN Inventory Management Inventory Aging Report
    73. IMATPGSN Inventory Management Generate ATP File
    74. IMATPRSN Inventory Management Available to Promise Report
    75. IMBININV Inventory Management Bin Inventory File Maintenance
    76. IMBINMNT Inventory Management Bin File Maintenance
    77. IMBINTRN Inventory Management Inventory Transfer Processing
    78. IMCHGBIN Inventory Management Change Bin No/Pick Seq
    79. IMEXPTSN Inventory Management Export Item for Take an Order
    80. IMFRMMNT Inventory Management Item Label Form Setup
    81. IMGENMNU Inventory Management Generate Location History
    82. IMIMSITM Inventory Management Item Import Utility
    83. IMITMUDS Inventory Management Item User-Defined Fields Import Utility
    84. IMSUBMNT Inventory Management Substitute Item Class File Maintenance
    85. IMUSRMNT Inventory Management User-Defined Code File Maintenance
    86. IMVESMNT Inventory Management Vessel File Maintenance
    87. IMVLSMNT Inventory Management I/M Serial/Lot History File Maintenance
    88. Feature - Expanded Selection Parameters for Reorder Advise User Defined CSV Export
    89. Change - Update Both Kit Parent and Components Usage
    90. Feature - Create Price Code 1 from Item Minimum Price Utility
    91. Change - Available to Promise by Item Report - Add Item Description 2
    92. Feature - Support AR (Attribute) Type in Reorder Advise User Defined CSV Export
    93. Can System Default Item User-Defined Code?
    94. Feature - Item Label Printing Enhancements
    95. Feature - Ability to Initialize Character Fields During Change Existing Item Import
    96. Understanding Status Code at Inventory Transfer Dispatch Screen
    97. Feature - Support GTIN Based on Two Different UPC Codes
    98. Feature - Update Estimated Date/Time When Printing Transfer Tickets
    99. Feature - Allow Physical Count Tag Import Without Serial Number
    100. Feature - Delete Kit Components When Item Activity Code is Set to 'O'
    101. Feature - New QTYAVAILBYCOMP Attribute Fields
    102. Feature - Allow GTIN Maintenance in I/M Change Bin No/Pick Seq Processing
    103. Feature - Improvements to Reorder Advise Reports
    104. What Is the Difference Between INV_TRX_UN_COST and INV_TRX_NEW_AVG_COST in IMINVTRX Table?
    105. Feature - Added a Function "Create Inventory Trx CSV Import"
    106. Feature - Added Support to “Create I/M Trx CSV Import” for Kit Items
    107. Feature - Improvements to QTYAVAILBYCOMP and QTYAVAILBYCOMP2 Attributes
    108. Feature - Add Desc2 Column to Item Linkage CSV Report
    109. Feature - Numeric Item List Changes
    110. Feature - Added 1C and LD Column Types to Reordering Advise User Defined CSV Export
    111. What's the Difference Between Lead Time & Planning Lead Time?
    112. Explanation of ATP LP Type - Negative Sign (-) vs (*) Symbols
    113. IM0000 Inventory Management Package Overview 7
    114. IM0000 Inventory Management Package Overview 1
    115. IM0000 Inventory Management Package Overview 2
    116. IM0000 Inventory Management Package Overview 3
    117. IM0000 Inventory Management Package Overview 4
    118. IM0000 Inventory Management Package Overview 6
    119. IM0000 Inventory Management Package Overview 5
    120. IM0000 Inventory Management Package Overview 8
    121. IM0000 Inventory Management Package Overview: Index
    122. IM0101 Inventory Management Item File Maintenance 1
    123. IM0101 Inventory Management Item File Maintenance 2
    124. IM0101 Inventory Management Item File Maintenance 3
    125. IM0101 Inventory Management Item File Maintenance 4
    126. IM0101 Inventory Management Item File Maintenance 5
    127. IM0101 Inventory Management Item File Maintenance 6
    128. IM0101 Inventory Management Item File Maintenance 7
    129. IM0101 Inventory Management Item File Maintenance 8
    130. IM0101 Inventory Management Item File Maintenance: Index
    131. IM2400 Inventory Management Inventory Transaction Processing 1
    132. IM2400 Inventory Management Inventory Transaction Processing 2
    133. IM2400 Inventory Management Inventory Transaction Processing 3
    134. IM2400 Inventory Management Inventory Transaction Processing 4
    135. IM2400 Inventory Management Inventory Transaction Processing 5
    136. IM2400 Inventory Management Inventory Transaction Processing 6
    137. IM2400 Inventory Management Inventory Transaction Processing 7
    138. IM2400 Inventory Management Inventory Transaction Processing: Index
    139. How to Run Inventory Stock Status Report for a Certain Date
    140. Feature - Transition to Obsolete
    141. Item Last Received Date Not Updated When Received from I/M Transaction Processing
    142. Feature - Zero Item Weight When Using F3 to Copy Item
    143. Feature - Initialize User-Defined Note, Date and Amount Fields When Using F3 to Copy Item
    144. What Information Is Stored in the Location History (IMLOCHST) Table?
    145. Feature - Prevent Excel from Dropping Leading Zeroes in CSV Item Number Field
    146. Feature - Add User ID to User Defined CSV Export
    147. Feature - User Defined CSV Export Item Batch Support
    148. Feature - Export Inventory TRX Audit Trail to CSV
    149. Feature - Add User-Defined Code Table Support in IM User-Defined CSV Export
    150. Feature - Added “Item 1st Received Date Range” Criteria to Item User Defined CSV Export
    151. How to Get a List of Items That Does Not Have GTIN Code Defined
    152. Feature - User Defined CSV Export Quantity Sold by Customer#, Customer Type or Customer Group
    153. Feature - Added inKit (IK) and inBOMP (IB) Column Types to the User-Defined CSV Report
    154. Feature - Added Phantom Locking to Item File Maintenance
    155. Feature - Added CSV Support to the Physical Count Posting Update Edit List and the Variance Report By Item/Warehouse
    156. Feature - Inventory Transfer Management Batches Using CSV Import
    157. Limitations for Inventory Aging Report

    Feedback and Knowledge Base