Refer to the last page for a suggested format for assignments Problem 1 (90 marks) The management of Bob Company is trying to determine the amount of each of two products to produce over the coming planning period. The following information concerns labor availability, labor utilization, and product profitability. Product (hours/unit) Department 1 2 Labor cost per hour Labor-hours available A 1.00 0.35 20.00 100 B 0.30 0.20 30.00 36 0.20 0.50 25.00 50 Rev. per unit 64.00 40.50 Products not completed during one processing cycle are completed during the next processing cycle. Management requires that the company produce at least 40 Product 1 in each processing cycle. Considering the above equations answer the following questions: a. What are the two decisions that need to be made? (2 marks) b. What equations you will use to develop the algebraic model? (10 marks) c. Please complete the graph below and find the Optimal Solution by drawing Iso-Profit line. (Total 20 marks) Add all constraints (except Non-neg.). (5 marks) ii. Label all the constraints by name. (5 marks) iii. iv. V. vi. Shade and identify the feasible region. (2 marks) Draw and identify the iso-profit line on this graph showing where the optimal solution is located. (2 marks) Identify at which intersection the optimal solution exists. (2 marks) Label all the corner points of feasible region with a, b, c, d, e, ---- (4 marks) Product 2 Bob Company 300 280 260 240 220 200 180 160 140 120 100 80 60 40 20 0 0 20 40 60 80 100 120 140 160 180 200 220 240 260 product 1 d. Identify amount of profits at the points a, b, c, d, e, - -? (5 marks) e. The Boss at Bob Company has said that the company should only produce product 1 since each product 1 translates higher profit than product 2. referring to the, yet unknown, optimal solution, how would you respond to this argument? (2 marks) f. Bob Company is hoping to produce 120 Product 1 and 120 Product 2 in the next processing cycle. Comment on their plans. (2 marks) g. What is the optimal solution? (2 marks) h. If this problem was formulated in Excel, what would be the value of the target cell? (1 mark) i. How many hours will Bob Company use in the Department A? (1 mark) j. Determine the allowable increase and allowable decrease on the objective function coefficient for Product 1. (4 marks) k. Determine the allowable increase and allowable decrease on the objective function coefficient for product 2. (5 marks) I. FOR THIS QUESTION ONLY, suppose the Min Dem constraint was changed so that the minimum is now 50 units. {Circle the correct responses.} (3 marks) i. Would the feasible region change? ii. Would the optimal solution change? Yes No Yes No Yes No iii. Would the set of binding constraints change? m. Determine the shadow price for the Department A labor hour constraint. (4 marks) n. Determine the allowable increase and allowable decrease on the Department A labor hour constraint. (4 marks) o. Set up the above problem in Excel using the style that we used in class. Make sure that your spreadsheet model is logical, well organized and easy to understand. In building formulas, use the sumproduct function where appropriate. Use text boxes to identify the changing cells, target cell and constraints. Print out the model in landscape orientation (full page) and include it with your answers. (15 marks) p. Print out the cell formulas in landscape orientation (full page) and include it with your answers. (5 marks) q. Which constraints are binding? non-binding? 2 marks) r. The Bob Company problem was correctly formulated and solved in Excel with the following partial sensitivity report output. (3 marks) Cell $E$26 Name Final Value Shadow Price Constraint R.H. Side B Labour used 36 47.368421 Allowable Increase 36 0.627906977 Allowable Decrease 6 $E$27 C Labour used 47.15789 0 50 1E+30 2.842105263 Suppose bob Company can secure up to a one additional labor hour in either the Department B or Department C at the cost of $40, but not both. In which department, B or C, would you recommend purchasing extra time? Circle the correct response and provide convincing evidence. Department B Evidence: Department C None