Forum Discussion
Using FIFO to Determine Sale Price of Fuel
Now the formula with a mixed price.
This is a technically far more complicated solution using advanced features of Excel 365.
salesPrice
= LET(
cumPurchases, SCAN(0,purchaseQuantity, Sumλ),
cumSales, SCAN(0, quantitySold, Sumλ),
MAP(cumSales-quantitySold, cumSales,
LAMBDA(s₀,s₁,
SUM(
MAP(purchasePrice, cumPurchases-purchaseQuantity, cumPurchases,
LAMBDA(pr,v₀,v₁,
MAX(MIN(v₁,s₁)- MAX(v₀,s₀),0)* pr
)
)
) / (s₁-s₀)
)
)
);The formula accumulates both sales and purchase volumes. Then it calculates the overlaps and the price of each tranche. The unit price is obtained by dividing by the sales quantity.
As the sales quantities are small relative to the purchase quantities, very few sales actually require an interpolated price calculation.
- I'm hitting a snag with this one, Excel isn't recognizing SCAN() or MAP() functions best as I can tell. And a little research isn't giving me any answers as to why. I'm running Microsoft 365 Apps for Business as a subscription product. Version 2202 (Build 14931.20724 Click-to-Run) Semi-Annual Enterprise Channel.
That is a surprise to me. It would not have been any surprise had you not had access to Excel 365, most users do not realise just how profound the changes since 2018 have been. My belief is that even with a semi-annual license you should have had access to the Lambda helper functions for a while now. Do you control the update cycle, or do you need to go to corporate IT?
SergeiBaklan Can you advise?
Just in case you can gain access to SCAN and MAP, I have tried to repackage the solution as a Lambda function to facilitate reuse, hopefully for you, but otherwise for anyone else with FIFO based challenges.
Sorksheet formula = CostFIFOλ(quantitySold, purchaseQuantity, purchasePrice) CostFIFOλ = LAMBDA(outputQuantity,inputQuantity,inputCost, LET( Addλ, LAMBDA(x, y, x + y), cumInput, SCAN(0, inputQuantity, Addλ), cumOutput, SCAN(0, outputQuantity, Addλ), MAP(cumOutput-outputQuantity, cumOutput, LAMBDA(u₀,u₁, SUM( MAP(cumInput-inputQuantity, cumInput, inputCost, LAMBDA(v₀,v₁,w, MAX(MIN(v₁,u₁) - MAX(v₀,u₀), 0) * w ) ) ) / (u₁-u₀) ) ) ) );- Thanks! I'll keep this bookmarked and come back to it when I can figure out access. I'll have to talk to our IT Team.