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

# Finding Purchase Price Based on Available Quantity

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.