0 Replies Latest reply: Nov 24, 2017 11:33 AM by Mikhail Bespartochnyy RSS

    Finding Purchase Price Based on Available Quantity

    Mikhail Bespartochnyy

      Hello world,

       

      If you don't feel like reading all this, just check out the attachments. They're pretty self-explanatory. For those who share my optimism for details, they're below. Thanks in advance for any help!

       

      I’m working on an app for a retailer that has products with varying products. I need a basic app functionality that would tell the user what was the purchase price of item that was sold on a given day. Here’s a scenario and sequence of events:

       

          1. Retailer bought 1 pack of toothpaste on September 1st at $10/pack

          2. Retailer bought same 1 pack of toothpaste on September 15th also at $10/pack

       

      At this point, there are 2 packs of toothpaste in the inventory, each costing $10.

       

          3. On October 1st, the price went up and retailer bought another pack of toothpaste for $15/pack.

          4. On October 15th, retailer bought another pack of toothpaste at $15/pack

       

      Now there are 4 packs of toothpaste in the inventory, 2 @ $10/pack and 2 @ $15/pack.

       

          5. Now on November 23rd, retailer sells 3 packs of toothpaste. They sold 2 packs at $12/pack and 1 pack at $17/pack.

       

      The question is, how much money did they make?

       

      My math tells me that they made a total of $6. Simple, right? ($12 x 2) + $17 = $41 (total profit from sales) minus ($10 x 2) + $15 = $35 (total purchase price). If I’m wrong here, please tell me. I’m not too familiar with accounting  principles so I’m open to a possibility that that math is not right.

       

      I have a simplified register file attached and an app that I have so far. The register has two tabs one where they record purchases and one where they record sales. The challenge is somehow to build a data model that’s smart enough to figure out that the first two packs of toothpaste were bought at $10/pack and not $15/pack and that the third one was bought at $15 because we ran out of packs of toothpaste that cost $10.

       

      I’ve put together an app with a script that I have so far. Does anyone know how this can be done? Even if not in QlikView, if you know how something like this is executed in a retail environment or some common accounting practices in this situation and you can point me to them, I’ll appreciate that.

       

      Mikhail B.