You work as an operations consultant for a textile company. Your client has a well-established distribution system in the US market. The company has hundreds of stores and four distribution centers. You will work with your client to determine the best way to distribute goods from distribution centers to retail stores. The following information will guide you in analyzing the facility location decisions. 

● The company has 350 stores in 15 different states. See the locations in the Excel file.

● The company has 5 distribution centers: Valdosta GA, Salisbury NC, Ft. Worth TX, Salt Lake City UT, and Wichita, KS. 

● For each distribution center, information on o estimated monthly capacity (in terms of the number of truckloads of goods that can be shipped), and o the distance between the distribution center locations and store locations are provided in the Excel file. 

● Your client tells you that the transportation cost will be $1.6 per mile per truckload. 

● Following Zara’s strategy of “fast fashion” or fast-response supply chain, your client makes two deliveries to each store every week. Each delivery involves only one truckload. That is, a total of two truckloads of goods are shipped from the distribution centers to each retail store each week. In your analysis, assume that a month is only four weeks; this brings monthly store demand to 2x4=8 truckloads per month. You will analyze the information provided, build spreadsheet models, and use optimization to make a recommendation to your client on which distribution centers should supply which retail stores. NOTE: SHOW ALL YOUR WORK. PART-I: Transportation Problem You first analyzes the cost of the logistics network. For this problem, you will formulate a transportation problem and determine the monthly shipment plan that minimizes the total transportation costs. Your client is not familiar with optimization models. You must clearly explain your approach to computing the minimum transportation. To do so,

a) Clearly define your decision variables in English. How many decision variables do you have? NOTE: In your Solver model, the unit of the shipment should be a number of truckloads.

b)  What is the objective function of this problem? Explain how you would compute the objective function (in English). The mathematical expression for the objective function is very long, so do NOT explicitly state the mathematical formula.

c) Clearly explain the constraints (in English). What type of constraints do you have? Do NOT provide the mathematical formula for the constraints.

d) What are the sign and type restrictions in optimization problems? Are there any signs or type restrictions on this problem? Clearly explain why or why not.

e) Prepare a spreadsheet to determine the optimal production plan using Excel Solver. 

1. Provide a screenshot of your spreadsheet model. The screenshot should show all the cells involved in formulating the optimization problem. NOTE: Do not copy and paste your spreadsheet to your answer report as an Excel object. Instead, capture a screenshot in Excel and paste this as an image. 

2. Provide a screenshot of your spreadsheet with the formula view. You can switch from normal view to formula view in Excel by clicking the CTRL and ~ keys on your keyboard simultaneously.  

3. Prepare the Solver model by logging all the information to the Solver Parameters window in Excel. Provide a screenshot of the Solver Parameters window in Excel. NOTE: Ignore all type restrictions in this problem. Do not enforce your decision variables to be integer while using Excel Solver. If you formulate the problem correctly, the optimal solution provided by the Solver will be in whole numbers even without the type restrictions. 

4. Finally, solve the optimization problem using the Simplex LP method in Excel Solver. What is the optimal solution, i.e. the optimal objective function value and the optimal values of decision variables?PART-II Expand the Outbound Logistics Capacity

f) 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 Valdosta, GA 625 Salisbury, NC 650 Ft. Worth, TX 525 Salt Lake City, UT 710 Wichita, KS 450 All distribution centers Table-Q6-f. Capacity utilization when the optimal transportation plan is used g)

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 hikes in demand. The client asked you to determine whether the distribution network capacity should be expanded by 150 units (where capacity is measured in terms of the number of truckloads of shipment). Expansion, if approved, will occur at only one location. You have to determine which distribution center if expanded by 150 units, will lead to the 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 Valdosta, GA Salisbury, NC Ft. Worth, TX Salt Lake City, UT Wichita, KS Table-Q6-g. Transportation costs of different capacity expansion plans Which distribution center, if expanded, leads to the highest reduction in optimal transportation costs? NOTE: Optimal transportation cost is obtained by solving a transportation problem using Excel Solver. You already computed this in Part-I for the existing network capacity. To complete Table-Q6-g, you have to formulate and solve five optimization problems. In each optimization problem, only one of the distribution centers has 150 additional units of capacity available.

What additional information should be taken into account before making a final decision about the capacity expansion? Clearly explain the type of information needed and how that information could be obtained.

 

Distance From/To (in miles) Planned store locations as Valdosta, GA Salisbury, NC Ft. Worth, TX Salt Lake City, UT Wichita, KS Number of Stores in each State State 1109 1286 193 1090 493 40 as Florida 261 638 1202 2372 1455 30 Florida Arizona 1918 1981 986 644 935 25 Arizona North Carolina 495 105 1216 2126 1190 20 North Carolina Louisiana 619 833 361 1590 707 24 Louisiana Georgia 111 326 973 2031 1113 20 Georgia Alabama 280 482 642 1840 897 16 Alabama Missouri 894 840 593 1293 354 Missour Oklahoma 1040 1107 182 1236 209 8 Oklahoma Tennessee 454 450 715 1691 735 23 Tennessee Arkansas 685 747 397 1491 464 10 Arkansas California 2546 2551 1533 814 1503 40 California South Carolina 288 153 1020 2080 1163 25 South Carolina New York 977 586 1579 2172 1459 40 New York Illinois 944 733 999 1399 705 22 Illinois Monthly capacity (truckloads) 625 650 525 710 450