We recently had a vendor decrease pricing (yes…you read that right…DECREASE!!!!). While this is wonderful to see, it has cause a little bit of a problem for us. We use a product call SmartConnect to import and update items and pricing. When our vendor sent us our updated price sheet, we imported the updates en masse, which caused our Standard Cost to decrease on many of the items. The problem with this is that we had products where their FIFO (FIFO is what we use…this would apply to LIFO as well) valuation was now higher than the Standard Cost. This means we are selling our product based on a cost that is actually less than we paid for it. This means lower margins or even losing money on the sell of the product.

For example:

We purchased 5 x widgets for $100.00. We have sold 3 x widgets for $125 based on a Standard Cost of $100. That is a 25% margin we just made on each widget. Now the price decrease comes in and our new Standard Cost is $90.00. We sell the next 2 widgets, based on a standard cost of $90 and 25% margin, for $120. Now if our actual costs were $90, then that would be a 25% margin as well, but remember we still have 2 x widgets in stock that we paid $100 for. Now our margin is only 20% ($100 cost and $120 sell = 20% margin).

The solution:
I have written a script that will compare our Standard Cost to our inventory valuation table to see if we have purchased something for more than we have our Standard Cost setup.

***This script assumes you are using Standard Cost as your calculation for setting margin/markup on your item. If you are not, then let me know and I will show you how to change to Current Cost.***

This script uses the following views:

Item Master (tspvItemMaster)

Inventory Receipts (tspvInventoryReceipts)

***SQL Script***

SELECT  Item_Class_Code,
        [OnHand] = Qty_Received – Qty_Sold,
        [CostDiff] = a.Standard_Cost – b.Unit_Cost
FROM    dbo.tspvItemMaster a
        LEFT JOIN dbo.tspvInventoryReceipts b ON a.Item_Number = b.Item_Number
WHERE   Qty_Received – Qty_Sold > 0                       
        AND ( a.Standard_Cost – b.Unit_Cost < -0.02 )   
        AND Standard_Cost <> ’0.00′                       
ORDER BY (a.Standard_Cost-b.Unit_Cost)