Finding Purchase Price Based on Available Quantity

mbespartochnyy — Fri, 24 Nov 2017 16:33:25 GMT

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.

topic Finding Purchase Price Based on Available Quantity in QlikView

Finding Purchase Price Based on Available Quantity