First in first out calculation but with a look back on multiple elements

[PhillSelby]

I am trying to calculate the price of internal depot transaction, where we move stock between depots which has no price attached to it, I need to calculate the price.

Affectively its first in first out pricing, however, there are external sales outside of the data, which can impact the holding stock meaning that one of the external sales, might eat up one or more of the previous buys meaning that stock is no longer eligible for the price calculation. I have attached sample input data and put more of an explanation below in the table which may make it clearer. Essentially where there is no price per unit, i need to run the calculation based on which buys make up that sell and calculate the average of all the cash across the buys divided by the number of units to give me an average unit price.

	Settlement Category	Direction	Date of trans	No. Units	Cash Value	PPU
1	EXTERNAL	BUY	20230104	282262500	-271624873.2	96.2313
2	EXTERNAL	BUY	20230104	225000000	-216624375	96.2775
3	EXTERNAL	BUY	20230104	225000000	-216582750	96.259
4	EXTERNAL	BUY	20230105	70000000	-67390750	96.2725
5	EXTERNAL	SELL	20230201	-5500000	5310250	96.55
6	EXTERNAL	BUY	20230131	25000000	-24131250	96.525
7	EXTERNAL	SELL	20230201	-18000000	17376300	96.535
8	EXTERNAL	BUY	20230224	10000000	-9627500	96.275
9	INTRABRCH	SELL	20230227	-200000000	0	0	Buy 1 is large enough to cover this sell and all the sells before hand so price for this would be 96.2313
10	EXTERNAL	BUY	20230314	45000000	-43272000	96.16
11	EXTERNAL	SELL	20230314	-45000000	43359750	96.355
12	Internal account	SELL	20230517	-5600000	5444320	97.22
13	Internal account	BUY	20230517	5600000	-5444320	97.22
14	EXTERNAL	BUY	20230613	150000000	-146178750	97.4525
15	EXTERNAL	BUY	20230614	44000000	-42875800	97.445
16	EXTERNAL	BUY	20230622	5380000	-5248997	97.565
17	Internal account	SELL	20230712	-11845000	11580856.5	97.77
18	Internal account	BUY	20230712	11845000	-11580856.5	97.77
19	Internal account	BUY	20230714	13000000	-12717900	97.83
20	Internal account	SELL	20230714	-13000000	12717900	97.83
21	EXTERNAL	BUY	20231012	50000000	-49496250	98.9925
22	EXTERNAL	SELL	20231012	-50000000	49505000	99.01
23	Internal account	BUY	20231012	-600000	594000	99
24	Internal account	BUY	20231012	600000	-594000	99
25	EXTERNAL	BUY	20231018	3500000	-3469550	99.13
26	EXTERNAL	BUY	20231027	1000000	-991825	99.1825
27	EXTERNAL	SELL	20231027	-1085000	1076211.5	99.19
28	Internal account	SELL	20231127	-189587000	0	0	buy 2 and 3 units to cover so avg is 96.2765
29	EXTERNAL	BUY	20231214	10000000	-9976250	99.7625
30	Internal account	SELL	20231215	-10000000	0	0	buy 3 covers this so price is 96.259
31	Internal account	SELL	20231228	-600000000	0	0	buys 3,4,5,8,10,13,14,15,16,18,19,21 so avg is 96.9080

[Brando]

Hey @PhillSelby,

This looks like a really interesting puzzle; however, it's unclear what you are trying to solve for.

Are the line items of data with no price the ones listed as zero? Buys have a positive [No. Units] value so I'm assuming that's inventory in and sells are the inverse, but how is the [PPU] calculated for these if you're attempting to calculate the [PPU]?

Maybe we just need some additional explanation to get a solution your way.

[PhillSelby]

Thanks for the reply.

So yes, Price per unit column, where it is 0, I need to calculate the units in (buys) that cover that sell. Positive units is a buy, negative is a sell, and the opposite applies for the cash column .

The Price per unit, is cash value divided by units, which will give you an average price. So where there is a value in PPU column, that is calculated at the point of transaction based on external factors.

So row 9 which is the first internal account move, is covered solely by the volume of units bought in line 1, so that is the only price that is required. Line 31, is made up of the multiple buys listed in the additional information to give more perspective. 

The solution will essentially have to look back and see where the buys total equal to or more than the internal sale, but also ensure its accounting for the sales that would impact the 'holdings' as it were.