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 |
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.
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.