VERY URGENT

NEED TO BE SOLVED ONLY USING EXCEL. POST THE OPTIMIZATION MODEL ALSO.

FlexMan, an electronics contract manufacturer, uses its Topeka, Kansas, facility to produce two product categories: routers and switches. Consultation with customers has indicated a demand forecast for each category over the next 12 months (in thousands of units) to be as shown in the table below.

Section I

a) Assuming no backlogs, no subcontracting, no layoffs, and no new hires, what is the optimum production schedule for FlexMan? What is the annual cost of this schedule? What inventories does the optimal production schedule build? Does this seem reasonable?

b) Is there any value for management to negotiate an increase of allowed overtime per employee per month from 20 hours to 40? What variables are affected by this change?

c) Reconsider parts (a) and (b) if FlexMan starts with only 5,900 employees. Reconsider parts (a) and (b) if FlexMan starts with 6,700 employees. What happens to the value of additional overtime as the workforce size decreases?

Section II

The firm is considering the option of changing workforce size with demand. The cost of hiring a new employee is $700 and the cost of a layoff is $1,000. It takes an employee two months to reach full production capacity. During those two months, a new employee provides only 50 percent productivity. Anticipating a similar demand pattern next year, FlexMan aims to end the year with 6,300 employees.

d) What is the optimal production, hiring, and layoff schedule? What is the cost of such a schedule?

e) If FlexMan could improve its training so new employees achieve full productivity right away, how much improvement in annual cost would the company see? How is the hiring and layoff policy during the year affected by this change?

Section III

FlexMan has identified a third party that is willing to produce routers and switches as needed. The third party will charge $6 per router and $4 per switch. Assume all other data as in Section I, except that hiring and layoffs are allowed as in Section II.

f) How should FlexMan use the third party if new employees provide only 50 percent productivity for the first two months? g) How should FlexMan use the third party if new employees are able to achieve full productivity right away?

h) Why does the use of the third party change with the productivity of new employees?

Section IV

Return to the FlexMan data in Section I. The company has signed a service-level agreement with its customers and committed to carry safety inventory from one month to the next that equals at least 15 percent of the following months demand. Thus, FlexMan is committed to carrying over at least

0.151,800,000=270,0000.151,800,000=270,000 routers and 0.151,600,000=240,0000.151,600,000=240,000

switches in inventory from December to January.

i) Assuming no backlogs, no subcontracting, no layoffs, and no new hires, what is the optimum production schedule for FlexMan? What is the annual cost of this schedule?

j) How much does the service contract mandating minimum inventories increase costs for FlexMan?

k) What would be the increase in cost if FlexMan agreed to a 15 percent minimum for switches but only a 5 percent minimum for routers? What would be the increase in cost if FlexMan agreed to only a 5 percent minimum for switches but a 15 percent minimum for routers? Which of the two is better for FlexMan?

Demand Forecast for Flex Man Router Demand Switch Demand Month January 1,800 1,600 February 1,600 1,400 March 2,600 1,500 April 2,500 2,000 May 800 1,500 June 1,800 900 July 1,200 700 August 1,400 800 September 2,500 1,400 October 2,800 1,700 November 1,000 800 December 1,000 900