FIFO calculation

**MsZy** · 07-04-2013, 01:52 AM

I do have a sheet with multiple items. My aim is it to figure out the storage period of an item. Meaning how long has an item been in the warehouse from the time received to the time taken out (storage period)? We issue the items on the principle of first in first out.

See below an example.
I want to calculate the average days of the plugs being in the warehouse. We get 35 in on 1 Janunary and take 2 days later 30 out. Meaning that 5 still left from 1 January. On 5 Janunary the warehouse receives another 10 in. On 17 January 15 plugs are taken out. 5 out of the 15 had been stored for 16 days (17 January - 1 January) and the other 10 are stored for 12 days (17 January - 5 January).

Date Item Quantity Movement
1/1/2011 PLUG 35 In
1/3/2011 PLUG 30 Out
1/5/2011 PLUG 10 In
1/17/2011 PLUG 15 Out

1/2/2011 Stamp 40 In
1/3/2011 Stamp 30 Out
1/5/2011 Stamp 5 In
1/17/2011 Stamp 15 Out

**humdingaling** · 07-04-2013, 08:32 PM

as to no reinvent the wheel of your spreadsheet
please provide sample of your structure and sample data in and out

we don't want to give your response that requires you to change your whole sheet in the process

**MsZy** · 07-05-2013, 02:21 AM

See attached the structure of my data.

The highlighted part is my proposal.

If you have a look in the red cells, you will see that the formula doesn’t work correctly. For instance in K2 its supposed to be 1000 instead of 0. And the formula for the row "days" is missing.

Thanks for your assistance!

**humdingaling** · 07-05-2013, 02:38 AM

try in K2
=IF(D2=D1,J2+K1,J2)
and m2
=IF(D2=D1,L2+M1,L2)

**humdingaling** · 07-05-2013, 02:41 AM

your up down isnt meant to cross over products i assumed so
in column O
=IF(D2=D1,IF(N3>N2,"Up","Down"),"New")

put new because dont know if you want it to be up or down
i notice you got some outs with no stock

**MsZy** · 07-07-2013, 04:46 PM

hi humdingaling,
thx for your help. I still am finding it hard to retrieve the time of the storage. Any idea on that?

**humdingaling** · 07-07-2013, 08:02 PM

Fixed up column P, slight issue with up/down before
add column H for New product, pretty self explanatory

Column J gets a bit complicated and not sure if its what you want but should point you in the right direction

in code

Please Login or Register  to view this content.

the logic below

So if H says new product then days = 0
next if product is not new, check date of previous line
if same day (ie =0) then look at the above cell for the date...this should equal zero most of the time
next if the product line of the above is OUT
then use the difference in the days + the days of the above line
otherwise use date of current line minus previous line

as mentioned probably not 100% but point you in right direction