P19 fx A B C D E F G H 1 Capacitated Location 2 J K L M N 3 Atlanta Birmingham Columbia Jackson Jacksonville Louisville Memphis Miami Nashville New Orlean: Orlando Capacity 4 Atlanta 0.00 0.15 0.21 0.40 0.31 0.42 0.38 0.66 0.25 0.48 0.43 15000 5 Birminghar 0.15 0.00 0.36 0.25 0.46 0.36 0.26 0.75 0.19 0.35 0.55 15000 6 Columbia 0.21 0.36 0.00 0.60 0.30 0.50 0.62 0.64 0.44 0.69 0.44 15000 7 Louisville 0.42 0.36 0.50 0.59 0.73 0.00 0.38 1.09 0.17 0.70 0.86 15000 8 Memphis 0.38 0.26 0.62 0.21 0.69 0.38 0.00 1.00 0.21 0.41 0.78 15000 9 Nashville 0.25 0.19 0.44 0.41 0.56 0.17 0.21 0.91 0.00 0.53 0.69 15000 10 Orlando 0.43 0.55 0.44 0.70 0.14 0.86 0.78 0.23 0.69 0.65 0.00 15000 11 Volume Ne 5000 3000 1400 2200 8800 3000 7800 4400 6800 5800 2200 12 13 14 15 Q2) Southeastern Foods has hired you to analyze their distribution system design. The company has eleven distribution centers, with monthly volumes as listed below. Seven of these sites have warehouses, in terms of the infrastructure available. Information has been compiled showing the cost per carton of shipping from any potential warehouse location to any distribution center. Southeastern has standardized its warehouse design so that all such facilities can handle up to 15,000 cartons in a month. (a) What is the total distribution cost (fixed warehouse costs plus variable distribution cost) for the optimal configuration? (b) Now you learn that the monthly fixed cost for operating one of these ware- houses is estimated at $3600 a month. Southeastern is debating whether to close some of these warehouses to avoid paying the fixed $3600 for each of them. i) Update the problem with the new information. What is the new opti- mal total cost? Hint: Note that we don't need to use every warehouse! If we don't use one, we can save $3600! For this, create a new set of decisions, where you plug 1 if you use the warehouse (and add the $3600 cost to the total), and 0 if you do not. ii) How many warehouses should I use in this case? iii)What is the optimal distribution system? (leave it on Excel for me to see) 2 3 Data Regions 4 Locations Fixed Costs East South Midwest West Capacity 5 New York 6000 206 225 230 290 150 99 6 7 Atlanta 5500 225 206 221 270 150 Chicago 5800 230 221 208 262 150 8 Los Angeles 6200 290 270 262 215 150 9 Demand 100 150 110 90 10 11 Decisions In Use Sent Linking 12 New York 1 100 0 0 0 100 -50-150-150-150 13 Atlanta 1 0 150 0 150 -150 0-150-150 14 Chicago 1 0 0 50 50 15 Los Angeles 1 0 0 60 16 Received 100 150 110 985 90 150 -150 150 -100-150 -150-150 -90 -60 90 17 18 Objective Operating 23,500 Distribution 96,970 Total 120,470 19 1 Loc14 Figure 7.9 Spreadsheet model for Example 7.3. The Decisions section of the spreadsheet contains an array for decision variables. Column B holds the values of the y; variables. The preliminary solution shown in the figure uses all four DC locations, so the entries in this column are all 1s. The range C12:F15 contains a solution to the transportation subproblem that constitutes the kernel of the model, although this solution may not be optimal. Finally, the array I12:L15 contains the left-hand side of the linking constraints corresponding to (7.3), expressed in the form x Cy. Thus, the linking constraints are satisfied when each element in this array is less than or equal to zero. - Finally, the Objective section contains the evaluation of costs. The total operating cost in cell C18 and the total distribution cost in E18 are added to generate the total cost in G18. The model specification is as follows: