Hi,
I have 3 tables as below mocked examples:
Goods received:
| part | DateTime | quantity | unit cost |
|---|
| AB12345 | 2016-07-23 09:00 | 100 | 25.37 |
| AB12345 | 2016-09-15 20:00 | 150 | 28.72 |
| AB12345 | 2017-11-23 12:00 | 100 | 30.25 |
Stock history:
| part | DateTime | On Hand Quantity |
|---|
| AB12345 | 2016-08-12 12:19 | 85 |
| AB12345 | 2017-12-27 15:00 | 125 |
Sales:
| Part | DateTime | Sale Quantity |
|---|
| AB12345 | 2016-08-12 12:20 | 2 |
| AB12345 | 2017-12-27 15:00 | 1 |
I need to calculate the cost price of the item at the point of sale by doing the following and using first in first out logic:
* Work out how many of the good receipt lines are still relevant based on the 'on hand quantity' at point of sale.
* Work out the average cost of the parts in stock based on the quantity of each batch remaining and the unit cost of those items.
i.e.
* For the first sale there are 85 items in stock, these are fulfilled from the goods received on 2016/07/23
so the cost of sale is 25.37* 2 = 50.74
* For the second sale, there are 125 items in stock, 100 of these came from goods received on 2017/11/23, the remaining 25 come from the goods received on 2016/09/15.
The cost price should be ((100*30.25) + (25*28.72))/125
..(3025+718)/125 = 29.944
Any help on this greatly appreciated. I've managed to use our stock table to get as far as this but I'm struggling with how to implement this logic.
Thanks,
Colin.
