please answer a,b,c,d and e
please fill in the excel document as listed below and provide each step to solve and answer each question.
there is two excel pages, part (b) and part (d) please show both and final answers.
Instructions: electronically submit your answers to Canvas by the due date. For Parts (b) \& (d) use the Excel template file that is posted along with this assignment on Canvas and attach your original Excel solutions to your submission (KEEP the formulas in the cells.) For parts (a), (c), \& (e), please type your answers in a single Microsoft Word document. Therefore, for this assignment, you will submit two files to Canvas: 1) The completed Excel template file for Parts (b) and (d); 2) A word document containing your answers to the other parts. You can work with your classmates to discuss the solutions. However, each student should submit her/his own original work. Question 1) Skycell, a major European cell phone manufacturer, is making production plans for the coming year. Skycell has worked with its customers (the service providers) to come up with forecasts of monthly requirement, as shown in Table 1. Manufacturing is primarily an assembly operation, and capacity is governed by the number of people on the production line. The plant operates for 20 days a month, eight hours each day. One person can assemble a phone every 10 minutes. Workers are paid 20 euros per hour and 50 percent premium for overtime. The plant currently employs 1,250 workers. Component costs for each cell phone total 20 euros. Given the rapid decline in component and finished product prices, carrying inventory from one month to the next incurs a cost of 3 euros per phone per month. Skycell currently has a no-layoff policy in place. Overtime is limited to a maximum of 20 hours per month per employee. Assume that Skycell has a starting inventory of 50,000 units and wants to end the year with the same level of inventory. a) Assuming no backlogs, no subcontracting, and no new hires, mathematically formulate a linear program that models the aggregate planning problem at Skycell. (20 points) Hint: review the Red Tomato example that we covered in class. First, carefully define the decision variables similar to Page 23 of the post-class slides for aggregate planning. A mathematical formulation is similar to what you find on Slide 28 of the post-class slides. b) Formulate the linear program you found in Part (a) in Excel and solve it using excel solver. What is the optimal annual cost of operating the plant? Use Sheet "Part (b)" of the Excel template file for your formulation. (20 points) Hint: review the excel formulation for Red Tomato and FlexMan examples that we covered in class. You can also watch the lecture recordings for them posted on Canvas. c) What is the "average" per unit cost of in-house production (including holding and overtime cost)? (5 points) Hint: You calculated the optimal annual cost in Part (b). You can also calculate the total number of units produced over the year by summing up monthly productions found in Column E of the Excel template file. Then the average unit cost is simply the total cost divided by the total number of units produced. d) Now suppose a third party has offered to produce cell phones as needed at a cost 26 euros per unit (this includes the component costs of 20 euros per unit). Formulate an aggregate planning problem for Skycell that includes the subcontracting decision in Excel and solve it using Excel solver to find out how Skycell should use the third party. Use Sheet "Part (d)" of the Excel template file for your formulation. (10 points) e) From Part (c), you should be able to observe that the average per-unit cost of in-house production is less than 26 euros. Why would Skycell use the third party even when the per-unit cost of the third party is higher than the average per-unit cost for in-house production? (5 points) Aggregate Planning at Skycel Total Aenual Colt Aggregate Planning at Skycell with Subcontracting Total Aonual Cont Instructions: electronically submit your answers to Canvas by the due date. For Parts (b) \& (d) use the Excel template file that is posted along with this assignment on Canvas and attach your original Excel solutions to your submission (KEEP the formulas in the cells.) For parts (a), (c), \& (e), please type your answers in a single Microsoft Word document. Therefore, for this assignment, you will submit two files to Canvas: 1) The completed Excel template file for Parts (b) and (d); 2) A word document containing your answers to the other parts. You can work with your classmates to discuss the solutions. However, each student should submit her/his own original work. Question 1) Skycell, a major European cell phone manufacturer, is making production plans for the coming year. Skycell has worked with its customers (the service providers) to come up with forecasts of monthly requirement, as shown in Table 1. Manufacturing is primarily an assembly operation, and capacity is governed by the number of people on the production line. The plant operates for 20 days a month, eight hours each day. One person can assemble a phone every 10 minutes. Workers are paid 20 euros per hour and 50 percent premium for overtime. The plant currently employs 1,250 workers. Component costs for each cell phone total 20 euros. Given the rapid decline in component and finished product prices, carrying inventory from one month to the next incurs a cost of 3 euros per phone per month. Skycell currently has a no-layoff policy in place. Overtime is limited to a maximum of 20 hours per month per employee. Assume that Skycell has a starting inventory of 50,000 units and wants to end the year with the same level of inventory. a) Assuming no backlogs, no subcontracting, and no new hires, mathematically formulate a linear program that models the aggregate planning problem at Skycell. (20 points) Hint: review the Red Tomato example that we covered in class. First, carefully define the decision variables similar to Page 23 of the post-class slides for aggregate planning. A mathematical formulation is similar to what you find on Slide 28 of the post-class slides. b) Formulate the linear program you found in Part (a) in Excel and solve it using excel solver. What is the optimal annual cost of operating the plant? Use Sheet "Part (b)" of the Excel template file for your formulation. (20 points) Hint: review the excel formulation for Red Tomato and FlexMan examples that we covered in class. You can also watch the lecture recordings for them posted on Canvas. c) What is the "average" per unit cost of in-house production (including holding and overtime cost)? (5 points) Hint: You calculated the optimal annual cost in Part (b). You can also calculate the total number of units produced over the year by summing up monthly productions found in Column E of the Excel template file. Then the average unit cost is simply the total cost divided by the total number of units produced. d) Now suppose a third party has offered to produce cell phones as needed at a cost 26 euros per unit (this includes the component costs of 20 euros per unit). Formulate an aggregate planning problem for Skycell that includes the subcontracting decision in Excel and solve it using Excel solver to find out how Skycell should use the third party. Use Sheet "Part (d)" of the Excel template file for your formulation. (10 points) e) From Part (c), you should be able to observe that the average per-unit cost of in-house production is less than 26 euros. Why would Skycell use the third party even when the per-unit cost of the third party is higher than the average per-unit cost for in-house production? (5 points) Aggregate Planning at Skycel Total Aenual Colt Aggregate Planning at Skycell with Subcontracting Total Aonual Cont
