1. For a level output/workforce production plan with the current level of permanent workforce, 140, using inventory to absorb the uneven forecast demand but allowing some backorder, the following aggregate production plan can be developed:

Month 1 2 3 4 5 6 Total

Forecast demand 2000 2000 3000 4000 5000 2000 18000 Production 2800 2800 2800 2800 2800 2800 16800 Backorder 0 0 200 1200 2200 200 3800 Inventory 1000 1800 2600 2000 0 800 9200

The total cost of this plan is $2,286,000.

2. For a production plan that uses overtime to make up for the shortage and desires an ending inventory of 1000 units at the end of the sixth month, the following aggregate production plan can be developed:

Month 1 2 3 4 5 6 Total

Forecast demand 2000 2000 3000 4000 5000 2000 18000 Production 2800 2800 2800 3200 4000 2400 18000 Overtime 0 0 0 400 1200 0 1600 Inventory 1000 1800 2600 2200 1800 1000 10800

The total cost of this plan is $2,263,000, which is slightly lower than the cost of plan 1.

3. For a production plan that uses temporary workers during months of high demand and desires an ending inventory of 1000 units at the end of the sixth month, the following aggregate production plan can be developed:

Month 1 2 3 4 5 6 Total

Forecast demand 2000 2000 3000 4000 5000 2000 18000 Production 2800 2800 2800 3200 4000 2400 18000 Temp workers 0 0 0 0 800 0 800 Inventory 1000 1800 2600 2200 2400 1600 12000

The total cost of this plan is $2,225,000, which is the lowest of all three plans.

please convert it into excel solution