I've been researching all afternoon and have not come up with a reasonable way to calculate a FIFO based inventory value for the following scenario:
Worksheet one (My valuation worksheet):
Inventory //Item description// On-hand// Calculated cost
123456// Nut// 3 // {}
123457// Bolt// 2// {}
Second tab (inventory purchase transactions - I am ignoring the sales transactions for now):
Date //Inventory //Transaction//Qty// Transaction cost
1/1/2012 //123456 //8 //$16.00
2/1/2012 //123457 //2 //$8.00
3/1/2012 //123456 //6 //$36.00
4/1/2012// 123456 //2 //$6.00
Now I just need to solve for {}. The math tells me it's $4, so now I need excel to tell me it's $4. Needless to say, there are roughly 900 items and 6k transactions - I really don't want to do this on an item by item basis. Any help would be much appreciated.
Thanks in advance-
Worksheet one (My valuation worksheet):
Inventory //Item description// On-hand// Calculated cost
123456// Nut// 3 // {}
123457// Bolt// 2// {}
Second tab (inventory purchase transactions - I am ignoring the sales transactions for now):
Date //Inventory //Transaction//Qty// Transaction cost
1/1/2012 //123456 //8 //$16.00
2/1/2012 //123457 //2 //$8.00
3/1/2012 //123456 //6 //$36.00
4/1/2012// 123456 //2 //$6.00
Now I just need to solve for {}. The math tells me it's $4, so now I need excel to tell me it's $4. Needless to say, there are roughly 900 items and 6k transactions - I really don't want to do this on an item by item basis. Any help would be much appreciated.
Thanks in advance-
