4 Replies Latest reply on Jun 11, 2014 7:02 AM by egbaumier

    How to get average cost and running sub summary

    egbaumier

      I'd like to get some directions how to solve this issue:

       

      There is source table with:

      ProductID

      Date IN

      Quantity IN (Purchased)

      Cost of Purchase

      Date OUT

      Quantity OUT (Sold)

       

      There are 400 different ProductID's and 1100 registers that include Purchased and Sold items.

       

      I need a Report to show these information by Month:

       

      ProductID

      DateIN

      Quantity IN

      Date OUT

      Quantity OUT

      Quantity Balance (Running)

      Average Cost

       

      The issue is how to handle Average Cost until Balance Qt is <> 0 for next register and update it when you have a new entry value when your balance is 0.

      The table below just represent 01 item but there are 400 items in this source table that should be summarized.

       

       

      DateProductIDQt IN
      Cost IN
      SubTotal Cost
      Qt OUT
      Cost OUTBalance QtAverage Cost
      01/01
      ABC
      10
      100.0010x100=1000.00

      101000/10 = 100.00
      01/02ABC
      10
      110.0010x110=1100.00

      10+10=201000.00+1100.00/20 =105.00
      01/04 ABC



      15
      15*105.0020-15=5105.00 (REPEAT LAST VALID AVERAGE COST)

      01/10
      ABC



      5
      5*105.00
      5-5=0
      105.00

      01/12
      ABC
      10
      115.00
      10x115=1150.00


      10
      1150/10 = 115.00

      01/14
      ABC



      8
      8*115.00
      10-8=2
      115.00











       

      Regards,

       

      Edison Garcia

      FM13

        • 1. Re: How to get average cost and running sub summary
          erolst

          See if the attached file helps you.

          • 2. Re: How to get average cost and running sub summary
            egbaumier

            Thank you for suggestion.

             

            There are some issues yet.

             

            1. costOUT should be a calculation using last valid average cost for that Product.

             

            2. If QtyRunning = 0, it means that all Qty from Purchase products are sold, Average Cost should be 0. Try to get OUT 8 pcs and you'll see that cAverageCost remain as last register.

            But when you sell all parts Average Cost shpud be 0 until new Purchase (QTy and CostIN) and make a new calculation.

             

            3. For a new Product (not ABC) Average Cost should be related just for this new product, for example try to add Product TTC.

            When I add a new product such as TTC, Average Cost got from last valid average cost from Product ABC .

             

            I tried to use some variable to get this Last Average Cost from Product but I failed.

             

            Regards,

             

            Edison

            • 3. Re: How to get average cost and running sub summary
              erolst

              Edison Garcia wrote:

              There are some issues yet.

              1. Well, then make it so; GetNthRecord - 1 etc., and remember that this all hinges on the sort order by date.

               

              2. I didn't understand that – unless you were saying the same thing twice, which always confuses me.

               

              Do you mean that if after a sale the running quantity is 0, the average price should be 0, too?

               

              Then you only need to wrap the OUT part of cAverageCost in a Case(), too, and define as first check 'not sQuantity' (or 'sQuantity = 0', if you prefer); if so, return 0, otherwise the value from the last record.

               

              3. If the different products don't impact each other (as they shouldn't), why not sort by product and date, and use running with restart (when sorted by product) for quantity and cost?

               

              If you want to only sort by date AND still look at the most recent entry of a product (and use its summary values), you should probably use a self-join by product or some SQL magic instead of summary fields, because this could get really messi.


              Edison Garcia wrote:

              I tried to use some variable to get this Last Average Cost from Product but I failed.

               

              That happens when one uses any old variable, instead of the right one …

              • 4. Re: How to get average cost and running sub summary
                egbaumier

                Please see attached file with your improvements.

                 

                Because I'll import all registers from spreadsheet to FM with Invoices, Dates, Amounts, etc ... there is a last one information that I would like your help.

                 

                At SubSummary I can get Product, Running Qty and LAST Average Cost from all products. I did a new calculation field that works for all sorted products ... but for product that contain register #1 I couldn't get this information.

                 

                What is my missing point?

                 

                Edison