Please utilize Excel for this problem, and show all of the steps that were used to find the solutions (including the Solver properties used in Excel). Thank you!

Q4. (40pt) 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 the following Table. Monthly Demand doe Cell Phones, in Thousands Month Demand January 1,000 February 1,100 March 1,000 April 1,200 May 1,500 June 1,600 July 1,600 August September 1,100 October November 1,400 December 1,700 900 800 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 costs for each cell phone total 20 euros. Given the rapid decline in component and 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 is limited 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. You need to take screenshots of your excel files and insert screenshots in your pdf or word file report to show your analysis process when you submit your assignment solution on Canvas. Highlight your final solution in excel files and label everything (variables, parameters, objective function, and constraints) clearly. a) Formulate this problem as an aggregate planning problem using linear programming to minimize the total cost. Show your complete mathematical LP formulation including decision variable definition, objective function, and contains. b) Assuming no backlogs, no subcontracting, and no new hires, what is the optimum production schedule (including overtime, inventory, and production quantity for each month) in the aggregate plan? What is the annual cost of this schedule? Use Excel to solve your LP formulation in part (a). c) Is there any value for management to negotiate an increase of allowed overtime per employ per month from 20 hours to 40? d) Reconsider parts (a) and (b) if Skycell starts with only 1,200 employees. Reconsider parts (a) and (b) if Skycell starts with 1,300 employees. What happens to the value of additional overtime as the workforce size decreases? e) 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. Thus, monthly production including overtime should be no more than 1,291,667. What would be the cost of this level production schedule? What is the value of overtime flexibility? duction schedule? What is the value