Formula to calculate lead times using inbound, outbound and stock data

[mattlud]

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.

[#2]

@mattlud 

Hi, Matt

I'm on the verge of understanding your need here, but want to ask a question or two.

1. Do you have data on expected "inbound" quantities out through week 12? If so, why not show that (just to make the picture complete)?
2. 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.
3. 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.

[#3]

@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.

[#4]

@mattlud 

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.

[#5]

@mathetes 

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.

[#6]

@mattlud 

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?

[#7]

@mathetes 

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.

[#8]

@mattlud 

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.

[#9]

@mathetes 

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.

[#10]

@mattlud 

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.