Calc inventory balance, Qty, $, FIFO method

[oomfh]

Unable to find solution to this in prior posts.

Single product (liquid propane) purchased and dispensed at cost. Purchase price flucuates daily.

Looking for formula to track quantities and $ - using FIFO method

Column B Gallons Purchased
Column C Purchase Price, Unit $
Column D Gallons Dispensed
Column E Dispensed, Unit $
Column F Gallon Balance
Column G $ Balance

 

[number kruncher]

Perhaps something like...

Excel Workbook
	B	C	D	E	F	G
1	Purchased	Price	Dispensed	Price	Balance	Value
2	100	0.12	100	0.12	0	0.12
3	120	0.14	55	0.14	65	0.14
4	110	0.13	0	0.13	110	0.13
5	120	0.14	0	0.14	120	0.14
6
7
8	59.90		19.70		40.20
Sheet

HTH

 

[oomfh]

Thank you for the timely reply NK.

This will be a good learning tool.

Just beginning to experiment with your formulas. The intent of column F was to keep a running balance of gallons on-hand. So the result for cell F4 would be 175, F5 would be 295 . . .

I see the formula in F8 incorporates the formula in F4 - so I am not sure how inserting more generic calculation impact F8.

 

[oomfh]

Perhaps I need to leave column F as-is and create a new column to calculate gallons on-hand.

 

[number kruncher]

To use the FIFO method, you will always need to know the balance of each stock purchased.

I took Col F as this balance, however, you could have another Col, with a running balance of Total Quantity Remaining.

HTH

 

[oomfh]

Understood, thanks again

 

[number kruncher]

Pleased to help.

Thanks for feedback.

 

[oomfh]

The above example does not appear to calculate the $amount dispensed based on FIFO.

If I insert 85 in D4 and 110 in D5 the example results in $46.15 . . . mutliplying the unit price by gallons dispensed in the same rows.

If I calculate the $amount dispensed manually the result is $45.90.

Thoughts anyone?

 

[number kruncher]

In order for FIFO to work, you have to release the 65 balance from row 3, before you release from row 4 and 5.

If you want to release 195 (85 + 110), then from row 3 down column D, enter 120, 110 and 20, to get your desired result of 45.90

HTH