Forum Discussion
Set a Low Inventory Reminder/Alert in Excel?
Our operations team has a very complex inventory report which pulls information from several platforms to help plan inventory for our regular customers. Unfortunately, we do not have an automated system in place which notifies us when we need to replenish our inventory. I would like to set up a formula to create an automated reminder for when inventory falls below a certain quantity.
Info I have on tab #1 of the file which I believe is necessary to create a formula…
Allocated QTY – This cell value identifies the quantity of material which is in stock
Unallocated QTY – This cell value identifies the quantity of material that is currently in the process of being produced at the factory
Info on tab #2 of the file which I believe is necessary to create the formula.
Average quantity – Customers average quantity per project
QTR forecast – Quantity of projects per fiscal quarter
The goal is to have 3 months or a fiscal quarters worth of material in stock at all times. If the “Allocated QTY” falls below the projected forecast for 3months/fiscal quarter, and the "unallocated cell" is blank, I would like to create a column titled “Inventory Health” to have an automatic reminder of “Replenish Hold”
Any help is appreciated.
Info I have on tab #1 of the file which I believe is necessary to create a formula…
Allocated QTY – This cell value identifies the quantity of material which is in stock
Unallocated QTY – This cell value identifies the quantity of material that is currently in the process of being produced at the factory
Info on tab #2 of the file which I believe is necessary to create the formula.
Average quantity – Customers average quantity per project
QTR forecast – Quantity of projects per fiscal quarter
The goal is to have 3 months or a fiscal quarters worth of material in stock at all times. If the “Allocated QTY” falls below the projected forecast for 3months/fiscal quarter, and the "unallocated cell" is blank, I would like to create a column titled “Inventory Health” to have an automatic reminder of “Replenish Hold”
Any help is appreciated.
6 Replies
- I would want an example file that included at least 10 rows of data, so as to test any formulas, and the data should be such that it produces different results to test whether or not the conditions are being set appropriately. You are very familiar with the reality that this is supposed to represent, so minimal data may suffice in your mind; but folks from the outside need a more complete picture. Help us help you by painting a fuller picture of the kinds of situations that will arise in the real world, situations in which some of the data requires a replenishment reminder, some is just fine, etc.
- It can be done if I see sample file..this requires logical conditional Analysis..
Give us something to help you with maybe a fake example of dataHi Donald_Genes_, Please see attached for fake example. Hopefully, this is what you're looking for. Let me know what you think..
