production process with two products, calculation of the end date

Dear All,

I would like to do the following in Excel:

I have a chemical product (compound) that is evenly brought into the production process. It is available in two different strengths:

Product X, strength 10 (available package sizes: 25, 50 und 100)
Product X, strength 30 (available package sizes: 50 und 100)

The planner shall at first enter the available quantities (bought, ordered or in stock):

Quantity product X, strength 10: To be entered
Quantity product X, strength 30: To be entered

The production eeds exactly a dose of strength 50 every day. This is done by using one unit of strength 30 and two units of strengt 10.

The user shall now enter the production start date (example: 1 January). Excel should then calculate the date when the last unit will be used in order to forecast when we run out of stock. Depending on the quanitities, it might be the case that only one of either strenghts would run out. In this case it should calculate the last day where the product can be produced.

Ideas anyone?

Thank you in advance

PP81

That's incredible. It works and it's a simple solution without any scripting. You're a real Guru! Thank you soooo much :-)

I have two issues which make it a bit more complex:

1.
We usually have two production cycles every day. In some processes we need to add the compounds twice a day so that it might happen that we run out of stock after the 1st round of the day. Is there a way to cover this aspect?

2.
Is it possible to create a schedule for the support staff. The ideal output would be list like this (columns: Date, Time, Cycle, Message/Note)

Date 0|Time (6am) | Cycle 1
Date 0|Time (2pm) | Cycle 2
Date +1 |Time (6am) | Cycle 1
Date +1 |Time (2pm) | Cycle 2
Date +2 |Time (6am) | Cycle 1
Date +2 |Time (2pm) | Cycle 2
Date +3 |Time (6am) | Cycle 1
Date +3 |Time (2pm) | Cycle 2
Date +4 |Time (6am) | Cycle 1
Date +4 |Time (2pm) | Cycle 2 only five supply for 5 cycles left, please reorder
Date +5 |Time (6am) | Cycle 1
Date +5 |Time (2pm) | Cycle 2
Date +6 |Time (6am) | Cycle 1
Date +6 |Time (2pm) | Cycle 2
Date +X |Time (6am) | Cycle 1 out of stock after this cycle, please refill
(end of this list, process would start again)

Unfortunately, the compounds can only be filled after the machine is out of stock. Therefore, the support staff must receive the message "out of stock after this cycle".

Any idea?

Wow. Thank you. These formulas are quite complex.

Your assumption is entirely right. Sometimes one would start with cycle 1, sometimes with cycle 2. Where would you insert the dropdown (for the dropdown I'd choose Cycle 1/Cycle 2.

Cycle D and F do not work for me. But this could be due to the fact that I can try this with Excel 2003 only. Can try it with a newer version later. Or do you think there could be an error which prevents this from working?

Cell E2 is already filled... even when D2 is empty. Is this correct?