Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

FIFO Costing

Status
Not open for further replies.

leiela

Programmer
Dec 14, 2007
15
GB
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.
 
Yeah i've looked at that article and i have to admit it just confused the hell out of me.

TBH i'm not great at working with temporary tables.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top