[PLEASE SHOW SCREENSHOTS IN EXCEL OF EXCEL SOLVER AND ANY TABLES]

[PLEASE SHOW SCREENSHOTS IN EXCEL OF EXCEL SOLVER AND ANY TABLES]

Sitka Manufacturing is planning to build at least one new plant, and three cities are being considered: Baytown, Texas; Lake Charles, Louisiana; and Mobile, Alabama. Once the plant or plants have been constructed, the company wishes to have sufficient capacity across all the operational plants to produce at least 38,000 units each year. The following table summarizes the production capacities that would come up at each of the possible sites if a plant is built there, and the costs that would be incurred in operating those plants (if built). The management wants to come up with a plan that would minimize the total production costs (fixed and variable costs). This planning problem can be formulated as the following integer program (a variation of LP where decision variables are integers). For an explanation of how this formulation relates to the above planning problem, see Example B5 - "A Fixed-Charge Problem Using Binary Variables" - in the textbook (in Section B.9, Module B). Decision variables: Minimize340,000Y1+270,000Y2+290,000Y3+32X1+33X2+30X3subjectto:X1+X2+X338,000X121,000Y1X220,000Y2X319,000Y3Y1,Y2,Y3=0or1X1,X2,X30andinteger (a) Set up the above problem in Excel, and solve it using Excel solver. You can verify your solution against that given in the textbook. Submit this excel spreadsheet