I've simplified the example somewhat but essentially im looking for a way of calculating the cost price of items recently sold based on a FIFO methods.
Here's two basic tables (somewhat simplified)
[Sold] ITemID, SaleDate, QTY, Price
1, '2009-05-01', 2 ,£13
1, '2009-06-01', 6 ,£10
1, '2009-07-01', 4 ,£14
1, '2009-08-01', 2 ,£12
1, '2009-08-01', 2 ,£14
1, '2009-09-01', 2 ,£13
[Bought] ITemID, BoughtDate, QTY, Price
1, '2009-05-01', 5 ,£1
1, '2009-06-01', 10 ,£2
1, '2009-07-01', 6 ,£1
1, '2009-08-01', 5 ,£2
1, '2009-08-01', 6 ,£3
1, '2009-09-01', 10 ,£1
1, '2009-09-01', 4 ,£1
I need to establish the cost price of the items sold, so for example SaleDate '2009-05-01' is fairly straight forwards. 5 where bought as 1, 2 where sold, So thats a cost price of 2 per item. (total 4)
However on '2009-06-01', 10 where bought, and 3 remained in stock from the previous day. 6 where sold, So the calulation would be :-
(3 x £1) = £3 (leftover stock From '2009-05-01')
(3 x £2) = £6 (Stock bought '2009-06-01')
Equals = £9 Total Price (£1.50 each)
As the remaining stock would be used before the later stock.
I have a View which calculates the "stock levels" per given day but im struggling to calculate the "cost value" of that stock.
I've concidered creating a stock table and using an update statment to update the current stock level and recalculate the current value of the stock on hand everytime an item is bought and sold, however i also have a reporting requirement to report on the exact cost of any item bought historically so i need sometime a little more elaborate.
Here's two basic tables (somewhat simplified)
[Sold] ITemID, SaleDate, QTY, Price
1, '2009-05-01', 2 ,£13
1, '2009-06-01', 6 ,£10
1, '2009-07-01', 4 ,£14
1, '2009-08-01', 2 ,£12
1, '2009-08-01', 2 ,£14
1, '2009-09-01', 2 ,£13
[Bought] ITemID, BoughtDate, QTY, Price
1, '2009-05-01', 5 ,£1
1, '2009-06-01', 10 ,£2
1, '2009-07-01', 6 ,£1
1, '2009-08-01', 5 ,£2
1, '2009-08-01', 6 ,£3
1, '2009-09-01', 10 ,£1
1, '2009-09-01', 4 ,£1
I need to establish the cost price of the items sold, so for example SaleDate '2009-05-01' is fairly straight forwards. 5 where bought as 1, 2 where sold, So thats a cost price of 2 per item. (total 4)
However on '2009-06-01', 10 where bought, and 3 remained in stock from the previous day. 6 where sold, So the calulation would be :-
(3 x £1) = £3 (leftover stock From '2009-05-01')
(3 x £2) = £6 (Stock bought '2009-06-01')
Equals = £9 Total Price (£1.50 each)
As the remaining stock would be used before the later stock.
I have a View which calculates the "stock levels" per given day but im struggling to calculate the "cost value" of that stock.
I've concidered creating a stock table and using an update statment to update the current stock level and recalculate the current value of the stock on hand everytime an item is bought and sold, however i also have a reporting requirement to report on the exact cost of any item bought historically so i need sometime a little more elaborate.