Problem 2. (15 points) The distribution system of a pipe company consists of three plants, two warehouses, and four customers. Plant capacities and shipping costs per foot (in dollars) from each plant to each warehouse are as follows: Plant 1 2 Production capacity (feet) 450 600 380 Production cost ($/foot) 35 30 32 3 The shipping costs per foot (in dollars) from each plant to each warehouse are as follows. Plant Warehouse 1 ($/foot) 4 1 Warehouse 2 ($/foot) 7 5 2 8 5 3 6 Customer demand and shipping costs per foot (in dollars) from each warehouse to each customer are given below. Management at the company requires all demands be met. Customer 4 Warehouse 1 ($/foot) 2 ($/foot) Demand (feet) 1 6 3 300 2 4 6 300 3 8 7 300 4 5 400 Use Distribution Data.xlsx to build your Excel model. 1. (12 points) Formulate and solve a mathematical model to determine the optimal plan that minimizes the total cost (production and distribution) of this company. 2. (3 points) Because of the availability of the space and handling crew, warehouse 1 cannot handle more than 600 feet of pipe. How will you modify your model in part (2) to incorporate this constraint? What is the new optimal production and transportation plan