Linear Programming: Transshipment with Excel Solver (Network Flows Part 3)

[@DJ-pn9te]

i think this is a good explanation and an overview for grasping the problems faced in the real world. And definitely Kudos to you Dr. I would add the time vector is important, is this for 1 day, 1 week, 1 month, etc. is this analysis run hourly as new demand and supply forecasts are introduced? Production may be on a schedule that differs from demand and Demand may increase or soften which are both pull/push on the system. Sometimes it is good to introduce dummy/slack supply and demand nodes that act as storage..........you touched on the warehouse phenomenon (that may have supply or demand characteristics) I think the main thing is to really think about the problem/map it in a diagram then when you have something, either write a LP program or if Excel can handle methodically go through an identify the obejctive function and segment the constraints... A+ classroom lecture to real world problems.... VALEU!

[@TallysYunes]

You're correct. The problem in the video is, like every model, a simplification of the real-life situation. As you explain, there are many other factors that need to be taken into account. For the sake of a student learning these things for the first time, we could start with the model presented here and, little by little, incorporate additional details and increase the complexity. Depending on the needs of the transportation company, the level of complexity can reach a point where optimization can't handle it anymore and we need to resort to so-called heuristic approaches (a topic that I don't yet cover in my videos, but hope to get into at some point). I have a few friends who work as research scientists at Amazon and they tell me that this is indeed the case.

[@evolvingtech1]

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.

[@TallysYunes]

Thank you very much for your feedback!

[@agagagag867]

That video is so detailed and helped me a lot! Thank you!!!

[@TallysYunes]

You're welcome! Glad to hear it was helpful.

[@rodrigosollberger3146]

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!!!

[@TallysYunes]

You're very welcome! Glad to hear it was useful!

[@timothyperera67]

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!

[@TallysYunes]

So happy to hear the videos have helped you! And thank you for subscribing!

[@nneisler]

Good video. Thanks! Can be hard to get from the linear programming to the Excel set-up; you've been clear and concise.

[@TallysYunes]

Thank you! I’m glad the video was helpful!

[@bluetaurean]

Fantastic videos and topics are well explained. Would love to see more videos with advanced topics on scheduling. You must publish a book or a paid course covering advanced topics.

[@TallysYunes]

Thank you! I do have many video ideas for both basic and advanced topics. The hard part has been finding time to make them.

[@AlejandroRios88]

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

[@TallysYunes]

Cool! Nice to see you managed to adapt this one to another situation. That's the spirit!

[@figueroacesar98]

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

[@TallysYunes]

You’re welcome. Glad to hear this was helpful!

[@vinayakdixit8318]

Hello Cesar...can you share which MIT course it is?

[@jayeshrathod4271]

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

[@TallysYunes]

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.

[@manivannanav8246]

Thank you Tallys. Was struggling with these types of problems. You explained it in an understandable way.

[@TallysYunes]

You're welcome. Glad it helped!

[@sayadyosefi2660]

your videos are super helpful, thanks for sharing

[@TallysYunes]

I'm glad to hear! You're welcome!

[@tireedaly2015]

How does the solver know that the flow has to originate at nodes 1 and 2 and cannot arrive at 5 and 6 by coming directly come from 3 and 4?

[@TallysYunes]

The flow constraints ensure that supply departs from the supplier notes (1 and 2) and that what happens at intermediate notes makes logical sense: whatever goes in has to go out. It also ensures that the demand nodes receive what they need.

[@vinayakdixit8318]

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

[@TallysYunes]

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.

[@vinayakdixit8318]

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

[@jayeshrathod4271]

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?

[@TallysYunes]

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.

[@jayeshrathod4271]

@@TallysYunes Thanks a lot. It was very helpful

[@ciai7317]

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.

[@TallysYunes]

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)

[@luci1474]

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?

[@TallysYunes]

I'm not sure I understand your question. The final total cost is what appears in cell H11 after you solve the problem.

[@aaromalajikumar4340]

if a problem include production cost in plant how to include those in to the cost equation

[@TallysYunes]

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.

[@ie95_Moh]

Hello Sir, please make a video about this: drive.google.com/file/d/1QLdiRT3rzqgYntI6sJy5SOmhFCqUaTWf/view?usp=sharing but using Excel not Lingo! @@TallysYunes

[@ie95_Moh]

I hope you can do that sir🙏

[@stevemansfield8530]

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.

[@TallysYunes]

Yes. You are correct because transportation is a special case of transshipment.

[@vishalbhosale4513]

Thank you sir, Very well explained. Can you please make a video on the multicommodity transshipment problem using excel?

[@TallysYunes]

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.

[@ye1024]

Can the capacity be different for all cases? (for example: in the case of air+land freight)

[@TallysYunes]

Yes, of course. Each arc can have its own specific capacity.

[@stephenschmidt6309]

How would this change if the warehouses in the situation have a maximum capacity?

[@TallysYunes]

In terms of the math, you can write (inflow into warehouse)

[@עמירצין]

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

[@TallysYunes]

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

[@TallysYunes]

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.

[@TallysYunes]

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.

[@ruthcourtney9364]

what happens when the capacity is unlimited?

[@TallysYunes]

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.

[@ruthcourtney9364]

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

[@TallysYunes]

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?

[@ruthcourtney9364]

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

[@apasajaada23]

thank you