PLEASE only answer questions 7a and 7b at the TOP of this page, the rest of the information below is merely context.

PLEASE ANSWER:

7a) 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 PartI. Complete the table below to report the capacity utilization levels.

Distribution center(s)

GA

NC

TX

All distribution centers

Available capacity (no. of truckloads of shipment)

Capacity used (no. of truckloads of shipment)

Capacity Utilization

PLEASE ANSWER:

TableQ7a. Capacity utilization when the optimal transportation plan is used

7b) 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.

GA NC TX

NOTE: Optimal transportation cost is obtained by solving a transportation problem using Excel Solver. This is already computed in the following screenshots attached below (for the existing network capacity). To complete TableQ7b, 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?

*context below*

Use the data and the Excel Solver model developed below to for help with this question, thanks!

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

There are thirty decision variables since there are three distribution centers amongst ten states (10x3= 30). Let XIj be the truckload transported from the i, the distribution center, to j which is the stores. The i being 1,2,3 for distribution centers and j being stores from the ten states: 1,2,3,4,5,6,7,8,9,10.

• b) (2 points) What is the objective function in 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.

The objective function of this problem is to minimize the total cost of transportation. In order to compute the total cost of transportation, you would find the product (in other words, multiply) the per unit transportation cost by the decision variables. In this case, you would find the total cost by multiplying the distance in miles by 1.3 dollars which is the cost indicated by our client.

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

There are three constraints: demand, capacity, and non-negativity constraints. The demand constraint is based on the row totals of the decision variables which must be greater than or equal to the demand of the truckloads in the ten states of stores. This indicates that there are ten demand constraints. The capacity constraint is based on the column totals of the decision variables which must be less than or equal to the capacity of the three distribution centers. Since there are three distribution centers, there are three capacity constraints. Lastly, there is the non-negativity constraint which indicates that all decision variables must be greater than or equal to zero. We cannot have any negative integers as that wouldnt work with the operations.

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

The sign and type restrictions in this problem are non-negative which means all decision variables must be greater than or equal to zero. It is not possible to compute a negative number as there cannot be a negative transportation cost between the distribution centers and stores. This is the only restriction as it is not possible to compute a negative transportation cost nor is it possible to send a negative number of truckloads from the distribution centers to stores.

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

  1. (2 points) 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. (2 points) 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. (2 points) 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. (2 points) 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?

Distance from/to (in miles) Distribution center located in Retail stores Stores located in GA NC TX States No. of stores Texas 1115 1286 200 Texas 30 Florida 265 645 1205 Florida 25 Arizona 1920 1985 986 22 Arizona North Carolina North Carolina 495 129 1217 21 Louisiana 621 833 361 20 120 330 973 Louisiana Georgia Tennessee Georgia Tennessee 16 460 475 717 19 Arkansas 690 749 400 Arkansas 15 2550 2557 1550 California 32 California South Carolina 288 185 1020 South Carolina 27 Distribution center located in Distribution center capacity GA NC TX Monthly truckload capacity 1200 650 900 E F G H J K 1 2 2 3 4 1115 5 6 A B C D Distance from/to (in miles) Distribution center located in Stores located in GA NC TX Texas 1286 200 Florida 265 645 1205 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 7 Retail stores States No. of stores Texas 30 Florida 25 Arizona 22 North Carolina 21 Louisiana 20 Georgia 16 Tennessee 19 Arkansas 15 California 32 South Carolina 27 8 9 10 11 12 13 14 Distribution center located in transportation cost dollor/mile 1.3 GA 1200 NC 650 TX 900 GA NC TX constraint 15 Distribution center capacity 16 17 Monthly truckload capacity 18 19 decision variables 20 Texas 21 Florida 22 Arizona 23 North Carolina 24 Louisiana 25 Georgia total 252 108 0 360 1200 300 0 ol 3001 650 0 0 264 900 264 0 1200 should not exceed 624 should not exceed 900 should not exceed 360 should not exceed 300 should not exceed 264 should not exceed 0 252 360 252 240 240 0 0 300 192 0 0 192 264 180 48 0 228 26 27 252 GA capacity NC capacity TX capacity Texas demand Florida demand Arizona demand North Carolina demand Louisiana demand Georgia demand Tennessee demand Arkansas demand California demand South Carolina demand 180 0 0 1801 Tennessee Arkansas California South Carolina 240 28 0 0 384 384 192 252 should not exceed 240 should not exceed 192 should not exceed 228 should not exceed 180 should not exceed 384 should not exceed 29 0 324 0 01 324 228 30 180 31 total capacity 1200 624 900 384 324 should not exceed 324 32 33 objective function 34 min 35 transportation cost 2080197.6 ecision variables GA NC TX 108 0 252 300 0 0 0 264 0 0 0 Texas Florida Arizona North Carolina Louisiana Georgia total =SUM(B20:020) ESUM(B21:D21) =SUM(B22:022) =SUM(B23:D23) =SUM(B24:D24) =SUM(B25:025) 0 252 0 240 0 0 192 0 lo 180 48 0 =SUMB26:026) 180 o 0 Tennessee Arkansas California South Carolina o 0 384 =SUM(B27:D27) =SUM(B28:D28) =SUM(B29:029) lo 324 0 total capacity =SUM(B20:329) ESUM(C20:c29) =SUM(D20:029) bjective function hin transportation cost =SUMPRODUCT(B20:D29,34:01 =B31 =B17 constraint GA capacity NC capacity TX capacity =C31 =C17 =D31 =D17 should not exceed should not exceed should not exceed should not exceed should not exceed should not exceed =H4*12 Texas demand Florida demand Arizona demand =E20 =H5*12 =E21 =H6*12 =E22 =H7*12 =E23 =H8*12 =E24 =H9*12 =E25 North Carolina demand Louisiana demand Georgia demand Tennessee demand Arkansas demand California demand should not exceed should not exceed should not exceed should not exceed should not exceed should not exceed =H10*12 =E26 =H11*12 =E27 =E28 =H12*12 South Carolina demand =H13*12 should not exceed =E29 Solver Parameters E Set Objective: $B$34 To: Max Min O Value Of: 0 By Changing Variable Cells: $B$20:$D$29 A Subject to the Constraints: $H$20:$H$22