Refer to the last page for a suggested format for assignments I Problem 2 (50 martes) Goldrite Company manufacturers, assembles and rebuilds material handling equipment used in warehouses and distribution. One product, called a Liftmaster, is assembled from four components a frame, a motor, two supports, and a metal strap. Goldrite's production schedule calls for 10,000 Liftmasters to be made next month. Goldrite purchases the motors from an outside supplier, but the frames, supports, and straps may either be manufactured by the company or purchased from an outside supplier Manufacturing and purchase costs per unit are as follows: Component Frame Support Strap Manufacturing Cost $35.00 $11.00 $5.00 Purchase Cost $50.00 $15.00 $7.00 Three departments are involved in the production of these components. The time (in minutes per unit) required to process each component in each department and the available capacity (in hours) for the three departments are as follows: Department Time Available Hours Cutting Milling Shaping Production Time per unit (minutes) Frame Support Strap 3 1 0.3 2 1 0 2 2 1 900 500 800 Exceptas noted, use only the Excel Model and Sensitivity Report to answer the questions below. Some questions below only require you to report numbers that are easily read from your model or sensitivity report. Other questions may require calculations, and you must show these calculations to receive full marks. Questions are independent a) Set up the above problem in Excel using the style that we used in dass. Make sure that your sprendshoot model is logical, well organized and easy to understand solve using Solver 10 (b) What recommendations would you give to management in terms of how mary units of each component should be made and how many should be purchased? (6 marks) (c) If management follows your recommendations presented in part (a) above, what would be the expected cost? (1 mark) (d) Which department(s) are limiting the manufacturing volume? (2 mark) (e) Suppose management could arrange for exactly 30 hours of additional shaping time. (4 marks) (0) What specific effect would this change have on total cost? () Would the optimal solution change? Explain. Suppose management wanted to have an extra 10 frames that they could use for spare parts. (4 marks) (1) What specific effect would this change have on total cost? (m)Would the optimal solution change? Explain. (9) You will notice that in the optimal solution, no frames are purchased. Using the information provided by the reduced cost, comment on what this reduced cost number means. (Hint: You will have to refer to your textbook to get information on reduced cost). (3 marks) th) Print out your spreadsheet model printed on one page in landscape orientation and include it with your answers. You do NOT need to use a color printer (1) Print out your Sensitivity Report printed on one page in landscape orientation and include it with your answers. You do NOT need to use a color printer. (10 marks) () Suppose another manufacturer has offered to sell frames to Goldnite for $38 each. Re-solve the Excel model with the frame purchase cost of $38 and report on any changes to the optimal solution and any cost savings/increases that may result (Noto You will notice that some of your answers will be fractional. In this question, a reasonable thing to do is round of those fractional values to more appropriate values and report thoso (rounded) values to management. Make sure your rounded values do not violate any constraints.). Include a printout of this re-solved model with this homework mo (b) What recommendations would you give to management in terms of how many units of each component should be made and how many should be purchased? (6 marks) (c) If management follows your recommendations presented in part (a) above, what would be the expected cost? (1 mark) (d) Which department(s) are limiting the manufacturing volume? (2 mark) (C) Suppose management could arrange for exactly 30 hours of additional shaping time. (4 marks) (1) What specific effect would this change have on total cost? C) Would the optimal solution change? Explain. Suppose management wanted to have an extra 10 frames that they could use for spare parts. (4 marks) (1) What specific effect would this change have on total cost? (Would the optimal solution change? Explain. (9) You will notice that in the optimal solution, no frames are purchased. Using the information provided by the reduced cost, comment on what this reduced cost number means. (Hint: You will have to refer to your textbook to get information on reduced cost). (3 marks) (h) Print out your spreadsheet model printed on one page in landscape orientation and include it with your answers. You do NOT need to use a color printer. (O) Print out your Sensitivity Report printed on one page in landscape orientation and include it with your answers. You do NOT need to use a color printer. (10 marks) Suppose another manufacturer has offered to sell frames to Goldrite for $38 each. Re-solve the Excel model with the frame purchase cost of $38 and report on any changes to the optimal solution and any cost savings/increases that may result. (Note: You will notice that some of your answers will be fractional. In this question, a reasonable thing to do is round off these fractional values to more appropriate values and report those (rounded) values to management. Make sure your rounded values do not violate any constraints.). Include a printout of this re-solved model with this homework. (10 marks) Page 2 GDPM 540: Quantitative Decision Modeling Assignment #2 Due: March 15, 2021 Refer to the last page for a suggested format for assignments Problem 2 (50 marks) Goldrite Company manufacturers, assembles and rebuilds material handling equipment used in warehouses and distribution. One product, called a Liftmaster, is assembled from four components: a frame, a motor, two supports, and a metal strap. Goldrite's production schedule calls for 10,000 Liftmasters to be made month. Goldrite purchases the motors from an outside supplier, but the frames, supports, and straps may either be manufactured by the company or purchased from an outside supplier. Manufacturing and purchase costs per unit are as follows: Component Manufacturing Cost Purchase Cost Frame $35.00 $50.00 Support $11.00 $15.00 Strap $5.00 $7.00 Three departments are involved in the production of these components. The time in minutes per unit) required to process each component in each department and the available capacity (in hours) for the three departments are as follows: Department Cutting Milling Shaping Production Time per unit (minutes) Frame Support Strap 3 1 0.3 2 1 0 2 2 1 Time Available Hours 900 500 800 Except as noted, use only the Excel Model and Sensitivity Report to answer the questions below. Some questions below only require you to report numbers that are easily read from your model or sensitivity report. Other questions may require calculations, and you must show these calculations to receive full marks. Questions are independent. (a) 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. Solve using Solver. (40 marks) Page 1 Refer to the last page for a suggested format for assignments I Problem 2 (50 martes) Goldrite Company manufacturers, assembles and rebuilds material handling equipment used in warehouses and distribution. One product, called a Liftmaster, is assembled from four components a frame, a motor, two supports, and a metal strap. Goldrite's production schedule calls for 10,000 Liftmasters to be made next month. Goldrite purchases the motors from an outside supplier, but the frames, supports, and straps may either be manufactured by the company or purchased from an outside supplier Manufacturing and purchase costs per unit are as follows: Component Frame Support Strap Manufacturing Cost $35.00 $11.00 $5.00 Purchase Cost $50.00 $15.00 $7.00 Three departments are involved in the production of these components. The time (in minutes per unit) required to process each component in each department and the available capacity (in hours) for the three departments are as follows: Department Time Available Hours Cutting Milling Shaping Production Time per unit (minutes) Frame Support Strap 3 1 0.3 2 1 0 2 2 1 900 500 800 Exceptas noted, use only the Excel Model and Sensitivity Report to answer the questions below. Some questions below only require you to report numbers that are easily read from your model or sensitivity report. Other questions may require calculations, and you must show these calculations to receive full marks. Questions are independent a) Set up the above problem in Excel using the style that we used in dass. Make sure that your sprendshoot model is logical, well organized and easy to understand solve using Solver 10 (b) What recommendations would you give to management in terms of how mary units of each component should be made and how many should be purchased? (6 marks) (c) If management follows your recommendations presented in part (a) above, what would be the expected cost? (1 mark) (d) Which department(s) are limiting the manufacturing volume? (2 mark) (e) Suppose management could arrange for exactly 30 hours of additional shaping time. (4 marks) (0) What specific effect would this change have on total cost? () Would the optimal solution change? Explain. Suppose management wanted to have an extra 10 frames that they could use for spare parts. (4 marks) (1) What specific effect would this change have on total cost? (m)Would the optimal solution change? Explain. (9) You will notice that in the optimal solution, no frames are purchased. Using the information provided by the reduced cost, comment on what this reduced cost number means. (Hint: You will have to refer to your textbook to get information on reduced cost). (3 marks) th) Print out your spreadsheet model printed on one page in landscape orientation and include it with your answers. You do NOT need to use a color printer (1) Print out your Sensitivity Report printed on one page in landscape orientation and include it with your answers. You do NOT need to use a color printer. (10 marks) () Suppose another manufacturer has offered to sell frames to Goldnite for $38 each. Re-solve the Excel model with the frame purchase cost of $38 and report on any changes to the optimal solution and any cost savings/increases that may result (Noto You will notice that some of your answers will be fractional. In this question, a reasonable thing to do is round of those fractional values to more appropriate values and report thoso (rounded) values to management. Make sure your rounded values do not violate any constraints.). Include a printout of this re-solved model with this homework mo (b) What recommendations would you give to management in terms of how many units of each component should be made and how many should be purchased? (6 marks) (c) If management follows your recommendations presented in part (a) above, what would be the expected cost? (1 mark) (d) Which department(s) are limiting the manufacturing volume? (2 mark) (C) Suppose management could arrange for exactly 30 hours of additional shaping time. (4 marks) (1) What specific effect would this change have on total cost? C) Would the optimal solution change? Explain. Suppose management wanted to have an extra 10 frames that they could use for spare parts. (4 marks) (1) What specific effect would this change have on total cost? (Would the optimal solution change? Explain. (9) You will notice that in the optimal solution, no frames are purchased. Using the information provided by the reduced cost, comment on what this reduced cost number means. (Hint: You will have to refer to your textbook to get information on reduced cost). (3 marks) (h) Print out your spreadsheet model printed on one page in landscape orientation and include it with your answers. You do NOT need to use a color printer. (O) Print out your Sensitivity Report printed on one page in landscape orientation and include it with your answers. You do NOT need to use a color printer. (10 marks) Suppose another manufacturer has offered to sell frames to Goldrite for $38 each. Re-solve the Excel model with the frame purchase cost of $38 and report on any changes to the optimal solution and any cost savings/increases that may result. (Note: You will notice that some of your answers will be fractional. In this question, a reasonable thing to do is round off these fractional values to more appropriate values and report those (rounded) values to management. Make sure your rounded values do not violate any constraints.). Include a printout of this re-solved model with this homework. (10 marks) Page 2 GDPM 540: Quantitative Decision Modeling Assignment #2 Due: March 15, 2021 Refer to the last page for a suggested format for assignments Problem 2 (50 marks) Goldrite Company manufacturers, assembles and rebuilds material handling equipment used in warehouses and distribution. One product, called a Liftmaster, is assembled from four components: a frame, a motor, two supports, and a metal strap. Goldrite's production schedule calls for 10,000 Liftmasters to be made month. Goldrite purchases the motors from an outside supplier, but the frames, supports, and straps may either be manufactured by the company or purchased from an outside supplier. Manufacturing and purchase costs per unit are as follows: Component Manufacturing Cost Purchase Cost Frame $35.00 $50.00 Support $11.00 $15.00 Strap $5.00 $7.00 Three departments are involved in the production of these components. The time in minutes per unit) required to process each component in each department and the available capacity (in hours) for the three departments are as follows: Department Cutting Milling Shaping Production Time per unit (minutes) Frame Support Strap 3 1 0.3 2 1 0 2 2 1 Time Available Hours 900 500 800 Except as noted, use only the Excel Model and Sensitivity Report to answer the questions below. Some questions below only require you to report numbers that are easily read from your model or sensitivity report. Other questions may require calculations, and you must show these calculations to receive full marks. Questions are independent. (a) 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. Solve using Solver. (40 marks) Page 1