Hi I'm looking for a formula which can calculate the usage value for inventory items at different prices.
For example my at the start of July I have 20 George Foreman Grills @ 15.00 and in July I receive a delivery of 20 George Foreman Grills @ 12.50. Then in July I have used 30 George Foreman Grills on a LIFO valuation.
I'm looking for a formula which will value the 30 used.
Basically if I enter 30 in the QTY used column I want a formula which will look up to the opening stock QTY column and if that is less than or equal to the QTY used it will multiply the amount by the O/S price e.g 20 x 15 then it will say we still need 10 so move on to the next delivery and say we need 10 still and check if that is less than or equal to 10 and if it is will take the 10 multiply that by the price and then add them both together and keep moving along the delivery columns until it has met the usage value?
Is that something I can do in excel?
For example my at the start of July I have 20 George Foreman Grills @ 15.00 and in July I receive a delivery of 20 George Foreman Grills @ 12.50. Then in July I have used 30 George Foreman Grills on a LIFO valuation.
I'm looking for a formula which will value the 30 used.
Basically if I enter 30 in the QTY used column I want a formula which will look up to the opening stock QTY column and if that is less than or equal to the QTY used it will multiply the amount by the O/S price e.g 20 x 15 then it will say we still need 10 so move on to the next delivery and say we need 10 still and check if that is less than or equal to 10 and if it is will take the 10 multiply that by the price and then add them both together and keep moving along the delivery columns until it has met the usage value?
Is that something I can do in excel?
