Forum Discussion
Formula to calculate lead times using inbound, outbound and stock data
Hi Mathetes
That's a neat solution, thank you for taking a look. Sadly, it doesn't quite solve my particular problem. Looking back my explanation wasn't very good.
What I should have explained better is that orders are fulfilled in order, so the earliest orders consume the stock first. This means that orders can be fulfilled when there is inbound even if the overall stock position is negative.
Here is a data set that illustrates the point - your method would give lead times of 3/2/1/0.
Inbound Outbound Stock Lead Time
25 50 25 1
25 50 50 1
50 50 50 1
100 50 0 0
In hindsight, the logic I gave earlier is just plain wrong. I think I'll stick to general terms to try and be clearer.
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.
As a result, I am attempting to calculate the correct lead time to give customers 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.
That means 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, 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.
I think that's all I can usefully add at this moment.
Thanks again for looking.
All the best, Matt.
To tell the truth, I wondered about that little wrinkle as i was doing this.
I'll give more thought to it. Just out of curiosity, are the numbers typically multiples of 5, like you have it in your example, or are orders (in particular) sometimes far smaller, just 1 or 2 items. Or are you dealing from a distribution center so retailers are ordering from you, rather than individual customers? And the inbound replacements, are they always in multiples of 25?
It would just help visualize the nuances if the numbers themselves were nuanced, if that in fact is the real case.
And, to put on my "process consultant hat"--is it possible to take action to build up inventory so that it's always (or "almost always) possible to ship immediately upon receiving an order? That is, can we prevent the problem from existing in the first place? Or is that just not the nature of your real world situation?
The numbers are typically 1s and 2s, as the orders that come through are individual customer orders, with an average quantity of approximately 2 units per order. There are multiple individual orders per week. I just made the numbers divisible by 5 to try and make it easier to visualise mentally.
We are instituting a safety stock programme to guard against stock outs, but there are bound to be times during the year when we need to extend the lead time.
Thanks again for looking at this!
All the best, Matt.
If the volume of individual orders is manageable, I think the same logic that we used (I used) to resolve your situation would work, but you'd have to make an entry for every individual order, rather than just weekly figures....then each order could have its own "lead time" calculation.
Hi, thanks again, but don't think that will work for my purposes. I am attempting to calculate the lead times based on planned volumes in and out (I have no individual orders to work with at that stage).
I've done some further digging though, and it looks as though the inventory management term for what I am trying to do is calculate available to promise quantities for each week.
Here is an explanation and some worked examples - https://ofienterprises.com/available-to-promise-inventory-calculations/
It looks as though the most relevant formula is the 2nd one (the items do not have an expiry date and orders are fixed) - CUMULATIVE ATP with LOOK AHEAD (ATP-CL).
Interestingly, it makes this comment at the end of the explanation: The observant reader will notice an alternate technique for calculating Cumulative ATP with lookahead. That is. Calculate the Discrete ATP first, then apply look ahead to those values to get the values for Cumulative ATP with Lookahead.
I wonder if that could be the answer to rendering the formula in Excel?
I have been puzzling at it for the past hour, but I've yet to have a breakthrough. I do wish formula work came to me more intuitively. I've got to finish for the day now, and will try again tomorrow.
Thanks again for looking at this. All the best, Matt.