October 22, 2014

How to Adjust Serial and Lot Tracking for Inventory in Sage 300 ERP

In this article, we take a look at how small and medium sized businesses track inventory with Sage 300 ERP (formerly Sage Accpac). Specifically, we will go through the process of adjust serial and lot tracking when there is a difference between inventory items on hand and those recorded in the system.
Inventory Control Systems for Small and Medium sized Businesses

Small and medium sized businesses more and more have come to use some kind of software inventory control system. In truth, the size of the business has little to do with the necessity of implementing an inventory control system. What does matter is the volume of items being purchased, shipped, received, tracked, and stored.

Companies that operate with a high volume of inventory turnover are wise to incorporate some type of electronic inventory tracking, whether the inventory is for manufacturing, finished products, or for distribution. The spread of ERP technology to the SMB market segment, like Sage 300 ERP (formerly Sage Accpac), has enabled businesses to rely on these systems’ inventory modules to track levels, locations, lots, changes, orders, and so much more.

When items are received, shipped, or stored, the physical activities of handling inventory are virtually recorded within the system. Each item received is accounted for and given a serial number. Items are ordered and received in lots, generally of one pre-determined size.

For example, a company buying a product from a supplier will do so in lots of 100 units. All units in the lot will share some identifying characteristic, like being received on the same day or manufactured in a single production run. Each unit within the lot will then have its own serial number.

Adjusting Variance in Serial/Lot Tracking

Companies usingSage 300 ERP (formerly Sage Accpac) may find that through the physical handling of inventory during receiving, usage, and storage, the numbers in the warehouse may not match the numbers in the system. This can happen at any time, but often it is noticed when companies do regularly scheduled inventory accounting. When that happens, there is a difference, or variance, in the quantities within lots in the systems, and in the serial numbers appointed the items.

When this happens, it is very easy for users to fix and update serial and lot tracking to match the real status of items in inventory. It consists of two steps:

1. Identifying the variance between serial/lot quantity and inventory at hand
2. Adjusting serial/lot quantity without affecting inventory on hand.

How to Check for Variance between Serial/Lot items versus actual inventory on hand

In a great tutorial video available at Sage City, the Sage 300 team explains how to do this procedure. We will cover the basic step-by-step here for serial items as well, and while the steps are mostly identical for lot number items, you should watch the video for details on that procedure. Note that for the exercises presented here, inventory on hand is assumed correct.

Check for Variance Between System and Inventory At Hand

To check whether there is a difference between lot and serial items in the system, as opposed to inventory at hand, users must first generate a report. To do that, go the Inventory Control>I/C Stock Control Report, within the main menu of Sage 300 ERP, and select the Serial/Lot Number Quantity On Hand Report.

This report generates either a list of lot items or serial items with matching quantities in each inventory location. This report allows you to filter the results by type (summary or detail), by location, or by item. The option is also available to isolate results for items with variance in inventory control.

Inventory Variance Report

In this report, we see that there are two items with variance between the serial number and actual quantity on hand.

Here you can see that for #NOKIA100 there are 97 serial numbers assigned, with only 95 items on hand, resulting in a variance of -2. This means that two items must be removed from the serial numbers in order for the accounting and tracking to be accurate. For S1-300/Y, there are fewer serial numbers assigned than there are items in inventory. This gives us the exact knowledge of what items needs adjustment.

Adjusting Serial and Lot Items in Sage 300 ERP – Serialized Items with Positive Variance to Inventory on Hand

Adjust Serial Items

To adjust serial items, go to Inventory Control>I/C Periodic Processing and go to Serial Lot Reconciliations.

There are four transaction types available from within the Serial/Lot Reconciliation screen. We will be looking at Receipt and Shipment. In essence, if there are more serial items than quantity in hand, we want the system to perform a shipment transaction to do away (ship) with the extra items. If there are less serial items, we need to receive them into the system.

In this adjustment exercise we will match the serial quantity to the quantity on hand. For item S1-300/Y, we will receive 4 additional serial numbers, a Receipt transaction type. First enter the item number, “S1-300/Y”, specify a location, enter a stock date (date of receipt), enter a quantity (in this case 4), and the total cost of all four items (according to the item’s cost).

The next step is to assign serial numbers to the items being received. You have two options: to enter the specific serial number you wish to assign, or to auto-generate a serial number. Using the auto-generate function, you will see that four serial numbers are generated into increments of one. When ready, click on the “Post” button.

Adjusting Serial and Lot Items in Sage 300 ERP – Serialized Items with Negative Variance to Inventory on Hand

For this case, we need to remove a total quantity of 2 serial items to match the total items on hand. In this case, we set the transaction type to “Shipment” which will enable us to take out the quantity of serials we need to adjust.

We again specify the item number (#NOKIA100), location, stock date, and quantity (in this case 2).

By clicking on the serial number heading, we are then prompted to select the serial numbers we want to remove, or in this case ship. Once the transaction is posted this serial numbers will be marked as not available.

The same steps can be applied when dealing with lot numbers, by following the specific instructions related to quantities of items in each lot.

Whether you are a small business receiving 10 parts without access to have bar codes scanners or a large enterprise receiving hundreds of items, you will likely need to adjust inventory at some point. Hopefully this helps those adjusting variance in inventory. For more helpful tips you can contact us or visit the Sage City community website.