This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
First ever post here. I am stuck trying to figure out how to proceed...I have a list of open overdue orders from our SAP system, I also have an Inventory number from the same system. I finally have an incoming supply plan for the next 8 weeks.
What I am trying to figure out is how to calculate when orders can be filled based on inventory and incoming supply.
I worked on Challenge 65 here to help me understand how to calculate a remaining inventory here:
Now my challenge is taking the left over orders that were not able to be filled with current inventory and use the incoming supply plan to figure out when it will be supplied. I have provided some mock data of what I am working with and what I am trying to get to.
Interesting one. I’ve attached an iterative macro solution here (and added an 'Order 10' for testing):
Note that the way I’ve built this is to is to fulfill orders in delivery date ascending, so prioritizing the earliest unfulfilled order and waiting until it can fulfill that. Therefore the iterative macro will take one row each time (the earliest unfulfilled order) and process from there.
It looks like in your example that you’re fulfilling a later unfulfilled order (with lower [Qty]) where possible, even though this impacts results further downstream. E.g. you're fulfilling order 7 before the others, which later means order 9 isn't fulfilled until a later date than mine (mine 2 Sep, yours 9 Sep). My concern with this approach would be that a massive unfulfilled order might never get served because it would forever prioritize orders way in the future, for example... but the reverse could be said of the first (attached) approach too
Obviously both are viable, if it's the latter that you're after then hopefully you can still just tweak this iterative macro into prioritizing how you want (e.g. sorting by Qty, or changing the filters in the macro)
Yes, that is a concern that I was also thinking about, but left that out of the conversation for now because I was trying to figure out how to approach it. In this particular business use case I was thinking of applying additional logic that would take a split based on % of Global forecast for that specific country and apply that to the remaining Qty and then continue allocating down the line. This be a good balance of maximizing the number of orders while partially filling the large orders. If I did that I would need to figure out how to split the line with the decremented Qty so the remaining can be filled later.
The problem gets pretty complex the deeper I go.
Thanks for the reply. This does answer the original question.
I just realized something. Your solution provides an answer shows 370 can be filled on 2 Sep. when only 300 will come in. I am trying to figure out how the flow is calculating this. Does this keep a running total of everything being received and then filling the order? When I am looking at what has been filled vs. what has come in I am only able to come up with a max of 350. Do you happen to have any insight on this?
I think this is correct though (if my understanding of the problem is right - please feel free to correct me if not!)
The reason being:
- we have to fulfill 90 remaining (from order 2); to do this we use 50 (from 22 July) + 40 of the 150 (from 12 Aug). This gives us 110 left over(from 12 Aug) to carry forward to our next rows.
- so on 2 Sep we have 300 come in, but + the 110 carried over = 410 to play with. We need 370 to fulfill orders 6, 7, 8 and 9 (=370), which is < 410
This is why I’m using the running total in the iterative macro, to reflect that we may have more than the exact value that came in that day – am I missing something though? Feel free to correct me if I am!