Calculating Inventory Turns

  • Hello All,

    So I have been struggling with a way to perform inventory turn calculations in SQL. I'll start off with the formula. Basically the formula is Cost of Goods Sold over last 12 months / Average Inventory Value over the last 12 months.

    Short hand as COGS/AvgInventoryValue = Turns

    In order to obtain the COGS value I take the Avg(UnitCost) * Sum(InvoicedQty) in the last 12 Months for Sales documents. This is represented as DOCUMENTTYPE = 2

    Then divide AvgInventoryValue

    To obtain the AvgInventoryValue I need to know the Quantity purchased in the 12 month period * Avg(UnitCost) + QuantityOnHand at the start of the period * Avg(UnitCost)

    The purchased documents are represented with DOCUMENTTYPE = 6

    The purchases will be positive since we are putting value in. The sales are negative since value is going out.

    I'll start with a simple example where the product is at zero Quantity On Hand at the beginning of the period.

    --CREAT THE TEMP TABLE

    create table #VALUEENTRY

    (

    ITEMNO VARCHAR (50) NOT NULL

    , POSTINGDATEDATETIME NOT NULL

    , DOCUMENTTYPEINT NOT NULL

    , LOCATIONCODEVARCHAR (50) NOT NULL

    , INVOICEDQTYINT NOT NULL

    , UNITCOSTDecimal(38,10) NOT NULL

    , COSTDecimal(38,10) NOT NULL

    , DROPSHIP INT DEFAULT '0'

    )

    /*

    --IF NECESSARY DROP THE TABLE

    drop table #VALUEENTRY

    */

    --INSERT TEST DATA

    INSERT INTO #VALUEENTRY

    (

    ITEMNO, POSTINGDATE, DOCUMENTTYPE, LOCATIONCODE, INVOICEDQTY, UNITCOST, COST

    )

    VALUES

    ('MP120 SIEM1','2014-03-17','2','NJ','-15','2.09','-31.35')

    ,('MP120 SIEM1','2014-03-12','2','NJ','-20','2.09','-41.80')

    ,('MP120 SIEM1','2014-02-28','2','NJ','-1200','2.09','-2508')

    ,('MP120 SIEM1','2014-02-28','2','NJ','-96','2.09','-200.64')

    ,('MP120 SIEM1','2014-02-20','2','NJ','-300','2.09','-627')

    ,('MP120 SIEM1','2014-02-11','2','NJ','-600','2.09','-1254')

    ,('MP120 SIEM1','2014-02-10','2','NJ','-192','2.09','-401.28')

    ,('MP120 SIEM1','2014-01-24','2','NJ','-144','2.09','-300.96')

    ,('MP120 SIEM1','2014-01-15','2','NJ','-720','2.09','-1504.80')

    ,('MP120 SIEM1','2014-01-15','2','NJ','-480','2.09','-1003.20')

    ,('MP120 SIEM1','2014-01-15','2','NJ','-360','2.09','-752.40')

    ,('MP120 SIEM1','2014-01-09','2','NJ','-96','2.09','-200.64')

    ,('MP120 SIEM1','2013-12-27','2','NJ','-200','2.09','-418')

    ,('MP120 SIEM1','2013-12-13','2','NJ','-5052','2.09','-10558.68')

    ,('MP120 SIEM1','2013-12-12','2','NJ','-2000','2.09','-4180')

    ,('MP120 SIEM1','2013-12-06','2','NJ','-960','2.09','-2006.40')

    ,('MP120 SIEM1','2013-11-18','2','NJ','-960','2.09','-2006.40')

    ,('MP120 SIEM1','2013-11-15','2','NJ','-480','2.09','-1003.20')

    ,('MP120 SIEM1','2013-11-06','2','NJ','-24','2.09','-50.16')

    ,('MP120 SIEM1','2013-10-04','2','NJ','-60','2.09','-125.40')

    ,('MP120 SIEM1','2013-09-24','2','NJ','-3271','2.09','-6836.39')

    ,('MP120 SIEM1','2013-09-13','2','NJ','-48','2.09','-100.32')

    ,('MP120 SIEM1','2013-09-09','2','NJ','-144','2.09','-300.96')

    ,('MP120 SIEM1','2013-08-29','2','NJ','-288','2.09','-601.92')

    ,('MP120 SIEM1','2013-08-28','2','NJ','-288','2.09','-601.92')

    ,('MP120 SIEM1','2013-08-27','6','NJ','18000','2.09','37620')

    ,('MP120 SIEM1','2013-03-11','2','FL','-24','1.96','-47.04')

    ,('MP120 SIEM1','2013-03-07','6','FL','24','1.96','47.04')

    ,('MP120 SIEM1','2013-02-28','2','FL','-492','1.96','-964.32')

    ,('MP120 SIEM1','2013-02-07','6','FL','268','1.96','525.28')

    ,('MP120 SIEM1','2013-02-07','6','FL','17376','1.96','34056.96')

    ,('MP120 SIEM1','2013-02-05','2','FL','-268','1.96','-525.28')

    ,('MP120 SIEM1','2013-01-25','2','FL','-17376','-14.76','256469.76')

    ,('MP120 SIEM1','2013-01-25','2','FL','-17376','16.72','-290526.72')

    ,('MP120 SIEM1','2013-01-16','2','FL','-216','1.96','-423.36')

    ,('MP120 SIEM1','2013-01-16','2','FL','-240','1.96','-470.40')

    ,('MP120 SIEM1','2013-01-16','2','FL','0','1.96','-1881.60')

    ,('MP120 SIEM1','2013-01-16','2','FL','0','-14.76','70848')

    ,('MP120 SIEM1','2013-01-16','2','FL','-960','0','0')

    ,('MP120 SIEM1','2013-01-16','2','FL','-240','0','0')

    ,('MP120 SIEM1','2013-01-16','2','FL','-960','0','0')

    ,('MP120 SIEM1','2013-01-16','2','FL','-4800','16.72','-80256')

    ,('MP120 SIEM1','2013-01-15','2','FL','-1440','-14.76','21254.40')

    ,('MP120 SIEM1','2013-01-15','2','FL','-1440','16.72','-24076.80')

    ,('MP120 SIEM1','2013-01-15','6','FL','9108','1.96','17851')

    ,('MP120 SIEM1','2013-01-11','2','FL','-960','1.96','-1881.60')

    ,('MP120 SIEM1','2013-01-11','2','FL','-960','0','0')

    --SELECT ALL TO SEE DATA

    SELECT * FROM #VALUEENTRY

    --FIND TURNS IN A SIMPLE SCENARIO

    select ITEMNO, LOCATIONCODE

    , AVG(UNITCOST) as AvgUnitCost

    , Sum(INVOICEDQTY)

    - ABS((Select Sum(INVOICEDQTY)

    from #VALUEENTRY

    where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1)) as QtyOnHandInPeriod

    , ABS((Select Sum(INVOICEDQTY)

    from #VALUEENTRY

    where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1)) as QtySoldInPeriod

    , AVG(UNITCOST)

    * ABS((Select Sum(INVOICEDQTY)

    from #VALUEENTRY

    where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1)) as COGS

    , Sum(INVOICEDQTY)

    * AVG(UNITCOST)

    / (AVG(UNITCOST)

    * ABS((Select Sum(INVOICEDQTY)

    from #VALUEENTRY

    where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1))) as Turns

    from #VALUEENTRY

    where DOCUMENTTYPE IN(6) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1

    GROUP BY ITEMNO, LOCATIONCODE

    /*

    BASED ON THESE RESULTS THE NUMBER OF INVENTORY TURNS FOR THE ITEM WAS 1.000111

    THIS SOUNDS CORRECT

    WHEN I RUN THIS AGAINST THE ACTUAL TABLE I GET INNACCURATE RESULTS AND WHEN

    I LOOK AT THE SAME ITEM FROM THIS EXAMPLE THE TURNS RESULT IN 0.001445 WHICH MEANS

    IM NOT RUNNING THE QUERY IN A WAY THAT WILL GIVE ACCURATE CALCULATIONS

    PLEASE HELP

    */

    Is there another approach I should take to obtain the inventory turns?

    Also how do I account for periods earlier than 12 months where inventory is left over?

    In the sample data this item happened to be at zero inventory on hand at the start of the period.

    Also I believe it is my subquery's which are throwing the numbers off but I'm not sure how to isolate the subquery to just perform the result for a specific item. It is aggregating the entire table. How can I make the subquery more specific?

    Any help would be appreciated.

  • Hi

    You subqueries are returning the sum for all ITEMNOs and LOCATIONCODEs. I suspect you should be joining these to the outer query of ITEMNO and LOCATIONCODE.

    select ITEMNO, LOCATIONCODE

    , AVG(UNITCOST) as AvgUnitCost

    , Sum(INVOICEDQTY)

    - ABS((Select Sum(INVOICEDQTY)

    from #VALUEENTRY v1

    where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1

    and vm.ITEMNO = v1.ITEMNO and vm.LOCATIONCODE = v1.LOCATIONCODE)) as QtyOnHandInPeriod

    , ABS((Select Sum(INVOICEDQTY)

    from #VALUEENTRY v2

    where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1 and vm.ITEMNO = v2.ITEMNO

    and vm.LOCATIONCODE = v2.LOCATIONCODE)) as QtySoldInPeriod

    , AVG(UNITCOST)

    * ABS((Select Sum(INVOICEDQTY)

    from #VALUEENTRY v3

    where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE())

    and DROPSHIP <> 1 and vm.ITEMNO = v3.ITEMNO and vm.LOCATIONCODE = v3.LOCATIONCODE)) as COGS

    , Sum(INVOICEDQTY)

    * AVG(UNITCOST)

    / (AVG(UNITCOST)

    * ABS((Select Sum(INVOICEDQTY)

    from #VALUEENTRY v4

    where DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1

    and vm.ITEMNO = v4.ITEMNO and vm.LOCATIONCODE = v4.LOCATIONCODE))) as Turns

    from #VALUEENTRY vm

    where DOCUMENTTYPE IN(6) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1

    GROUP BY ITEMNO, LOCATIONCODE

    You could also use a CTE query to make it easier to read

    WITH SumInvoiceQty AS (

    SELECT ITEMNO, LOCATIONCODE, SUM(INVOICEDQTY) SumInvoiceQty

    FROM #VALUEENTRY

    WHERE DOCUMENTTYPE IN(2) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1

    GROUP BY ITEMNO, LOCATIONCODE

    ),

    ItemStats AS (

    SELECT ITEMNO, LOCATIONCODE

    ,AVG(UNITCOST) as AvgUnitCost

    ,Sum(INVOICEDQTY) as SumInvoiceQty

    FROM #VALUEENTRY v

    WHERE DOCUMENTTYPE IN(6) and POSTINGDATE between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and DROPSHIP <> 1

    GROUP BY ITEMNO, LOCATIONCODE

    )

    SELECT s.ITEMNO, s.LOCATIONCODE

    ,s.AvgUnitCost

    ,s.SumInvoiceQty - ABS(q.SumInvoiceQty) as QtyOnHandInPeriod

    ,ABS(q.SumInvoiceQty) as QtySoldInPeriod

    ,AvgUnitCost * ABS(q.SumInvoiceQty) as COGS

    ,s.SumInvoiceQty * s.AvgUnitCost / (AvgUnitCost * ABS(q.SumInvoiceQty)) as Turns

    FROM ItemStats s

    INNER JOIN SumInvoiceQty q ON s.ITEMNO = q.ITEMNO and s.LOCATIONCODE = q.LOCATIONCODE;

    Hope this helps

  • this worked well. Thanks for the assistance!

    here is the final script. I'll be adding more onto it but this was a good start.

    WITH SumInvoiceQty AS

    (

    SELECT [Item No_], [Location Code], SUM([Invoiced Quantity]) SumInvoiceQty

    FROM [50LIVE].[dbo].[Live$Value Entry]

    WHERE [Document Type] IN(2) and [Posting Date] between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and [Drop Shipment] <> 1

    GROUP BY [Item No_], [Location Code]

    ),

    ItemStats AS

    (

    SELECT [Item No_], [Location Code]

    ,AVG([Cost per Unit]) as AvgCostperUnit

    ,Sum([Invoiced Quantity]) as SumInvoiceQty

    FROM [50LIVE].[dbo].[Live$Value Entry] v

    WHERE [Document Type] IN(6) and [Posting Date] between DATEADD(year,-1,GETDATE()) and DATEADD(year,0,GETDATE()) and [Drop Shipment] <> 1

    GROUP BY [Item No_], [Location Code]

    )

    /*******************************************************************************************/

    SELECT s.[Item No_], s.[Location Code]

    ,s.AvgCostperUnit

    ,s.SumInvoiceQty - ABS(q.SumInvoiceQty) as QtyOnHandInPeriod

    ,ABS(q.SumInvoiceQty) as QtySoldInPeriod

    ,AvgCostperUnit * ABS(q.SumInvoiceQty) as COGS

    ,s.SumInvoiceQty * s.AvgCostperUnit / NULLIF((AvgCostperUnit * ABS(q.SumInvoiceQty)),0) as Turns

    FROM ItemStats s

    INNER JOIN SumInvoiceQty q ON s.[Item No_] = q.[Item No_] and s.[Location Code] = q.[Location Code]

  • You're welcome

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply