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 the following monthly demand forecasts for cell phones: Month Demand for Cell Phones (in Units) January 1,000,000 February 1,100,000 March 1,000,000 April 1,200,000 May 1,500,000 June 1,600,000 July 1,600,000 August 900,000 September 1,100,000 October 800,000 November 1,400,000 December 1,700,000

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 per day. One person can assemble a phone every 12 minutes. Workers are paid 25 euros per hour and a 50 percent premium for overtime. The plant currently employs 750 workers. The cost of hiring an employee is 500 euros and the cost of laying off an employee is 1000 euros. Overtime is limited to a maximum of 25 hours per month per employee. Component costs for each cell phone total 35 euros. Given the rapid decline in component and finished-product prices, carrying inventory from one month to the next incurs a cost of 1.5 euros per phone per month. Assume that Skycell has a starting inventory of 20,000 units and wants to end the year with the 25,000 units of inventory. Assume backlog/stockout and subcontracting are not possible.

a) Develop an optimization spreadsheet in Excel to satisfy all the demands with minimum cost :

Use Excel Solver to answer the following questions: b) What is the optimum production schedule? What is the annual cost of this schedule? What aggregate planning strategy emerges from the results?

c) Suppose storage space for inventory is limited to a maximum of 25,000 units per month. What is the cost of this production schedule? How does this production schedule differ from the production schedule from part b)?

d) Suppose Skycell aims to level the production schedule such that the quantity produced each month cannot exceed average monthly demand over the next twelve months (i.e., 1,241,667units) by 100,000 units. Thus, monthly production should be no more than 1,341,667 units. What is the cost of this production schedule? How does this production schedule differ from the production schedule from part b)?