Formula to calculate lead times using inbound, outbound and stock data

[MattLudlam]

Hello everyone,

I am getting in touch because I can't work out the correct formula I need to calculate lead times. Is it a nested IF, is it a COUNTIFS or is it something else entirely?

All I need is the number of weeks it will take to fulfil an order when using weekly data. It seemed a simple problem to begin with, but now appears really tricky.

Here's hoping that somebody already has something worked out for this. Below is a method I can see working, but it may not be possible in Excel?

Btw I am totally open to other ways of handling the calculation or formatting the data.

Many thanks if you're taking the time to look at this problem, I really do appreciate your help.

All the best, Matt.

Here is my effort at the calculation logic. I have given an example where an item goes out of stock and then takes 3 weeks to come back in. I am wanting to show lead times up until 12 weeks, so the calc needs to look ahead 12 weeks...

Calculate weekly volume - (stock+inbound)-sales
Count weeks until weekly volume is greater than zero
If Weekly volume > 0, Enter 0
If Weekly volume < 0, check future Inbound data (look up to 12 weeks ahead, but stop as soon as volume is > 0)
Week 1: If sum of balance + inbound > 0, then provide count (so 1 in this instance)
Week 1: If sum of balance + inbound < 0, then look at next week
Week 2: If sum of balance + inbound > 0, then provide count (so 2 in this instance)
Week 2: If sum of balance + inbound < 0, then look at next week
Week 3: If sum of balance + inbound > 0, then provide count (so 3 in this instance)
Week 3: If sum of balance + inbound < 0, then look at next week
Week 4: keep going until a positive balance is achieved (up to 12 weeks)

Here is a simple Excel example with inbound/sales/stock data plus examples of the lead time column where I'd like to replace the static figures with a dynamic formula - Lead Time Example Sheet