15 Replies Latest reply: Apr 30, 2014 2:32 AM by Stefan Wühl RSS

    Inventory Reconciliation

    David Young

      I was wondering if anyone has done any Inventory Reconciliation Applications that can share how they did it.  Here is an example of my data set:

       

      Beginning Balance 30
      Item NumberTransDateTransTypeQTYTotal_QTY_onHand
      1231/1/2012Receiving535
      1231/12/2012Issue1025
      1231/23/2012Receiving530
      1231/31/2012Receiving2050
      1232/5/2012Issue545
      1232/8/2012Issue1520
      1232/14/2012Issue525
      1232/28/2012Receiving1035
      1233/1/2012Receiving2055
      1233/15/2012Receiving1065
      1233/23/2012Issue560
      1233/30/2012Issue560

      I have done the math for demonstration purposes.but Recieving's increase and Issues decrease my inventory qty.  What I would like to do is have a date variable, lets say for demo purposes 2/8/2012.  The Total_QTY_onhand for the above example would be 20.  I am trying to determine the best way to accomplish this and I am open to suggestions.  Let me know if there are any questions.

       

      David

        • Re: Inventory Reconciliation
          Stefan Wühl

          I am not sure what the best way would be, because the best way depends on your total requirements.

           

          One way would be to calculate the Total QTY in the script, maybe like

           

          SET DateFormat = 'M/D/YYYY';

           

          Let vBeginBalance =30;


          LOAD *, Rangesum(if(recno()=1,$(vBeginBalance),peek(TotalQTY)),DiffQTY) as TotalQTY;

          LOAD *, if(TransType='Receiving',QTY,-QTY) as DiffQTY;

          LOAD * INLINE [

          Item Number, TransDate, TransType, QTY, Total_QTY_onHand

          123, 1/1/2012, Receiving, 5, 35

          123, 1/12/2012, Issue, 10, 25

          123, 1/23/2012, Receiving, 5, 30

          123, 1/31/2012, Receiving, 20, 50

          123, 2/5/2012, Issue, 5, 45,

          123, 2/8/2012, Issue, 15, 20,

          123, 2/14/2012, Issue, 5, 25,

          123, 2/28/2012, Receiving, 10, 35

          123, 3/1/2012, Receiving, 20, 55

          123, 3/15/2012, Receiving, 10, 65

          123, 3/23/2012, Issue, 5, 60

          123, 3/30/2012, Issue, 5, 60

          ];

           

          You will notice a small difference to your numbers, but I think QV is doing the math quite well..

            • Re: Inventory Reconciliation
              David Young

              The requirement is to select a specific date.  Upon selecting that date we want a list of all of our Inventory Items, QTY, Cost per Item, and Over-all Price of Inventory on hand.  So the only way I thought was to start with the beginning balance of all the items.  Pick a date.  Once the date is selected add all the receiving transactions through that date and add it beginning balance.  Then take that number and subtract the total number of issue transactions to get your on hand qty on the date you selected.  You would need to do it on a item by item basis and then run some totals.  Does this make sense?

            • Re: Inventory Reconciliation
              Massimo Grossi

              SET DateFormat='MM/DD/YYYY';

               

              source:

              load 30 as BeginningBalance AutoGenerate 1;

               

              join (source)

              LOAD * INLINE [

              Item Number, TransDate, TransType, QTY, Total_QTY_onHand

              123, 1/1/2012, Receiving, 5, 35

              123, 1/12/2012, Issue, 10, 25

              123, 1/23/2012, Receiving, 5, 30

              123, 1/31/2012, Receiving, 20, 50

              123, 2/5/2012, Issue, 5, 45,

              123, 2/8/2012, Issue, 15, 20,

              123, 2/14/2012, Issue, 5, 25,

              123, 2/28/2012, Receiving, 10, 35

              123, 3/1/2012, Receiving, 20, 55

              123, 3/15/2012, Receiving, 10, 65

              123, 3/23/2012, Issue, 5, 60

              123, 3/30/2012, Issue, 5, 60

              ];

               

              final:

              load

                *, 

                if(peek([Item Number])=[Item Number],

                peek(Total_QTY) + QTY * if(TransType='Receiving',1,-1),

                BeginningBalance + QTY * if(TransType='Receiving',1,-1)

                ) as Total_QTY

              Resident source

              order by TransDate;

               

              DROP field BeginningBalance;

              DROP Table source;

              • Re: Inventory Reconciliation
                Stefan Wühl

                Sure, makes sense.

                 

                One question is if you need to calculate the total qty on hand selection specific or if this is somewhat static (what I assume right now).

                 

                If it's static, you can calculate it in the script, similar to what I shown above (essentially, create an input table ordered by Item and TransDate, then use peek() function to access values from the previous output table record (for the accumulation over time) and to check for item change (like Massimo showed in his post).

                  • Re: Inventory Reconciliation
                    David Young

                    The problem I see is that each item will have a different beginning balance.  What I would like to do is to put a variable in as the date for example 2/14/2012.  From there I would like the following:

                     

                    Item Number     Total QTY on Hand      Total Value on Hand

                     

                     

                    The only way I see to do it is to take the beginning balance of each item.  Add all the receiving transactions.  Subtract all the issue transactions.  Once that is done you can get your on hand qty.  I am not sure how to accomplish this with the example above since it shows beginning balance of 30.  I am sure there will need to be some grouping done.

                      • Re: Inventory Reconciliation
                        Stefan Wühl

                        Right, just create a table in your load with the beginning balances, then either join or look up the values when needed:

                         

                        SET DateFormat = 'M/D/YYYY';

                         

                        BeginBalanceTable:

                        LOAD * INLINE [

                        ItemNumber, BeginBalance

                        123,30

                        124,15

                        ];

                         

                         

                        Transactions:

                        LOAD * INLINE [

                        Item Number, TransDate, TransType, QTY, Total_QTY_onHand

                        123, 1/1/2012, Receiving, 5, 35

                        123, 1/12/2012, Issue, 10, 25

                        123, 1/23/2012, Receiving, 5, 30

                        123, 1/31/2012, Receiving, 20, 50

                        123, 2/5/2012, Issue, 5, 45,

                        123, 2/8/2012, Issue, 15, 20,

                        123, 2/14/2012, Issue, 5, 25,

                        123, 2/28/2012, Receiving, 10, 35

                        123, 3/1/2012, Receiving, 20, 55

                        123, 3/15/2012, Receiving, 10, 65

                        123, 3/23/2012, Issue, 5, 60

                        123, 3/30/2012, Issue, 5, 60

                        124, 1/12/2012, Issue, 10, 25

                        124, 1/23/2012, Receiving, 5, 30

                        124, 1/31/2012, Receiving, 20, 50

                        124, 2/5/2012, Issue, 5, 45,

                        124, 2/8/2012, Issue, 15, 20,

                        124, 2/14/2012, Issue, 5, 25,

                        124, 3/1/2012, Receiving, 20, 55

                        124, 3/15/2012, Receiving, 10, 65

                        124, 3/30/2012, Issue, 5, 60

                        ];

                         

                        LOAD *,

                          Rangesum(

                          if(peek([Item Number]) <> [Item Number],

                          lookup('BeginBalance', 'ItemNumber', [Item Number], 'BeginBalanceTable'),

                          peek(TotalQTY))

                          ,DiffQTY) as TotalQTY;

                        LOAD *, if(TransType='Receiving',QTY,-QTY) as DiffQTY

                        Resident Transactions

                        order by [Item Number], TransDate;

                         

                         

                        drop table Transactions, BeginBalanceTable;

                    • Re: Inventory Reconciliation
                      Prem Kumar Thangallapally

                      Hi there try this

                       

                      Load *,

                      if(Peek(Item)=Item,rangesum(peek(Closingstock),if(TransType='Receiving' or TransType='Adjustment' or TransType='Return'  ,QTY,(-1)*QTY),

                      if(TransType='Receiving' or TransType='Adjustment' or TransType='Return'  ,QTY,(-1)*QTY))          as          ClosingStock,

                      resident Orginal

                      order by Item,Date asc;