Field Mapping–Inventory Item Import – 2 – Existing Stock Levels & Costs
Overview of the Existing Stock Details & Costs Spreadsheet Template
You may have 50,000 items, but you might only have 20,000 of those items in ‘stock’. The spreadsheet that you provide for this type of data will allow OneSource to know how many of each item you have in stock and what the cost is for each item. Note: You can list an item in this list MORE than 1 time if you want to track each individual shipment you have in stock, with its unique cost, which PO it is related to, where it is stored, etc. On the other hand, you can also just have 1 line for each part with the total qty of that item in stock and the average cost of those items.
Sample Spreadsheet Column Names and Values
Notes About Each Column (Field) Above
- ItemNumber: This field is required. This is the Item Number that links to the Item Number in the Inventory table.
- Cost: This is the ‘Cost’ of the item. This field is used by the General Ledger and various sales and profit reports. It is not required, but very important to list the cost. If this field is left blank you will NOT know if you made a profit when the part is sold.
- AddedCost: Suppose you bought a container of parts that cost $30,000 and you paid $5000 to get it to your dock. You might want to track the weighted cost in freight to get the item to your warehouse. This is sometimes also known as the ‘Landed Cost’. This field is not necessary but is helpful when prices are calculated using a markup above your cost.
- DateReceived: This is the date the shipment was received. If no date is entered, we will use the current date when we import the list.
- QtyInStockNow: This field is required. How many of these do you have in your warehouse now?
- PONumber: Do you know the PO # that this item was received from? If so list it here.
- SupplierNumber: If you have listed a PO # is the PONumber field do you know the Supplier Number? If so list it here.
- WarehouseCode: What is the name of the Warehouse Code you created in OneSource (on the Shipping Locations screen) for the warehouse this item is stored in?
- BinLocation: Which bin is the item # stored in your warehouse. The BinNumber field is in the Inventory List as well–which may be all that you need. However, if you store the same items in more than 1 location in your warehouse you may want to identify on this spreadsheet which BinLocation each item is stored in.
- LotNumber: This field can be used to store the Lot# for NSF Certification or the unique bin location where the part is stored. Note: If you have items in x locations you will need to have x records in this list to properly represent the location of all items.