Had difficulty doing my linear programming homework for college, and I was able to complete the assignment with ease after viewing this tutorial. Thank you from Georgia, USA!
1. A company has factories at F1, F2, and F3 that supply products to warehouses at W1, W2 and W3. The weekly capacities of the factories are 200, 160 and 90 units, respectively. The weekly warehouse requirements are 180, 120 and 150 units, respectively. The unit shipping costs (in rupees) are as follows: W1 W2 W3 Supply F1 16 20 12 200 F2 14 8 18 160 F3 26 24 16 90 Demand 180 120 150 Determine the optimal distribution for this company in order to minimize its total shipping cost using excel solver Iska bata digiye
The great Dr. Shah! I was planning to have my students do some optimization in a new advanced logistics class I'm teaching this semester, when I searched TH-cam for some examples and saw the name of my old PhD friend I was delighted (although not surprised) that you had already posted exactly what I needed. Thanks again!
Thank you so much for this video! You explain it so well and your instructions were very easy to follow. This helped me tremendously in my OM class. Thanks again!
+Neerja Arora LCM and VAM are methods to get the initial feasible solution. We apply MODI on this to get the optimal solution. This is the procedure when using pen and paper. For MS Excel, it does not need an initial feasible solution. So, the question of using LCM or VAM does not arise. MS Excel uses neither.
Dear Piyush Sir is there any video reg. Facility location model(continous location model,network facililty location,tradeoffs with multiple location ,optomal DC etc)
Thank you! I like this video. If let's say the assignment was given different weightage to the overall score, how do I input the weightage consideration into Solver? For e.g. Operations and Finance were given 30% weightage each and Marketing and HR were given 20% weightage each. Could you advise?
Hi Kelly, I guess this is a comment on the Assignment video. So, we have a group score that we try to maximize. The group score is the sum product of two values - the marks and the assignment. In your case, it needs to be a sum product of three values - marks, assignment and the weight. I think that should solve your problem.
Hi, so I tried your method and it is very easy to understand, Thank you! However, one of my constraints was different from its respective capacity. All the other constraints were equal but one was 200 instead of the stated capacity 900. Is that possible? And if yes, what does it mean?
The minimum of the demand and capacity is the binding constraint. So, if total demand is lesser than total capacity, use '=' for demand constraints and '
I have tried and using '=' for both constraints and this time a different cell value was unequal to the stated capacity. Is it possible for this to happen?
Of course. Say the total demand is 1000 and the total capacity is 1200. Then, for some factory (source), the supply will be 200 less...as the excess capacity will not be used.
Okay! Thank you for the help. But quick question, should you always use '=' for the constraints? When do you know to use '=' also? In which instances can you tell which one to use? I hope you understand my question.
Big M and simplex method are algorithms that we use when we solve linear programming manually. Excel uses a different algorithm. Which ever way, all the methods should lead to the same optimum answer
Hi, If i hav the quantity of various products that i need to produce as well as the processing time (for 2 machines), machine capacity and due dates for each product order. How do I set up an excel solver to determine the optimum sequence to produce the order in order to be within capacity and meet the due date as well as reduce product changeovers? Any help would be greatly appreciated. Thank you.
Thank you. is it possible Le to set up an excel solver to generate the Johnsons rule solution. I have 2 more quesrions: 1 Can excel solver be used to get an optimal solution by rearranging cells. 2. Can a constraint be added in excel solver that states that a cell must be equal to a set of given numbers. For example the cell must be equal to 1 or 3 or 7 or 16 or 25 but no other number in between? Your help will be greatly appreciated. Thank you.
1. I think it would be very difficult to set up Johnson's rule in Excel. There could be some other Excel based solutions for n jobs 2 machine case, but I am not aware of it. 2. The arrangement of cells can be anything you want it to be. 3. To make a cell equal to one among a fixed set of integers can surely be done, though I am not sure how exactly at this moment. You would probably need an integer constraint and maybe additional decision variables.
Hi ji. I want to optimize freights based on following. 1) freight from plant to wherehouse (distance 70) is 700. Where as plant to direct orders having higher prices (same distance slab like 1 to 70) per ton per kilometre cost is abnormal. How can I optimize all other freights based on the plant to wherehouse ptpk.
@@piyushashah1sure.. for suppose from manufacting unit to warehouse shipment cost is 700 which is around 70 km from unit. where as shipping cost for direct party within the same distance slab in between 60 to 80 km radious is 900 km that is 200 more than the warehouse shipment.. how can find those outiers and nearly equate with warehouse cost
Hi, if I have 2 products and 6 potential production sites (supply) and 6 markets I'm planning to enter (demand) The fixed cost for each production site, the 2 products are given The variable cost per unit is given The transportation cost per unit is given too The objective is to minimise total cost I have formulated the problem but I don't know how to put it into excel such a way that solver can solve it, should I separate the 2 products and calculate the total cost of producing each product, because they have different demands Also how do I create linking constraints such that solver will take into account the fixed cost and variable cost if a production site is used All of the examples I have seen so far involves only 1 demand I've been stuck in this for so long please could you help me?? The assignment is due next Friday...
Easy to understand...but I have one question doubt related to this topic...As the question is slightly lengthy, difficult for me to write here .Can I get your email id or any contact detail so that I can send that question .Hope you will help me.Thank you sir
Had difficulty doing my linear programming homework for college, and I was able to complete the assignment with ease after viewing this tutorial. Thank you from Georgia, USA!
1. A company has factories at F1, F2, and F3 that supply products to warehouses at W1,
W2 and W3. The weekly capacities of the factories are 200, 160 and 90 units,
respectively. The weekly warehouse requirements are 180, 120 and 150 units,
respectively. The unit shipping costs (in rupees) are as follows:
W1 W2 W3 Supply
F1 16 20 12 200
F2 14 8 18 160
F3 26 24 16 90
Demand 180 120 150
Determine the optimal distribution for this company in order to minimize its total
shipping cost using excel solver
Iska bata digiye
The great Dr. Shah! I was planning to have my students do some optimization in a new advanced logistics class I'm teaching this semester, when I searched TH-cam for some examples and saw the name of my old PhD friend I was delighted (although not surprised) that you had already posted exactly what I needed. Thanks again!
Thanks brother.
Got 10 mark in EMBA midterm xm by following this lecture
Explore my channel Sajan. You will find other supply chain and operations content that will also be useful.
dude thanks for this. working on a project, i had no idea what i was doing and this video helped a lot
Wow. I have been struggling with this for awhile, and today stumbled upon your tutorial. Very easy! Thank you so much for sharing.
Thanks a lot for your comment Susan.
Thank You Mr Shah, was having difficulty, your video cleared up my formulas and constraints.
love the presentation. Clean and Clearn! thank you!
Thank you for such a great explanation, Piyush Shah! Very concise and straightforward!
Thanks for dropping the comment Lana, happy that the video helped you.
You are my hero. thank you so much for a succinct and easy to understand tutorial!
Haha @Sapnakudchadkar I have never recd such a compliment before. Thank you,
Thank you so much for this video! You explain it so well and your instructions were very easy to follow. This helped me tremendously in my OM class. Thanks again!
Thanks a lot for dropping a comment Clyanne.Am glad that the video could help you.
Sir, you are terrific!
And you watched this one also! Thank you again.
This was an excellent lesson.
Thank you Sir for your videos ❤️👍
thank you so much sir! I have been confused about it so long, now I got it!!!
Happy this helped. There are other videos on transhipment on my channel also. You might find that useful also.
You sir just saved my life :)
very nice explanation..helped solve my problem..thank you sir
Do have a look at other videos in this series Jyotirath, and also please share with your friends.
your method save my life!!!
Thank you so much!!!!!
Superb 👍👌👏
Thank you for the clear explanation. This was very helpful.
Thank you so much. You help on understanding my math subject.
The clip was very useful. Does Solver use the Least Cost Method or Vogel's Approximation method as the algorithm?
+Neerja Arora LCM and VAM are methods to get the initial feasible solution. We apply MODI on this to get the optimal solution. This is the procedure when using pen and paper. For MS Excel, it does not need an initial feasible solution. So, the question of using LCM or VAM does not arise. MS Excel uses neither.
Thanks, that was helpful.
thank you very much. just saved my life :D
That's the whole purpose of these videos - to save lives, Haha. Thanks for the comment, and best wishes.
Dear Piyush Sir
is there any video reg. Facility location model(continous location model,network facililty location,tradeoffs with multiple location ,optomal DC etc)
Thank you! I like this video. If let's say the assignment was given different weightage to the overall score, how do I input the weightage consideration into Solver? For e.g. Operations and Finance were given 30% weightage each and Marketing and HR were given 20% weightage each. Could you advise?
Hi Kelly, I guess this is a comment on the Assignment video. So, we have a group score that we try to maximize. The group score is the sum product of two values - the marks and the assignment. In your case, it needs to be a sum product of three values - marks, assignment and the weight. I think that should solve your problem.
Hi, so I tried your method and it is very easy to understand, Thank you! However, one of my constraints was different from its respective capacity. All the other constraints were equal but one was 200 instead of the stated capacity 900. Is that possible? And if yes, what does it mean?
The minimum of the demand and capacity is the binding constraint. So, if total demand is lesser than total capacity, use '=' for demand constraints and '
Okay trying that now.
I have tried and using '=' for both constraints and this time a different cell value was unequal to the stated capacity. Is it possible for this to happen?
Of course. Say the total demand is 1000 and the total capacity is 1200. Then, for some factory (source), the supply will be 200 less...as the excess capacity will not be used.
Okay! Thank you for the help. But quick question, should you always use '=' for the constraints? When do you know to use '=' also? In which instances can you tell which one to use? I hope you understand my question.
Thank you so much sir! But i still have trouble solving with unbalanced transportation model. Hope you'll upload one. :)
Hello sir, what is the difference in solving simplex problem and big m problem using excel sheet
Big M and simplex method are algorithms that we use when we solve linear programming manually. Excel uses a different algorithm. Which ever way, all the methods should lead to the same optimum answer
thank you brother
is it balanced transporation or unbalanced?
Hi, If i hav the quantity of various products that i need to produce as well as the processing time (for 2 machines), machine capacity and due dates for each product order. How do I set up an excel solver to determine the optimum sequence to produce the order in order to be within capacity and meet the due date as well as reduce product changeovers?
Any help would be greatly appreciated.
Thank you.
Hi Crystal search for n jobs 2 machines Johnson's rule. This should tell you what to do.
Thank you. is it possible Le to set up an excel solver to generate the Johnsons rule solution.
I have 2 more quesrions:
1 Can excel solver be used to get an optimal solution by rearranging cells.
2. Can a constraint be added in excel solver that states that a cell must be equal to a set of given numbers. For example the cell must be equal to 1 or 3 or 7 or 16 or 25 but no other number in between?
Your help will be greatly appreciated. Thank you.
1. I think it would be very difficult to set up Johnson's rule in Excel. There could be some other Excel based solutions for n jobs 2 machine case, but I am not aware of it.
2. The arrangement of cells can be anything you want it to be.
3. To make a cell equal to one among a fixed set of integers can surely be done, though I am not sure how exactly at this moment. You would probably need an integer constraint and maybe additional decision variables.
what if the problem has another cost which is the operational cost of the warehouse? how should we add that cost?
Hi ji. I want to optimize freights based on following.
1) freight from plant to wherehouse (distance 70) is 700. Where as plant to direct orders having higher prices (same distance slab like 1 to 70) per ton per kilometre cost is abnormal. How can I optimize all other freights based on the plant to wherehouse ptpk.
Could you explain your question again please
@@piyushashah1sure.. for suppose from manufacting unit to warehouse shipment cost is 700 which is around 70 km from unit. where as shipping cost for direct party within the same distance slab in between 60 to 80 km radious is 900 km that is 200 more than the warehouse shipment.. how can find those outiers and nearly equate with warehouse cost
thanks it really help
Hi, if I have 2 products and 6 potential production sites (supply) and 6 markets I'm planning to enter (demand)
The fixed cost for each production site, the 2 products are given
The variable cost per unit is given
The transportation cost per unit is given too
The objective is to minimise total cost
I have formulated the problem but I don't know how to put it into excel such a way that solver can solve it, should I separate the 2 products and calculate the total cost of producing each product, because they have different demands
Also how do I create linking constraints such that solver will take into account the fixed cost and variable cost if a production site is used
All of the examples I have seen so far involves only 1 demand
I've been stuck in this for so long please could you help me??
The assignment is due next Friday...
Checked mixed integer planning models. That should work for you
Awesome! Thank you!
Thanks a million!!!!!!! Really!
Youssef El ksabi glad that the video helped you. Do share it with your friends and also look at the other video on assignment model.
Thanks for the video! It helped alot! p.s.: Its transportation :P
thank you so muchhh!!
Thank you sir!
Is your keyboard mechanical?
+emperoy8 lol, well the laptop mike is very close to the keyboard and so unfortunately each and every key stroke is captured very accurately.
However, you done good job in video.
+emperoy8 Thank you.
very helpful, thank you!
Got number of products to ship correct but cost is not coming
Thank you!!!
hi, there this clip was so useful..I have a prblem here is that once i solve it, the total cost is still 0 ... i wonder why, guidance anyone?
Must be some minor error in formulation. Check if the constraint for 4 marks is "more than equal to" and not "less than equal to".
Hello are you there I really need your help.
Thanks
Happy that it helped you. You will find videos on all major topics of LP with excel solver on my channel. Do have a look
Tysm :)
Easy to understand...but I have one question doubt related to this topic...As the question is slightly lengthy, difficult for me to write here .Can I get your email id or any contact detail so that I can send that question .Hope you will help me.Thank you sir
I found a lower cost using gusek solver
Can you post the lower cost solution here? Am very curious to know if it exists.
@@piyushashah1 i'm sorry it's my mistake i forget to add a constraint