Question

1. A manufacturing company would like to maximize the profit of manufacturing four products.  The time in hours required for each product in each production department is tabled below, along with the profit for each product.

	Product 1	Product 2	Product 3	Product 4
Department A	25	20	30	15
Department B	12	10	10	15
Department C	8	7	5	6
Profit	$110	$90	100	95

Currently, labor assignments provide for 4800 hours in Department A, 3000 hours in Department B and 2800 hours in Department C.

Formulate a linear programming model for the above situation by determining

(a) The decision variables.

(b) Determine the objective function. What does it represent?

(c) Determine all the constraints. Briefly describe what each constraint represents.

Hint: There are 4 variables and 3 constraints (in addition to the non-negativity constraints) for this problem

2. Find the computer solution, including the ranging (sensitivity analysis) results, for Question 1 by using QM for Windows or Excel. Determine the optimal solution (including the decision variables and the slack/surplus variables) and optimal profit. Interpret the optimal solution and optimal profit.

Need help with #2 Please answer question #2 in excel

Answer 1

a)

Decision variables

A = No of units of Product 1 to be produced

B = No of units of Product 2 to be produced

C = No of units of Product 3 to be produced

D = No of units of Product 3 to be produced

b)

Objective function

Max Z = 110A +90B+100C+95D

The Objective function is to maximize the profits for these four products.

c)

Constraints

25A +20B+30C+15D <= 4800 (Labor hours available for department A)

12A + 10B + 10C + 15D <= 3000 ( Labor hours available for department B)

8A+7B+5C+6D <=2800 (Labor hours available for department C)

A,B,C,D >= 0 (Non negativity constraints)

Each constraints represent that they must be completed or those products needs to be manufactured within or at given hours.

2)

Below is the screenshot of working data table -

Below is the screenshot of formula applied -

Below is the screenshot of solver -

Below is the screenshot of result -

Below is the screenshot of solver report -