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 may be either manufactured by the company or purchased from an outside supplier. Manufacturing and purchase costs per unit are shown. Component Frame Support Strap Manufacturing Cost $39.00 $12.50 $7.50 Purchase Cost $52.00 $16.00 $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 departments are as follows: Department Milling 2.3 Cutting 3.6 1.4 Component Frame Support Strap Capacity (hours) Shaping 3.2 1.8 0.9 2.7 1.8 690 370 430 a. Formulate and solve a linear programming model for this make-or-buy application. How many of each component should be manufactured and how many should be purchased? Let FM = number of frames manufactured FP = number of frames purchased SM = number of supports manufactured SP = number of supports purchased TM = number of straps manufactured

Answer 1

Based on the decision variables defined in the question,

Total Cost = 39*FM + 12.5*SM + 7.5*TM + 52*FP + 16*SP + 8.5*TP

We have to minimize this cost

Subject to constraints

3.6*FM + 1.4*SM + 0.9*TM <= 370 hours * 60 minutes i.e. 22200..........Constraint for availability of minutes of Cutting time

2.3*FM + 1.8*SM + 0*TM <= 430 * 60 = 25,800 .....................................Constraint for availability of minutes of Milling time

3.2*FM + 2.7*SM + 1.8*TM <= 690*60 = 41,400......................................Constraint for availability of minutes of Shaping time

Total Demand of final product = 6000 units

FM + FP >= 6000..........Constraint for demand of Frames

SM + SP >= 12000........Constraint for demand of Support (2 supports per 1 Liftmaster)

TM + TP >= 6000..........Constraint for demand of Strap

Hence, we get formulation as

Minimize Total Cost C = 39*FM + 12.5*SM + 7.5*TM + 52*FP + 16*SP + 8.5*TP

Subject to Constraints:

3.6*FM + 1.4*SM + 0.9*TM <= 22200

2.3*FM + 1.8*SM + 0*TM <= 25800

3.2*FM + 2.7*SM + 1.8*TM <= 41400

FM + FP >= 6000

SM + SP >= 12000

TM + TP >= 6000

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

We solve in Excel using Excel solver as shown below:

Variables FM SM TM FP 6000 429 0 0 SP 11571 6000 ТР Objective Min C $ 4,75,500 Constraints 1 2 3 22200 14571 20357 6000 12000 6000 Condition <= 22200 <= 25800 <= 41400 6000 12000 6000 4 5 6

The above solution in the form of formulas along with Excel Solver extract is shown below for better understanding and reference:

D E F H Solver Parameters Set Objective: $B$10 To: O Max Min O Value of: 0 By Changing Variable Cells: $B$2:$B$7 Subject to the Constraints: $B$13:$B$15 <= $D$13:$D$15 $B$16:$B$18 >= $D$16:$D$18 Add Condition 22200 Change A B 1 Variables 2 FM 6000 3 SM 428.571428571429 4 TM 0 5 FP 0 6 SP 11571.4285714286 7 TP 6000 8 9 Objective 10 Min C =39*B2+12.5*B3+7.5*B4+52*B5+16*B6+8.5*B7 11 12 Constraints 13 1 =3.6*B2+1.4*B3+0.9*B4 14 2 =2.3*B2+1.8*B3+0*B4 15 3 =3.2*B2+2.7*B3+1.8*B4 16 4 =B2+B5 17 5 =B3+B6 18 6 =B4+B7 19 20 21 22 23 24 25 26 27 28 29 Delete <= 25800 41400 6000 12000 6000 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

As seen from the above solution, we get

Frames Supports Straps Manufacture Purchase 6000 0 429 11571 0 6000

b. The total cost of manufacturing and purchasing plan = $475,500

c. Hours used:

Hours of Department Production Time used Cutting 370 Milling 242.86 Shaping 339.29

For part d and e, we generate a sensitivity report as shown below:

Variable Cells Cell Name $B$2 FM $B$3 SM $B$4 TM $B$5 FP $B$6 SP $B$7 TP Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 6000 0 39 4 48 428.5714286 0 12.5 1.944444444 1.555555556 0 1.25 7.5 1E+30 1.25 0 4 1E+30 4 11571.42857 0 16 1.555555556 1.944444444 6000 0 8.5 1.25 8.5 52 Constraints Cell Name $B$13 $B$14 $B$15 $B$16 $B$17 $B$18 Final Value 22200 14571.42857 20357.14286 6000 12000 6000 Shadow Constraint Allowable Allowable Price R.H. Side Increase Decrease -2.5 22200 8733.333333 600 0 25800 1E+30 11228.57143 0 41400 1E+30 21042.85714 48 6000 166.6666667 4500 16 12000 1E+30 11571.42857 8.5 6000 1E+30 6000

d. As seen from the above sensitivity report,

The shadow price for Shaping Department (Cell B15 in Constraints table) is 0. Hence, any increase in the number of hours in the shaping department will lead to a 0 decrease in total cost. Hence, 0 hours should be Frandec should be willing to pay $0.

e. As seen from the above sensitivity report,

The Reduced cost for Frames to be purchased (Cell B5 in Variables table) is 4. In order to make the optimal solution such that Frames are purchased, the price of frames should be reduced by at least $4 i.e. 52-4 = $48. Since the supplier is supplier at $46 which is less than minimum reduced price, Frandec can improve by pursuing this opportunity

Answer: Yes

Because the current purchase price is $52. The reduced cost is $4 which means that the solution may be improved if the cost is $48 or below.