Please help with answer A - G
6. ABC Corporations Northern Division produces three products. Each requires a certain amount of labor, machine time and materials. The marketing department has signed a contract for Product 2 at least 10 units must be produced. ABC's Production Director has formulated the problem as a linear programming problem and run it on EXCEL Solver. Use the EXCEL Sensitivity report below to answer the following questions. A. What is the optimal solution and total profit? B. IfABC could obtain additional machine time what would it be worth? Within what limits could ABC be sure of this benefit? C. If ABC could obtain additional labor time what would it be worth? D. How much would the profit for XI have to increase in order for the firm to start producing them? E. ABC is considering introducing a new product, X4, and the Finance manager wants to know how much profit it will have to make in order to be added to the product mix. The X4 will use 4 minutes of machine time, 6 hours of labor and 3 pounds of material. Calculate the opportunity cost of producing this new product F. An additional constraint has arisen because another Division of ABC needs an assembly unit for its products. ABC Western will be limited to 300 hours on this unit. XI requires ten hours, X2 fiftleen hours and X3 six hours. Will the current solution satisfy this new constraint or will the problem have to be reformulated and re-solved? G. If the contract for X2 was increased to 15 units, what would be the effect on the firm's profit? Microsoft Excel 12.0 Sensitivity Report Adjustable Cells Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 13 1E+30 21E+30 1.5 Name Cell SB$12 X1 SC$12 X2 SD$12 X3 12 18 15 1E+30 13 0 40 Constraints Final Shadow Constraint Allowable Allowable Value Price R.H.Side Increase Decrease 28 20 Name Cell SBS16 Machine time (min) 160 SBS18 Materials (pounds) 160 288 1E+30 200 1E+30 15 SB$17 Labor (hours) 10 10 $B$19X2 reqd 6. ABC Corporations Northern Division produces three products. Each requires a certain amount of labor, machine time and materials. The marketing department has signed a contract for Product 2 at least 10 units must be produced. ABC's Production Director has formulated the problem as a linear programming problem and run it on EXCEL Solver. Use the EXCEL Sensitivity report below to answer the following questions. A. What is the optimal solution and total profit? B. IfABC could obtain additional machine time what would it be worth? Within what limits could ABC be sure of this benefit? C. If ABC could obtain additional labor time what would it be worth? D. How much would the profit for XI have to increase in order for the firm to start producing them? E. ABC is considering introducing a new product, X4, and the Finance manager wants to know how much profit it will have to make in order to be added to the product mix. The X4 will use 4 minutes of machine time, 6 hours of labor and 3 pounds of material. Calculate the opportunity cost of producing this new product F. An additional constraint has arisen because another Division of ABC needs an assembly unit for its products. ABC Western will be limited to 300 hours on this unit. XI requires ten hours, X2 fiftleen hours and X3 six hours. Will the current solution satisfy this new constraint or will the problem have to be reformulated and re-solved? G. If the contract for X2 was increased to 15 units, what would be the effect on the firm's profit? Microsoft Excel 12.0 Sensitivity Report Adjustable Cells Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 13 1E+30 21E+30 1.5 Name Cell SB$12 X1 SC$12 X2 SD$12 X3 12 18 15 1E+30 13 0 40 Constraints Final Shadow Constraint Allowable Allowable Value Price R.H.Side Increase Decrease 28 20 Name Cell SBS16 Machine time (min) 160 SBS18 Materials (pounds) 160 288 1E+30 200 1E+30 15 SB$17 Labor (hours) 10 10 $B$19X2 reqd
