ABC Manufacturers produces different models of product P. The Operations Manager of ABC has to develop aggregate production plan for the next 6 months, April to September. Below given are the data for the aggregate unit of product P.

Month	Working Days	Demand
April	20	15000
May	24	7000
June	18	9000
July	26	12000
August	22	20000
September	15	14000

The Operations Manager found that there will be 130 workers working in March and 900 units in the inventory at the end of March. He wants to have 1400 units in the inventory at the end of September. A worker requires 2 hours to make one unit. It works 8 hours a day. Cannot hold more than 200 workers.

Hiring cost/worker $500

Firing cost/worker $1,000

Inventory holding cost $8/unit/month

Shortage (stockout) cost $15/unit/month

Regular labor cost $20/hour

Overtime labor cost $30/hour

Material cost $75/unit

Subcontract cost $140/unit

ABCs labor policy restricts the overtime production not to exceed 15% of regular production. Workers are paid full time payment. Develop aggregate production plan and find cost of the plan for ABC for the month April to September using the following methods: a. Pure Level (constant workforce with no overtime, subcontract, hire, or fire during the planning horizon) b. Pure Chase ((production = net demand) with no overtime, subcontract, shortage or inventory during the planning horizon. But inventory at the end of the planning horizon is accepted) c. Constant workforce of 125 and any excess requirement will be met by subcontracting (no overtime during the planning horizon) d. Optimal plan using excel i. Write down the mathematical model (Decision Variables, Objective Function, and Constraints). ii. Develop the excel model iii. Use excel solver to get the optimal plan.