I WILL UPVOTE IF YOU ANSWER CORRECTLY!!!!!!

The anxwermust be completed in Microsoft Excel. Please send excel screenshots. Answers to the questions must be written in separate worksheet tab. Plz READ QUESTIONS CAREFULLY THEY ARE DIFFERENT FROM OTHER CHEGG ANSWERS!! ANSWER CORRECTLY AND DO NOT COPY FROM OTHER CHEGG ANSWERS and don't copy from the internet or other chegg answers. And show the equations you used on excel and explain the answers. THANKS <3

 Note: 

Add the following two constraints to your models.

• Workforce in period 12= Workforce in period 0

• Inventory in period 12= Inventory in period 0

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.

Demand Forecast for Planters and Harvesters
Month	Planters	Harvesters
January	850	350
February	1100	350
March	1550	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 months 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.