Problem 1 (40 points) A firm has six retail stores centers in Toronto and has decided to open new distribution centers to support its retail stores and better control the day-to-day costs. There are two types of costs associated with establishing this new center: initial construction cost are capital costs which may usually be amortised over several years, and transportation costs which depend on the distance covered. There are 6 sites available for the construction of new distribution centers to deliver goods to the six retail stores. We have two types of distribution centers A and B, and we can only build a maximum of two of each type. The following table (Table 1) gives the transportation costs (in thousand dollars) of delivering the entire demand of each retail store from a distribution center. Certain deliveries that are impossible are marked with the infinity symbol (∞). Winter 2024 ADM2302 Winter 2024 Table 1 Retail Store Distribution Center 1 2 3 4 5 6 1 - Type A $100 $80 $50 $50 $60 $100 2- Type A $120 $90 $60 $70 $65 8 3 - Type B $140 $120 $80 $80 $75 $130 4- Type B $160 $125 $100 $100 8 $150 5-Type A $190 $150 $130 8 00 8 6-Type B $150 80 8 00 $50 $120 Page 1 Assignment 2 The construction costs for each distribution center as well as the capacity of each center are listed in Table 2. Table 2 Distribution Center 1 2 3 4 5 6 Cost ($1000) 8000 9000 6500 5000 9000 6000 Capacity(tons) 250 250 150 150 250 150 There are estimations for demand for each retail store which are shown in Table 3. Table 3 Retail store 1 2 3 4 5 6 Demand (tons) 150 90 80 160 100 200 Considering that the demand of a retail store needs to be satisfied and each retail stores can be supported by several distribution centers. a) Formulate algebraically the corresponding model that will determine which distribution centers should be opened to minimize the total cost of construction and of delivery, while satisfying all demands and respecting all the limitations as stated in the problem. Define the decision variables, objective function, and constraints. (25 points) b) Formulate this problem on a spreadsheet and use Excel's Solver to determine the optimal solution. (Provide the corresponding "Excel Spreadsheet" and the "Answer Report"). Include "managerial statements" that communicate the results of the analyses (i.e. describe verbally the results). (15 points)

Fig: 1