calculating rolling run out dates of stock

[ADAMC]

Hi all, I have a very simple spread sheet I have to look at daily and I wondered if anyone might be able to help me with a formula or macro which can save me endless hours working things out?

Basically, I have a stock report which I am looking to create a line of balance for.

A1 - TODAYS DATE
B3 - Material number
C3 - daily usage of material
D4 - CURRENT STOCK LEVEL
E4 - NEXT DELIVERY DATE
F4 NEXT DELIVERY QUANTITY
G4 2ND DELIVERY DATE
H4 2ND DELIVERY DATE


Basically I want to know from the current date, with stock I have at the daily usage in column C whether I will make it to the delivery date in E4 or what gap will I have and then after the next delivery on stock I will have on the delivery date will I make it to the second delivery date and if not what gap will I have?

At the moment lets say:
Part number ABC123 I use 10 a day of these and I have a delivery of 100 in 9 days exactly - my stock gap is minus 1 because I have 10 left |(an extra day) but my second delivery is 12 days out for 100pcs so if you include the 10 I had in stock from the previous delivery I have a 1 day gap.

I hope this makes sense and someone can help me. columns on my spreadsheet are exactly as above and no columns are used beyond H.

Thank you

 

[ADAMC]

can anyone help at all? All I want to do in simplest term is calculate an absolute run out date based on:

PART NUMBER
CURRENT STOCK
DAILY USAGE
DELIVERY DATE
DELIVERY QUANTITY
FOLLOW ON DATE
FOLLOW ON QTY
So maybe a formula? I would enter part number daily usage and any future supply chain deliveries I had and it would work out with my daily usage and current stock and tell me if I had any delivery gaps or what my absolute run out date including all deliveries?

Like a rolling total but including future delivery dates and quantities.

Thanks