Forum Discussion
Formula to calculate lead times using inbound, outbound and stock data
@mathete
Hi, thank you again for taking a look at this.
Data - I have added in data up to 12 weeks.
Lead time - that's it exactly, the number of weeks until there is stock to ship to fulfil the order (with the first orders in being the first ones out).
Please let me know if there is anything else you want to know.
All the best, Matt.
Matt -- I rearranged your data. I tend to think more clearly with raw data arranged as a table, i.e., in the form of rows arranged vertically. I also only dealt with SKU 1. I don't know how many products you actually have, but at least at this stage, what I've done shows how your "Lead Time" calculations could be done.
My approach uses the WEEKNUM function to calculate number of weeks between (a) the time when stock will equal or exceed 0 and (b) the current week. It results in a single digit representing the weeks until there's a balance sufficient to fill all orders.
To do this more cleanly, I also added a "helper column" to determine whether or not the current balance is positive. That formula (or a variation on it) no doubt could be nested in the primary WEEKNUM formula, but at this stage, where you are wanting to master the basic approach, I prefer the clarity of separate formulas.
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 0In 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.