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-7. 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 a 50 percent premium for overtime. The plant currently employs 1,250 workers.Component cost for each cell phone totals 20 euros. Given the rapid decline in componentand 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 islimited 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, what is the optimum production schedule? What is the annual cost of this schedule? (Model formulation and Excel solution are both required)

(b) Is there any value for management to negotiate an increase of allowed overtime peremployee per month from 20 hours to 40? (Only Excel solution is required)

(c) Reconsider parts (a) and (b) if Skycell starts with only 1,200 employees. Reconsiderparts (a) and (b) if Skycell starts with 1,300 employees. What happens to the value of additional overtime as the workforce size decreases? (Only Excel solution is required)

(d) Consider part (a) for the case in which Skycell aims for a level production schedule such that the quantity produced each month does not exceed the average demand over the next 12 months (1,241,667) by 50,000 units. Monthly production including overtime is no more than 1,291,667. What would be the cost of a level production schedule? What is the value of overtime flexibility? (Only Excel solution is required)

please list the modle,thanks)

TABLE 8-7 Monthly Demand for Cell. Phones, in Thousands Month Demand Month Demand January February March April May June 1,000 1,100 1,000 1,200 1,500 1,600 July August September October November December 1,600 900 1,100 -800 1,400 1,700