Inventory: FIFO, LIFO and Average Cost

[pegbol]

.
.

Hello,

I need to compare and calculate the Unit Cost Price of my Inventory based on the 3 methods of inventory valuation: FIFO (First In, First Out), LIFO (Last In, First Out) and Average Cost.

Next, I enclose 3 snapshots of each method with the results required (columns color yellow).

Thanks in advance for your help.

regards,
Pedro
.
.

 

[Bobo Manu]

Thank you very much for your help.
Its works great but unfortunately I have many type of items not only one.
In column N is the criteria .I tried something with if n = n+1 then run macro but doesn’t work.
I will be grateful for any help.
Thanks a lot
Bobo
P.S. I'd like to attach the fold but I don't know how

 

[zviadi]

Hi All

Tell me somebody how to use this script?
I've inserted it in the macro but i got:
"run-time error '9' subscript out of range"


Thanks,

 

[legitsys]

I've sent you an Excel file following the sample.
- Everything have to be the same to the Sheet naming
- activate Macro scripting in Excel when it open or else it would not work.
That's all

Hi All

Tell me somebody how to use this script?
I've inserted it in the macro but i got:
"run-time error '9' subscript out of range"

or send me the template by mail: tsagareishvili@gmail.com

Thanks,

 

[legitsys]

Great Script.
Very Helpful

 

[obulirajanr]

Hi,
Can some one share me the excel sheet with the formula?
my email id obulirajanr@gmail.com

Thanks,
OR

 

[tripleg98]

I have found this code to be very useful. As life would have it, the user moved the target. I have expanded the dataset so that there is a balance and cost for every day. What the user would like to do is reset the cost on the first day of each year. Thus the previous year would capture the cost and value would be realized in that year. Then on the first of the year, the cost would be the value at the end of the previous year.

I think this is doable but just wasn't sure how.

any help would be greatly appreciated

tripleg

 

[Parakorn]

OK
If any bugs, please let me know.

Dear Mr. Jindon

If I want to add more than one items in the same database, how can I apply your FIFO code

FIFO_VBA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
5					QUANTITY				COST
6	N	DATE	Item	DESCRIPTION	UNIT PRICE	IN	OUT	BALANCE	UNIT COST	DEBIT	CREDIT	BALANCE
7	1	01/09/2005	Item1	BEG. BAL.	10	100		100		1000	0	1000
8	2	12/09/2005	Item1	IN	20	300		400		6000	0	7000
9	3	12/09/2005	Item1	IN	25	170		570		4250	0	11250
10	4	12/09/2005	Item1	OUT			50	520	10	0	500	10750
11	5	23/09/2005	Item1	OUT			370	150	18.91891892	0	7000	3750
12	6	27/09/2005	Item1	IN	30	700		850		21000	0	24750
13	7	29/09/2005	Item1	OUT			450	400	28.33333333	0	12750	12000
14	8	29/09/2005	Item1	OUT			250	150	30	0	7500	4500
15	9	02/10/2005	Item1	IN	25	320		470		8000	0	12500
16	10	03/10/2005	Item1	OUT			50	420	30	0	1500	11000
17	1	01/09/2005	Item2	BEG. BAL.	10	100		100		1000	0	1000
18	2	12/09/2005	Item2	IN	20	300		400		6000	0	7000
19	3	12/09/2005	Item2	IN	25	170		570		4250	0	11250
20	4	12/09/2005	Item2	OUT			50	520	10	0	500	10750
21	5	23/09/2005	Item2	OUT			370	150	18.91891892	0	7000	3750
22	6	27/09/2005	Item2	IN	30	700		850		21000	0	24750
23	7	29/09/2005	Item2	OUT			450	400	28.33333333	0	12750	12000
24	8	29/09/2005	Item2	OUT			250	150	30	0	7500	4500
25	9	02/10/2005	Item2	IN	25	320		470		8000	0	12500
26	10	03/10/2005	Item2	OUT			50	420	30	0	1500	11000
FIFO

Cell Formulas
Range	Formula
J7:J26	J7	=F7*E7
K7:K26	K7	=G7*I7
L7,L17,H7,H17	L7	=J7
L8:L16,L18:L26,H8:H16,H18:H26	L8	=L7+J8-K8
I10,I20	I10	=E7
I11,I21	I11	=((50*E7)+(300*E8)+(20*E9))/G11
I13,I23	I13	=((150*E9)+(300*E12))/G13
I14,I24	I14	=E12
I16,I26	I16	=E12