Purpose To assess your ability to: solve basic linear programming problems by using Excel Solver solve basic linear programming problems by using a graphical solution perform sensitivity analysis on linear programming solutions Overview You will use the following information to complete this assignment. The Ohio Metal Works (OMW) produces three main products from steel bars-- P1, P2 and P3. The features of these products are given below. P1 P2 P3 Profit (excluding labor Minimum Weekly Production cost) perfoot of product Requirement (feet) $0.017 218,000 $0.019 114,000 $0.020 111,000 One of the initial steps is a shaping operation performed by rolling machines. There are three machines available for this purpose-- RM1, RM2 and RM3. Their features are given by the following table. Machine RM1 RM2 RM3 Speed in Feet/Min 150 100 75 Products Processed in the Machine P1, P2 P2, P3 | P2, P3 Available Hours Per Week 35 35 35 Labor Cost Operating Hour $10 $15 $17 Also, the shipping department has a limit of 600,000 feet per week, regardless of thickness. Action Items 1. Determine the objective functions and constraints of this case if the objective of the OMW is to maximize the profits. 2. Create an Excel Solver model to solve the problem and answer the questions in Action Item 3. A help document and template for setting up the problem/case study have been provided here: o Help Document o Solution Template Hint: i. Convert labor costs from "$/hour" to "feet/hour" when determining objective function. ii. There are three main products, but since these products are processed in three different machines, there are actually six different products with different net profit values. iii. Convert speed from "feet/min" to "hours/feet" when dealing with time constraints (available hours per week). D E F G H K A B 2 3 Initial conversions: 4 5 Profit (excluding labor cost) Demand per Week 6 perfoot of product (feet) 7 P1 8 P2 9 P3 10 Product Available Hours Per Week Labor Cost/ Operating Hour Machine Speed in Feet/Min RM1 RM2 RM3 Products Processed in the Machine P1, P2 P2, P3 P2, P3 Hours in each 11 Profit Labor cost Net Profit/feet machine 12 P11 13 P21 14 r22 15 2 16 P23 17 P33 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 conversions solution + A B D E F G . | J L P21 P11 P22 P32 P23 P33 Total profit 1 2 Net Profit 3 4 P11 P21 P22 P32 P23 P33 Total Production Shipping Capacity 5 Production 6 7 P11 P21 P22 P32 P23 P33 total hours Capacity 8 RM1 9 RM2 Demand 10 RM3 11 12 Production Requirements 13 P1 14 P2 15 P3 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 conversions solution