Fifo Method Of Stock Valuation

[sujittalukde]

Also posted at http://www.ozgrid.com/forum/showthread.php?t=71862 but not geeting any reply So posting here.

have a file where I want to make a report which will put the stocks having positive balances along with their prices and values.

Stocks are to be valued on FIFO (First in first out) method ie when the stocks are sold it means it were sold from the first lot bought and so on.



Reporting is required for Shares Held For eg. for Stock A, Buy qtn is (50+50+100+<sell>+100) and sell qtn is 100 So total balance is Buy Qtn 300 - sell Qtn 100 = 200 Sell Qtn 100 will out of first 50 & then next 50 (First in so first out) balance held in hand is out of last two Qtn 100 purchases. The prices are corresponding to these two lots of 100 stocks.

Reporting will be for Stock held (lotwise) See Sheet 2 of the file attached.
Basis of price will be fixed ie no formula etc and corresponding to those lots.

Actually these data are copied from web qwery so formula etc are not required. For cost purpose, price and Total correponding the balnce qtn lot are relevant

In case more clarification required, do let me know. I will try to make them clear.

FIFO.xls
	A	B	C	D	E	F	G
1	Stock	Date	Action	Qty.	Rate	Total
2	A	25-Nov-04	Buy	50	70.3	3,546.70
3	A	20-Jul-05	Buy	50	66	3,330.85
4	A	01-Sep-05	Buy	100	82	8,275.97
5	A	23-Sep-05	Sell	100	76.5	7,579.12
6	A	17-Oct-05	Buy	100	73.8	7,448.38
7	B	03-Jan-05	Buy	200	125.49	25,325.16
8	B	12-Jan-05	Sell	200	98.5	19,522.39
9	BA	05-Sep-06	Sell	300	143	42,485.23	Qtn balance is negative so ignore this stock in reporting
10	BC	21-Dec-04	Buy	20	284.7	5,745.33
11	BC	12-Jan-05	Sell	20	248.6	4,927.18
12	C	21-Dec-04	Buy	300	15.3	4,631.40	Red font means buy sell matched
13	C	12-Jan-05	Sell	300	17.7	5,262.12
14	C	17-Jan-05	Buy	200	18.35	3,703.08
15	C	03-Mar-06	Buy	97	25.55	2,508.38	yellow background means these are in balance
16	C	26-Apr-06	Buy	103	24.05	2,507.69	and should be reported
17	C	05-Dec-06	Buy	100	30.8	3,111.91
18	C	08-Mar-07	Buy	100	32.95	3,327.18
19	D	15-Jan-07	Buy	100	128.5	12,974.24
20	E	08-Mar-07	Buy	100	64.65	6,527.36
21	HH	31-May-07	Buy	100	62.5	6,310.48
22	ID	07-Dec-04	Buy	70	50.85	3,591.59
23	ID	12-Jan-05	Sell	60	68.65	4,081.87
24	IDF	30-Dec-04	Buy	10	107.25	1,100.85
25	IDF	12-Jul-05	Sell	17	112.8	1,888.13	Qtn balance is negative so ignore this stock in reporting
Sheet1

 

[jindon]

Do you want the inventory for all the items? or just for a particular item?

 

[sujittalukde]

Thanks for the reply Actually I need the same for all the items Final reporting format will be as follows

FIFO.xls
	A	B	C	D	E
1
2	Name of the Stock	Qtn.	Price	Amount
3	A	100	82	8,275.97
4	A	100	73.8	7,448.38
5	C	200	18.35	3,703.08
6	C	97	25.55	2,508.38
7	C	103	24.05	2,507.69
8	C	100	30.8	3,111.91
9	C	100	32.95	3,327.18
10
11	Note: Ignaore negative balances stock ie if the Qtn.
12	Balance is in negative ignore those stocks
Sheet2

 

[jindon]

What does it mean

"Qtn balance is negative so ignore this stock in reporting"

Do you mean if the item once get negative balance, exclude that item in reporting?

 

[sujittalukde]

For eg. for Stock "BA" there is only Sell quantity without any buying qtn. So this should not be reported in the final output. (If you user Buy-Sell = Balance, then For "BA" result will be 0-300 = (300) so ignore this type of negative balances.)

 

[jindon]

So exclude the item whenever it got negative balance.
Is that correct?

 

[sujittalukde]

Yeah, correct.

 

[jindon]

OK
I don't think I have enough time to write a code today, hopefully tomorrow if you still need a help then.

 

[sujittalukde]

Sure you may try tomorrow, but dont forget.

 

[jindon]

One more question

What will be the balance at 23-Sep-05 for item A sold 70 instead of 100?