1. What is the annual cost of serving the entire nation from Chicago?

2. Do you recommend adding any plant(s)? If so, where should the plant(s) be built and what lines should be included? Assume that the Chicago plant will be maintained at its current capacity but could be run at lower utilization. Would your decision be different if transportation costs are half of their current value? What if they were double their current value?

3. If Matt could design a new network from scratch (assume he did not have the Chicago plant but could build it at the cost and capacity specified in the case), what production network would you recommend? Assume that any new plants built besides Chicago would be at the cost and capacity specified under the new network options. Would your decision be different if transportation costs were half of their current value? What if they were double their current value?

Pay attention to Column H in the variable section which is where you will use the 1 for Open Plant or 0 for Closed Plant.

 

1 2 3 Plant 4 Chicago 5 Princeton, NJ 6 Atlanta LA 7 8 9 10 Zone 11 Wipes 12 13 14 A 21 Ointment 15 16 Chicago 17 Princeton, NJ 18 Atlanta 19 LA 20 22 23 Chicago 24 Princeton, NJ 25 Atlanta 26 LA 27 Demand 28 B Wipes Variable Fixed Cost Cost/Unit Capacity Fixed Cost 5,000 2,200 2,200 2,200 500 50 C D E Plant Costs and Capacity 6.32 6.60 6.72 4.36 10 10 10 10 500 700 90 Northwest Southwest Regional Demand by Product Upper Lower Northwest Southwest Midwest Midwest 5,000 2,000 2,000 2,000 6.32 6.60 6.48 3.68 900 120 700 1,500 1,500 1,500 1,500 Transportation Costs 3.68 5.76 5.92 6.32 800 65 Upper Lower Northwest Southwest Midwest Midwest 900 4.04 5.92 4.08 6.32 Ointment Variable Cost/Unit Capacity Upper Lower Midwest Midwest Northeast Southeast Wipes Variables 800 20 20 20 20 Northeast Southeast 1,000 120 G 5.76 3.68 4.04 6.72 Northeast Southeast 1,000 1,000 1,000 1,000 1,000 600 70 5.96 4.08 3.64 6.60 - 600 H Total 4,500 515 Fixed 0 0 0 0 I Transport Cost Multiplier Capacity 1 J K L M N Question 1 Set Cells H23 and H31 to be 1. Copy B11 to G11 to B23 to G23. Copy B12 to G12 to B31 to G31. Total cost is obtained in Cell B37. Question 2 Use Data Analysis | Solver to solve for the optimal configuration. To change transportation costs, change the multiplier in Cell 116. Question 3 Delete the constraints in Solver setting Cells H23 and H31 to 1 and resolve. O