Use the data and the Excel Solver model to answer this question.

a) (4 points) Your client asked you to determine the capacity utilization level of the existing distribution system. Compute the capacity utilization levels for each distribution center and for the entire distribution network based on your optimal transportation plan in Part-I. Complete the table below to report the capacity utilization levels.

Distribution center(s)	Available capacity (no. of truckloads of shipment)	Capacity used (no. of truckloads of shipment)	Capacity Utilization
GA
NC
TX
All distribution centers

Table-Q7-a. Capacity utilization when the optimal transportation plan is used

b) (6 points) Your client thinks that they are operating at a very high capacity utilization rate, which does not give them any flexibility to effectively respond to unexpected increases in demand. The client asked you to determine whether the distribution network capacity should be expanded by 100 units (where capacity is measured in terms of number of truckloads of shipment). Expansion, if approved, will occur at only one location. You have to determine which distribution center, if expanded by 100 units, will lead to largest savings in transportation costs. Complete the table below to report your findings.

Location of capacity expansion	Optimal transportation cost after expansion	Reduction in optimal transportation cost	Percentage change in optimal transportation cost due to expansion
GA
NC
TX

Table-Q7-b. Transportation costs of different capacity expansion plans

NOTE: Optimal transportation cost is obtained by solving a transportation problem using Excel Solver. You already computed this in the previous question for the existing network capacity. To complete Table-Q7-b, you have to formulate and solve separate optimization problems. In each optimization problem, only one of the distribution centers has 100 additional units of capacity available.

Which distribution center, if expanded, leads to the highest reduction in optimal transportation costs?

E F I J I 2 Stores located in 3 TX 4 5 1205 B D Distance from/to (in miles) Distribution center located in Stores located in GA NC TX Texas 1115 1286 200 Florida 265 645 Arizona 1920 1985 986 North Carolina 495 129 1217 Louisiana 621 833 361 Georgia 120 330 973 Tennessee 460 475 717 Arkansas 690 749 400 California 2550 2557 1550 South Carolina 288 185 1020 $ $ $ $ 6 H Distance from/to (in miles) Distribution center located in GA NC 1,449.50 $ 1,671.80 $ 344.50 $ 838.50 $ 2,496.00 $ 2,580.50 $ 643.50 $ 167.70 $ 807.30 $ 1,082.90 $ 156.00 $ 429.00 $ 598.00 $ 617.50 $ 897.00 $ 973.70 $ 3,315.00 $ 3,324.10 $ 374.40 $ 240.50 $ 7 Texas Florida Arizona North Carolina Louisiana Georgia Tennessee Arkansas California South Carolina 8 260.00 1,566.50 1,281.80 1,582.10 469.30 1,264.90 932.10 520.00 2,015.00 1,326.00 $ 9 10 $ 12 $ 13 14 15 16 17 Distribution center located in GA NC TX Distribution center capacity Monthly truckload capacity 1200 650 900 18 19 20 21 Cost Per Truck Load / $ truckloads per month 1.30 12 Stores located in GA Decision Function Distribution center located in NC TX 108 0 300 0 Amount of Truckloads 252 360 Texas 23 Retail stores Florida 0 300 States Arizona 0 0 264 24 25 264 0 252 0 240 26 252 0 0 0 240 27 0 192 28 48 228 stores 30 25 22 21 20 16 19 15 32 27 29 No. Truckloads per Month 360 300 264 252 240 192 228 180 384 324 Texas Florida Arizona North Carolina Louisiana Georgia Tennessee Arkansas California South Carolina North Carolina Louisiana Georgia Tennessee Arkansas Califomia South Carolina Amount of Truckloads 192 180 180 0 0 180 30 0 0 324 384 384 31 0 0 324 32 1200 624 900 33 Objective Function Total Shipping Cost Minimize Total Shopping Cost 2,080,197.60 34 35 36 37 E F I J I 2 Stores located in 3 TX 4 5 1205 B D Distance from/to (in miles) Distribution center located in Stores located in GA NC TX Texas 1115 1286 200 Florida 265 645 Arizona 1920 1985 986 North Carolina 495 129 1217 Louisiana 621 833 361 Georgia 120 330 973 Tennessee 460 475 717 Arkansas 690 749 400 California 2550 2557 1550 South Carolina 288 185 1020 $ $ $ $ 6 H Distance from/to (in miles) Distribution center located in GA NC 1,449.50 $ 1,671.80 $ 344.50 $ 838.50 $ 2,496.00 $ 2,580.50 $ 643.50 $ 167.70 $ 807.30 $ 1,082.90 $ 156.00 $ 429.00 $ 598.00 $ 617.50 $ 897.00 $ 973.70 $ 3,315.00 $ 3,324.10 $ 374.40 $ 240.50 $ 7 Texas Florida Arizona North Carolina Louisiana Georgia Tennessee Arkansas California South Carolina 8 260.00 1,566.50 1,281.80 1,582.10 469.30 1,264.90 932.10 520.00 2,015.00 1,326.00 $ 9 10 $ 12 $ 13 14 15 16 17 Distribution center located in GA NC TX Distribution center capacity Monthly truckload capacity 1200 650 900 18 19 20 21 Cost Per Truck Load / $ truckloads per month 1.30 12 Stores located in GA Decision Function Distribution center located in NC TX 108 0 300 0 Amount of Truckloads 252 360 Texas 23 Retail stores Florida 0 300 States Arizona 0 0 264 24 25 264 0 252 0 240 26 252 0 0 0 240 27 0 192 28 48 228 stores 30 25 22 21 20 16 19 15 32 27 29 No. Truckloads per Month 360 300 264 252 240 192 228 180 384 324 Texas Florida Arizona North Carolina Louisiana Georgia Tennessee Arkansas California South Carolina North Carolina Louisiana Georgia Tennessee Arkansas Califomia South Carolina Amount of Truckloads 192 180 180 0 0 180 30 0 0 324 384 384 31 0 0 324 32 1200 624 900 33 Objective Function Total Shipping Cost Minimize Total Shopping Cost 2,080,197.60 34 35 36 37