Chapter 2d: Master files
Inventory Maintenance
Add, Change, Delete or Copy Inventory Items
Your Inventory master file will contain mostly permanent data regarding the merchandise you purchase or make, stock, and sell. In addition, the file contains historical sales information on each item. But the information you are concerned with when setting up an inventory record is the basic items such as the item number, description, unit of measure, and price along with information for purchasing and inventory management such as primary vendor, vendor's cost, minimum and maximum levels, and bin location.
The inventory data you collect and write on the data entry worksheets is entered into the system using the Inventory Maintenance program. Like all maintenance programs, you may add a new inventory record, or change or delete an existing record. Each inventory record requires a unique identifying code called the item number. It may be as many as 17 characters long. You should discuss with your systems analyst the maximum number of characters needed for any item. We cannot recommend a commonly used strategy for inventory numbering. Most companies categorize their merchandise and assign meaningful numbers within each category. Others simply use the manufacturer's number.
Before determining a numbering scheme it is important to understand a few facts about the computer system. First, all inventory reports may be printed in one of five sequences:
1) by item number
2) by product class code, then item number
3) by vendor number, then item number
4) by "generic" description, then item number
5) by item description
Similarly, items may be brought to the screen for display purposes using the Inventory Insight program, by requesting items in one of the above five ways.
Regardless of the chronological sequence in which items are added to the system, they are sorted into these five sequences. The system sorts any field from left to right using a standard "collating" method in which a space precedes a numeric digit, which precedes a letter.
Therefore, be aware of how the system will sort your numbering technique, and thus how items will list on reports. An example of how some numbers would be sequenced:
1 A101
10 A1B
101 A20
11 A201
2 AA2
3
30
301
An important topic to review before creating data entry worksheets for inventory is the technique for handling mixed units of measure for a single item. You should review the section on UNIT OF MEASURE codes in the Codes Maintenance instructions.
There are four unit-of-measure codes required:
1) STOCK/SELL: the base unit for stocking and selling to customers.
2) PRICE/COST: the base unit for unit prices, average cost, and labor and burden costs.
3) PURCHASE QTY: the base unit when placing a purchase order.
4) PURCHASE COST: the unit for the vendor's cost.
Because quantities such as ON HAND and SALES HISTORY are based on the STOCK/SELL unit of measure, and because average cost has been perpetually recomputed based on the PRICE/COST unit of measure, these unit of measure codes may not be changed once an inventory record has been set up. So, please take care determining the U/M codes you will be using. Discuss with your systems analyst if you have any questions.
Another important topic is the method of PRICING. Each inventory price record may contain up to nine prices. Each of the nine categories relates to a customer type, and each customer record contains a price category code. Thus a customer can be set up to receive the same level of the nine prices for each inventory item they might buy. This is customer category matrix pricing.
A customer may, in addition, receive a trade discount percentage off this price.
The price record may be established so that the nine prices represent a base price and up to eight quantity breaks. In this case the customer price category is disregarded.
Also, contract pricing may be used to set up special prices for certain customers grouped by contract code, or for individual customers.
Each price record has a price method--either standard prices, discount off list, or cost plus.
Finally, a note about UNIT COST. Your systems analyst has discussed our two available methods of costing--standard and weighted average--and has set up your system accordingly. You may input the unit cost when adding a new inventory record. From that point on, the operator can change the unit cost only by using the Physical Inventory or Inventory Adjustment programs.
The system perpetually recomputes the weighted average cost whenever merchandise is added to stock on hand--either by Purchase Order Receipts Update, Workorder Production Update, or by Inventory Adjustment Update. But only the Physical Inventory Entry will allow you to change the average cost to a specific value.
When you are initially setting up inventory records, you may leave the AVERAGE COST field equal to zero, then input the cost for each item when you load the starting ON HAND balances using the Physical Inventory programs.
You may wish to create one or more miscellaneous, non-stock inventory records. If the nature of your business leads you to sell items that you normally would not carry in stock, you must use a miscellaneous, or non-stock item number. It is advisable to create one such record for each product class you have set up, so that sales of such items will be accumulated into the correct class.
By placing an "NS" code in the MATERIAL CODE field in the inventory record, the system will respond differently when this item is added to an order. The system will function as if it were in the "change" mode, allowing input (override) of the true item number, description, price, and cost.
Inventory File Field Definitions
ITEM NUMBER
You may enter up to seventeen alpha or numeric characters to identify your inventory item.
WAREHOUSE
This field will appear only if there exists more than one warehouse. If your firm operates with only one warehouse you will never see this field, but if there is more than one warehouse, you will need to enter the warehouse code that this inventory item is related to.
1. DESCRIPTION 35 characters
The description prints on all inventory reports, pick slips, and invoices.
2. PRODUCT CLASS 2 character code
You will categorize you inventory into product classes or lines to get sales analysis reports summarized by class. The critical aspect is selecting the appropriate number of divisions, enough to isolate slow-moving and costly lines, but not so many as to be unmanageable. Usually, somewhere between six and forty classes do the trick. Give a fair amount of thought to this subject. Envision how product lines might change or grow, because sales analysis is naturally historical in nature, so moving items in and out of product classes, or trying to combine or split classes, or trying to combine or split classes will result in very misleading sales history.
Also, inventory reports nay be printed in product class sequence, grouping items by product class. And items may be retrieved and displayed by product class when using the Inventory Insight program.
3. MATERIAL CODE 2 character code
This code is used to categorize your inventory into general groups such as finished goods or raw materials, if you are a manufacturer. This facilitates perpetual inventory accounting for the two inventory accounts. Most inventory reports allow the operator to print only items with a matching material code.
Some examples of the codes that may be used are:
FG Finished goods
RM Raw materials
NS Non-stock
IL Inside labor
4. STOCK/SELL UNIT OF MEASURE 2 character code
Indicate the base unit for stocking and selling the item (refer to Codes file instructions).
PACK QTY 4.4 digits (9999.9999)
If you stock product in cartons, cases, boxes, dozen, pounds, feet, or any other unit that may be broken when sold, indicate the quantity in each stock/sell unit of measure. You have the opportunity in Sales Order Entry to break a pack (for example, a case of 24) and sell eaches. The pack quantity then is used for conversion so that the price extension and stock quantities will be calculated accurately.
If the stock/sell unit of measure cannot be broken, enter one (1).
5. PRICE/COST U/M 2 character code
Indicate the base unit for pricing and costing the item. It is not necessarily the same as the stock/sell U/M.
MULTIPLIER 4.4 digits (9999.9999)
The multipliers for each unit of measure are used to accurately and automatically convert between different units of measure. Refer to the Codes Maintenance instructions under code "N", unit of measure.
Enter the number of stock/sell units that make up one price/cost unit. For example:
Stock/Sell U/M EA (each)
Price/Cost U/M C (100) Mult 100.000
Purch Qty U/M
Purch Cost U/M
6. PURCHASE QTY U/M 2 character code
Indicate the base unit in which the primary vendor sells the item. What is the vendor's pack quantity?
MULTIPLIER 4.4 digits (9999.9999)
The multiplier is used to accurately and automatically convert between different units of measure. Refer to the Codes Maintenance instructions under code "N", unit of measure.
Enter the number of stock/sell units that make up one purchase quantity unit.
For example:
Stock/Sell U/M C (100)
Price/Cost U/M
Purch Qty U/M M (1000) Mult 10.000
Purch Cost U/M
7. PURCHASE COST U/M 2 character code
Indicate the base unit for the primary vendor's cost. How does he price this item?
MULTIPLIER 4.4 digits (9999.9999)
The multiplier is used to accurately and automatically convert between different units of measure. Refer to the Codes Maintenance instructions under code "N", unit of measure.
Enter the number of stock/sell units that make up one purchase cost unit.
For example:
Stock/Sell U/M FT (foot)
Price/Cost U/M
Purch Qty U/M
Purch Cost U/M RL (300 ft reel) 300.000
8. VENDOR NUMBER 6 characters
Indicate the primary vendor (supplier) of the item. Most inventory reports may be printed in vendor number sequence, and vendor grouping can aid with purchasing. If you purchase an item from more than one supplier, placing one vendor number here does not restrict you from doing so.
9. VENDOR'S ITEM NUMBER 19 characters
When you purchase an item, it is likely you will need to identify it on the purchase order using the vendor's item number. The Purchase Order Entry program accepts input of your item number, but then prints the vendor`s item number, if available, on the purchase order form.
10. VENDOR`S COST 5.3 digits (99999.999)
The vendor's cost represents the unit cost paid to purchase the item from the vendor. It does not include add-on charges (freight, insurance, brokerage fee, etc.) that make up the "landed" cost. When entering a new purchase order, this cost becomes the unit cost value for the item, but may be overridden for that individual order, The vendor's cost is represented based on the PURCHASE COST UNIT OF MEASURE.
The vendor's cost is not changed by the P/O Receipts Update when the item is received at a different cost. You must change it manually using this program.
11. This field is not used.
12. MINIMUM STOCK 6 digits (999999)
This is the reorder point, the level at which the Stock Status Report and the Commitment Report will indicate an immediate need to reorder or replenish the item to the maximum stock level. This is not a required field.
13. MAXIMUM STOCK 6 digits (999999)
This represents the stock level goal when reordering or replenishing this item, This is not a required field.
14. SAFETY STOCK 6 digits (999999)
This represents the quantity to be included as safety stock when the reorder point is calculated. This is not a required field.
15. LEAD TIME (DAYS) 3 digits (999)
This indicates the number of days between placing a purchase order for this item and its anticipated arrival, or, if manufactured, its estimated time to manufacture. This field is used by the Min/Max Inventory system to help calculate the minimum stock level.
16. TAX CODE 1 character
If an item is always taxable, even if the purchaser is a resale customer, enter "T" for taxable.
If an item is always non-taxable, even if the purchaser is a taxable customer, enter "N" for non-taxable.
If an item is taxable when the customer is a taxable customer and non-taxable when the customer is a resale customer, that is, when the tax is based strictly on the customer, leave this field blank.
17. GENERIC DESCRIPTION 6 characters
The generic description is designed to give you one additional means of accessing inventory records when you use the Inventory Insight program. Also, inventory reports may be printed in generic sequence. With six characters available, generic description can be an extension of the product class code. Or it may serve to hold catalog page and column number, or a manufacturer's product number, or substitute item number, or any other means of cross-referencing inventory records.
18. BIN LOCATION 3 characters
The Physical Inventory programs help you when it is necessary to perform a physical inventory count. A Physical Book may be printed which sorts the inventory records by warehouse and bin location before printing them on 8 1/2" by 11" paper to be used in the warehouse by your counters. Thus, the bin location in the computer can help put the items into the same sequence as they are physically stored in the warehouse.
You may choose to print pick slips in bin location sequence as well. This is not a required field.
19. ABC CATEGORY 1 character
Use this field to separate the inventory into tracking groups. "A" items are the top 15 to 20% of sales, "B" items the next 30%, and "C" items the bottom 50%. When you run Inventory Min/Max programs, you can segregate the calculation of minimum and maximum based on the ABC category. This is not a required field.
Use "D" to indicate a discontinued item;. A discontinued item cannot be added to a sales order or purchase order.
Use "E" through "Z" for categories that will NOT have Min/Max levels updated by the computer.
20. COMMODITY CODE 4 character code
This code is used in contract pricing. Inventory may be grouped by commodity for pricing. This field is mandatory for Electrical Wholesalers using the Trade Service updates.
21. ASSET ACCOUNT CODE
This code refers to the type of inventory the item is--Finished Good, Raw Material, Non-Stock, etc. The code you enter must already exist in the “B” codes of Codes Maintenance.
22. P&L ACCOUNT CODE
This code refers to the profitability of an inventory item. The code you enter must already exist in the “Z” codes of Codes Maintenance.
23. UNIT WEIGHT 3.2 digits (999.99)
This represents the weight (in decimal) of each unit of the item. When an order is entered, the total weight of the order, and invoice, is computed. This is not a required field.
24. COMMISSION PERCENT 2.2 digits (99.99)
You may choose to compute salesman commissions based on a product line or item number basis. If so, enter the commission percent for each item. For example, "12.50" is twelve and one-half percent.
If commissions are based on items and you do not want commission calculated for an item, enter "99.00". This indicates to the invoicing programs that no commission is desired. Leaving the field equal to zero means that the salesman's regular commission rate will be in effect for the item.
25. CASH DISC PCT 2.2 digits (99.99)
Each item may have a cash discount percent. Then, the invoice may be printed to show a cash discount for each line and a total cash discount available. Cash discount percents may be put in contract price records also. This is common for Electrical Wholesalers.
26. LIST PRICE 5.2 digits (99999.99)
This represents the suggested list price. It is used when price records exist that use the LIST MINUS PERCENT method of computing prices. Also, if no price records exist when an item is added to a sales order, then the list price is in effect.
27. UNIT COST / AVERAGE COST 5.3 digits (99999.999)
This represents the cost per unit. The unit cost of each item may be a weighted cost or a standard cost, based on your current accounting practices. If you use the average cost method, then the cost of goods sold when an item is invoiced is based on the average cost of the item at the time of invoicing. This cost is carried forward to all sales analysis reports.
The number to the right of this field represents the current value of the item number. Its formula is: unit/average cost x on-hand (field #35). Also, this number should always agree with the value on the Inventory Valuation report.
28. LABOR COST 5.3 digits (99999.999)
This is to be used only in a manufacturing environment. Finished good costs are the aggregate of material costs, using an average cost method, and labor and burden cost, using a standard cost method. Place the labor and burden standard cost in the inventory record of the finished good item.
29. BURDEN COST 5.3 digits (99999.999)
Refer to LABOR COST.
30. BEGINNING BALANCE
This represents the quantity on hand at the beginning of the month. It is updated by the Month End Update. Access to this field is not permitted to anyone other than a systems analyst.
31. MTD ISSUES
This represents the quantity issued (shipped and invoiced) during the current month. It is updated by the Sales Journal Update. Access to this field is not permitted to anyone other than a systems analyst.
32. MTD ADJUSTMENTS
This represents the quantity adjusted during the current month. It is updated by the Inventory Adjustments Update and Physical Inventory Update. Access to this field is not permitted to anyone other than a systems analyst.
33. MTD RECEIPTS
This represents the quantity received during the current month. It is updated by the P/O Receipts Update. Access to this field is not permitted to anyone other than a systems analyst.
34. MTD USED
This represents the quantity used in workorder production during the current month. It is updated by the Production Journal Update. Access to this field is not permitted to anyone other than a systems analyst.
35. ON HAND
This represents the quantity on hand at the current point in time. Several update programs change this field: Sales Journal, P/O Receipts, Inventory Adjustments, Physical Inventory, Production Journal. This number does not reflect open commitments to customers or quantities on vendor purchase order. The available field is calculated to take these into account.
AVAILABLE = ON HAND - COMMITTED - ON B/O + ON P/O
Access to this field is not permitted to anyone other than a systems analyst.
36. COMMITTED
This represents the quantity currently committed to open sales orders and open work orders. It changes as you add, change, or delete order lines using Sales Order Entry and Sales Journal Update, and/or the Work Order Entry and Production Journal Update. Access to this field is not permitted to anyone other than a systems analyst.