| 1 2 3 4 5 6 7 8 9 20 40 60 70 10 30 100 300 30 50 90 20 400 150 60 40 200 15 30 250 140 250 150 20 10 200 80 120 130 40 60 30 20 40 00 vau WN Problem 4[15 points]: [You must use Excel] Nine work centers (1 through 9) must be located in a square building with 9 slots (A through lin a 3 x 3 layout of the building). The locations of work centers 1, 2, 3, and 4 are fixed as shown in the following Table 4.7. The table also provides the number of loads to be moved from a work center to the other work centers. Assume that transportation costs are $5 per load per meter. a) Propose TWO ALTERNATIVE layouts using the given in the table (note: you don't need find an optimal layout). [3 points) b) Create the distance tables for the two layouts from a). Assume that distances are 10 meters for adjacent departments/locations (e.g., A and B, ... etc), 20 meters for nonadjacent departments/locations (e.g., A and C, A and E, ... etc.), and 30 meters for departments in opposite corners (i.e., A and I or G and C). [3 points + 3 points] c) Compute the total transportation costs for the two proposed layouts. [2 points + 2 points) d) Recommend the best layout among the two you have tried. [1 point] e) Calculate the total number of possible layouts with the given information in the following table in total. [1 point] TABLE 4.7 | 1 2 3 4 5 6 7 8 9 20 40 60 70 10 30 100 300 30 50 90 20 400 150 60 40 200 15 30 250 140 250 150 20 10 200 80 120 130 40 60 30 20 40 00 vau WN Problem 4[15 points]: [You must use Excel] Nine work centers (1 through 9) must be located in a square building with 9 slots (A through lin a 3 x 3 layout of the building). The locations of work centers 1, 2, 3, and 4 are fixed as shown in the following Table 4.7. The table also provides the number of loads to be moved from a work center to the other work centers. Assume that transportation costs are $5 per load per meter. a) Propose TWO ALTERNATIVE layouts using the given in the table (note: you don't need find an optimal layout). [3 points) b) Create the distance tables for the two layouts from a). Assume that distances are 10 meters for adjacent departments/locations (e.g., A and B, ... etc), 20 meters for nonadjacent departments/locations (e.g., A and C, A and E, ... etc.), and 30 meters for departments in opposite corners (i.e., A and I or G and C). [3 points + 3 points] c) Compute the total transportation costs for the two proposed layouts. [2 points + 2 points) d) Recommend the best layout among the two you have tried. [1 point] e) Calculate the total number of possible layouts with the given information in the following table in total. [1 point] TABLE 4.7
