ACME produces an industrial product, called EZ-Lift, for its major customer, MegaMax. Acme does not make this the EZ-Lift for any other customers. Their contract is to produce 1000 units per month. EZ-Lift is made from three components: a frame, a support and a mounting bracket. ACME can either manufacture the components themselves or they can buy them from an outside supplier. Whether they manufacture the components or purchase them from an outside supplier, ACME will still assemble them into the final product. The costs of each option are given below:

If they manufacture the components, each unit goes through three processing departments. The table below show how much labor time (in hours) is required for each unit in each department as well as showing how much labor time(hours) is available per month in each department.

Part A (20 points)

Create an LP model to help Acme determine how many of each component to make and how many of each component to buy.

A1.How many of each component should ACME make?

A2. How many of each component should ACME purchase?

A3. What is the cost of this plan?

A4. If they could increase labor capacity in one of the departments, where should they do that?

Part B (20 points)

Now, assume that MegaMax wants to change the contracted amount to 2000 units per month Create an LP model to help Acme determine how many of each component to make and how many of each component to buy.

B1. How many of each component should ACME make?

B2. How many of each component should ACME purchase?

B3. What is the cost of this plan?

B4. If they could increase labor capacity in one of the departments, where should they do that?

B5. If they can add 100 hours of labor capacity to that department, what is the cost of this new plan?

Part C (10 points)

Now assume that the workers are cross-trained so that each of them can work in any of the departments. So they have a total of 14500 hours available each month and those worker hours can be assigned in any combination to the departments. Create a new LP to address the same issues as in Part A, but to also determine how many hours of work to assign to each department. (Hint: create a new set of decision variables corresponding to those assignments.)

Create an LP model to help Acme determine how many of each component to make and how many of each component to buy

C1. How many of each component should ACME make?

C2. How many of each component should ACME purchase?

C3. What is the cost of this plan?

C4. If they could increase labor capacity in one of the departments, where should they do that?

C5. If they can add 100 hours of labor capacity to that department, what is the cost of this new plan?

Your answer submission should take the following form

An Excel file named “Your Last Name – Q47 F20 Excel 1”

For each part, there should be 3 worksheets. I’ll use Part A as an example of how I want the sheets named:

“Part A – Main” – this will be your main submission for Part A – On this sheet, you should:

· Display your LP model. Create the model as a Word document, then copy and paste it into a text box on this sheet

· Create your input table from the model – this is the table that you will reference in the Excel solver

· Answer the questions listed for Part A

“Part A – Answer Report” – the answer report as generated by the Excel solver

“Part A – Sensitivity Report” – the sensitivity report as generated by the Excel solver

(So, your submission will have 9 worksheets in total. )