7 Replies Latest reply: Jul 19, 2011 5:44 AM by Massimiliano Favata RSS

    calculation of inventory costs

      Good evening.

      I have a problem in calculating the cost of inventory.

      example:

       

      cost Table

      Gruppo Articolo    Data    Costo

      1           A    01/01/2011    5

      1           A    01/01/2011    10

      1           A    01/02/2011    11

      1           A    01/03/2011    12

      2           B    01/01/2011    40

      2           B    02/01/2011    41

      2           B    01/02/2011    45

      2           B    01/03/2011    45

       

      stock chart

       

       

       

       

       

      Articolo    Data    Giacenza

      A    01/01/2011    1,00

      A    01/01/2011    3,00

      A    01/02/2011    4,00

      A    01/03/2011    3,00

      B    01/01/2011    3,00

      B    01/01/2011    100,00

      B    02/01/2011   

      B    01/02/2011    -10,00

      B    01/03/2011    -5,00

       

       

      I would like to find the formula for finding the stock on 02/01/2011 and multiply it by the cost to date of 02/01/2011 for each group

      an example

       

      Sum({$<Data = {">=01/01/2000<=$(#Alla)"} >}  Giacenza  *  max( {$<Data = P({1<Data = {">=01/01/2000<=$(#Alla)"} >} Data) >}    Costo) )

       

      but does not work...
      ps

       

      sorry for my English

       

       

        • calculation of inventory costs
          Stefan Wühl

          Hi Massimo,

           

          I do not completely understand, could you maybe give an explicite example on how you want to do the number crunching?

          $(Alla) is a date (02/01/2011 in your example)? If it is a date, I don't think you need to convert it to numerical using $(#..), and then you want to compare to a date (01/01/2000) again.

           

          Regards,

          Stefan

          • calculation of inventory costs

            Hi Stefan,

             

            I would, calocolare the average cost at a later date, present in the variable,
            with the stock at the date,

            considering the date 02/01/2011
            as storage = 4.00 x Cost = € 10.00 = 40.00

            in the table, SIZES, and not 'the article, but Group

            example
            Group   Stock  Cost      
            Stock X Cost

            1           4.00     10.00    40.00

              • Re: calculation of inventory costs
                Stefan Wühl

                Massimo,

                 

                sorry, I still didn't get it.

                 

                For example, let's look at the first day only: 01/01/2011

                 

                 

                 

                Gruppo Articolo    Data    Costo

                1           A    01/01/2011    5

                1           A    01/01/2011    10

                 

                Articolo    Data    Giacenza

                A    01/01/2011    1,00

                A    01/01/2011    3,00

                 

                So we have two entries for stock and two entries for cost. Do you want to use the average cost per sepcific date (7.5) to apply to the sum of stock (4)? This would result in 30.

                or do you want 5*1 + 10 *3, which is 35. I would use latter, if the cost entries and stock change entries have a 1:1 association to get the real inventory cost, but I can't see that 1:1 relation in your data set (one line more in stock chart).

                 

                What do you mean with "the average cost at a later date, present in the variable, with the stock at the date". It is the same date in both cases, isn't it?

                 

                Sorry that I am a bit slow, it is getting late,

                Stefan