The book you can use is: Supply Chain Management 7th Edition by Sunil Chopra

As a supply chain analyst, you are being asked to design a network for a supply chain that consists of three stages: (i) manufacturing plants, (ii) distribution centres, and (iii) regional markets. The regional markets have a demand for a specific product that can be produced in any of the plants, but can only be shipped to the regional markets through the distribution centres. Assume that the number of potential sites for plants is n, the number of potential distribution centres (DCs) is w, and number of regional markets is m. Your job is to determine which potential plant sites should be opened, and which potential DCs should be opened in order to satisfy demand in all regional markets. Simultaneously, you need to minimize the total cost of the network, and not violate any of the potential capacity constraints of the plants as well as the DCs. Costs, capacities and demands are defined as follows: F = annualized fixed cost of opening plant i, i = 1,2, ..., n fi= annualized fixed cost of opening DC j, j = 1,2, ..., w Cij= cost of producing and shipping one unit from plant i to DCj Wik= cost of holding inventory and shipping one unit from DC ; to regional market k Dx= annual demand from regional market k Ki= potential capacity of plant i W;= potential capacity of DCj 1. Define your decision variables and formulate the problem as a mathematical programming problem by clearly stating what the objective function is and what your constraints are. Suppose you are designing the network for Canada, where the potential plant and DC sites are Halifax, Montreal, Toronto, Winnipeg, Calgary, and Vancouver. Furthermore, assume the regional markets are Atlantic, Central, Prairies, and Western. The input data for these sites and markets are presented below: Regional DCS Production and Transport Cost per 1,000,000 units 132 156 126 Plants Halifax Montreal Toronto Winnipeg Calgary Vancouver Halifax Montreal Toronto Winnipeg Calgary Vancouver 120 140 170 185 98 85 90 115 145 95 114 136 130 108 110 124 143 129 109 100 148 140 139 126 117 110 102 108 100 125 107 Regional Markets Inventory and Transport Cost per 1,000,000 units Atlantic Central Prairies 113 Western 132 DC Halifax Montreal Toronto Winnipeg Calgary Vancouver Annual Demand (millions) 106 Capacity Fixed Cost (millions) ($millions) Capacity (millions) Fixed Cost ($millions) 2.8 3.5 7.0 Plants Halifax Montreal Toronto Winnipeg Calgary Vancouver 8.1 DCS Halifax Montreal Toronto Winnipeg Calgary Vancouver 6.4 2. Use the mathematical model from above to get an optimal design using Excel Solver for the Canadian supply chain. Suppose now you have two modes of transportation to use from the DCs to the regional markets. Mode 1 is fast and expensive, while Mode 2 is slow and less expensive. Based on the mode of transportation, define the following: wk= cost of holding inventory and shipping one unit from DC ; to regional market k using transportation mode 1,1 = 1,2 tx= transit time from DC to regional market k using transportation model Rx= maximum response time required by regional market k 3. Taking into consideration the mode of transportation, how would you model the constrained optimization problem now? Make sure you clearly define your decision variables, objective function, and your constraints. The response times in days that are required by the regional markets are Atlantic 7, Central 5, Prairies 5, and Western 6. The remaining input data for the Canadian supply chain with two modes of transportation are as follows: MODE 1 Regional Markets Inventory and Transport Cost per 1,000,000 units Atlantic Central Prairies Western 113 132 71 DCS Halifax Montreal Toronto Winnipeg Calgary Vancouver 106 MODE 2 Regional Markets Inventory and Transport Cost per 1,000,000 units Atlantic Central Prairies Western DCS 55 117 Halifax Montreal Toronto Winnipeg Calgary Vancouver Transit Time from DCs to Markets Mode 1 Atlantic Central Prairies Western Halifax Montreal Toronto Winnipeg Calgary Vancouver Mode 2 Transit Time from DCs to Markets Central Prairies Western Atlantic 8 + 00 00 Halifax Montreal Toronto Winnipeg Calgary Vancouver + ooo + 0 oooooo 00 + 4. Using the above data and Excel Solver, revise your network design. As a supply chain analyst, you are being asked to design a network for a supply chain that consists of three stages: (i) manufacturing plants, (ii) distribution centres, and (iii) regional markets. The regional markets have a demand for a specific product that can be produced in any of the plants, but can only be shipped to the regional markets through the distribution centres. Assume that the number of potential sites for plants is n, the number of potential distribution centres (DCs) is w, and number of regional markets is m. Your job is to determine which potential plant sites should be opened, and which potential DCs should be opened in order to satisfy demand in all regional markets. Simultaneously, you need to minimize the total cost of the network, and not violate any of the potential capacity constraints of the plants as well as the DCs. Costs, capacities and demands are defined as follows: F = annualized fixed cost of opening plant i, i = 1,2, ..., n fi= annualized fixed cost of opening DC j, j = 1,2, ..., w Cij= cost of producing and shipping one unit from plant i to DCj Wik= cost of holding inventory and shipping one unit from DC ; to regional market k Dx= annual demand from regional market k Ki= potential capacity of plant i W;= potential capacity of DCj 1. Define your decision variables and formulate the problem as a mathematical programming problem by clearly stating what the objective function is and what your constraints are. Suppose you are designing the network for Canada, where the potential plant and DC sites are Halifax, Montreal, Toronto, Winnipeg, Calgary, and Vancouver. Furthermore, assume the regional markets are Atlantic, Central, Prairies, and Western. The input data for these sites and markets are presented below: Regional DCS Production and Transport Cost per 1,000,000 units 132 156 126 Plants Halifax Montreal Toronto Winnipeg Calgary Vancouver Halifax Montreal Toronto Winnipeg Calgary Vancouver 120 140 170 185 98 85 90 115 145 95 114 136 130 108 110 124 143 129 109 100 148 140 139 126 117 110 102 108 100 125 107 Regional Markets Inventory and Transport Cost per 1,000,000 units Atlantic Central Prairies 113 Western 132 DC Halifax Montreal Toronto Winnipeg Calgary Vancouver Annual Demand (millions) 106 Capacity Fixed Cost (millions) ($millions) Capacity (millions) Fixed Cost ($millions) 2.8 3.5 7.0 Plants Halifax Montreal Toronto Winnipeg Calgary Vancouver 8.1 DCS Halifax Montreal Toronto Winnipeg Calgary Vancouver 6.4 2. Use the mathematical model from above to get an optimal design using Excel Solver for the Canadian supply chain. Suppose now you have two modes of transportation to use from the DCs to the regional markets. Mode 1 is fast and expensive, while Mode 2 is slow and less expensive. Based on the mode of transportation, define the following: wk= cost of holding inventory and shipping one unit from DC ; to regional market k using transportation mode 1,1 = 1,2 tx= transit time from DC to regional market k using transportation model Rx= maximum response time required by regional market k 3. Taking into consideration the mode of transportation, how would you model the constrained optimization problem now? Make sure you clearly define your decision variables, objective function, and your constraints. The response times in days that are required by the regional markets are Atlantic 7, Central 5, Prairies 5, and Western 6. The remaining input data for the Canadian supply chain with two modes of transportation are as follows: MODE 1 Regional Markets Inventory and Transport Cost per 1,000,000 units Atlantic Central Prairies Western 113 132 71 DCS Halifax Montreal Toronto Winnipeg Calgary Vancouver 106 MODE 2 Regional Markets Inventory and Transport Cost per 1,000,000 units Atlantic Central Prairies Western DCS 55 117 Halifax Montreal Toronto Winnipeg Calgary Vancouver Transit Time from DCs to Markets Mode 1 Atlantic Central Prairies Western Halifax Montreal Toronto Winnipeg Calgary Vancouver Mode 2 Transit Time from DCs to Markets Central Prairies Western Atlantic 8 + 00 00 Halifax Montreal Toronto Winnipeg Calgary Vancouver + ooo + 0 oooooo 00 + 4. Using the above data and Excel Solver, revise your network design