Supply Chain Network Design
Pre-class assignment:
Read the Designing the Production Network at CoolWipes case study at the end of Ch 4 in Chopra (2026) textbook. Prepare answers to the questions below to be submitted (as a hard copy) prior to the start of class.
The spreadsheet provided below contains an optimization model that can be used to answer the study questions at the end of the case study. Please review the spreadsheet, including the Solver model, and make sure you understand where the input data comes from and how the optimization model is set up. Then answer the following questions in writing and submit a hard copy at the start of class on Tuesday, Sept 23:
1. The cells highlighted in blue are the decision variables for the problem. Explain in words (clearly and fully) what decisions the following cells represent:
B23-G26
H23-H26
B31-G34
H31-H34
2. The objective function is the total cost, as given in cell B37. Please answer the following questions about that calculation:
Notice that the calculation of the total does not include the variable costs, as given in cells C4-C7 and F4 F7. Explain why it is not necessary to include those costs when building the optimization model.
Explain why it might be useful to include the variable costs, even though it is not necessary in order to find the optimal solution.
3. The input data and optimization model in Solver are set up to answer the first part of Question 3 at the end of the case study (but not the questions about changing the transportation costs). Run the solver model to obtain the optimal solution for the problem described in the first part of Question 3.
Describe the optimal solution in words. In other words, if you worked for Matt O'Grady, the VP of Supply Chain, how would you describe the optimal solution to him?
Supply chains and distribution networks are often represented graphically using a network, with a set of nodes (circles, ovals or rectangle) representing facilities or markets, and arcs (arrows) that connect the nodes and indicate product flow. See, for example, Figure 4-11 in the Chopra (2026) textbook. Draw one or more network graph(s) to show the optimal solution found in part a of this question. Make sure to add sufficient detail (e.g., the quantity and type of goods being produced and shipped). Also make sure that your graph(s) are well-formatted and easy to read. You may use whatever tool you prefer to create this graphic; graphs drawn by hand are also acceptable as long as they are readable.
4. The optimization model in Solver can also be used to answer Questions 1 and 2 at the end of the case study. However, to answer these questions, changes need to be made to the model and/or data. For example, for Questions 1 and 2, we need to assume that the existing Chicago plant continues to operate, while in Question 3 we assumed the Chicago plant does not yet exist, but could be built. Therefore, to answer Questions 1 and 2, we need to change the fixed costs and capacity for the Chicago plant to reflect the current costs and capacity, not the values for a new plant. In other words, we would need to change cell B4 to 5,000 and cell D4 to 5,000. In addition, starting from the model for Question 3:
Explain what constraints you would need to add to the Solver model to answer Question 1. Describe the constraints in words and write them as you would in Solver, referring to the appropriate cells.
Explain what constraints you would need to add to the Solver model to answer the first part of Question 2 (ignore the second part of Question 2, about changing the transportation costs). Describe the constraints in words and also write them as you would in Solver, referring to the appropriate cells.
For parts a and b, you are not required to implement these changes in Excel. However, you might choose to do so, and then run the optimization model, to make sure your model works as intended.
In-class assignment
During class, we will conduct additional analysis related to the case study, including considering the impact of changes in transportation costs and economies of scale when constructing new plants. Please come to class with your laptop and the Cool Wipes spreadsheet and be prepared to conduct additional analysis to make additional recommendations.