Forum Discussion
Formula to calculate lead times using inbound, outbound and stock data
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.
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).
Oh, I thought you were trying to do this so you could make a commitment to individual customers (and, I suppose, that is ultimately what you want to be able to do)...
I do wish formula work came to me more intuitively. I've got to finish for the day now, and will try again tomorrow.
The only way any of us ever get to that point is "practice, practice, practice." It helps to have time to read reference books--I used to do this back in the day when software used to come with a couple of volumes of text. Lotus 1-2-3 was where I cut my teeth on spreadsheets, and they had (if memory serves) as many as three volumes...and I would take them on my train ride home and read them, often saying under my breath, "So THAT'S how to do it!" when I came across a new function. Nowadays, you need to get to a bookstore (brick and mortar or on-line) and avail yourself of a few texts on the subject. But I'm quite serious, reading through a good book on Excel functions will open your eyes to the amazing versatility of this software package.
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.