IM0101 Inventory Management Item File Maintenance 2

Forecasting

Forecasting accurately how much of an item you are going to need at some point in the future can be very important to a company's profits.  If the forecast for an item is too high, too much of it may be produced and then have to be kept in inventory until it is consumed.  This ties up capital in the inventory investment, and costs you further money in carrying costs.  But if the forecast turns out to be too low, and customer demand exceeds the amount of the item you have produced, you can again lose money.

No one, so far, has come up with a sure‑fire technique for forecasting inventory usage.  Some of the techniques which have been developed are almost beyond the grasp of most college graduates (unless they majored in math), and these very sophisticated systems of prediction usually require more accuracy in the data input to them than can be easily obtained.

Elliott's Inventory Management package uses an easy to understand method of forecasting the next period's usage of an item based on the item's selling history.  The technique has the rather scientific sounding name of exponential smoothing, but is basically pretty simple.  It uses a weighted moving average to calculate next period's expected usage level for an item.  Let us take a few examples to illustrate how this calculation works.  We will assume that the forecasting period is a month.  This first example will use six month's worth of sales history.

Figure 1  

     65   50   55   70   50  40       Average Usage = 333 = 55

                                                       6

Figure 2

     50   55   70   50  40  155       Average Usage = 420 = 70

                                                       6


In Figure 1, we have a diagram showing the usage of an item for six prior months.  The average usage for these six months is 55.  Then in Figure 2, we have dropped the usage for month number 1, and added the usage for the current month, month number 7, to the end, and recalculated a new average usage for the item.  This new average usage is our forecast for next month.  As you can see, the suddenly higher usage in month 7 caused the average usage to increase quite a bit over the previous average usage.

 Here is another example of this technique using a longer period of sales history, 12 months.

Figure 3

     65  50  55  70  50  40  35  60  70  50  65   50

               Average Usage = 660 = 55

                               12


Figure 4 

     50  55  70  50  40  35  60  70  50  65  50  155

               Average Usage = 750 = 63

                               12

 

In Figure 3, we have 12 months of sales history, again with an average usage of 55.  In Figure 4, the usage for month 1 has been dropped, and the usage for the month just ended has been added.  Even though the usage for the most recent month jumped to 155, as in the previous example, the new average usage is only 63.  As you use a longer period of sales history, a sudden increase or decrease in any one month will be dampened more than it is dampened when you only use a few months of history.  In other words, the new forecast is more responsive to sudden increases and decreases in usage when only a few months of history are used, whereas, a more stable forecast which does not fluctuate as much is obtained by using a longer period of sales history.

 As it turns out, you do not actually have to have this sales history available to the program in order to do this calculation of the new forecast.  All you have to do is specify how much weight to place on the usage figures for the prior period.  This can be done using this table.

            Periods of             Usage Weighing

          Sales History                 Factor    

                3                        .50

                6                        .29

                9                        .20

               12                        .15

               18                        .11

 

As you can see from this table, as you use a larger number of periods in sales history, the importance placed on this period's usage decreases.

You specify the usage-weighing factor for each inventory item as part of Item File.

For high volume items whose sales can be very volatile, you may want to use a short period of sales history, so that new forecasts are very responsive to the current demand for the product.  For other items, you may want to use a long sales history period, so that forecasts do not fluctuate as much as sales fluctuate.

This new forecast is calculated whenever you run the Recalculate Reorder Fields application.  Further detail on the actual calculations can be found in the Recalculate Reorder Fields section of this manual.

 

Safety Stock

Safety Stock is the quantity of an item to be kept on‑hand in case of sudden demand. It serves to cushion your inventory against increases beyond your ability to meet an unanticipated demand for the item. Initially, you should decide how much safety stock you should keep on hand for each item.  Later on, each time you run the Recalculate Reorder Fields application, the optimum value of the Safety Stock field will be recalculated.

This calculation is based on how far the forecast is deviating from the actual usage.  If the forecast is consistently running lower than the actual usage (i.e., usage is exceeding the forecast) the amount of safety stock to keep on‑hand will increase.

 

ABC Analysis

 An ABC Analysis can be a useful tool for categorizing your inventory items.  It is based on the general principle that a small percentage (about 15‑20%) of your inventory items will be found to produce a large percentage (about 70‑80%) of your income (Category A items), a larger portion of your inventory items (about 30‑40%) will be found to produce about 15‑20% of your income, and that the remaining 40‑60% of your inventory items will account for only the remaining 5‑10% of income.

     Usage in Dollars         Inventory Items     Class

          70‑80%                   15‑20%           A

          15‑20%                   30‑40%           B

           5‑10%                   40‑60%           C

 

