Hansen Controls has been awarded a contract for a large number of control panels. To meet this demand, it will use its existing plants in San Diego and Houston, and consider new plants in Tulsa, St$.$ Louis, and Portland. Finished control panels are to be shipped to Seattle, Denver, and Kansas City.

 

Shipping Cost to Destination Sources Construction Cost Seattle San Diego Houston Denver Kansas City Capacity 5 7 8 2,500 10 8 6 2,500 Tulsa 350,000 9 4 3 10,000 St. Louis 200,000 12 6 2 10,000 Portland 480,000 4 10 11 10,000 Demand 3,000 8,000 9,000 a) Develop an optimization model whose solution would reveal which plants to build and the optimal shipping schedule. In other words, clearly define the decision variables, write the objective function and the constraints in terms of these variables. b) Develop the spreadsheet model. Solve the model using Excel Solver and provide your recommendation.