Question

Frandec Company manufactures, assembles, and rebuilds material handling equipment used in warehouses and distribution centers. One product, called a Liftmaster, is assembled from four components: a frame, a motor, two supports, and a metal strap. Frandec's production schedule calls for 6000 Liftmasters to be made next month. Frandec purchases the motors from an outside supplier, but the frames, supports, and straps purchased from an outside supplier. Manufacturing and purchase costs per unit are shown. may be either manufactured by the company Component Manufacturing Cost Purchase Cost Frame $33.00 $48.00 Support $10.50 $14.00 $7.50 Strap $8.50 Three departments are involved in the production of these components. The time (in minutes per unit) required to process each component in each department and the available capacity (in hours) for the three dona departments are as follows: Department Component Cutting Milling Shaping 2.6 Frame 1.2 Support 2.1 1.6 Strap Capacity (hours) 360 120 570 Formulate and solve a linear programming model for this make- r-buy application. How many each component should be manufactured and how many should be purchased? Let number f frames manufactured EM FP number of frames purchased f supports manufactured SM number P number supports purchased straps manufactured number TM P - number straps purchased

Answer 1

Min 33 FM + 48 FP + 10.5 SM + 14 SP + 7.5 TM + 8.5 TP

s.t.

3.0 FM + 1.3 SM + 0.9 TM <= 21600

2.0 FM + 1.6 SM + 0.0 TM <= 25200

2.6 FM + 2.1 SM + 1.6 TM <= 40200

1 FM + 1 FP >= 6000

1 SM + 1 SP >= 12000

1 TM + 1 TP >= 6000

FM, FP, SM, SP, TM, TP >= 0

Excel formulation

B A C D E G H K 1 EM 2 EP SM SP TM TP No. of 3 -SUMPRODUCT($C$3:$H$3,C4: H4) 4 Cost 33 48 10.5 14 7,5 8.5 5 S.t. SUMPRODUCT($C$3:$H$3,C6:H6) -SUMPRODUCT($C$3:$H$3,C7: H7) SUMPRODUCT($C$3:$H$3,C8:H8) SUMPRODUCT($C$3:$H$3,C9:H9) =SUMPRODUCT($C$3:$H$3,C10: H10) -SUMPRODUCT($C$3:$H$3,C11:H11) <= =360*60 6 Cutting 3 1.3 0.9 Milling <= =420* 60 7 2 1.6 <= =670*60 Shaping 2.6 2.1 1.6 Demand F 1 1 > 6000 Demand S 10 1 1 >= 12000 Demand T 11 1 1 >= 6000 LLI

SI$4 Set Objective: To: O Value Of: Min Max By Changing Variable Cells: $C$3:$H$3 Subject to the Constraints: $I$6:$I$8 $I$9:$I$11>= SK$9 : SK$ 11 SK$6:$K$8 <= Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear, Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Answer report

Objective Cell (Min)
	Cell	Name	Original Value	Final Value
	$I$4	Cost	0.00	407307.69
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$C$3	No. of FM	0	6000	Contin
	$D$3	No. of FP	0	0	Contin
	$E$3	No. of SM	0	2769	Contin
	$F$3	No. of SP	0	9231	Contin
	$G$3	No. of TM	0	0	Contin
	$H$3	No. of TP	0	6000	Contin
Constraints
	Cell	Name	Cell Value	Formula	Status	Slack
	$I$6	Cutting	21600.00	$I$6<=$K$6	Binding	0
	$I$7	Milling	16430.77	$I$7<=$K$7	Not Binding	8769.230769
	$I$8	Shaping	21415.38	$I$8<=$K$8	Not Binding	18784.61538
	$I$9	Demand F	6000.00	$I$9>=$K$9	Binding	0.00
	$I$10	Demand S	12000.00	$I$10>=$K$10	Binding	0.00
	$I$11	Demand T	6000.00	$I$11>=$K$11	Binding	0.00

Sensitivity report

Variable Cells
			Final	Reduced	Objective	Allowable	Allowable
	Cell	Name	Value	Cost	Coefficient	Increase	Decrease
	$C$3	No. of FM	6000	0	33	6.923076923	41.07692308
	$D$3	No. of FP	0	6.923076923	48	1E+30	6.923076923
	$E$3	No. of SM	2769.230769	0	10.5	2.055555556	3
	$F$3	No. of SP	9230.769231	0	14	3	2.055555556
	$G$3	No. of TM	0	1.423076923	7.5	1E+30	1.423076923
	$H$3	No. of TP	6000	0	8.5	1.423076923	8.5
Constraints
			Final	Shadow	Constraint	Allowable	Allowable
	Cell	Name	Value	Price	R.H. Side	Increase	Decrease
	$I$6	Cutting	21600	-2.692307692	21600	7125	3600
	$I$7	Milling	16430.76923	0	25200	1E+30	8769.230769
	$I$8	Shaping	21415.38462	0	40200	1E+30	18784.61538
	$I$9	Demand F	6000	41.07692308	6000	1200	4000
	$I$10	Demand S	12000	14	12000	1E+30	9230.769231
	$I$11	Demand T	6000	8.5	6000	1E+30	6000

--------------------------------------------------------------------------------------------------

	Maufacture	Purchase
Frame	6000	0
Support	2769	9231
Strap	0	6000

What is the total cost of the manufacturing and purchasing plan? $407,308

How many hours of production time are used in each department?

Deptt.	Hours of production time used
Cutting	21600.00
Milling	16430.77
Shaping	21415.38

How much should Frandec be willing to pay for an additional hour of time in the shaping department? $0 (because there is a slack of hours)

Another manufacturer has offered to sell frames to Frandec for $42 each. Could Frandec improve its position by pursuing this opportunity? Why or why not?

No, because the reduced cost is 6.92 which means that the solution may be improved if the cost is 48 - 6.92 = 41.08 or below.