Question

A local electronic company primarily manufactures four highly technical telecommunication products. Each product must be processed in the following departments: component preparation, circuit board fabrication, packaging, and testing. The time requirements in each department (in hours) for each unit produced and its unit profit are summarized in the following table:

Department Product	Preparation	Fabrication	Packaging	Testing	Unit Profit
A	.5	3	1	.5	$450
D	1.0	3	1	.5	$550
B	1.5	1	2	1.0	$600
C	1.5	2	.5	.5	$750

Time available per month in each department is 1,500 hours in the Component Preparation department, 2,350 hours in Fabrication, 1,400 hours in Packaging, and 1,200 hours in Testing, respectively. The minimum monthly production requirement for each product to meet the orders received for the coming month is 100 units for A, 300 units for B, and 250 units for D, respectively.

To maximize the profit for the coming month, find the optimal production plan for these four products for the coming month by answering the following questions.

1. Formulate the problem as a spreadsheet model. Color the cells properly. Use range names.
2. Solve the problem using the Solver.
3. Formulate the problem as an algebraic model. Don’t forget to define the decision variables, and express the objective function and all the constraints in terms of the decision variables.
4. How many units are produced for each product in the optimal production plan? What is the total profit?
5. How many hours are used in each department? Is there any department with slack hours? How many hours, if any?
6. What is the shadow price of the Fabrication Department? What does it mean?
7. If 100 more hours are available in the Packaging Department, how much would the total profit increase, if any? Would the optimal solution change?
8. How much can the unit profit of product C decrease before the optimal production plan changes? Explain why.
9. Suppose the unit profit of product A and B each increases by $100, would the optimal production plan change? Why? Explain and show your computation. What is the new total profit after the change?

question

Answer 1

a.

These are the results , that are found after defining the objective function & constraints in the solver.

b. These are the constraints used in the solver.

c. Let the no of units produced for each product A, D, B, C be a, d, b & c respectively. These are the decision variables.

The profits for each A,D, B & C is $450, $550,$600,$750 respectively.

Now, the objective is to maximize 450a + 550d + 600b +750c

subject to :

a 100; d 250 ; b 300;

0.5*a + 1*d + 1.5*b +1.5*c 1500;

3*a + 3*d + 1*b + 2*c 2350;

1*a + 1*d + 2*b +0.5*c 1400;

0.5*a + 0.5*d + 1 *b + 0.5*c 1200;

a, d, b & c > 0 ;  a, d, b & c = integer .

d.

Product	Allocated units
A	100
D	250
B	300
C	500

The total profit from this allocation is $737,500.

Hope I clarified your query. Do like and comment, if u like my answer.