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?

Answer 1

a) b)

Solver screenshot

Formula

c)

Let,

x1 = Number of product A to be made
x2 = Number of product B to be made
x3 = Number of product C to be made
x4 = Number of product D to be made

Objective is to maximize profit so objective function = Max 450x1+550x2+600x3+750x4

Subject to,

0.5x1+x2+1.5x3+1.5x4 <= 1500 (Preparation)
3x1+3x2+x3+2x4 <= 2350 (Fabrication)
x1+x2+2x3+0.5x4 <= 1400 (Packaging)
0.5x1+0.5x2+x3+0.5x4<= 1200 (Testing)

x1>= 100 (Minimum - Prod A)
x2 >= 300 (Minimum - Prod B)
x4>=250 (Minimum - Prod C)

x1,x2,x3 >= 0 (Non-negativity constraint)

d)

x1 = Number of product A to be made = 100
x2 = Number of product B to be made = 300
x3 = Number of product C to be made = 383.33
x4 = Number of product D to be made = 383.33

Total profit = 727500

Sensitivity report

e.How many hours are used in each department? Is there any department with slack hours? How many hours, if any?

Preparation dept took 1500 hours
Fabrication took 2350 hours
Packaging took 1358.33 hours
Testing took 775 hours

f.What is the shadow price of the Fabrication Department? What does it mean?

Shadow price of fabrication is $150 which means with 1 additional hours in fabrication profit will increase profit by $150

g.If 100 more hours are available in the Packaging Department, how much would the total profit increase, if any? Would the optimal solution change?
No profit would not change as packaging department has slack or unused resources

h.How much can the unit profit of product C decrease before the optimal production plan changes? Explain why.

Product C's unit profit can decrease by $225 to (600-225) = 376 before the optimal production plan changes as allowable decrease limit for Product C is 225 up to which present solution will keep intact

i.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?

No optimal solution will not change as increasing by $100 is within allowable increase limit for A and B. Only profit will change and will increase by 100*100+300*100 = 40000

New profit = 727500+40000 = 767500