You may find it of great benefit to tightly control the inventory levels of the relatively few Class A items, since these account for a higher percentage of activity and bring in a higher proportion of your income.  On the other hand, those items which are relatively low‑activity items can be managed by a looser Inventory Management package on a more casual basis.

When you first set up the Inventory Item File, you may not have a breakdown of these categories available.  If not, you can leave the ABC Analysis code (called the Inventory Class code on screen 3 of the Item File) blank.  Then later, after some sales history has accumulated for your inventory items, run the Print ABC Analysis Report application.  After the report has been run, and you are satisfied with the results obtained, you may have the program go through the inventory items, setting their inventory class.

 

Stocked vs. Non‑Stocked, Controlled vs. Non‑Controlled

   There are two fields in the Inventory Item record, which will be discussed here, the Stocked Flag and the Controlled Flag.

The stocked flag can have one of two values, either Y = Stocked, or N = Non‑Stocked.

A stocked item is one, which you plan to keep on the shelf either for sales to customers or for use in your manufacturing plan.  A non‑stocked item is one which is never kept on the shelf as a finished end item, ready for sale or use in the plant, even though its components may be kept on stock at all times.  A non‑stocked item may be manufactured or assembled to customer order, and so is not kept on the shelf itself.

The controlled flag can have one of two values, either Y = Controlled or N = Non‑Controlled.

 A controlled item has its quantity in inventory allocated when a customer order or shop order is issued which requires a quantity of the item, and this quantity of the item is de‑allocated when the customer order is shipped or the materials are issued to the shop.  This allocation and de‑allocation does not occur for a non‑controlled item.

Before covering how these fields are used by the other Manufacturing packages, let us take a few examples of items, which illustrate the possible combinations, which can occur using these two fields.  We will use a company, which manufactures bicycles for the example: 

1.    A stocked and controlled item. This would be an item, which is kept in stock and which is allocated when ordered, and de‑allocated when used.  An example of this would be the handlebars for the bicycle. It may be the company's policy to always keep these handlebars in stock, ready for issue when needed for assembly. But it is also important to know how much of the quantity on‑hand has already been allocated to orders, which currently exist.  Thus the handlebars are a controlled item as well.

2.    A stocked but non‑controlled item.  This would be an item which is always kept in inventory, but which is not allocated or de‑allocated by the processing of orders.  An example of this might be the nuts used for holding parts of the bicycle together.  These nuts are usually made available in boxes in appropriate areas of the shop, and they are used as needed.  They are replenished when a visual review or a two‑bin system shows that there is a need.

3.    A non‑stocked but controlled item.  This item is not kept in stock for regular orders but instead is purchased or manufactured for a particular customer order.  But once it is made, it is definitely controlled.  An example of this might be a particular seat assembly that is made for one particular customer.  Once it is made, you definitely want to have its use controlled.

4.    A non‑stocked and non‑controlled item.  This might be an item, which only exists as a temporary sub‑assembly at some point in the assembly procedure, such as a particular gear assembly.  This gear assembly might have engineering drawings associated with it, and the company may want to be able to determine how many of them have been made, even though the item never goes into stock and is not allocated or de‑allocated.  This type of item is often referred to as a phantom subassembly.

An understanding of these terms can be important if you plan to use the Customer Order Processing package, or any of the other Elliott packages which use Bill of Material Processor.  For example, when a customer orders a part which is non‑stocked but controlled, and for which a Bill of Material exists, the item itself is allocated.  The program then explodes through the Bill of Material and allocates those components, which are stocked, if the components are also controlled.

 

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
  158. Feature - ATP Inquiry Number of Days from Today
  159. Feature - Added Report to "Set TRX Audit File Beginning Balances"
  160. Feature - Added Product Category Selection Range to ABC Analysis Reports
  161. Feature - Added Days Out of Stock (DOS) Column to the Location History Information, Sales Desk Info - Screen
  162. Feature - Show Turn Over Ratio (TOR) in Stock Status Inquiry
  163. Feature - Added Browse Through Items When in the Stock Status Inquiry
  164. Why Isn't Planning Lead Time Based on Work Days?

Feedback and Knowledge Base