QUESTION 3 ( 25 points): Excel Spreadsheet Formulation and Solutions LAFARGE Lafarge Paving and Construction (Eastern) Limited is the largest supplier of asphalt, TNORTH AMERICA aggregates and concrete in the Ottawa area. Lafarge operates several facilities including sites at Boyce Road in South Ottawa, Merivale Road, and Gloucester. On any summer day, trucks arrive to a designated site, are loaded with paving materials and then proceed to the designated work site. The following Excel table describes the truck transportation problem and the unit costs of delivery for the trucks. Lafarge wants to determine the minimum total cost of how many trucks to assign to each facility to move the paving materials to each of the 3 work sites at (i) Nepean, (ii) Barrhaven, and (iii) (a) Define the decision variables of this problem. (3 points) (b) Formulate this transportation problem on a spreadsheet and use Excel's Solver to help Lafarge determine the optimal number of trucks to assign to each facility to move the paving materials to each of the 3 work sites in order to minimize the total delivery costs (Provide the corresponding "Excel Spreadsheet" and the "Sensitivity Report"). Include "managerial statements" that communicate the results of the analyses, (ie. describe verbally the results). (17 points) N.B.: To build the Excel Spreadsheet formulation quickly, you can copy/paste the two tables above into your Excel Worksheet. (c) Does this problem have an alternate optimal solution? Justify your answer. ( 2 points) (d) If trucks from Gloucester facility can NOT be assigned to the work site in Vanier, briefly describe how you will adjust the formulation in part b)? (3 points) QUESTION 3 ( 25 points): Excel Spreadsheet Formulation and Solutions LAFARGE Lafarge Paving and Construction (Eastern) Limited is the largest supplier of asphalt, TNORTH AMERICA aggregates and concrete in the Ottawa area. Lafarge operates several facilities including sites at Boyce Road in South Ottawa, Merivale Road, and Gloucester. On any summer day, trucks arrive to a designated site, are loaded with paving materials and then proceed to the designated work site. The following Excel table describes the truck transportation problem and the unit costs of delivery for the trucks. Lafarge wants to determine the minimum total cost of how many trucks to assign to each facility to move the paving materials to each of the 3 work sites at (i) Nepean, (ii) Barrhaven, and (iii) (a) Define the decision variables of this problem. (3 points) (b) Formulate this transportation problem on a spreadsheet and use Excel's Solver to help Lafarge determine the optimal number of trucks to assign to each facility to move the paving materials to each of the 3 work sites in order to minimize the total delivery costs (Provide the corresponding "Excel Spreadsheet" and the "Sensitivity Report"). Include "managerial statements" that communicate the results of the analyses, (ie. describe verbally the results). (17 points) N.B.: To build the Excel Spreadsheet formulation quickly, you can copy/paste the two tables above into your Excel Worksheet. (c) Does this problem have an alternate optimal solution? Justify your answer. ( 2 points) (d) If trucks from Gloucester facility can NOT be assigned to the work site in Vanier, briefly describe how you will adjust the formulation in part b)? (3 points)
