Please complete the problem $($questions$)$ in Excel.

Problem:

A certain corporation has three branch plants with excess production capacity. Fortunately, the corporation has a new product ready to begin production, and all three plants have this capability, so some of the excess capacity can be used to produce the new product. This product can be made in three styles $$ Classic, Modern, and Simple $$ that yield a net unit profit of $$760,$ $$700,$ and $$650$ respectively. Plants $1,2,$ and $3$ have excess capacity of $1000,900,$ and $1050$ labor hours per day respectively.

The amount of labor required to produce one unit of each style varies per plant and is given in the following table:

Plant$1$ Plant$2$ Plant$3$

Classic $1087$

Modern $1186$

Simple $975$

Each style requires a different quantity of subassembly A for production with Classic requiring $6$ units, Modern requiring $5$ units, and Simple requiring $3$ units. Plant $1$ has $600$ units of subassembly A available, Plant $2$ has $700$ units available, and Plant $3$ has $500$ units available.

Sales forecasts indicate that if available, $150,135,$ and $155$ units of the Classic, Modern, and Simple styles, respectively, would be sold per day.

At each plant, some employees will need to be laid off unless most of the plant$$s excess production capacity can be used to produce the new product. To avoid layoffs if possible, management has decided that the plants should use the same percentage of their excess capacity to produce the new product.

Part A $($Spreadsheet: Part A$)$

Requirements:

$1)$ Give a typed formulation with decision variables clearly defined and all constraints clearly defined. $(3$ pts$)$

$2)$ Solve the problem. $(3$ pts$)$

$3)$ Use Sensitivity Analysis. If you had $$2000$ to spend and it cost $$100$ to increase labor capacity in any plant by one hour, $$200$ to add one unit of subassembly A in any plant, and $$250$ to increase demand of any style by one unit; how would you spend the $$2000$ to maximize your return and what would be your net return? Explain your answer. $(3$ pts$)$

Part B $($Spreadsheet: Part B$)$

In Part B$,$ resolve the problem with the following requirements. Production of the Classic cannot be greater than five times the production of the Modern. Production of the Simple cannot be greater than five times the production of the Modern.

Requirements:

$4)$ Give a typed formulation with decision variables clearly defined and all constraints clearly defined. $(3$ pts$)$

$5)$ Solve the problem. $(3$ pts$)$

Part C $($Spreadsheet: Part C$)$

Resolve the problem requiring an integer solution.

$6)$ Solve the problem. $(3$ points$)$

$7)$ Comment on what you have learned in Part C$.(2$ pts$)$