I ONLY NEED HELP WITH d&e... I POSTED MY ANSWERS TO a,b,&c with the answer report for the solver function... PLEASE SHOW ALL FUNCTION FORMULAS USED, THATS WHERE IM GETTING STUCK

8. Hart Manufacturing makes three products. Each product requires manufacturing oper- ations in three departments: A, B, and C. The labor-hour requirements, by department, are as follows: Department A B Product 1 1.50 2.00 0.25 Product 2 3.00 1.00 0.25 Product 3 2.00 2.50 0.25 During the next production period the labor-hours available are 450 in department A, 350 in department B, and 50 in department C. The profit contributions per unit are $25 for product 1, $28 for product 2, and $30 for product 3. a. Formulate a linear programming model for maximizing total profit contribution. b. Solve the linear program formulated in part (a). How much of each product should be produced, and what is the projected total profit contribution? c. After evaluating the solution obtained in part (b), one of the production supervisors noted that production setup costs had not been taken into account. She noted that setup costs are $400 for product 1, $550 for product 2, and $600 for product 3. If the solution developed in part (b) is to be used, what is the total profit contribution after taking into account the setup costs? d. Management realized that the optimal product mix, taking setup costs into account, might be different from the one recommended in part (b). Formulate a mixed-inte- ger linear program that takes setup costs provided in part (c) into account. Manage- ment also stated that we should not consider making more than 175 units of product 1, 150 units of product 2, or 140 units of product 3. e. Solve the mixed-integer linear program formulated in part (d). How much of each product should be produced and what is the projected total profit contribution? Compare this profit contribution to that obtained in part (c). A Z B L C D E F G H 138 8.) Hart Manufacturing makes three products. Each product requires manufacturing operations 139 in three departments: A, B, and C. The labor-hour requirements, by department, 140 are as follows: 141 142 a. Formulate a linear programming model for maximizing total profit contribution. 143 b. Solve the linear program formulated in part (a). How much of each product should 144 be produced, and what is the projected total profit contribution? 145 146 Department Product 1 Product 2 Product 3 147 1.50 3.00 2.00 450 148 B 2.00 1.00 2.50 350 149 C 0.25 0.25 0.25 50 150 $ 25.00 $28.00 $ 30.00 151 152 During the next production period the labor-hours available are 450 in department A, 153 350 in department B, and 50 in department C. The profit contributions per unit are $25 154 for product 1, $28 for product 2, and $30 for product 3. 155 156 Product 15 X 157 Product 2 Y 158 Product 3= 159 160 Max: f(x)=25x + 28y + 302 161 1.50x + 3y + 22 = 0 165 166 Product 2 total profit 167 Unit Profit $ 25.00 $ 28.00 $ 30.00 168 # of units 60 80 601 S 5,540.00 169 170 Subject to: LHS RHS labor hours 1.50 3.00 2.00 450 = 0 2 total profit Product Unit Profit # of units 1 =C150 = 60 =D150 80 3 1=E150 60 =SUMPRODUCT(C168:E168,C167:E167) Subject to: LHS RHS labor hours avail. Dept. A = 0 165 166 Product 2 total profit 167 Unit Profit $ 25.00 $ 28.00 $ 30.00 168 # of units 60 80 601 S 5,540.00 169 170 Subject to: LHS RHS labor hours 1.50 3.00 2.00 450 = 0 2 total profit Product Unit Profit # of units 1 =C150 = 60 =D150 80 3 1=E150 60 =SUMPRODUCT(C168:E168,C167:E167) Subject to: LHS RHS labor hours avail. Dept. A