Skip to content

SQL Script – Standard Cost Less Than Valuation Cost

April 22, 2011

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,
        a.Item_Number,
        [OnHand] = Qty_Received – Qty_Sold,
        b.Unit_Cost,
        a.Standard_Cost,
        [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)

***Updated 4/22/2011***

The above script will return the true FIFO/LIFO layer.  So if you have 2 x widgets at a cost of $100 and 3 x widgets at a cost of $95 then you will see 2 results.  The first row showing how 2 x widgets with a unit_cost of $100 and then a second row showing 3 x widgets with a unit_cost of $95. 

The script below will give you the MAX FIFO/LIFO value found and only return 1 row for each item_number.  So the results would just show the widget (no quantities) with a unit_cost of $100 (because $100 is more than $95).

***SQL Script MAX***

Select  Item_Class_Code,
        Item_Number,
        Item_Description,
        Standard_Cost,
        [Unit_Cost]=( Select    MAX(b.Unit_Cost) AS ‘unit_cost’
          from      dbo.tspvInventoryReceipts b
          Where     b.Item_Number = a.Item_Number
                    AND ( Qty_Received – Qty_Sold > 0 )
          group by  b.Item_Number
        ),
        [Cost_Diff]=a.Standard_Cost-( Select    MAX(b.Unit_Cost) AS ‘unit_cost’
              from      dbo.tspvInventoryReceipts b
              Where     b.Item_Number = a.Item_Number
                        AND ( Qty_Received – Qty_Sold > 0 )
              group by  b.Item_Number
            )
from    dbo.tspvItemMaster a
WHERE   a.Item_Number NOT LIKE ‘*%’
        AND Item_Class_Code <> ‘used equip’
        AND ( Select    MAX(b.Unit_Cost) AS ‘unit_cost’
              from      dbo.tspvInventoryReceipts b
              Where     b.Item_Number = a.Item_Number
                        AND ( Qty_Received – Qty_Sold > 0 )
              group by  b.Item_Number
            ) IS NOT NULL
        AND a.Standard_Cost – ( Select  MAX(b.Unit_Cost) AS ‘unit_cost’
                                from    dbo.tspvInventoryReceipts b
                                Where   b.Item_Number = a.Item_Number
                                        AND ( Qty_Received – Qty_Sold > 0 )
                                group by b.Item_Number
                              ) < -0.02
ORDER BY Item_Class_Code, a.Item_Number

About these ads

From → Dynamics GP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: