Integer Programming: Fixed Costs and Linking of Variables (Facility Location) with Excel Solver
Вставка
- Опубліковано 17 чер 2020
- 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...
Given potential locations for opening facilities and target markets that can be served by these facilities, decide which facilities to open and how much of each market's demand to serve out of each open facility in order to minimize your total cost. The cost has two components: fixed costs of opening the facilities and variable costs of serving the demand out of each open facility. In this video we also learn about the need for linking variables representing decisions that are related to each other, and how to implement this linkage mathematically and in Excel.
This man is single handedly carrying me through my operations management class. Couldn't be more grateful!!
Haha! So glad to hear the videos are helping!
Thank you for a clear and detailed explanation. I am willing to see more videos from you.
Excellent! You helped me solve a final project.
Awesome!
I did my project thanks to you ...
That’s great to hear!
Thank you so much for your clear and detailed explanation.
I am currently stuck on an integer-linear problem and seem don’t know what to do next.
So, I have the exact same case model as in your video, but in my case there are 2 development options with different building cost and capacity for each sites (eg. Stanford, Altamonte, etc). How do I choose which options for each facility in order to minimize the total costs? And what if there is a constraint for the total cost (eg. Not more than $ 250,000)?
Thanks in advance, really appreciate your help
Instead of one binary variable per site, like I do in the video, you can use two (one for each development option), and add a constraint that at most one of them can be chosen. For the budget constraint you simply write the same cost expression as a constraint and make it no more than the budget you have.
@@TallysYunes Thank you so much for your help,
But what if there are 2 development options (A and B) and 3 time periods (year 1- year 3) for each site/region and I can only choose 1 development option (A or B) and 1 time period (year 1 or 2 or 3) which has a minimum cost? Also I don’t have any data about the cost of serving customer from each site. I am struggling to express this as a model, could you please help?
Thanks a lot in advance
Thank you very much for a detailed explanation! Your video helps my study a lot! I have one question: Is it possible to implement a new constraint to this model that limits the number of the Region for each Site to 2?
For example, Sanford is possible to serve Regions, 1, 2 and 5 according to the table, but I would like to figure out if we can reach to an optimal solution with limiting the number of Regions to 2 in total (no matter of which Regions).
I came up with the idea to use another binary variable, but I am struggling to express this as a model... could you please help?
You're welcome. I'm glad the video was helpful. And, yes, you'll need another set of binary variables: Zij = 1 if clinic i serves region j. Then you need to say that, for each clinic i, the sum of the Z's with that fixed i (varying the j) is
@@TallysYunes
Thank you for the reply. I used Big-M constraint, but the model didn't work properly. I do not know why. Could you please help...?
What I did was as follows:
-I want to limit the number of Regions of Apopka to 1, in this example case.
-I add new seven "Z_Aj" binary variables for Apopka.
-I add linking constraint with Big-M: "Y_Aj"-"Z_Aj"*325
There’s a subtle thing happening. There’s a difference between (1) “don’t let Apopka serve more than one region” and (2) “Apopka must serve exactly one region.” In (1), it’s still possible for Apopka to be closed/unused. In that case, you’d make the sim of Z’s
@@TallysYunes Thanks so much for helping me! I finally got the feasible solution!
Hi, @Tallys Yunes So for the building costs constraint, if we don't serve any region in a city (say for eg. Sanford), the LHS will be 0 and in that case, the xs can be 1 and still that will satisfy the constraint as 0 < 400 right? How do avoid that and push the solver to trigger xs as 0 whenever the LHS is 0?
Yes. What you describe is a feasible solution to the model. But a solution like this is suboptimal because it would mean that you opened a facility (which costs money) but aren’t using it.
Dear Tallys good morning,
Thank you for the detailed explanation.
I am currently stuck on a combined facility location and vehicle routing problem but dont seem to know what to do next.
The whole idea is that there is demand to be met across the city and a certain amount of hubs can be opened (how to do this has been clarified in your other videos) but there is also a selection of vehicle types (3) that can be used to do so (vans, cargo bikes).
Is there a way to introduce a variable (perhaps binary) that connects the opening of a facility to a specific vehicle type? as i have 10 regions and 10 hubs possibilities I cannot put the 3 different costs tables together next to each other as Excel gives the error of too many variables.
Thanks a lot in advance.
Hi Cesar. As you indicated, you'll need binary variables to pick the vehicle types. Without seeing the complete problem description, I cannot tell if the vehicle type choice is per arc in the network, or per facility, or what. But if the issue you're running into is that you're exceeding the number of variables the student version of Excel solver allows (which I believe is 200), you can get around that by using another solver add-in called OpenSolver. It's free to download and use, the way you build the Excel sheet is the same, and the way you fill out the Solver parameters is very similar to the regular Excel solver. And the good thing is that it has no size limitation. You can download it from here (the site also has installation and usage instructions): opensolver.org/
@@TallysYunes Dear Tallys thank you for your prompt reply.
In this case demand to each region can be satisfied using only 1 vehicle type. Vehicles departing from a hub can be a combination of different vehicle types.
Does any of your other videos handle a similar logic? I tried looking around but without sucess.
Thanks alot for your help and the open solver idea.
So, instead of Xij variables (how much node i ships to node j), you need Xijt variables: how much node i ships to node j, using mode of transportation t. Then you need binary variables Yijt = 1 if mode t is used to ship from node i to node j. Then you need to say only one such mode is to be picked, so for all arcs i->j, you write: Yij1 + Yij2 + Yij3 = 1 (assuming there are 3 possible values for t). Then you need to link the Xijt to the Yijt using the linkage constraints I explain in my previous videos. This formulation will have, in your example, 3 variables per arc in the network, plus the binary variables that say whether or not the facilities are open.
Hi Tallys, I wanted to know I can enter constraints for special conditions inside solver. For eg. 100 Region 7 customers should be treated by Sanford clinic OR 100 Region 7 customers should be treated by Altamonte clinic. I am struggling to enter this and find an optimal solution in the solver. Maybe my logic is going wrong while entering maybe.
Yes. What you indicate is possible. In your example, the number of region 7 customers served by Sanford is cell B23. So you could just add one more constraint in Solver saying: B23 = 100 or B23 >= 100 (if going above 100 is allowed).
@@TallysYunes thanks! But how to add or to the constraint, like region 7 100 customers by Sanford or 100 customers by Altamonte at the same time...I want a binary constraint for those two conditions...either or...please help
Ah. Now I understand what you're tying to do. You need to introduce a new binary variable, say Z, which will mean:
Z = 0 if B23 >= 100 and Z = 1 if C23 >= 100. Then you create two news constraints like this:
B23 >= 100*(1 - Z)
C23 >= 100*Z
Note what happens when Z = 0:
B23 >= 100 and C23 >= 0.
And when Z = 1:
B23 >= 0 and C23 >= 100.
If you need the values to be exactly 100 and the variable that isn't 100 needs to be zero, then you can replace the '>=' with '='.
@@TallysYunes thank you so much for helping man!!