1 & 2 been solved below. Please help solve Q3 below with excel formula and explain in detail.

Decision Variables Supply Zone 1 - Mailiao 1 - Mailiao - Erlun Objective function (in NTD) 3- Xiluo - Cihtong FAC cost 850000 5 - Taisi Transportation Cost 434740 - Tuku Total Cost 2284740 7 - Huwe 8 - Douliu 9 - Yuanzhang 10 - Dounan 1 - Gukeng 12 - Kouhu 13 - Beigang Constraints Supply Zone Excess capacity - Mailiao 2 - Erlun - Xiluo 4 - Cintong 596510 5 - Tai 5 - Tuku 7 - Huwei Os 8 - Douliu - Yuanzhang 10 - Dounan 1 - Gukeng 12 - Kouhu 13 - Beigang 598140> Demand Zone Unmet Demand 1 - Mailiao 2 - Erlun 3 - Xiluo 4 - Cintong 5 - Taisi 6 - Tuku o o o 7 - Huwei 8 - Douliu 9 - Yuanzhang 10 - Dounan 11 - Gukeng 12 - Kouhu 13 - Beigang nputs - Transportation Costs and Transportation Demand of 13 Zones in Yunlin Country 0 o Demand Zone Supply Zone 1 - Mailiao Transportation Cost (per ton) 2 - Erlun 3 - Xiluo 4 - Cihtong 5 - Taisi 1 - Mailiao 6 - Tuku 7 - Huwe 8 - Douliu 40 9 - Yuanzhang 10 - Dounan 2 - Erlun 432 582 952 11 - Gukeng 432 40 82 188 12 - Kouhu 432 13 - Beigang 3 - Xiluo 227 498 566 1234 Fixed Cost (NTD/yr) Capacity (Assume 600000 is very big to 582 556 82 898 40 384 228 508 1116 158 526 586 be considered as unlimited capacity) - Cihtong 952 592 358 18 484 466 844 400000 222 158 40 410 528 880 600 600000 5 - Taisi 188 566 934 490 23 408 400000 - Tuku 592 432 934 274 768 578 962 234 602 450000 7 - Huwe 384 40 358 474 682 112 490 1230 658 552 768 600000 498 474 228 40 216 894 778 1112 180 168 368 400000 462 8 - Douliu 1234 236 600000 508 682 274 40 442 658 400000 280 600000 - Yuanzhang 410 216 65 526 1230 454 556 778 308 528 578 454 294 450000 40 622 850 866 600000 10 - Dounan 898 552 168 308 230 48 8 450000 484 408 234 866 276 40 1416 1054 600000 1 - Gukeng 1116 844 768 894 658 442 294 532 230 750 532 458 500000 128 600000 12 - Kouhu 586 11 12 400000 880 962 1 128 658 622 40 276 324 750 1078 718 450000 600000 13 - Beigang 466 Transportation Demand (ton per year 600 602 368 658 768 850 1416 324 40 458 1304 500000 25 450 462 1078 944 490 280 280 48 8 1054 1304 40 400000 220 128 334 600000 280 70 1080 718 944 33 400000 600000 Transportation demand (per K population per year) 240 430 300 40 10 250 380 450000 500000 Unit transportation cost (per ton per km) (in TWD) 20Decision Variables Demand Zone Supply Zone 1 - Mailiao 5 - Taisi 6- Tuku Annual Shipping Volume Objective function (in NTD) 1 - Mailiao 2 - Erlun Warehouse (0 = Not open, 1 = Open) FACcost 3 - Xiluo 850000 Transportation Cost 1350286.65 4 - Cintong Total Cost 2200286.65 6- Tuku 7 - Huwei 8 - Douliu 9- Yuanzhang 10 - Dounan 11 - Gukeng 12 - Kouhu 13 - Beigang 220 280 Constraints Supply Zone 1 - Mailiao Excess capacity 2 - Erlun 3 - Xiluo 4 - Cihtong 5 - Taisi 7 - Huwei 8 - Douliu - Yuanzhang 10 - Dounan 11 - Gukeng 12 - Kouhu 13 - Beigang 598140 Unmet Demand 1 - Mailiao 2- Erlun 3- Xiluo 4- Cintong 5- Taisi Demand Zone of o 6-Tuku 7-Huwel 8- Douliu 9- Yuanzhang 10- Dounan 11- Gukeng 12- Kouhu 13 - Belgang Inputs - Unit Transportation Costs of 13 Zones in Yunlin Country (per ton) (in TWD) o Demand Zone Supply Zone 1 - Mailiao 1 - Mailiao 2 - Erlun 3 - Xiluo 4- Cintong Transportation Cost (per ton) 5 - Taisi 6 - Tuku 7- Huwei 2 - Erlun 22.97 18.56 22.97 22.31 23.66 8 - Douliu 9- Yuanzhang 10 - Dounan 11 - Gukeng 12 - Kouhu 13 - Beigang 19.17 23.20 21.89 3 - Xiluo 22.31 18.56 23.66 22.97 22.31 15.95 19.49 22.97 20.35 19.17 13.08 22.31 23.66 23.20 19.49 22.31 15.95 21.89 22.97 4 - Cihtong 13.08 20.35 18.56 22.97 5 - Taisi 19.17 22.31 23.66 19.49 21.89 22.97 20.35 18.56 22.97 22.31 19.17 15.95 22.31 13.08 23.66 22.31 23.20 19.49 15.95 21.89 22.97 20.35 6- Tuku 18.56 22.97 19.17 13.08 7 - Huwei 22.31 23.20 23.66 19.49 22.31 21.89 18.56 22.97 15.95 22.97 20.35 19.17 13.08 23.20 19.49 21.89 22.97 8 - Douliu 18.56 22.31 22.31 22.97 23.66 19.17 13.08 23.20 20.35 9 - Yuanzhang 22.31 23.66 18.56 22.31 15:95 19.49 15.95 21.89 13.08 22.97 20.35 22.97 19.17 22.31 23.66 19.49 10 - Dounan 22.31 23.20 21.89 22.97 13.08 20.35 11 - Gukeng 18.56 22.97 15.95 19.17 22.31 23.66 23.20 19.49 21.89 22.31 22.97 20.35 18.56 22.97 19.17 15.95 22.31 13.08 23.66 22.31 23.20 19.49 12 - Kouhu 18.56 15.95 22.97 19.17 13.08 21.89 22.97 22.31 23.20 20.35 19.49 22.97 13 - Beigang 20.35 18.56 22.31 21.89 22.97 15.95 22.31 25.66 13.08 23.66 23.20 19.49 22.31 15.95 21.89 22.97 13.08 23.20 20.35 19.49 21.89 22.97 20.35 Inputs - Transportation Costs and Transportation Demand of 13 Zones in Yunlin Country Demand Zone Supply Zone Transportation Cost (per ton) 1 - Mailia 2 - Erlun 3-Xllu 4 - Cinton 5- Tais 6 - Tuku 7 - Huwe 1 - Mailiao 8 - Douliu 9- Yuanzhang 10 - Dounan 11 - Gukeng 37.11 12 - Kouhu 496.12 557.85 13 - Beigang Capacity (Assume 600000 is very big to 2 - Erlun 1062.02 222.41 Fixed Cost (NTD/yr) 400.81 481.93 45.94 78. 60 397.06 247.66 $06.90 644.82 875.25 be considered as unlimited capacity) 669.59 539.98 428.38 94.17 181.78 1221.58 332.18 672.97 474.22 610.03 10000 4 - Cihtong 38.34 600000 176.26 471.74 923.85 1010.61 610.58 400000 183.26 700.35 399.37 254.95 143.51 44.62 268. 10 5 - Taisi 151.44 546.63 188.16 612:35 397.67 840.66 600000 79.17 1104.78 512.61 174.43 $50.01 567.43 670.34 6 - Tuku 1041.94 47.32 228.07 528.78 720.25 543.76 1295.42 781.54 450680 600000 440.99 343.14 640.18 400000 400.81 804.29 546.63 560.75 871.36 1217.20 422.62 600000 7 - Huwei 462.04 261.84 172.53 172.22 470.15 400000 600000 8 - Douliu 263.28 508.73 806.82 430.80 720.25 31.89 755.66 144.90 583.40 240.96 392.99 296.87 284.94 357.20 286.55 680.84 976.16 450000 496.60 600000 - Yuanzhang 305.67 1455.11 867.92 512-86 504.07 361.98 26.16 1004.35 224.17 450000 506.09 302.11 600000 10 - Dounan 833.16 644.80 653.03 555.84 187.42 1626.16 91.07 245.57 261.04 $66.27 1072.58 500000 11 - Gukeng 1057.62 600000 1035.42 969.27 493.08 234.41 820.95 130.26 736.13 734.05 150.40 616.99 38.99 1315.52 354.65 1238.00 730.66 402680 600000 12 - Kouhu 734.05 495.92 180.47 869.82 315.79 450000 543.69 1010.61 43.78 1497.54 600000 960.65 13 - Beigang 432.35 922.08 1258.37 689.05 435.35 925.91 531.17 400000 577.02 734.05 856.76 677.71 600000 Transportation Demand (ton per year) 546.56 490 312.36 389.08 1050.70 250 450 689.20 1427.36 45.94 280 148.45 220 699.81 339.89 383.57 600000 280 1033.31 400000 700 1080 40.71 240 450000 430 300 250 600000 Transportation demand (per K population per year) 380 10Unclo is a big retail chain for all kinds of furniture in Taiwan. The company is working on a project to determine the locations of its distribution centers (DCs) before it ofcially sets up stores to enter the market of Yunlin County. The potential DC sites are shown in the following map, with the related information in the table below. It has been estimated that each 1,000 people will lead to a transportation demand of 10 tons per year. The xed cost for each potential site can also be found in the same table. It is assumed that the capacity of the DC is big, and the associated capacity constraint can be ignored. The distance between each pair of zones (410i is provided in the data le on the E3 course platform. In addition, the unit trucking cost (per ton) is estimated to be about 20 TWD per km. Your team is asked to prepare an executive summary for the DC location analysis project, and the following must be addressed in the report. 1. Determine the DC locations of the distribution system with the minimum total cost and describe the detailed information of your design (ggt the assignment and the shares of cost components). 2. Suppose after a careful study, it is found that the unit transport cost is unlikely to be a constant, given signicant economy of scale in trucking operations. The cost (per ton per km) is assumed to follow the following relationship. Please reconsider the decision made in Part (1). C(x) = 10(1+0.5('1+x'400)), where x is annual shipping volume from a DC to a store. 3. In addition to the issue of unit transportation cost in Part 2, please ID \\OOOHIO'NUI-hb-INH IIIIIA Nl'O 13 easy? wee Aria ears Ef Wig lH'Ea mamas r: 075' Oi mars NEE ag irts Zone Mailiao Erlun Xiluo Cihtong Taisi Tuku Huwei Douliu Yuanzhang Dounan Gukeng Kouhu Beigang Population (K) 49 25 45 28 22 28 70 1 08 24 43 30 25 38 I. Fixed Cost (yr) 400,000 400,000 450,000 400,000 400,000 450,000 450,000 500,000 400,000 450,000 400,000 400,000 450,000 conceptually discuss what else might have been overlooked and oversimplied when making the DC location decision. How can they be dealt with or corrected? i The distance beliween the DC and the store in the same zone is assumed to be 2km as they are unlikely to be co-located. 8 9 10 13 3 4 5 6 7 11 12 1 2 48 9 22 25 62 28 45 56 29 23 2 22 29 11 25 26 24 42 44 30 22 2 11 28 19 48 30 29 4 8 30 18 9 21 26 20 38 11 8 2 47 25 12 14 29 12 33 56 38 48 28 45 56 18 23 9 28 30 47 2 24 34 62 33 33 14 F O Q UA W N - L 22 19 18 25 24 2 11 39 8 22 31 43 24 25 11 9 12 34 11 2 23 15 15 2 43 12 14 71 53 62 25 21 14 62 39 23 43 2 27 38 23 28 26 26 29 28 8 15 2 16 54 36 45 24 20 12 45 22 15 12 27 16 2 65 47 11 56 42 38 33 56 33 31 14 38 43 71 23 54 65 2 17 29 44 48 56 18 33 14 24 53 6 36 47 17 2 23 30 30 38 23ID Zone Population (K) Fixed Cost (yr) Mailiao 49 400,000 1 - Mailiao Erlun 25 400,000 2 - Erlun 3 Xiluo 45 450,000 3 - Xiluo Cihtong 28 400,000 4 - Cihtong Taisi 22 400,000 5 - Taisi Tuku 28 450,000 6 - Tuku Huwei 70 450,000 7 - Huwei Douliu 108 500,000 8 - Douliu Yuanzhang 24 400,000 9 - Yuanzhang 10 Dounan 43 450,000 10 - Dounan 11 Gukeng 30 400,000 11 - Gukeng 12 Kouhu 25 400,000 12 - Kouhu 13 Beigang 38 450,000 13 - Beigang