Forum Discussion
Solved
Using FIFO to Determine Sale Price of Fuel
In our business, we purchase fuel in bulk and it is then "sold" to our equipment at cost. I need to be able to attribute a price to each sale. I keep feeling like I've almost got it and then it ...
Now the formula with a mixed price.
Hello! You could use a function without VBA. XLOOKUP allows you to search for the next largest match. For this, I need an auxiliary column in which the purchased quantities are listed cumulatively. Have a look at the solution in the attached sheet to see if it meets your requirements.
- It comes close, but ideally I can be more exact with the pricing. With the attached sheet, I price only 22,564 litres at $1.4023 --That's a difference of 125 litres from what I actually purchased at that price. If I'm understanding how this is working, I could be looking at up to a 850 litre (the equipment's max capacity) difference each time the price changes.
What should the price be when the price changes. A mixed price?
3/16/2022 12:59:00 PM 520 607 1.4023 3/16/2022 1:07:00 PM 608 275 1.3936 3/16/2022 3:47:00 PM 546 280 1.3913 - I think a mixed price is probably the best/easiest way to accomplish it.
