Linear Programming: Transshipment with Excel Solver (Network Flows Part 3)
Вставка
- Опубліковано 26 лип 2024
- Enjoyed this content & want to support my channel? You can get the spreadsheet I build in the video or buy me a coffee! Links below:
Buy me a coffee: www.buymeacoffee.com/tallysyunes
Buy complete spreadsheet: www.buymeacoffee.com/tallysyu...
Buy me an item from my wishlist: www.buymeacoffee.com/tallysyu...
If you haven't watched my video on the Transportation problem (and aren't familiar with that kind of network optimization problem), I suggest you do that first. Here it is: • Linear Programming: Tr... .
It may also be helpful to watch the Assignment problem video before this one: • Linear Programming: As... .
The transshipment problem extends the transportation problem by adding nodes to the network that have both incoming and outgoing arcs. These are known as transshipment nodes. These nodes can be simple pass-through points (i.e. they neither supply nor consume goods), or they can have either a supply or demand of their own. In this video I cover all of these variations of the problem, and also explain what happens when supply and demand do not match.
This is an excellent overview of this problem. Thank you for making and posting superb videos on your channel. You really should seriously consider publishing a textbook; it would become very popular.
Thank you very much for your feedback!
Thank you Tallys. Was struggling with these types of problems. You explained it in an understandable way.
You're welcome. Glad it helped!
Good video. Thanks! Can be hard to get from the linear programming to the Excel set-up; you've been clear and concise.
Thank you! I’m glad the video was helpful!
Thank you so much! I've gone through optimization in my MSc but the class lecture was way simpler than the course assignment demanded and your explanation just saved the day. Absolutely brilliant. Thanks!!!
You're very welcome! Glad to hear it was useful!
Just adding to this thread. I'm doing a MProf course which requires this content... I really struggled to understand anything from the course content. But you... you made it seem so easy... i can't thank you enough. Subscribed!
So happy to hear the videos have helped you! And thank you for subscribing!
Thanks a lot, Professor Tallys, maybe you save my life hahaha I took your example and adapted to the airline network optimization problem by including a variable that represents the number of aircraft. Then I adapted the capacity constraint to be: flow
Cool! Nice to see you managed to adapt this one to another situation. That's the spirit!
Professor Yunes, thank you very much for this video, I'm undertaking right now an MIT online course where we see these kind of problems, but as you first showed, they showed the matrix method, and I find the one you teach here simpler and easier to understand, therefore I'm thankful.
Regard from Mexico
César Figueroa
You’re welcome. Glad to hear this was helpful!
Hello Cesar...can you share which MIT course it is?
your videos are super helpful, thanks for sharing
I'm glad to hear! You're welcome!
thank you
Thank you sir, Very well explained. Can you please make a video on the multicommodity transshipment problem using excel?
I'll add that request to my to-do list. In the meantime, you can think of it as multiple transshipment problems on the same network. You need to create one set of variables for each product/commodity and write separate (in)-(out) constraints for each set to make sure each product flows properly. What will differ is that each node will have potentially many supply and/or demand values (one per product). And if there are arc capacities, they may apply to all products that flow through the arc together or individually.
Great. THis can actually be a generic model for solving both transportation and trans-shipment contexts. WIth the latter, there will obvously be only inflow-only and outlow-only points, there won't be any pass-through points. But that given, it will work fine in both settings. I've set up both structures - the one illustrated in the transportation exercise and the one illustrated here - and it's fine.
Yes. You are correct because transportation is a special case of transshipment.
Hi, thank you so much. Enjoyed the video. what if the flow of goods has to happen through warehouse? I mean no direct shipments to customer from factory. All the shipments will flow from factory>>warehouse>>customer
You simply delete those direct shipment arrows from the picture and delete the corresponding variables (gray cells) from the Excel model. Everything else stays the same.
Thank you so much professor! How do I determine the cost bc this only determine the amount right? I should be able to determine it after I use solver. Is that correct?
I'm not sure I understand your question. The final total cost is what appears in cell H11 after you solve the problem.
Hello Prof. Yunes...Thanks for this great video. This is very helpful. I am trying to use this model to match the demand and supply from various suppliers. I am wondering if the model can be used to calculate month over month. How to adjust it if capacity in the network is lower than demand for some period and more in other. In other words how to accommodate inventory build plan in this type of model so that we can create buffer stock in the months where capacity of the supplier network is more than the demand and use it when demand is more that supply. Can Excel solver handle such issue? Do you have any pointers for me if it can?
Your help is greatly appreciated.
Vin
Yes. It's possible to do what you describe. It would be equivalent to creating as many copies of this one network as there are time periods in your planning horizon. Instead of Xij variables, you'd have Xijt variables: how much is shipped from node i to node j in period t. You'd then also add time indices to the supply and demand amounts, as well as, create inventory variables for the amounts stored in each node at each point in time.
@@TallysYunes thank you Prof. For the quick response. I will have to digest this reply and try it to model. If you are planning for new videos please consider helping us out with this type of complexity. I hope I can figure this out before then. Really grateful to you for your help.
Thank Tallys for the clarification, appreciated. I have one more dounbt- What if i have more than 2 intermediates in the supply chain? I mean, Movement will happen from Factory>>Warehouse>>CNF agent>>Customer?
Just add the corresponding nodes and arcs to the picture and model it the same way I describe in this video. One variable per arc, one constraint per node, etc. Nothing changes conceptually when you add more nodes. The solver will figure out the least expensive shipment plan.
@@TallysYunes Thanks a lot. It was very helpful
How would this change if the warehouses in the situation have a maximum capacity?
In terms of the math, you can write (inflow into warehouse)
Can the capacity be different for all cases? (for example: in the case of air+land freight)
Yes, of course. Each arc can have its own specific capacity.
Yunes would you have any problem solution related to quantity production, maximizing profit, with constrains of machine hours and man hours, but simultaneously with information about the manufacture operation a specific hours per week. I got confuse, when for instance, I'm given: man hours, numbers of machine, machine hours, number of hours available for all machines, but at the same time the problem a number of operation hours per week of the plant. Help please.
Think about each kind of resource separately. A product can consume a certain amount of man hours and a certain amount of machine hours. So your constraints could be:
(man hours consumed)
Hello, i am trying to solve a pickup and drop off problem in which i pickup from a node and must drop off only in a specific node. Im a student and that's not in the course material. Any information would be appreciated, thanks
I'd need to see the full details of the problem you're trying to solve in order to answer your question.
@@TallysYunes Hello again. I would rather send an email but i'm sure other people here may benefit from your response here.
Lets say I have employees that I need to pick up at a location and drive each employee to his own location. So it's A to B, C to D, E to F etc. I have a matrix with distances for each location to the other locations. The constraints are:
1. I must pickup in a certain location and drop off in a certain location - The direction is one way
2. There is a capacity of 4 passengers in a car - I can't pickup 5 passengers unless i drop one off first
3. I may have few trucks/cars
Thanks
What you describe can be a very complex problem, unless there are some simplifying assumptions you have not indicated (see: www2.isye.gatech.edu/~ms79/publications/ts29.pdf). Is this an assigned exercise? Undergraduate or graduate level? Is this to be solved in Excel?
@@TallysYunes This is to be a base for my final project , i'm an Industrial engineering student. I wish to have it in excel as i'm not a programer but know my way around with excel and some vba coding.
My recommendation is to sit with your teacher and narrow the problem down with enough details and simplifications so that it becomes more doable in Excel (and your teacher still approves it as being not-too-simple for a project). The open-ended version of this problem (as I've stated above) can be too challenging.
if a problem include production cost in plant how to include those in to the cost equation
If these are costs per unit produced (rather than fixed costs), they can be added to the costs of the arcs that depart from the plant.
Hello Sir, please make a video about this: drive.google.com/file/d/1QLdiRT3rzqgYntI6sJy5SOmhFCqUaTWf/view?usp=sharing but using Excel not Lingo! @@TallysYunes
I hope you can do that sir🙏
what happens when the capacity is unlimited?
When capacity is unlimited, the answer will end up using a single path to transport the goods (the least costly path). There will be no "splits" in the shipment like what it happened in this example.
@@TallysYunes i need help, do you mind if i send you what i am working on and you take a look. is there any way i can send it?
Only if you first explain to me what your situation is. Are you a student somewhere? If so, where? Did you try asking your teacher or your TA first? Is this question a homework question?
@@TallysYunes yes i am a student at makerere university and this is a homework question. i did ask the teacher but he didnt provide as much information as need and obviously i didn't understand