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.
