Hi I don't use excel often but have been asked to identify the earliest expiry date based on an excel spreadsheet that is used for stock management so looking for some assistance here.
I have a list of records of stock orders received which has the type of stock, number of stock and the expiry date of the stock - example image attached below.
order summary.gif
When purchases of the stock are made, the earliest stock received will be sold.
Based on the number of stock remaining on hand (which I can obtain from storage), I would like to see what the earliest expiry date would be and the quantity of stock that the date is applicable to.
For example:
I have received a total of 50 for the stock item '590002' where 20 will expire on 30/06/2014, 20 will expire on 16/08/2014 and 10 will expire on 7/07/2015.
If 27 of the 50 have been purchased and I enter this value somewhere on the spreadsheet, I'd like to be able to see what the earliest expiry date is on the remaining 23 stock and
As the stock expiring on the 30/06/2014 has been all sold, then I would expect to see that it has been able to calculate that the earliest expiry date is 16/08/2014 and is applicable to 13 stock.
Hope this makes sense and many thanks in advance!
Bookmarks