please solve problem 2 completly and you can see the the problem 1 how did he solve some of them

this is from problem one

Problem I You have recently been appointed Category Manager for nuts and bolts at Industry Co, 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 eases/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, weckly 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 S/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 S/case Capacity 7,000 casesiweck - Supplier 4: M. Bolton - Unit cost: 2.80 S/case Capacity 9,000 cases weck 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 in miles between the four suppliers and the three factories are given in the table below. To help, we build a preliminary spreadsheet model to solve the problem, See Excel file Problem1-xlsx. Plug in the formula in the required cells. 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. 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 selects 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 Industry 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 (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. Problem 2: Two echelon problem You have been hired by Faraday Protonic Company (FPC) to serve as their Pellet Transshipment Manager. FPC is a joint public-private energy company dedicated to generating clean and safe nuclear energy, FPC uses a new technology bassed on radioactive pellets. These pellets can generate electricity for years in FPC's nuclear plants. Once the radioactive pellets are depleted, they are removed from active use and collected at the plants for removal at the end of the month. Once 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 safekeeping. 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 our up to 280 pellets per month - 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 month. However, the exact number of pellets that will actually be moved out from each plant depends on the space that is available at the dedicated storage sites that would 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 to receive a total of 305 pellets per month - Storage Y is ready to receive a total of 325 pellets per month Prebl File Home: Insert Pegelyout Formulas: Duse Trel me whaty you want to do Enable A B f. Name C15 34 D E F G H Constraints \begin{tabular}{|c|c|c|c|c|c|c|} \hline 36 & Cell & Name & Cell Value & Formula & Status & Slack \\ \hline 37 & scs24 & Unmet Demand Factory 1 & 9800.00 & scS24)=0 & Not Binding & 9500.00 \\ \hline 36 & & Unmet Demand Factory 2 & 6500.00 & sos24>0 & Not Binding & 6500.00 \\ \hline 39 & ses24 & Unmet Demand Factory 3 & 17000.00 & ses24>=0 & Not Binding & 17000.00 \\ \hline 40 & $5$20 & U.S. Ainbolt Remaining Capacity (cases/week) & 9500.00 & 5F$200=0 & Not Binding & 9500.00 \\ \hline 41 & $F$21 & Der Bolt, Thun Remaining Capacity (cases/week) & 10000.00 & SFS21>00 & Not Binding & 10000.00 \\ \hline 42 & $$22 & U Tningbolt Remaining Capacity (cases/week) & 7000.00 & 57522>00 & Not Binding & 7000.00 \\ \hline 43 & $$23 & M. Bolton Remaining Capacity (cases/week) & 9000.00 & & Not Binding & 9000.00 \\ \hline 44 & scs20 & U.S. Ainbolt Factory 1 & 0.00 & scs200=0 & Binding & 0.00 \\ \hline 45 & $0$20 & U.S. Ainbolt Factory 2 & 0.00 & 50520>=0 & Binding & 0.00 \\ \hline 46 & se$20 & U.S. Ainbolt Factory 3 & 0.00 & 5E$200=0 & Binding & 0.00 \\ \hline 47 & scs21 & Der Bolt, Thun Factory 1 & 0.00 & scs21)=0 & Binding & 0.00 \\ \hline 48 & 50$21 & Der Bolt, Thun Factory 2 & 0.00 & 50$21>20 & Binding & 0.00 \\ \hline 49 & SE\$21 & Der Bolt, Thun Factory 3 & 0.00 & seS21>=0 & Binding & 0.00 \\ \hline 50 & scs22 & Li Tningbolt Factory 1 & 0.00 & scs220=0 & Binding & 0.00 \\ \hline 51 & 50522 & UTningbolt Factory 2 & 0.00 & 50522>00 & Binding & 0.00 \\ \hline$2 & ses22 & UTningbolt Factory 3 & 0.00 & ses20=0 & Binding & 0.00 \\ \hline 53 & SCS23 & M. Bolton Factory 1 & 0.00 & scs23>0 & Binding & 0.00 \\ \hline 54 & sos23 & M. Bolton Factory 2 & 0.00 & SOS23>=0 & Binding & 0.00 \\ \hline 55 & SE\$23 & M. Bolton Factory 3 & 0.00 & SES23> =0 & Binding & 0.00 \\ \hline \end{tabular} 56 57 58. 59 60 61 62. 63 64 65 66 67. 68 69 70 71 72 73 74. Problen O1 - Al Factovies Pert? Home Insert Pagetayout Formulas Dats Review View Q Tell mewhetyou want to dol. 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 storage sites. It is called Inspection 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 to 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 sites, so as to minimize the overall monthly transportation cost. How much will this cost FPC every month