Hi there. I often talk to our customers about inventory costing and thought many of you would find this document helpful when trying to figure out how the cost for an inventory transaction was determined. So here it is:
A cost tier stores the cost at which an item was added into the inventory and its quantity. The cost of an item is divided into four factors – Direct Cost, Freight Cost, Sales Tax Cost and Other Cost. These costs are the actual costs paid and not the estimated costs.
The quantities of an item in a cost tier (timCostTier table) are tracked in two ‘buckets’ – Original Quantity and Quantity Used. The cost and quantities in a cost tier are tracked by the system for every IM transaction. IM posting updates the cost tiers when the IM transactions or any other module transactions that affect inventory are posted. With SO shipments this will take place at the time of commit and with MF transactions this is a real time update when Material Issue or a progress step transaction is entered.
Cost Tiers are created and relieved based on the items Valuation Method in Maintain Items. Once it is selected and the item record is saved, the valuation method cannot be changed. The following valuation methods are available to use in Sage 500 ERP:
Standard – the value of an item is determined based on the amount in the Standard Cost field in Maintain Inventory.
Average – it calculated by adding the costs of purchased items to the value of stock on hand. The sum is divided by the total number of items.
LIFO – items are received and issued at their actual cost. The newest items are issued first.
FIFO - items are received and issued at their actual cost. The oldest items are issued first.
Actual – uses the actual cost of each lot/serial item as it is received in inventory.
There is only one negative cost tier per inventory item (Except Standard).
In Standard, there is only 1 cost tier.
In Average, there is 1 positive cost tier and possibly 1 negative cost tier, if negative inventory is allowed.
In FIFO/LIFO, there is 1 cost tier created for every purchase made by date, so there can be multiple Cost Tiers.
In Actual, there is one cost tier created for every purchase and for serial tracked items, there is one cost tier for each serial number. For example, if there are 1000 Serialized items on hand, then there are 1000 cost tiers open.
For Lot/Serial/Both tracked item, negative cost tier cannot be created irrespective of IM Options.
For <none> tracked items, negative cost tier can only be created when ‘Allow Neg Qty in Hand’ is selected in Set Up IM Options.
If ‘Allow Neg Qty on Hand’ is selected in Set Up IM Options, then the Neg Tier Cost Method must be selected.
Positive Adjustments use the Adjustment Cost Method selected in Set UP IM Options
If ‘Allow Neg Qty on Hand’ is not selected in Set Up IM Options, then an item cannot have a negative quantity on hand. Posting will fail stating ‘Error Creating Cost Tiers’ or ‘Insufficient Quantity on Hand’.
Adjustments to inventory value are made through Cost Tier Adjustments. Inventory Quantity cannot be adjusted through Cost Tier Adjustments.
A row exists in timCostTier for each ‘Cost Tier’. This includes Open and Closed cost tiers. A cost tier is closed when FIFO, LIFO or Actual valuation method is used and the inventory quantity in the tier has been fully relieved. Once the status of a Cost Tier is Closed, the cost tier cannot be adjusted.
One of the most useful tools in troubleshooting IM Costing issues, is the Inventory Cost Report. The inventory cost report is a direct view into the timCostTier table. Select to run by Valuation Basis and select to Include Cost Tiers.
Three Step Transfer Cost Tiers
Cost tier information is created and used differently in transit warehouses. In the case of inventory with an ‘actual’ valuation method, the cost tiers are moved over to the receiving warehouse individually. In other cases, the entire costs for the shipped inventory are averaged, with a weighted average, and one averaged cost tier is created for the transit warehouse. The receiving warehouse will have new cost tiers created upon three step transfer receipt according to the normal inventory valuation rules for the inventory in the receiving warehouse.