Inventory - Calculating the shelf life based on consumption and expiry date

**haroun** · 09-29-2012, 01:56 PM

Hi,

I am facing a problem with regards to calculating the remaining life of inventory based on the quantity in hand, its expiry dates and the monthly consumption. Its as follows :

Assuming today's date is 1st of September. I have 20 packets of cheese and the average monthly consumption is 4 packets. Ideally if the shelf life of those 20 packets is 6 months or more, then I can safely say that the total quantity will last for 5 months (20 divided by 4).However, with a slight twist in the data, assume that out of those 20 packets, 6 packets expire on 15 Oct, 7 packets expire on 10 Nov, and the remaining 6 on 15 December.

Now I know that the total inventory is not going to last for 5 months due to various expiry dates and on the basis of FIFO. If I calculate this manually, the answer is 3 and a half months.

How can I put all this in a formula to get the right answer ?

Please help.

Many thanks

**shg** · 09-29-2012, 02:24 PM

Maybe

       ----A---- B ----C---- ---D---- ---E----
   1   Exp Dates   Ship date Cheese # Exp Date
   2   15-Oct                      0          
   3   15-Oct      01-Oct          1  15-Oct  
   4   15-Oct      08-Oct          2  15-Oct  
   5   15-Oct      15-Oct          3  15-Oct  
   6   15-Oct      22-Oct          8  10-Nov  
   7   15-Oct      29-Oct          9  10-Nov  
   8   15-Oct      05-Nov         10  10-Nov  
   9   10-Nov      12-Nov         15  15-Dec  
  10   10-Nov      19-Nov         16  15-Dec  
  11   10-Nov      26-Nov         17  15-Dec  
  12   10-Nov      03-Dec         18  15-Dec  
  13   10-Nov      10-Dec         19  15-Dec  
  14   10-Nov      17-Dec        #N/A     #N/A
  15   10-Nov      24-Dec        #N/A     #N/A
  16   15-Dec      31-Dec        #N/A     #N/A
  17   15-Dec      07-Jan        #N/A     #N/A
  18   15-Dec      14-Jan        #N/A     #N/A
  19   15-Dec      21-Jan        #N/A     #N/A
  20   15-Dec      28-Jan        #N/A     #N/A
  21   15-Dec      04-Feb        #N/A     #N/A
  22               11-Feb        #N/A     #N/A

The ship dates in col C are manual entries.

In D3, confirmed with Ctrl+Shift+Enter and copied down,

=MATCH(TRUE, C3 <= INDEX($A$2:$A$21, D2 + 1):A$21, 0) + D2

E3 and copied down,

=INDEX($A$2:$A$21, D3)

In E3 and copied down

Inventory - Calculating the shelf life based on consumption and expiry date

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Inventory - Calculating the shelf life based on consumption and expiry date

Re: Inventory - Calculating the shelf life based on consumption and expiry date

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions