Solve this problem by LINGO software, write the code (DON'T COPY THE SOLUTION FROM CHEGG! )
Problem 1 Factories to Suppliers Allocation You have recently been appointed Category Manager for nuts and bolts at IndustryCo, a manufacturer with a large demand for bolts at its three (3) factories. You have surveyed the market and found four (4) suppliers for a certain steady selling SKU, XA21R45. Your factories' weekly demand for bolts are: - Factory 1: 9,500 cases/week - Factory 2: 6,500 cases/week - Factory 3: 17,000 cases/week Each of the four suppliers can provide the XA21R45, but they differ in unit price, weekly capacity, and distance to IndustryCo's three factories. The unit prices and the weekly capacity for each of the four suppliers are shown below: - Supplier 1: U.S. Ainbolt - Unit cost: 2.50$/ case Capacity 9,500 cases/week - Supplier 2: Der Bolt, Thun - Unit cost: 3.20 \$/case Capacity 10,000 cases/week - Supplier 3: Li Tningbolt - Unit cost: 1.90 \$/case Capacity 7,000 cases/week - Supplier 4: M. Bolton - Unit cost: 2.80 S/case Capacity 9,000 cases/week You are trying to plan the weekly flow of bolts from these suppliers to your factories. Based on the current contract with your transportation carrier, the unit transportation cost for a case of bolts is $0.05 per mile from any of the suppliers to any of the factories. The distances between the four suppliers and the three factories are given in the table below. Build the spreadsheet model to solve the problem, and plug in a "1" for each decision variable. There should be 12 decision variables. Your total cost should be 390.25. 1. Currently, the factories operate independently. Disregarding any of the other factories, which supplier should Factory 2 select in order to have the lowest total (purchase and transport) cost for them? 2. You want to take a holistic view of bolt sourcing instead of having each factory select its supplier on its own. You decide to create a model to help you select the optimal sourcing assignment for all of the factories for the XA21R45 bolts. Based on this optimization model answer the following questions: a. What is the total weekly cost for supplying bolts for IndustryCo? Be sure to include the purchase price as well as the transportation costs. b. Based on your optimization model, which supplier should Factory 2 select in order to have the lowest total (purchase and transport) cost for IndustryCo? c. You decide to re-negotiate with Supplier 1 (U.S. Ainbolt) to increase their capacity. They agree to increase it to 14,000 cases per week. How many cases per week does Supplier 1 (U.S. Ainbolt) send to Factory 2 in the new optimal solution? d. Based on this new optimization model, what is the total weekly cost for supplying bolts for IndustryCo? Be sure to include the purchase price as well as the transportation costs
