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?