Problem 2: Please see the data in the Excel file. The work needs to be completed in Excel. Show all your work. Your company manufactures five products: A, B, C, D, and E. The company wants to maximize its monthly profit. The maximum possible sales of products A, B, C, D, and E are 125, 150, 175, 130, and 100 respectively. The selling price and the number of labor hours and material required for each unit of the product is given below: For example, one unit of product A sells for $125, requires 3.35 hours of labor, 3 units of Material 1, 0 units of Material 2, 7 units of Material 3, and 8 units of Material 4. A $125 3.35 B $103 $ 153 3.6 D $167 3.9 E $ 177 4.3 2.7 3 4 4 0 2 0 Product Selling Price Labor hours per unit of product Material 1 per unit of product Material 2 per unit of product Material 3 per unit of product Material 4 per unit of product 0 7 1 1 2 7 1 8 11 12 8 1 7 6 5 The company has 1600 labor hours, 1000 units of Material 1, 1300 units of Material 2, 2300 units of Material 3, and 1700 units of Material 4 available. The hourly labor rate is $12.50, one unit of Material 1 costs $3.33, one unit of Material 3 2 costs $3.00, one unit of Material 3 costs $3.70, and one unit of Material 4 costs $4.10. Your job is to determine how many units of products A, B, C, D, and E should be produced in order to maximize profits. Setup a linear programming model in Excel and then use Solver to solve. B F G H Product A Product B Product C Product D Product E $ 125.00 $ 103.00 $ 153.00 $ 167.00 $ 177.00 125 150 175 130 100 3.35 2.7 3.6 3.9 4.3 3 4 2 0 7 1 1 2 7 1 8 11 12 8 1 7 6 5 NO 1 2 Selling Price 3 Max Sales 4 Labor required for each unit of the product (in hours) 5 Material 1 required for each unit of the product 6 Material 2 required for each unit of the product 7 Material 3 required for each unit of the product 8 Material 4 required for each unit of the product 9 10 Labor hourly cost 11 Material 1 cost per unit 12 Material 2 cost per unit 13 Material 3 cost per unit 14 Material 4 cost per unit 15 16 Labor hours available 17 Material 1 available 18 Material 2 available 19 Material 3 available 20 Material 4 available 21 22 23 24 $ $ $ $ $ 12.50 3.33 3.00 3.70 4.10 1600 1000 1300 2300 1700 Problem 2: Please see the data in the Excel file. The work needs to be completed in Excel. Show all your work. Your company manufactures five products: A, B, C, D, and E. The company wants to maximize its monthly profit. The maximum possible sales of products A, B, C, D, and E are 125, 150, 175, 130, and 100 respectively. The selling price and the number of labor hours and material required for each unit of the product is given below: For example, one unit of product A sells for $125, requires 3.35 hours of labor, 3 units of Material 1, 0 units of Material 2, 7 units of Material 3, and 8 units of Material 4. A $125 3.35 B $103 $ 153 3.6 D $167 3.9 E $ 177 4.3 2.7 3 4 4 0 2 0 Product Selling Price Labor hours per unit of product Material 1 per unit of product Material 2 per unit of product Material 3 per unit of product Material 4 per unit of product 0 7 1 1 2 7 1 8 11 12 8 1 7 6 5 The company has 1600 labor hours, 1000 units of Material 1, 1300 units of Material 2, 2300 units of Material 3, and 1700 units of Material 4 available. The hourly labor rate is $12.50, one unit of Material 1 costs $3.33, one unit of Material 3 2 costs $3.00, one unit of Material 3 costs $3.70, and one unit of Material 4 costs $4.10. Your job is to determine how many units of products A, B, C, D, and E should be produced in order to maximize profits. Setup a linear programming model in Excel and then use Solver to solve. B F G H Product A Product B Product C Product D Product E $ 125.00 $ 103.00 $ 153.00 $ 167.00 $ 177.00 125 150 175 130 100 3.35 2.7 3.6 3.9 4.3 3 4 2 0 7 1 1 2 7 1 8 11 12 8 1 7 6 5 NO 1 2 Selling Price 3 Max Sales 4 Labor required for each unit of the product (in hours) 5 Material 1 required for each unit of the product 6 Material 2 required for each unit of the product 7 Material 3 required for each unit of the product 8 Material 4 required for each unit of the product 9 10 Labor hourly cost 11 Material 1 cost per unit 12 Material 2 cost per unit 13 Material 3 cost per unit 14 Material 4 cost per unit 15 16 Labor hours available 17 Material 1 available 18 Material 2 available 19 Material 3 available 20 Material 4 available 21 22 23 24 $ $ $ $ $ 12.50 3.33 3.00 3.70 4.10 1600 1000 1300 2300 1700