Chendhanal Aruchamy GBS 220 (Fall 2017) Chapter 3 (Linear Programming: Sensitivity Analysis and Interpretation of Solution) Lesson 3 Essay Essay Questions 1. Formulate a linear programming model you can use to determine the percentage of wood tables and the percentage of steel tables that should be assigned to each of the three subcontractors to minimize the total cost of completing both projects. 2. Solve the model from Part 1. What percentage of the wood tables and what percentage of the steel tables should be assigned to each subcontractor? 3. Based on your model, what is the total cost of completing both projects? 4. Suppose Subcontractor 2 reduced its costs to $38 per hour. What effect would this change have on the optimal solution? Explain. Formulation of Linear Programming Model Objective: To minimize total cost of completing both projects Total Cost = total cost of production of wood and steel tables by three different subcontractors C1 = Total hours allotted to Subcontractor 1 and cost per hour: $36 C2 = Total hours allotted to Subcontractor 2 and cost per hour $42 C3 = Total hours allotted to Subcontractor 3 and cost per hour $55 Allocation of hours for wood tables and steel tables for each contractor C1 = xw + xs (xw = hours for wood tables and xs = hours for steel tables by contractor 1) C2 = yw + ys (yw = hours for wood tables and ys = hours for steel tables by contractor 2) C1 = zw + zs (zw = hours for wood tables and zs = hours for steel tables by contractor 3) Objective function: Min 36C1 + 42C2 + 55C3 Percentage of tables (wood and steel) completed by each contractor: Wood tables: (100/50) xw + (100/42) yw + (100/30) zw =100 Chendhanal Aruchamy GBS 220 (Fall 2017) or (xw /50) + (yw /42)+ (zw /30)= 1 Steel tables: (100/60) xs + (100/48) ys + (100/35) zs =100 or (xs /60)+ (ys /48)+ (zs /35) = 1 Constraints 1. Hours allocated for wood tables and steel tables for each contractor C1 = xw + xs < 40 (xw = hours for wood tables and xs = hours for steel tables by contractor 1) C2 = yw + ys < 30 (yw = hours for wood tables and ys = hours for steel tables by contractor 2) C1 = zw + zs < 35 (zw = hours for wood tables and zs = hours for steel tables by contractor 3) 2. Negativity constraints: C1 > 0 C2 > 0 C3 > 0 Tasks to be completed: Computer solutions for this problem will be obtained by using Excel Solver for the original cost per hour. Then, sensitivity analysis will be performed by changing the cost per hour of second subcontractor from $42/hour to $38/hour. Hours required to complete wood tables Hours required to complete steel tables Hours available Cost per hour Subcontractor 1 Subcontractor 2 Subcontractor 3 50 42 30 60 48 35 40 $36 30 $42 35 $55 For example, Subcontractor 1 estimates it will take 50 hours to complete all wood tables and 60 hours to complete all steel tables. However, Subcontractor 1 only has 40 hours available. Therefore, Subcontractor 1 can only complete 40/50= .80 or 80% of the wood tables if the company worked only on the wood tables or 40/60= .67 or 67% if it worked only on the steel tables