Assignment:
Sawyer Furniture is one of the few remaining domestic manufacturers of wood furniture. In the current competitive environment, cost containment is the key to its continued survival. Demand for furniture follows a seasonal demand pattern with increased sales in the summer and fall months, culminating with peak demand in November.
The cost of production is $16 per unit for regular production, $24 for overtime, and $33 for subcontracting. Hiring and firing costs are $500 per worker. Inventory holding costs are $20 per unit per month. There is no beginning inventory. Ten workers are currently employed. Each worker can produce 50 pieces of furniture per month. Overtime cannot exceed regular production. Given the following demand data, use Excel Solver to design an aggregate production plan for Sawyer Furniture that will meet demand at the lowest possible cost.
|
Input:
|
Beg. Wkrs
|
10
|
Regular
|
$16
|
Hiring
|
$500
|
|
|
|
Units/wkr
|
50
|
Overtime
|
$24
|
Firing
|
$500
|
|
|
|
Beg. Inv.
|
0
|
Subk
|
$33
|
Inventory
|
$20
|
|
|
|
Month
|
Demand
|
Reg
|
OT
|
Subk
|
Inv
|
#Wkrs
|
#Hired
|
#Fired
|
|
|
Jan
|
500
|
500
|
0
|
0
|
0
|
10
|
0
|
0
|
|
|
Feb
|
500
|
500
|
0
|
0
|
0
|
10
|
0
|
0
|
|
|
Mar
|
1000
|
1,000
|
0
|
0
|
0
|
20
|
10
|
0
|
|
|
Apr
|
1200
|
1,000
|
200
|
0
|
0
|
20
|
0
|
0
|
|
|
May
|
2000
|
1,000
|
1,000
|
0
|
0
|
20
|
0
|
0
|
|
|
Jun
|
400
|
400
|
0
|
0
|
0
|
8
|
0
|
12
|
|
|
Jul
|
400
|
400
|
0
|
0
|
0
|
8
|
0
|
0
|
|
|
Aug
|
1000
|
1,000
|
0
|
0
|
0
|
20
|
12
|
0
|
|
|
Sep
|
1000
|
1,000
|
0
|
0
|
0
|
20
|
0
|
0
|
|
|
Oct
|
1500
|
1,500
|
0
|
0
|
0
|
30
|
10
|
0
|
|
|
Nov
|
7000
|
3,500
|
3,500
|
0
|
0
|
70
|
40
|
0
|
|
|
Dec
|
500
|
500
|
0
|
0
|
0
|
10
|
0
|
60
|
|
|
Total
|
17,000
|
12,300
|
4,700
|
0
|
0
|
246
|
72
|
72
|