3. In Chapter 8, Exercise 1, your textbook includes the following: "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 requirements (in thousands of phones) as shown in Table 8-10. 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 a 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) Assume that there are no backlogs, no subcontracting and no new hires. With minimizing cost as the objective, formulate an aggregate planning optimization model for Skycell. b) Use Excel Solver to find the optimal solution. Submit your Excel file. c) Report the optimal solution and the minimum cost. d) Management is interested in knowing if it would be worthwhile to negotiate an increase of allowed overtime per employee per month from 20 hours to 40 hours. Make a copy of your model from part a), then modify it. e) Complete parts and c using your model from part d. f) Should management negotiate an increase of allowed overtime per employee per month up to 40 hours? What is the difference in cost? How much less/more overtime is used? Demand | 1.600 900 Table 8-10: Monthly Demand for Cell Phones, in Thousands Month Demand Month January | 1.000 July February | 1,100 IA August March | 1,000 September April | 1.200 October May | 1,500 November June | 1,600 December 800 1.400 1,700 3. In Chapter 8, Exercise 1, your textbook includes the following: "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 requirements (in thousands of phones) as shown in Table 8-10. 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 a 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) Assume that there are no backlogs, no subcontracting and no new hires. With minimizing cost as the objective, formulate an aggregate planning optimization model for Skycell. b) Use Excel Solver to find the optimal solution. Submit your Excel file. c) Report the optimal solution and the minimum cost. d) Management is interested in knowing if it would be worthwhile to negotiate an increase of allowed overtime per employee per month from 20 hours to 40 hours. Make a copy of your model from part a), then modify it. e) Complete parts and c using your model from part d. f) Should management negotiate an increase of allowed overtime per employee per month up to 40 hours? What is the difference in cost? How much less/more overtime is used? Demand | 1.600 900 Table 8-10: Monthly Demand for Cell Phones, in Thousands Month Demand Month January | 1.000 July February | 1,100 IA August March | 1,000 September April | 1.200 October May | 1,500 November June | 1,600 December 800 1.400 1,700