Formula to manage inventory of raw materials

Dear Excel Guru’s,

Not my first time on your blog, but have trouble to figure out my old account. All of you helped me very much with my previous questions, many thanks! Once more I have a problem that is difficult for me to solve:

I am trying to build a new spreadsheet to track when I need to order raw materials for a soft drink product.

My soft drink in this example is made of the following raw materials:

• Citric Acid
• Glass bottle
• Bottle cap
• Label

All these raw materials come with different minimum order quantities and lead times.

I also added some more variables:

• I want to keep twice the amount of raw materials necessary for producing a batch
• There is a waste of 10% citric acid for each batch produced
• There is a minimum inventory amount (threshold) of final product (cases of drinks). If below this value —> produce new batch

I have trouble calculating the following three things:
1. With the lead time and minimum stock requirements in mind, when do we need to re-order the raw materials?
2. With the lead time in mind, when can we expect the new stock to arrive in the warehouse?
3. How can we keep track of total stock for the individual raw materials?

I have been looking at this problem for a few days now and cannot figure it out. I gave it a try with a bunch of different tables that you can also see in the excel sheet, but I feel that this might not be the way to go. Please have a look at the example and feel free to edit it wherever you feel necessary, I am just not so sure what to do with the spreadsheet at the moment.

Many thanks for the help!