Hi All,
I am trying to create a macro that would allow to perform FIFO (First In First Out) and LIFO (Last In Last Out) analysis of stock volume for a defined date period. Below is a dummy data that is set out similar to the source data.
Supplier Date Stock
a 1/01/2016 -600
b 2/01/2016 300
c 3/01/2016 1000
d 4/01/2016 600
d 5/01/2016 -200
b 6/01/2016 -500
b 7/01/2016 -100
c 8/01/2016 -300
a 9/01/2016 -100
a 10/01/2016 800
d 11/01/2016 300
a 11/02/2016 -200
a 12/01/2016 400
a 13/01/2016 -600
d 14/01/2016 -400
a 15/01/2016 800
c 16/01/2016 -600
c 17/01/2016 600
c 18/01/2016 -100
For example, having regard to this data, lets say the defined period is from 11/1/2016 and 18/1/2016. For example I will explain the answer for Supplier 'a'.
If you filter the first column for 'a' you should get the following:
Supplier Date Stock
a 1/01/2016 -600
a 9/01/2016 -100
a 10/01/2016 800
a 11/02/2016 -200
a 12/01/2016 400
a 13/01/2016 -600
a 15/01/2016 800
Now, the opening balance for supplier just before the start of the defined period (11/1/2016) is 100 (-600-100+800). Then on the next date, you sell 200 which is more than your opening balance (100), this is called short-selling. If you short-sell in the defined period, the FIFO and LIFO rules don't exactly apply and therefore this sell of 200 is going wait to be offset from a buy in the defined period.
Therefore on the next day when you buy 400, the 200 you bought yesterday offsets the 400 you bought today, therefore within the defined period you are in a net buy position of 200. Similarly, on the next day when you short-sell 600, your net position would change to short-sell of 200. Similarly, as you buy 800 the following day, this offsets your previous short-sell position and now you are back into a net buy position of 400. Overall, in both FIFO and LIFO scenarios, you end up with a net buy position of 400.
Another example with is much simpler is filtering for supplier 'c' where you should get the following:
Supplier Date Stock
c 3/01/2016 1000
c 8/01/2016 -300
c 16/01/2016 -600
c 17/01/2016 600
c 18/01/2016 -100
Now, the opening balance just prior to the defined period is 700 (1000-300). In FIFO, the 600 you sell on 16/1 offset the opening balance (700) giving you 100 opening balance left, you buy another 600 the next day and then sell 100 the following. However with FIFO, this 100 is offset against 100 leftover in the opening balance, whereas with LIFO it would be offset against the 600 you most recently bought. Therefore, with FIFO, Supplier c is in the net buy position of 600 within the defined period and with LIFO, a net buy position of 500 within the defined period.
I hope the explanations above make sense. This is too hard for me and I need some guidance/solutions because I cant seem to figure it out.
The solutions you should expect for each supplier is as below:
FIFO LIFO
a 400 a 400
b 0 b 0
c 600 c 500
d 300 d 0
I hope someone can help out with this this!
Thanks!
I am trying to create a macro that would allow to perform FIFO (First In First Out) and LIFO (Last In Last Out) analysis of stock volume for a defined date period. Below is a dummy data that is set out similar to the source data.
Supplier Date Stock
a 1/01/2016 -600
b 2/01/2016 300
c 3/01/2016 1000
d 4/01/2016 600
d 5/01/2016 -200
b 6/01/2016 -500
b 7/01/2016 -100
c 8/01/2016 -300
a 9/01/2016 -100
a 10/01/2016 800
d 11/01/2016 300
a 11/02/2016 -200
a 12/01/2016 400
a 13/01/2016 -600
d 14/01/2016 -400
a 15/01/2016 800
c 16/01/2016 -600
c 17/01/2016 600
c 18/01/2016 -100
For example, having regard to this data, lets say the defined period is from 11/1/2016 and 18/1/2016. For example I will explain the answer for Supplier 'a'.
If you filter the first column for 'a' you should get the following:
Supplier Date Stock
a 1/01/2016 -600
a 9/01/2016 -100
a 10/01/2016 800
a 11/02/2016 -200
a 12/01/2016 400
a 13/01/2016 -600
a 15/01/2016 800
Now, the opening balance for supplier just before the start of the defined period (11/1/2016) is 100 (-600-100+800). Then on the next date, you sell 200 which is more than your opening balance (100), this is called short-selling. If you short-sell in the defined period, the FIFO and LIFO rules don't exactly apply and therefore this sell of 200 is going wait to be offset from a buy in the defined period.
Therefore on the next day when you buy 400, the 200 you bought yesterday offsets the 400 you bought today, therefore within the defined period you are in a net buy position of 200. Similarly, on the next day when you short-sell 600, your net position would change to short-sell of 200. Similarly, as you buy 800 the following day, this offsets your previous short-sell position and now you are back into a net buy position of 400. Overall, in both FIFO and LIFO scenarios, you end up with a net buy position of 400.
Another example with is much simpler is filtering for supplier 'c' where you should get the following:
Supplier Date Stock
c 3/01/2016 1000
c 8/01/2016 -300
c 16/01/2016 -600
c 17/01/2016 600
c 18/01/2016 -100
Now, the opening balance just prior to the defined period is 700 (1000-300). In FIFO, the 600 you sell on 16/1 offset the opening balance (700) giving you 100 opening balance left, you buy another 600 the next day and then sell 100 the following. However with FIFO, this 100 is offset against 100 leftover in the opening balance, whereas with LIFO it would be offset against the 600 you most recently bought. Therefore, with FIFO, Supplier c is in the net buy position of 600 within the defined period and with LIFO, a net buy position of 500 within the defined period.
I hope the explanations above make sense. This is too hard for me and I need some guidance/solutions because I cant seem to figure it out.
The solutions you should expect for each supplier is as below:
FIFO LIFO
a 400 a 400
b 0 b 0
c 600 c 500
d 300 d 0
I hope someone can help out with this this!
Thanks!
