Averaging Liquid Inventory Price on a 1st in 1st out basis

[BStern]

I am trying to build a spreadsheet that would average the inventory that we currently have stored. As our inventory is constantly changing, I need to know at any given time the cost of what is in inventory based on a percentage (liquid). Ideally I would like to enter a percentage of what we have in a tank, and get the average price of that based on loads that have come in to fill it... however, the first load that comes in to fill the tank needs to be the first load that leaves...

example: 500 gallons come in at $1.00, 500 gallons come in at $.80, 500 gallons come in at $.50 But we only have 700 gallons in our inventory, I would need to average the last load of 500 gallons at $.50 and a partial amount of the second load 200 gallons.

So if there was some way enter the percentage of what the tank actually has in it and a formula that could compute the true average cost of it, I would be thrilled!

ANY HELP is APPRECIATED!

 

[Eric W]

Welcome to the forum.

A single-cell solution might be too ambitious. It might be possible, but it would be very complicated. Instead, I created a helper column that should work for you.

	A	B	C	D	E	F	G
Purchase amount	Purchase cost	Helper	Amount in tank	Average cost

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"] $ 1.00 [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]700[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 0.59 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"] $ 0.80 [/TD]
[TD="align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"] $ 0.50 [/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet16​

C2: =IF(A2="","",IF(C3="",MIN(A2,E2),MAX(0,MIN($E$2-SUM($C3:$C$100),A2))))
G2: =SUMPRODUCT(B2:B100,C2:C100)/E2

Layout the sheet as shown. Put the formula in C2 and copy down as far as needed. I assumed a bottom row of 100, but you can change that. Then put in the purchase amounts in columns A:B as shown, and the amount in the tank in E2 as shown. The formula will figure out how much of the amount in the tank is due to the latest purchase, and the formula in G2 figures the weighted average.

Let me know if this helps.

 

[BStern]

Thank you so much, however, I tried it out, and I am going to be entering the loads continually and I set up a scenario where I put 2 loads in at 10000 gallons each, the 1st at $3.00 and 2nd at $2.00. I put 20000 gallons as the amount in the tank and the average price came to be $1.00.... :/

 

[Eric W]

Can you show me what your sheet looks like? This is what I see when I try that scenario:

	A	B	C	D	E	F	G
Purchase amount	Purchase cost	Helper	Amount in tank	Average cost

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"] $ 1.00 [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"][/TD]
[TD="align: right"] $ 2.50 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"] $ 0.80 [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"] $ 0.50 [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"] $ 3.00 [/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"] $ 2.00 [/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet16​

 

[BStern]

I got it to work. Thank you so much for you help! One more question... this workbook will be split by months (1 month per sheet), is there anyway to bridge the months so that if we have inventory we are still holding from November, but we are in December, it will pull from the November sheet?

 

[Eric W]

I just noticed an issue with my formula, the first E2 should be $E$2.

As far as your latest question, let's say at the end of November you have 700 gallons left with an average cost of $1.25. On the first row on the December sheet, put a dummy purchase of 700 gallons with a cost of $1.25. Then add purchases below that as normal. That should calculate the average for you using the November surplus.

Hope this helps!