An Excel worksheet is provided that shows the required time for each product (SKU) on each "resource" (machine), as well as demand and margin/unit for each product (SKU). Determine the system constraint (i.e., the resource that has demand, in terms of aggregate workload, that is greater than the capacity for that resource.) DATA O a. Quality Control O b. Drill O c. Lacquer O d. Overhead Sander e. Lathe A business manufactures four products (A, B, C, D), each requiring a number of input materials for production. Information on each of the products, including selling price and profit margin per unit, demand per week, and the number of each input required to produce one unit of product is provided below. Selling Price ProductA $ ProductB ProductC 125 $ 135 $ 150 $ ProductD 175 - Variable Cost $ 70 $ 65 $ 90 $ 90 =Profit Margin/Unit $ 55 $ 70 $ 60 $ 85 Demand 60 70 25 45 Number of input materials required for each unit of production Total Total Input Material ProductA ProductB ProductC ProductD Cross-beams Butterfly clamps 2 16 20 Stabilizing brackets 9 10 Articulating hinges 4 22 6 2224 3 3 Required 470 Available 600 24 28 12 6 26 4,220 5,000 15 2,215 940 2,500 400 It has been found that the business does not have enough articulating hinges to meet the full demand for all four products, and must thus find the "product mix" that maximizes profit. Please find the optimal product mix. How many units of Product D will the optimal product mix include? QUESTION 3 A "product mix" problem can be solved quite easily by hand, as long as there is only one constraint (i.e. one resource in which demand > capacity). However, when there are multiple constraints it becomes quite complex. In the lab, we used Solver to find the product mix that results in the highest profit, subject to capacity constraints for each resource, and the constraint that we would not make more than the demand for any given product. Set up a model based on what was done in the lab to find the optimal product mix for the problem in the attached spreadsheet. What was the resulting total profit? less than $30,000 between $30,000 and $30,250 between $30,250 and $30,500 O between $30,500 and $30,750 O O between $30,750 and $31,000 more than $31,000 SKU Demand Profit Margin j-66 t-835 u-487 g-408 82 175 86 190 p-102 166 u-794 S-997 128 131 r-920 e-299 143 100 h-47 o-524 n-461 179 177 55 $ 29.02 $18.75 $19.67 $ 20.24 $16.81 $33.17 $13.66 $26.70 $31.51 $23.71 $25.52 $ 29.75 Time required for each SKU on each resource, in minutes per unit of production Resource Capacity j-66 t-835 u-487 g-408 p-102 u-794 S-997 r-920 e-299 h-47 o-524 n-461 Saw 2160 0.27 0.14 1.04 1.90 0.57 0.35 0.02 0.30 1.06 1.82 0.93 0.12 Drill 2112 1.00 0.69 0.00 1.36 1.40 1.81 0.00 0.00 0.43 1.62 0.89 1.93 Lathe 2208 1.58 1.82 1.55 1.47 0.69 1.72 1.35 0.92 1.16 1.27 1.59 1.20 Overhead Sander 2040 1.24 1.16 1.64 1.38 1.46 1.39 0.45 0.19 0.15 1.40 1.98 3.30 Veneer Press 2208 0.33 1.86 0.70 1.83 0.25 0.48 0.56 0.55 0.29 0.28 1.90 1.53 Edging Machine 1968 1.52 0.29 1.45 1.20 1.53 0.87 1.32 0.65 0.89 1.01 0.25 0.83 Lacquer 1560 0.89 0.69 1.14 0.21 1.95 0.96 0.63 0.87 1.86 0.72 1.11 0.62 Staining 2136 0.00 0.64 0.61 0.41 0.15 0.60 0.00 0.53 0.25 0.20 0.12 0.00 Quality Control 2088 0.00 1.51 1.64 1.22 1.21 1.99 0.00 2.05 1.41 1.34 2.01 1.19 Shipping Prep 2352 1.03 0.29 0.27 0.73 0.20 1.94 1.94 0.15 1.41 1.39 1.35 1.58 Product A B C D E F Doo-hickey 1.55 2.46 3.9 1.6 0.65 1.55 Time required on each workstation (minutes required per unit of production) Simegma 0.58 1.3 2.65 1.5 0.98 2 Thing-a-ma-jig 0.82 1.7 2.55 1.99 1.01 0.8 Whatchamacallit 1.1 2.14 4.05 2.58 0.95 1.2 Prototon 2000 1.04 1.24 4.15 3.07 1.04 2.2 Forecast demand per week 250 280 280 210 160 110 Profit margin per unit $ 12.00 $ 32.50 $44.00 $ 22.00 $ 16.00 $ 28.00 *assume that each machine has 40 hours/week of capacity (2,400 minutes) + *assume that production of a specific product should not exceed demand for that product *add a constraint that all production quantities must be integers *use the "Simplex LP" Solving Method in Solver