Ideas on how to track inventory expiration dates

[David77]

NOTE* Please skip until the next line highlighted in bold if you're not interested in seeing how my setup functions. Thank you!

Hello everybody,

I am currently managing a minor inventory consisting of 101 unique goods. Currently my excel sheet looks like this:

To briefly explain the sheet:

Column F = minimum stock
Column G = current stock. I have a conditional formatting filter here that highlights the stock in a red color if it is < or = the value in the corresponding F column
Column H = Add a value to the stock. So if I enter in 5 on H3, G3 will change value to 11 and the number 5 will immediately disappear from H3 allowing me to enter a new number to add to G3
Column I = Subtract a value to the stock. So if I enter in 6 on I3, G3 will change value to 0 and the number 6 will immediately disappear from I3 allowing me to enter a new number to subtract from G3
Column J = This shows me the last time that the stock in column G was updated. It also takes account for if the value in column G was updated by adding/subtracting to it from column H and I, so it's not completely dependent on manual alterations to the numbers in column G.

Finally, the macro is shown to the right, which is given by:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Columns("H:I"))
If Not r Is Nothing Then
Application.EnableEvents = False
For Each c In r
With Cells(c.Row, "G")
If IsNumeric(.Value) Then
.Value = .Value + IIf(c.Column = 9, c.Value, -c.Value)
Cells(c.Row, "J").Value = Now
Cells(c.Row, "J").NumberFormat = "dd-mm-yyyy"
c.ClearContents
End If
End With
Next c
Application.EnableEvents = True
End If
End Sub

Now, to my actual question:

Currently, I receive my stock at random intervals ranging anywhere from 7-60 days. Whenever this happens I naturally have to update my stock, which works fine. However, I have some trouble with my expiration dates.

Every batch of items I receive has the same expiration date (meaning that if I order 50 items of "Material XYZ 527" all 50 of these similar materials will have the same expiration date (for example 10-12-2020)). However some times I may still have 1 or 2 of these laying around when I receive a new batch, thus meaning I can have the same item in one shelf but with different expiration dates.

I want to somehow keep track of all these expiration dates through a FEFO (first expired, first out) concept, so I can check and see how many items for each material I have expiring on this and that date.

I would love for this to somehow be compatible with my current system.

Does anybody have any experience with this or good ideas? I would truly appreciate any inputs that I can get! If anybody has some nice videos, guides, links or anything that might help, please do not hesitate to drop those in the comments either

Thank you so much for your time everybody! It is truly appreciated

Best regards,
David

 

[jasonb75]

Could you assist me on how to implement it?

Sure, that is not a problem. Although, as I said above, I would need to see your revised layout first (or at least the intended layout).

Your original screen capture had no reference to product identifiers, expiry dates, etc. In addition to this, the example of the method that I suggested was done on a single sheet. There would be some significant differences in the code if you want to set it out over two sheets so that only the necessary information is seen by the person using it. One key point to remember with things like this is that the person helping you will not know the specific needs of your company or line of business. It is rare that any two people present there data in an identical format.

so whichever good is closest to the expiration date

Also, could you clarify if it is ok to use expired stock? 'good' implies that it should be future date, yet closest implies that out of date could be permitted.
If there is expired stock, should the user be informed of date that it expired and given the option to use it or reject it? Should expired stock automatically be deducted from the total and in some way recorded as such?

 

[David77]

Hello Jason,

I currently have this (from you):

Where I'd like a column of material numbers and expiration date to be inserted in the two respective areas. The big sheet with all of the heavy information (how much we have in stock etc), looks like this:

Where:

Afdeling = Department (irrelevant for tracking expiration date)
Varenummer = material number
Beskrivelse = Material description
Location = Location of the good (irrelevant for tracking expiration date)
Enhed = Unit (irrelevant for tracking expiration date)
Bestillingsmængde = How much to order when the minimum stock has been reached (irrelevant for tracking expiration date)
Lageret = Current goods in stock (turns red when below minimum stock)
Pluk fra lageret = subtract from stock (-)
Put på lageret = add to stock (+)
Last quantity shift = Date in your sheet
Bestillingsstatus & PO nummer = order status (irrelevant for tracking expiration date)

I would imagine that the easiest solution would be to create a new sheet (next to this sheet) with the vba formula that you created and then enter the expiration dates here through the system that you created (thank you so much once again!).

Expired stock should never be used and if it has been expired, it would be amazing if some sort of notification could inform us of this or anything similar to this.

Thank you so much Jason! I truly appreciate your help and time

Let me know if you have more questions!

 

[jasonb75]

Just one thing I'm not seeing at the moment. Where are we finding the expiry date in order to track it (In your sheet, not my example)? Also, does everything have an expiry?

How much of that information needs to be transferred to the second sheet? Looking at some part I'm thinking now that I may have misunderstood what you asked originally. With my way or working, once stock has been added the purchase order number would be transferred with the stock record to the second sheet and cleared from the first one. Is that going to work for what you need? From the way I read your post, it looks like the PO number is ordered but not yet received?

Then the last thing to figure out (for now) is location, in my view that should show the location of the stock to be used based on fifo date. Assuming that any stock line could be stored in multiple locations, a second location column would be needed to track newly received stock.

Having now seen more detail, my personal method would involve userforms, but I think that it might be too advanced for you at this stage and it is unfortunately not something that I have enough free time to work on at the moment.

Once I have your feedback on the above I'll see what I can come up with. Probably not going to get much done until the weekend though, things like this I prefer to do when I can spend a couple of hours on it without interruption.

 

[philborrero]

how did this end, love to see the output...