Homework SourseProblem 2 Sawyer Furniture is one of the few remaining domes
Problem 2.
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.
Use Excel solver and list contraints.
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
| 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 |
Solution
Month Demand Reg OT Subk Inv #Wkrs #Hired #Fired Cost 1 Jan 500 500 0 0 0 10 0 0 $8,000 Feb 500 500 0 0 0 10 0 0 $8,000 Mar 1000 1,000 0 0 0 20 10 0 $21,000 Apr 1200 1,000 200 0 0 20 0 0 $20,800 May 2000 1,000 1,000 0 0 20 0 0 $40,000 Jun 400 400 0 0 0 8 0 12 $12,400 Jul 400 400 0 0 0 8 0 0 $6,400 Aug 1000 1,000 0 0 0 20 12 0 $22,000 Sep 1000 1,000 0 0 0 20 0 0 $16,000 Oct 1500 1,500 0 0 0 30 10 0 $29,000 Nov 7000 3,500 3,500 0 0 70 40 0 $160,000 Dec 500 500 0 0 0 10 0 60 $38,000 Total 17,000 12,300 4,700 0 0 246 72 72 $381,600
