Forum Discussion
Formula to calculate lead times using inbound, outbound and stock data
Hi, Matt
I'm on the verge of understanding your need here, but want to ask a question or two.
- Do you have data on expected "inbound" quantities out through week 12? If so, why not show that (just to make the picture complete)?
- What does "Lead time" actually mean? I'm assuming that if it's 0, for example, there's no lead time needed, so you're able to ship out "item X" immediately. (I realize that the answer here is obvious to you, but I'd still like to make sure I'm grasping the picture clearly.) If, on the other hand, the number that one gets by going out to the future turns out to be 1, 2, 3 etc., that means the customer is going to have to wait 1, 2, 3 (etc) weeks before an outbound shipment can be created.
- You write that for week 2, just to give an example, If sum of balance + inbound > 0, then provide count (so 2 in this instance), BUT wouldn't it also depend on the size of the Outbound orders (assuming that "Outbound" represents known orders), or are you taking that into account in your "Stock" number [which is really what you mean by "Balance")?
Anyway, it would help to have those expected inbound figures for weeks 6-12 in order to test the formulas I (or someone else) creates.
@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?