QUESTION

Linear Programing Pro jeet 20 Points Problem: A certain corporation has three branch plants with excess production capacity. Fortunately, the...

Good morning,

I have a linear programming assignment that I would need assistance with. It is due on Wednesday, 26th September at 11:59 p.m.

It is required that the assignment be done in Excel format and that calculations and formulas be shown.

Attached are the questions.

Thanks and best regards.

Bob

Linear Programing Pro jeet20 PointsProblem: 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 newproduct. This product can be made in three styles The amount of labor required to produce one unit of each style varies per plant and isgiven in the following table: Plantl P1ant2 Plant3Classic 10 8 7Modern 1 1 8 6Simple 9 7 5 Each style requires a different quantity of subassembly A for production with Classicrequiring 6 units, Modern requiring 5 units, and Simple requiring 3 units. Plant 1 has 600units of subassernbly A available, Plant 2 has 700 units available, and Plant 3 has 500units 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 excessproduction 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 excesscapacity to produce the new product. Part A (Spreadsheet: Part A)Requirements: 1) Give a typed formulation with decision variables clearly defined and allconstraints clearly deﬁned. (3 pts) 2) Solve the problem. (3 pts) 3) If you had $2000 to spend and it cost$100 to increase labor capacity in anyplant 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? Explainyour answer. (3 pts) Part B (Spreadsheet: Part B)