Please Use excel below to help you:

Distribution Network Design Problems ISE 470 Problem 1 You have recently been appoimed Conegory Manager for nuts and bolts at IndastryCa, 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 X.A21R45, but they differ in unit price, weekly capacity, and distance to Indestryco's three factories. The unit prices and the woekly capacity for each of the four suppliers are shown below: - Supplier 1: US. Ainbolt - Unit cost: 2.50 \$/case Capacity 9,900 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. Bohon - Unit cost: 2$0 \$icase Capacity 9,000 cases week You are trying to plan the weekly flow of bolts from these suppliers to you factories. Based on the current contract with your transportation carrier, the unit transportation cost for a case of bolts is \$a.0S per mile from any of the suppliers wo any of the factories. The distances in miles between the four suppliers and the three factories are given in the table below. To help, we build a preliminary spreadshect model wo solve the problem, See Exel file Problem1-xlsx. Plug in the fommla in the required oelk. To check if your Excel model is correct, plug in a "1" for each decision variable. Your total cost should be 390.25 . 1. Currently, the factories operate independently. Disegarding any of the other factories, which supplier should Factory 2 select in order to have the lowest votal (parchase and transport) cost for them? 2. You want to take a bolistic view of bolt sourcing instead of having each factory selects its supplier on its onn. 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: 2. What is the total weekly cost for sapplying bolts for Indastry Co ? Be sure to include the purchase price as well as the transportation costs. b. Based on your optimization model, which supplier should Factory 2 selects in order to have the lowest total (purchase and transport) cost for Industry Co? 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 (US. 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 Industry C ? Be sare to include the purchase price as well as the transportation costs. Problem 2: Two echelon problem You have been hired by Faraday Protonic Company (FPC) to serve as their Pellet Transshipasent Manager. FPC is a joint public-private energy company dedicated to generating clean and safe woclear energy. FPC wes a new technology based on radicactive pellets. These pellets can penerate electricity for years in FPC's nuclear plants. Once the radioactive pellets are depleted, they are removed from active use and collecsed at the plants for removal at the end of the moenth. Onoe a month, the depleted pellets are removed from the plants, transported to an inspection site for examination, and then moved again to a dedicated storage site for safekecping. You have been informed that two of FPC's nuclear plants, called Plant One and Plant Two, need to move out depleted pellets in the following amounts: - Plant One wants to ship out up to 280 pellets per moenth - Plant Two wants to ship out up to 360 pellets per month This is how many pellets these two plants want to ship out per moenth. However, the exact number of pellets that will actually be moved out from each plant depends on the spoce that is available at the dedicated storape sites that woald ultimately receive them. FPC is mandated by law to move as many pellets out of the plants as the storage sites are able to receive. You have been informed that two of FPC's dedicated storage sites, called Storage X and Storage Y, are ready to receive depleted pellets from FPC's plants, in the following amounts: - Storage X is ready so receive a total of 305 pellets per month - Storage Y is ready to receive a total of 325 pellets per month Since the two plants want to ship out more pellets per month than the two storake sites can receive, you know that some pellets will have to stay in their plant of origin until a later time. This is not a problem, however, since every one of FPC's plants has the capacity to temporarily store whatever pellets are not shipped out, and the law allows this. At this time, only one inspection site is available to receive the pellets from the two plants, inspect them, and send them out to the two slorage sites. It is called Inspection Site A. Notice that inspection sites do not have storage capacity: the pellets come in, are inspected, and coentinue their trip to the storage sites. Moving radioactive material is not easy and therefore it is not cheap either. There is a lot of planning and security involved. Each shipment of pellets has to be closely monitored and peotected so peevent the loss of radioactive material. Also, the vehicles transporting the maserial have to move relatively slowly, to avoid accidents. Therefore, it is very expensive to transport the radicactive pellets from the plant to the inspection sile and from there to the storage site. Based on estimases and historical data, FPC has calculated the following transpoetation costs, in dollars per pellet moved: - It costs 13.58 dollars to move one pellet from Plant One to Inspection Site A - It costs 16.54 dollars to move one pellet from Plant Two to Inspectioe Site A - It costs 7.57 dollars to move one pellet from Inspectioen Site A to Storage X - It costs 16.46 dollars to move one pellet from Inspection Site A to Storage Y For this problem there is no preliminary Excel Model. You have to maloe one. 1. You decide to optimixe the transhipment of deplesed pellets from the plants to the storake sites (stopping on the way at Inspectioe Site A, of course) in onder to minimize the overall monthly transportatioe cost. In this optimized solution, how many pellets will Plant Two be able to ship out to the Inspection Site A every month? 2. In this optimized solution, how much is the overall monthly transpoetation cost for FPC? Good news! A second inspection site has become available to receive pellets from the two plants, inspect them, and send them out to the two ssorage sites. It is called lnspection Site B. To the extent that it is more economical to use this inspection site, it can be used. FPC has calculated the following transportation costs for Inspection Site B, in dollars per pellet moved: - It costs 12.6 dollars to move one pellet from Plant One to Inspection Site B - It costs 16.2 dollars to move one pellet from Plant Two to Inspection Site B - It costs 9.45 dollars to move one pellet from Inspection Site B to Storage X - It costs 15.64 dollars to move one pellet from Inspection Site B so Storage Y 3. As before, you will optimize the transshipment of depleted pellets from the plants to the storage sites, stopping first at the inspection sises, so as to minimize the overall monthly transpoetation cost. How mach will this cost FPC every month? \begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|c|c|} \hline 4 & A & B & C & D & E & F & G & H & 1 & J & K & L \\ \hline 1 & Problem 1 & & & & & & & & & & & \\ \hline 2 & & \begin{tabular}{|l|} Unit \\ transporation \\ cost \$\$ per mile \end{tabular} & 0.05 & & & & & & & & & \\ \hline 3 & & & \multicolumn{3}{|c|}{ Transportation cost per unit product } & & & & \multicolumn{2}{|c|}{ Distance Matrix } & & \\ \hline 4 & & & Factory 1 & Factory 2 & Factory 3 & Capacity[caseslyeek] & Unitcost$/case & & Factory 1 & Factory 2 & Factory 3 & \\ \hline 5 & & U.S. Ainbolt & 6.00 & 19.00 & 40.30 & 9500 & 2.5 & U.S. Ainbolt & 120 & 380 & 806 & \\ \hline 6 & & Der Bolt, Thun & 23.25 & 35.85 & 42.65 & 10000 & 3.2 & Der Bolt, Thun & 465 & 717 & 853 & \\ \hline 7 & & Li Tningbolt & 27.65 & 28.60 & 41.50 & 7000 & 1.9 & Li Tningbolt & 553 & 572 & 830 & \\ \hline 8 & & M. Bolton & 45.75 & 20.30 & 28.20 & 9000 & 2.8 & M. Bolton & 915 & 406 & 564 & \\ \hline 9 & & Demand & 9500 & 6500 & 17000 & & & & & & & \\ \hline 10 & & & & & & & & & & & & \\ \hline 11 & & & Total transpor & tion and pur & hase cost per & unit product & & & & & & \\ \hline 12 & & & Factory 1 & Factory 2 & Factory 3 & & & & & & & \\ \hline 13 & & U.S. Ainbolt & 8.50 & 21.50 & 42.80 & & & & & & & \\ \hline 14 & & Der Bolt, Thun & 26.45 & 39.05 & 45.85 & & & & & & & \\ \hline 15 & & Li Tningbolt & 29.55 & 30.50 & 43.40 & & & & & & & \\ \hline 16 & & M. Bolton & 48.55 & 23.10 & 31.00 & & & & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|} \hline \multicolumn{6}{|l|}{17} \\ \hline 18 & & \multicolumn{2}{|c|}{ Decision variables } & \multirow[b]{2}{*}{ Factory 3} & \multirow[b]{2}{*}{RemainingCapacity[casesireek]} \\ \hline 19 & & Factory 1 & Factory 2 & & \\ \hline 20 & U.S. Ainbolt & 0.00 & 0.00 & 0.00 & 9500.00 \\ \hline 21 & Der Bolt, Thun & 0.00 & 0.00 & 0.00 & 10000.00 \\ \hline 22 & Li Tningbolt & 0.00 & 0.00 & 0.00 & 7000.00 \\ \hline 23 & M. Bolton & 0.00 & 0.00 & 0.00 & 9000.00 \\ \hline 24 & Unmet Demand & 9500.00 & 6500.00 & 17000.00 & \\ \hline 25 & & & & & \\ \hline 26 & & & & & \\ \hline 27 & & total cost & 0 & & \\ \hline \end{tabular}