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

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