LinkBack URL
About LinkBacks
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 relatively simple problem to start with, but I am now stumped.
Here's hoping that somebody already has something worked out for this.
Btw I am totally open to re-formatting the data or using helper columns.
Here is the problem...
Orders are individual customer orders that tend to be 2 to 3 pieces each and are fulfilled on a first in first out basis, so the earliest orders consume the available stock first.
The forecast sales quantities is made up of multiple individual orders for each week.
I need to work out how many weeks it will take to fulfill an order. Orders are still taken when the stock balance is negative, the customer lead time is lengthened instead.
The formula I am trying to identify will automatically calculate the lead time that should be set based on forecast sales and planned receipts.
Orders will be fulfilled on a first in first out (FIFO) basis, so the first orders received will consume the first inbound stock. Here is an example data set - if stock is available to fulfil the orders for that week, the lead time is 1.
Inbound Outbound Stock Lead Time
25 50 25 2
25 50 50 2
50 50 50 2
100 50 0 1
I think that the formula will have to check back against the starting balance of the previous week and the previous order quantity to identify if it can consume the stock.
If it can consume the stock (because earlier orders haven't claimed it already), the formula then needs to look ahead and calculate the number of weeks until the stock balance and inbound receipts fulfil this week's order quantity.
Many thanks if you're taking the time to look at this problem, I really do appreciate any help.
All the best, Matt.
Register To Reply
Bookmarks