FIFO Inventory Tracking

[excelhelp243]

I have multiple products with days until next shipment, inventory, and the start of a formula but I'm stuck. I don't know what to do to subtract the outbound inventory from a FIFO standpoint and keep my days in inventory calculation correct.

To best summarize where I'm at I have written some nested IF functions

The first one is if I have more inventory since the last load (meaning that my inventory today is greater than the last date of inventory) Then I add the last number of days in inventory to the new number of days.

The next portion is if inventory remains the same. Then I add the number of days since the last shipment and the cumulative days in inventory so far.

The next portion is if inventory drops to zero. Then we no longer have any days in inventory so it is 0 automatically.

The last portion is what I am struggling with. I need the new portion of inbound inventory to subtract from the oldest amount of inventory and then subtract the number of days the oldest was in inventory from my number of days in inventory.

My current formula is IF(CurrentRunningInventory>LastRunningInventory,LastDaysinInventory+CurrentDaysinInventroy,IF(CurrentRunningInventory=LastRunningInventory,LastDaysinInventory+RunningDaysinInventory,IF(CurrentRunningInventory=0,0,?)))

best example is below

Widget 1 10 days 25 widgets 10 total days in inventory
Widget 1 5 days 30 widgets 15 total days in inventory
Widget 1 20 days 10 widgets 35 total days in inventory
Widget 1 5 days -15 widgets 40 days in inventory
Widget 1 5 days -40 widgets 30 days in inventory

The italics part are what my formula cannot return correctly.

 

[oldbrewer]

Widget 1 10 days 25 widgets 10 total days in inventory

infers to me that 2.5 widgets are used every day

can you state the issue more clearly please

 

[excelhelp243]

Sorry if I wasn't clear. Here is a much more organized example with my actual numbers. This is just an inventory location so there is not consumption just shipping in and shipping out. Formulas used in this are (from row 3 '8/8/2014 Apples')

Days until next Shipment: =DATEDIF(A3,A4,"d")
Inventory: =IF(E3=0,0,DATEDIF(A3,A4,"d"))
Days in Inventory: =IF(E3>E2,D2+D3,IF(E3=E2,D3+F2,IF(E3=0,0,"*")))

Date	In/Out Tons	Commodity	Days until next shipment	Inventory	Days in Inventory
5/12/2014	1.48	Apples	88	1.48	88
8/8/2014	2.82	Apples	18	4.30	106
8/26/2014	(1.48)	Apples	7	2.82	*
9/2/2014	(2.82)	Apples	161	0.00	0
End of Apples Inventory				0.00	0
3/20/2013	3.77	Bananas	91	3.77	91
6/19/2013	(3.77)	Bananas	0	0.00	0
9/3/2013	15.10	Bananas	13	15.10	13
9/16/2013	16.52	Bananas	0	31.62	13

Is this more clear? The * is where I cannot figure out the right formula.

 

[excelhelp243]

I don't know if it's allowed but I posted in another forum as well where I can attach an excel file. The link is here.

 

[oldbrewer]

Date	In/Out Tons	Commodity	Days until next shipment	Inventory	Days in Inventory
5/12/2014	1.48	Apples	88	1.48	88 so you have 1.48 tons of apples in stock 5/12/2014 how is this translated into days or are you just trying to record stock levels rather than how long stock will last with a predicted demand

<tbody>
</tbody>

 

[oldbrewer]

Date	In/Out Tons	Commodity	Days until next shipment	Inventory	Days in Inventory
09 Feb 14	1.48	Apples	167	1.48	167
26 Jul 14	2.82	Apples	13	4.3	13
08 Aug 14	-1.48	Apples	119	2.82	119
05 Dec 14	-2.82	Apples		0	0
	I have changed the date order with newest item last
	you cannot put anything in the days to next shipment column for 5 dec 14
	unless you work with planned next shipment
	so you had 1.48 tons for 167 days
	4.3 tons for 13 days
	2.82 tons for 119 days
	and zero inventory from 5/12/14
	do you need col D and col F ?

<tbody>
</tbody>

 

[excelhelp243]

Date	In/Out Tons	Commodity	Days until next shipment	Inventory	Age of OLDEST Inventory
09 Feb 14	1.48	Apples	167	1.48	167
26 Jul 14	2.82	Apples	13	4.3	180
08 Aug 14	-1.48	Apples	119	2.82	132
05 Dec 14	-2.82	Apples		0	0

<tbody>
</tbody>

That's what I'm trying to keep track of. For days until next shipment I use a formula that uses todays date for the last piece of inventory.

I'm not concerned with what the amount of inventory is, the concern is what is my oldest piece of inventory in stock.

I want to be able to say that in the however many tons of apples in inventory the oldest apple in the stack is however many days old.

 

[oldbrewer]

on 8 aug 14 you shipped out 1.48 tons from 4.3 tons in stock, are you assuming the control measures in the store are so tight that only the 1.48 tons shipped in on 9 feb 14 were shipped out ?

 

[excelhelp243]

Yes. That is the assumption. But the outbound is not always an exact match to the inbound.