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

Question

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 CategoryDirectionDate of transNo. UnitsCash ValuePPU 1EXTERNAL  BUY20230104282262500-271624873.296.2313 2EXTERNAL  BUY20230104225000000-21662437596.2775 3EXTERNAL  BUY20230104225000000-21658275096.259 4EXTERNAL  BUY2023010570000000-6739075096.2725 5EXTERNAL  SELL20230201-5500000531025096.55 6EXTERNAL  BUY2023013125000000-2413125096.525 7EXTERNAL  SELL20230201-180000001737630096.535 8EXTERNAL  BUY2023022410000000-962750096.275 9INTRABRCH SELL20230227-20000000000Buy 1 is large enough to cover this sell and all the sells before hand so price for this would be 96.231310EXTERNAL  BUY2023031445000000-4327200096.16 11EXTERNAL  SELL20230314-450000004335975096.355 12Internal accountSELL20230517-5600000544432097.22 13Internal accountBUY202305175600000-544432097.22 14EXTERNAL  BUY20230613150000000-14617875097.4525 15EXTERNAL  BUY2023061444000000-4287580097.445 16EXTERNAL  BUY202306225380000-524899797.565 17Internal accountSELL20230712-1184500011580856.597.77 18Internal accountBUY2023071211845000-11580856.597.77 19Internal accountBUY2023071413000000-1271790097.83 20Internal accountSELL20230714-130000001271790097.83 21EXTERNAL  BUY2023101250000000-4949625098.9925 22EXTERNAL  SELL20231012-500000004950500099.01 23Internal accountBUY20231012-60000059400099 24Internal accountBUY20231012600000-59400099 25EXTERNAL  BUY202310183500000-346955099.13 26EXTERNAL  BUY202310271000000-99182599.1825 27EXTERNAL  SELL20231027-10850001076211.599.19 28Internal accountSELL20231127-18958700000buy 2 and 3 units to cover so avg is 96.276529EXTERNAL  BUY2023121410000000-997625099.7625 30Internal accountSELL20231215-1000000000buy 3 covers this so price is 96.25931Internal accountSELL20231228-60000000000buys 3,4,5,8,10,13,14,15,16,18,19,21 so avg is 96.9080

Example Data.xlsx

PhillSelby · Answer

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.

Brando · Answer

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.