Set a Low Inventory Reminder/Alert in Excel?

[ALM]

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.

[#2]

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 data

[#3]

Hi @Donald_Genes_, Please see attached for fake example.  Hopefully, this is what you're looking for. Let me know what you think.. 

[#4]

Hi @mathetes you were kind enough to help me out with a formula on another post a few weeks back and I was hoping to see if you could review this post and let me know your thoughts?

[#5]

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.

[#6]

@mathetes Please review attached and let me know if you think this will help.  

[#7]

@ALM  I am not entirely sure if this is the kind of thing you're looking for. Again, you are familiar with your data, your situation. So I was doing my best to guess from the spreadsheet and your earlier description. The formula shown below is in column R, and gets the total of the allocated quantities from the inventory sheet for each item number shown in column B of the forecast sheet. You can then compare those totals with your quarterly projections---I"m assuming you can do that part.