We are at the end of the year and many of you are preparing or already processing physical counts. If you are familiar with this process in Sage 500 you have already noticed that unit cost field is not available in any of the forms in this task. This is because the unit cost is calculated based on the item’s valuation method and a setting in Set Up IM Options form. So here is the logic:

For all valuation methods (except Standard, see below) when adding (doing positive adjustment) inventory on hand Sage 500 will look into Adjustment Cost Method in Set Up IM Options, go to Maintain Inventory record and read the value for that particular unit cost. For example if you are counting an item using Average cost method and Adjustment Cost Method in Set Up IM Options is set to be ‘Last’ unit cost Sage 500 will look for that item and warehouse Last unit cost that is displayed in Maintain Inventory form and with that unit cost calculate transaction amount and ultimately update the cost tier with the same value.

Here is the exact break down by each cost method: 

For Average costed item positive adjustment as explained above will use the unit cost defined by Adjustment Cost Method in Set Up IM Options and then average with the existing value of the tier to arrive with the new unit cost. This is because Average costed items have only one open cost tier at the time. For the negative adjustments Sage 500 will simply deplete the existing cost tier using the existing Average unit cost.

For items using FIFO/LIFO costing methods and positive adjustments  Sage 500 will also use the unit cost defined by Adjustment Cost Method in Set Up IM Options and then create a new cost tier. For the negative adjustments Sage 500 will relieve the oldest cost tier for FIFO items and calculate the unit cost based on the value and quantity on hand for that tier. And for the negative adjustments Sage 500 will relieve the youngest cost tier for LIFO items and calculate the unit cost based on the value and quantity on hand for that tier.

For items using Actual cost method and positive adjustments  Sage 500 will also use the unit cost defined by Adjustment Cost Method in Set Up IM Options. There will be one cost tier for each serial number when item is tracked by serial number, and there will be one cost tier for each lot when item is tracked by lot number only. So for example if you are using physical count to add 100 serialized items using Actual valuation method, then there will be 100 new open cost tiers. For the negative adjustments Sage 500 will use the unit cost associated with serial or lot number that is being counted. You can find out this unit cost by running Inventory Cost report and searching for serial or lot number but before you post your physical count batch. 

For Standard costed items there is only one cost tier and for positive or negative adjustments Sage 500 will always use the Standard unit cost of the item (displayed in Maintain Inventory). When the quantity on hand  in the cost tier becomes negative the unit cost will be determined based on the Negative Tier Cost Method as explained in the next segment. 

If ‘Allow Neg Qty on Hand’ is selected in Set Up IM Options, then the Negative Tier Cost Method must be selected.  The selections are:  Average, Landed, Last, Replacement and Standard. This will determine your unit cost when physical count is depleting the quantities on hand below zero. If the item count is going from positive quantities on hand into negatives Sage 500 will use the unit cost from the open positive tier and close it, and then open negative tier using Negative Tier Cost Method. However the physical count transaction will show a unit cost that is calculated by  summing the transaction amount of depleted positive tier and just created negative tier, and then dividing it by the entire negative adjustment quantity.

Also important to notice is that for Lot/Serial/Both tracked items, negative cost tier cannot be created irrespective of IM Options.