Add the following two constraints to your models.

• Workforce in period 12= Workforce in period 0

• Inventory in period 12= Inventory in period 0

THIS MUST BE COMPLETED USING MICROSOFT EXCEL

Explain in detail

Kelvin Ho, general manager at JPK, left the meeting with his strategic planning team wondering whether it made sense to replace the separate plants for planters and harvesters with a single plant that could assemble both. JPK Production Planning JPK assembled planters in Davenport, Iowa, and harvesters in Ames, Iowa. Demand for each product was highly seasonal, as shown in the following Table. Corn was generally planted between March and May and harvested between September and November. As a result, demand for planters peaked in March, whereas demand for harvesters peaked in September. Each plant aimed for a production plan that allowed them to meet annual demand at the lowest possible cost. 350 1100 1550 Demand Forecast for Planters and Harvesters Month Planters Harvesters January 850 350 February March 350 April 1050 350 May 800 350 June 350 450 July 350 750 August 350 1250 September 350 1750 October 350 950 November 350 700 December 550 350 The capacity of each plant was determined by the number of assembly workers available. Each machine (planter or harvester) required 100 labor hours for assembly. Each plant operated for 20 days a month, 8 hours a day. Assembly workers were paid $20/hour during regular time. They could be asked to work up to an additional 20 hours per month as overtime. Overtime was paid at the rate of $30/hour. Given the highly seasonal nature of demand, JPK laid off some workers during the low season and rehired them for the peak. Each layoff cost JPK $5,000 and each rehiring cost $3,000. Each plant generally built inventory in anticipation of the peak. It cost $300 to carry a machine in inventory from one month to the next. JPK could also choose to delay a customer order by stocking out in a given month and filling the stockout from next month's production. However, each unit delayed in this manner cost $2,000 in discounts offered to customers to keep them happy. The company had a policy of ensuring that there were no stock-outs in December so the new year started out without any unfilled orders. The material cost for each machine was $20,000. The planter plant ended December with 244 workers and 250 planters in inventory. The harvester plant ended December with 100 workers and 50 machines in inventory. The production plan at each plant attempted to meet demand at the lowest possible cost while ensuring that the plant ended December of the coming year with the same labor and inventory as the previous December. Options for New Plants Given the age of the current plants, Kelvin planned to replace them with new plants. One option was to replace each plant with a similar new plant. The other option was to expand the Davenport plant to allow for both planter and harvester assembly. The investment for the two options was going to be very similar. Thus, Kelvin requested his strategic planning team to identify any advantages of a single plant. The team pointed out that countercyclical nature of demand for planters and harvesters canceled their individual seasonality of demand to some extent. Common assembly steps for the two products would allow JPK to use the same labor for the two products. This had the potential to significantly reduce the number of employees laid off and rehired each year. Besides improving employee morale, such a change had the possibility of reducing costs. Question Quantify any cost related and employee related advantages offered by the single plant