Problem 2: Production Planning for UKea (50 marks): Over the next 6 months, UKea, a furniture company, must deliver 39,000 tables with the following schedule: The company can operate in either normal or overtime shifts in each month (or not operate at all), varying in cost and capacity as follows: The capacity of the firm is how many tables it can produce in a month and the fixed labor cost is paid regardless of the production quantity. That said, the manufacturing regulation is such that if we decide to produce any tables at any given month, at least 2,000 units should be produced in that month. To hire the extra help for running the company in overtime, it costs $15,000, which is paid each time the company switches from normal to overtime. There is no cost associated for switching back to normal or staying at overtime. Each table that is held in inventory would cost the company $2 per month for holding and handling. The company has 3,000 tables by the end of Dec (produced in normal capacity) and needs to have an inventory of at least 2,000 in July. Formulate an integer programming problem and solve it using Excel Solver. Make sure your model is linear and can be solved by Simplex LP solver. For example, using IF function is not allowed. 1. (25 marks) Clearly define your decision variables, objective function, and constraints. Write your complete mathematical model. 2. (15 marks) Implement your model in Excel. Make sure you Excel model is clear and easy to understand. 3. (10 marks) Solve your model in Excel. What is the optimal strategy? What is the optimal value of the objective function? Hint: At the beginning of each month, you should decide whether you will produce in normal or overtime or not operate at all. You must pay the switching cost only if you decide to operate in overtime at any certain month and in normal time in the preceding month. Problem 2: Production Planning for UKea (50 marks): Over the next 6 months, UKea, a furniture company, must deliver 39,000 tables with the following schedule: The company can operate in either normal or overtime shifts in each month (or not operate at all), varying in cost and capacity as follows: The capacity of the firm is how many tables it can produce in a month and the fixed labor cost is paid regardless of the production quantity. That said, the manufacturing regulation is such that if we decide to produce any tables at any given month, at least 2,000 units should be produced in that month. To hire the extra help for running the company in overtime, it costs $15,000, which is paid each time the company switches from normal to overtime. There is no cost associated for switching back to normal or staying at overtime. Each table that is held in inventory would cost the company $2 per month for holding and handling. The company has 3,000 tables by the end of Dec (produced in normal capacity) and needs to have an inventory of at least 2,000 in July. Formulate an integer programming problem and solve it using Excel Solver. Make sure your model is linear and can be solved by Simplex LP solver. For example, using IF function is not allowed. 1. (25 marks) Clearly define your decision variables, objective function, and constraints. Write your complete mathematical model. 2. (15 marks) Implement your model in Excel. Make sure you Excel model is clear and easy to understand. 3. (10 marks) Solve your model in Excel. What is the optimal strategy? What is the optimal value of the objective function? Hint: At the beginning of each month, you should decide whether you will produce in normal or overtime or not operate at all. You must pay the switching cost only if you decide to operate in overtime at any certain month and in normal time in the preceding month