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

MattLudlam

New Member
Joined
Mar 9, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,362
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top