SOLVE THIS IN AN EXCEL AND USE SOLVER TO SOLVE THIS BUT GIVE ME STEP BY STEP AND SHOW THE FORMULAS THAT YOU USE ALSO ANSWER THE QUESTIONS, THANKS.

A leading networking equipment contract manufacturer produces two product categories: routers

and switches. In consultation with customers the demand forecast for each category over the next

$12$ months $($in thousands of units$)$ are given below:

Manufacturing is primarily an assembly operation and the number of people on the production line

governs capacity. The plant operates $20$ days a month, $8$hours each day. Production of a router

Page $2$ of $3$

takes $20$ min. and production of a switch takes $10$ min. of worker time. Each worker is paid $$10$

per hour, with a $50$ percent premium for any overtime. The plant currently has $6300$ employees.

Overtime is limited to $20$ hours per employee per month. The plant currently maintains $100,000$

routers and $50,000$ switches in inventory. The cost of holding a router in inventory is $$3$ per month,

and the cost of holding a switch in inventory is $$1$ per month. The holding cost arises because

products are paid for by the customer at existing market rates when purchased. Thus, if the contract

manufacturer produces early and holds in inventory, the company recovers less given the rapidly

dropping component prices.

A$.$ Assuming no backlogs, no subcontracting, no layoffs and no new hires, what is the

optimum production scheduled for the contract manufacturer? What is the annual cost of

this schedule? What inventories does the optimal production schedule build?

B$.$ Is there any value for management to negotiate am increase of allowed overtime per

employee per month from $20$ hours to $40$ hours? What variables will be affected by this

change?