**************Please solve using Excel solver and show Formulas***************

(Q3: 20 pts) The ABC Company is one of the largest producers of power tools in the United States. The company is preparing to replace its current product line with the next generation of products: specifically, three exciting new power tools with the latest state- of-the-art features. Because of the limited amount of capital available, management needs to make some difficult choices about how much to invest in each of these products. Another concern is the effect of these decisions on the company's ability to maintain a relatively stable employment level. In addressing these decisions, management wants primary consideration given to three factors: total profit, stability in the workforce, and the level of capital investment needed to launch these products. Goal 1: Achieve a total profit (NPV) of at least $500 million. Goal 2: Maintain the current employment level of 8,000 employees. Goal 3: Hold the capital investment down to no more than $220 million. All goals are important, but their order of importance is: Priority 1: Goal 1 Priority 2: Goal 3 Priority 3: Part of Goal 2 (avoid decreasing the employment level) Priority 4: Part of Goal 2 (avoid increasing the employment level) The company estimated contributions per unit of each product along with all the necessary information as follows: Factor Total profit ($mil) Employee level (00s) Capital investment ($mil) Prod 1 12 5 5 Prod 2 Prod 3 9 15 3 4 7 8 Goal > 500 = 80 3220 Goal Factor Penalty Weight for Missing Goal Goal 1 Total profit 10 (per $1 mil under the goal) Goal 2 Employment level 7 (per 100 employees under the goal 5 (per 100 employees over the goal) Goal 3 Capital investment 8 (per $1 mil over the goal a) Formulate the above problem into Goal Programming (GP). b) Find the optimal solution. Submit Excel file (Solver solution) (Q3: 20 pts) The ABC Company is one of the largest producers of power tools in the United States. The company is preparing to replace its current product line with the next generation of products: specifically, three exciting new power tools with the latest state- of-the-art features. Because of the limited amount of capital available, management needs to make some difficult choices about how much to invest in each of these products. Another concern is the effect of these decisions on the company's ability to maintain a relatively stable employment level. In addressing these decisions, management wants primary consideration given to three factors: total profit, stability in the workforce, and the level of capital investment needed to launch these products. Goal 1: Achieve a total profit (NPV) of at least $500 million. Goal 2: Maintain the current employment level of 8,000 employees. Goal 3: Hold the capital investment down to no more than $220 million. All goals are important, but their order of importance is: Priority 1: Goal 1 Priority 2: Goal 3 Priority 3: Part of Goal 2 (avoid decreasing the employment level) Priority 4: Part of Goal 2 (avoid increasing the employment level) The company estimated contributions per unit of each product along with all the necessary information as follows: Factor Total profit ($mil) Employee level (00s) Capital investment ($mil) Prod 1 12 5 5 Prod 2 Prod 3 9 15 3 4 7 8 Goal > 500 = 80 3220 Goal Factor Penalty Weight for Missing Goal Goal 1 Total profit 10 (per $1 mil under the goal) Goal 2 Employment level 7 (per 100 employees under the goal 5 (per 100 employees over the goal) Goal 3 Capital investment 8 (per $1 mil over the goal a) Formulate the above problem into Goal Programming (GP). b) Find the optimal solution. Submit Excel file (Solver solution)