Chapter 2b: Master Files
M...MATERIAL TYPE
This code is located in the Inventory master file record (field #3). It helps to categorize the types of material in your inventory. Most inventory reports will segregate the inventory by the MATERIAL CODE. The report option will read "ONLY: Material Code" and the operator may specify only one code to be printed.
The code "NS" for non-stocked allows you to create "miscellaneous" item numbers so that non-stocked items can be sold and sales history can be captured. During Sales Order Entry, the system can detect if an item has an "NS" code, and it will then require input of additional information--ITEM NUMBER, DESCRIPTION, UNIT COST, and UNIT PRICE. One such miscellaneous item record should be created for each product classification.
Examples of valid codes:
Code Description Mat.Code Inv.Flag
FG Finished Goods 0.00 0.00
RM Raw Materials 4.00 1.00
NS Non-Stock 3.00 1.00
SV Service (No on-hand) 5.00 1.00
SA Sub-Assembly 1.00 0.00
LB Labor 0.00 1.00
OT Other 0.00 1.00
MS Miscellaneous 0.00 1.00
The “MS” for miscellaneous is a most helpful code. Use this code for any non-inventory item that you wish to issue a purchase order for; such as office supplies or computer equipment.
Note the columns titled “Mat.code” and “Inv.Flag”. These two columns play an important role in determining the cost that is used in production and whether or not inventory will be tracked.
The “Mat.Code” column determines which cost to use for the item in workorders. “0” uses the unit cost (average or standard, depending on what your system uses) and “1” uses the vendor cost.
The “Inv.Flag” column determines whether or not the item is tracked in inventory. “0” tells the item to be tracked in inventory. “1” tells the item to not be tracked in inventory.
N...UNIT OF MEASURE
This code is used in the Inventory master file record. It simply describes the basis for quantity, price, and cost measurement for each item. In your industry, perhaps most items are stocked and priced as eaches, while others are more conveniently dealt with in units of 100, or dozens, or gallons, or yards.
Although this seems quite straightforward, setting up the UNIT OF MEASURE codes can be very complex. This is due to the system's capability to mix units of measure for one item and to perform automatic conversions between different units of measure.
Each inventory master file record, each inventory item, has four units of measure. They are:
STOCK/SELL U/M the unit of stocking and selling
PRICE/COST U/M the unit for pricing and costing
PURCHASE QTY U/M the unit for purchasing quantity
PURCHASE COST U/M the unit for the vendor's cost
The best explanation about the interrelation of the four codes is by example.
You may have merchandise that is purchased by the roll, but you keep record of it, and sell it by the foot, but the industry normally prices by the yard:
STOCK/SELL = FT (foot)
PRICE/COST = YD (yard) multiplier = 3 ft/yd
PURCH QTY = R (roll) multiplier = 300 ft/roll
PURCH COST = R (roll) multiplier = 300 ft/roll
Or, your supplier sells cartons of twelve, you break the cartons and sell eaches:
STOCK/SELL = EA (each)
PRICE/COST = EA (each) multiplier = 1
PURCH QTY = DZ (dozen) multiplier = 12 ea/dz
PURCH COST = DZ (dozen) multiplier = 12 ea/dz
Or perhaps you stock eaches, but because unit prices are so small, you need to price by the hundred:
STOCK/SELL = EA (each)
PRICE/COST = C (100) multiplier = 100 ea/c
PURCH QTY = EA (each) multiplier = 1
PURCH COST = C (100) multiplier = 100 ea/c
In each case, the conversion to a common base unit is performed automatically by the system, if the conversion factors (multipliers) are set up correctly in the inventory master file. You can simplify the input of these multipliers in the inventory records by establishing common multipliers in the U/M codes records.
If your inventory has items with mixed units of measure, please review them with your systems analyst before Inventory worksheets are written. Unit of measure codes may not be changed in an inventory master file record.
Multiplier
Enter a numeric conversion factor (multiplier) relative to EA (each).
O...F.O.B.
This one-character code is required during Sales Order Entry and Purchase Order Entry, and simply helps define the terms of the transaction. F.O.B. (free on board) destination means that the seller of the merchandise bears the shipping costs and maintains ownership until the merchandise is delivered to the buyer. F.O.B. source, or shipping point, means that the buyer of the merchandise bears the shipping costs and acquires ownership at the point of shipment.
An eight-character description may be entered which then prints on pick slips, invoices, and purchase orders.
Examples:
Code Description
S Source
D Destination
P...PRODUCT CLASSES
This code is required in the Inventory master file record. It helps you categorize your inventory into product lines so that sales activity can be summarized and reported to you in a simple-to-use format. Also, most inventory reports will list items in product class sequence, often with subtotals by class. It is a two-character code with a twenty character description which will print on inventory reports to help identify product groups.
The key to establishing the product classes is to delineate the inventory enough to analyze separate lines, but to not create so many classes that it becomes unmanageable. Also, codes should be created so they will list in alphabetic sequence in a manner suited to your needs. Code your product lines in the sequence you want to see them.
A byproduct of the invoicing subsystem is the capturing of sales and profit by customer, salesman, and inventory item. An Inventory Sales History Report can summarize by the product classifications that exist at the time the report is printed. An Inventory Detail Sales Analysis Report summarizes by the product classes in effect at the time the invoices are updated. Therefore, changing product classes can make inventory sales analysis quite misleading. Give ample thought to devising product classes and product lines so that as your business changes, product classes may be added or discontinued, but will not have to be changed.
Examples:
Minimum Maximum
Code Description G/P% G/P%
10 Mens Apparel 15.00 55.00
12 Ladies Apparel 15.00 65.00
14 Boys Apparel 20.00 60.00
16 Girls Apparel 20.00 60.00
20 Downhill Equipment 30.00 60.00
30 X-Country Equipment 30.00 60.00
40 Racing Equipment 40.00 60.00
50 Accessories 10.00 50.00
60 Mountaineering 40.00 60.00
Minimum G/P percent
Enter a minimum allowable gross profit percent; for items in this product class. During Sales Order Entry, a warning message will appear if the calculated G/P% for an item is less than this minimum. Set this value to zero if no such warning message is desired.
G/P% = 100 * (net price - unit cost) / net price.
Maximum G/P percent
Enter a maximum allowable gross profit percent for items in this product class. A similar warning message appears during Sales Order Entry if the calculated G/P% for any time exceeds this maximum. Set this value to zero if no such warning message is desired.
Q...PRICE CATEGORIES
An inventory price record may contain as many as nine prices. Each customer may then be coded to automatically receive prices from a pre-set price level. By providing price category codes you can customize your system to better describe your pricing levels. This code is required for both the Inventory and Customer master file records. The code is a one-character code with an eight character description.
You must specify to which price level (1 through 9) each code applies.
Examples:
Price
Code Description Level
R RETAILER 1.00
D DEALER 2.00
W WHOLESLE 3.00
S SPECIAL 4.00
X future 5.00
Y future 6.00
Z future 7.00
C COSTPLUS 8.00
Description
The 8-character description appears on several screens that display customer category pricing, including the Inventory Insight screen.
Price level
Enter a price level (1 through 9). Do not use the same level number for more than one price code.
R...CUSTOMER CLASS
You may wish to classify your customers into groups so that you can analyze customer sales summarized by class. The Sorted Customer Listings; can provide this analysis.
The two-character code may have a 35-character description. It is found only in the Customer master file record.
Examples:
Code Description
DE DEPARTMENT STORES
SP SPECIALTY SHOPS
SG SPORTING GOODS STORES
DI DISCOUNT OUTLET
- or -
NE NORTHEAST
SE SOUTHEAST
MW MIDWEST
NW NORTHWEST
SW SOUTHWEST
NC NORTHERN CAL.
SC SOUTHERN CAL.
S...STATE
Very simply, this code validates the state code when customer and vendor records are created. The state code could be used when running the Sorted Customer Listings to summarize sales by territory.
T...DIVISION
Many firms require accounting records for divisions. GENESYS provides for this using the DIVISION CODE.
It is a two-character code that is required in the customer and vendor master file records. Each sales order and purchase order transaction contains a division code that defaults from the customer or vendor record. In this manner, sales (revenue) and cost of goods sold (expense) are automatically posted by division when invoices are updated. Purchases (expense) can be updated when purchase orders are received. And, with care, the operator can post vendor invoices (expense) to divisional expense accounts.
The financial statement format program allows you to create your own income statements (P&L's) for each division, any combination of divisions, or consolidated for the entire company.
A division code is brought to the sales order record when a customer is specified, and may be overridden for that transaction. Similarly, a division code is brought to the purchase order record when a vendor is specified.
The two-digit code should be used as a suffix to each of your revenue and expense accounts. Please discuss divisional accounting with your systems analyst before writing customer worksheets.
Examples:
Code Description
01 MEDICAL PRODUCTS DIVISION
02 SERVICE DIVISION
03 EQUIPMENT LEASING AND RENTAL DIVISION
The chart of accounts would then have:
4000 INCOME
400001 INCOME - MEDICAL
400002 INCOME - SERVICE
400003 INCOME - LEASING AND RENTAL
5000 COST OF GOODS SOLD
500001 COST OF GOODS SOLD - MEDICAL
500002 COST OF GOODS SOLD - SERVICE
500003 COST OF GOODS SOLD - LEASING AND RENTAL
5200 SELLING EXPENSE
520001 SELLING EXPENSE - MEDICAL
etc.
The absolute rule, however, is that every division must have the following general ledger accounts:
Account Description X-REF Code
Sales-Revenue SLSxxx01
Sales-Returns & Allowances RTNxxx01
Sales-Cash Discounts DSCxxx01
Sales-Writeoffs WROxxx01
Cost of Goods Sold CGSxxx01
The “01” in the cross-reference (X-REF) code represents the DIVISION CODE. The “xxx” represents the PROFIT & LOSS CODE, as is discussed further in this chapter.
If your company has two divisions, the necessary general ledger accounts would look like this:
Account Description X-REF Code
Sales-Revenue SLSxxx01
Sales-Revenue SLSxxx02
Sales-Returns & Allowances RTNxxx01
Sales-Returns & Allowances RTNxxx02
Sales-Cash Discounts DSCxxx01
Sales-Cash Discounts DSCxxx02
Sales-Writeoffs WROxxx01
Sales-Writeoffs WROxxx02
Cost of Goods Sold CGSxxx01
Cost of Goods Sold CGSxxx02
U...CUSTOMER TAX CODE
This two-character code designates the tax jurisdiction in which each customer is located. It is mandatory in the Customer master file record.
When an order is entered, the TAX CODE is added to the order when the customer is specified, and may be changed for any single transaction. Then when the order is confirmed, invoiced, and updated, two things happen: sales tax is calculated and added to the invoice, and general ledger credits are posted to the sales revenue and sales tax liability account.
Sales taxes may be posted to just one general ledger liability account, or they may be posted to individual county sales tax liability accounts, depending on whether or not you wish to see this detail on your general ledger. The SALES TAX REPORT, however, does provide both detailed and summarized information for monthly county, out-of-state and resale sales and the sales tax liabilities.
The link between the account numbers and the CUSTOMER TAX CODES is the cross-reference (XREF) code in the general ledger master file record which must be encoded as follows. You may enter a fifteen-character description and, if needed, a tax rate (percent):
Ex