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:

                    Plant1              Plant2              Plant3

Classic             10                    8                      7

Modern            11                    8                      6

Simple              9                      7                      5

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

Requirements:

1) Give a typed formulation with decision variables clearly defined and all constraints clearly defined.

2) Solve the problem.

3) 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.

Part B (Spreadsheet: Part B)

In Part B, Keep everything from Part A and 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.
5) Solve the problem.

Part C (Spreadsheet: Part C)

Take everything in Part A and B and answer below.

Resolve the problem requiring an integer solution.

6) Solve the problem.

7) Comment on what you have learned in Part C.

Needs to be solved in MS Excel using Linear Programming. Need steps of how you arrive at each answer.