A soft drink manufacturer, Sara Soda, Ltd., has recently begun negotiations with brokers in the areas where it intends to distribute its products. Before finalizing the agreements, however, Manager Dave Pepper wants to determine shipping routes and costs. The firm has three plants with capacities as shown below: Capacity Plant (cases per week) Metro (M) 40,000 Ridge (R) 30,000 Colby (C) 25,000 I Estimated demands in each of the warehouse localities are: Demand Warehouse (cases per week) RS1 24,000 RS2 22,000 RS3 23,000 RS4 16,000 RSS 10,000 The estimated per unit shipping costs per case for the various routes are: To: RS1 RS2 RS3 RS4 From: Metro .80 .75 .60 .70 Ridge .75 .80 .85 .70 Colby .75 .70 RS5 .70 .90 .85 .80 .80 a. Formulate this transportation problem algebraically and use Excel's Solver to determine the optimal shipping plan that will minimize total shipping cost (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). (20 points) b. Adjust the formulation when Route Ridge-RS4 is unacceptable. What is the new optimal shipping plan under this condition? (3 points) c. What is the additional cost of the Ridge-RS4 route not being acceptable? (2 points)