Forum Discussion
Formula to calculate lead times using inbound, outbound and stock data
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 simple problem to begin with, but I'm struggling with it.
Here's hoping that somebody already has something worked out for this. Below is the logic I can see working, but is it possible in Excel?
Btw I am totally open to other ways of handling the calculation or formatting the data.
Many thanks if you're taking the time to look at this problem, I really do appreciate your help.
All the best, Matt.
Here is my effort at the calculation logic. I am wanting to show lead times up until 12 weeks, so the calc needs to look ahead for x number of weeks please...
Calculate weekly volume = (stock+inbound)-sales
Count weeks until weekly volume is greater than zero
If Weekly volume > 0, Enter 0
If Weekly volume < 0, check future Inbound data (look up to 12 weeks ahead, but stop as soon as volume is > 0)
Week 1: If sum of balance + inbound > 0, then provide count (so 1 in this instance)
Week 1: If sum of balance + inbound < 0, then look at next week
Week 2: If sum of balance + inbound > 0, then provide count (so 2 in this instance)
Week 2: If sum of balance + inbound < 0, then look at next week
Week 3: If sum of balance + inbound > 0, then provide count (so 3 in this instance)
Week 3: If sum of balance + inbound < 0, then look at next week
Week 4: keep going until a positive balance is achieved (up to 12 weeks)
I have also attached a simple Excel example that shows what I trying to calculate automatically.
9 Replies
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.
