Problem $1(30$ points$)$

A manufacturing firm produces two products. Each product must undergo an assembly process and a finishing process. It is then transferred to the warehouse, which has space for only a limited number of items. The firm has $80$ hours available for assembly and $120$ hours for finishing, and it can store a maximum of $10$ units in the warehouse. Each unit of Product $1$ has a profit of $$50$ and requires $4$ hours to assemble and $12$ hours to finish. Each unit of Product $2$ has a profit of $$70$ and requires $10$ hours to assemble and $8$ hours to finish. The firm wants to determine the quantity of each product to produce in order to maximize profit.

$($Please note that we have formulated this problem as a linear program in Homework $5.)$

$1.(10$ Points$)$ Now please solve the problem using Excel$$s solver. Set up a spreadsheet to represent your model, and rename it as $$Solution$.$ As part of solver$$s solution, please include both Answer Report and Sensitivity Report. You can keep the names of these two reports that Excel gives them.

$$

$$

$2.(20$ Points$)$ Without resolving the LP model, answer the following questions using the information in Answer Report and Sensitivity Report. Please include all the answers in an orderly fashion in the worksheet Solution.

$$

a$.$ Which constraints are binding at the optimal solution?

$$

b$.$ What will be the optimal solution if the profit on one unit of Product $2$ decreases from $$70$ to $$60?$ What will be the maximum profit? Please explain.

$$

c$.$ If overtime can be scheduled in one of the processes $($i$.$e$.$ the assembly process or the finishing process$),$ in which process would you recommend doing so$?$ Please explain.

$$

d$.$ How much will the maximum profit improve if $10$ extra hours of assembly time are made available? Please explain.