Tables 8-2, 8-3
Aggregate Planning (Chapter 8-9)
Demand Forecast
Month Demand Forecast
January 1,600
February 3,000
March 3,200
April 3,800
May 2,200
June 2,200
Costs
Item Cost
Materials cost/unit 10$
Inventory holding cost/unit/month 2$
Marginal cost of stockout/unit/month 5$
Hiring and training cost/worker 300$
Layoff cost/worker 500$
Labor hours required/unit 4
Regular time cost/hour 4$
Over time cost/hour 6$
Marginal subcontracting cost/unit 30$
Page 1
Planning
Aggregate Plan Decision Variables
HtLtWtOtItStCtPt
Period # Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand
0 0 0 80 0 1,000 0 0
1 0 16 64 0 1,960 0 0 2,560 1,600 640
2 0 0 64 0 1,520 0 0 2,560 3,000 640
3 0 0 64 0880 0 0 2,560 3,200 640
4 0 0 64 0 0 220 140 2,560 3,800 640
5 0 0 64 0140 0 0 2,560 2,200 640
6 0 0 64 0500 0 0 2,560 2,200 640
Total Cost = 422,660$
Base Price 40$
Total Revenue =
640,000$ Promote? (0/1) 0 Consumption 0.10
Profit = 217,340$ Month (1/4) 1 Forward buy 0.20
Max.
Overtime
Available
Chapter 8
Set Cell E14 to 0 (there is no
promotion)
1. To get Table 8-4, enter
appropriate values in Cells
B5:B10, C5:C10, E5:E10,
H5:H10.
2. To get Table 8-6, change
Cells B6-B11 in sheet Tables 8–
2, 8-3 to be as shown in Table
8-5 and then enter appropriate
values in Cells B5:B10, C5:C10,
Chapter 9
1. To get Figure 9-1, set Cell E14 to 0 and enter appropriate values in Cells B5:B10, C5:C10,
E5:E10, H5:H10.
2. To get Figure 9–2, set Cell E14 to 1 (promotion on) and Cell E15 to 1 (January promotion),
H14 to 0.1, H15 to 0.2 and enter appropriate values in Cells B5:B10, C5:C10, E5:E10, H5:H10.
3. To get Figure 9–3, set Cell E14 to 1 (promotion on) and Cell E15 to 4 (April promotion), H14
to 0.1, H15 to 0.2 and enter appropriate values in Cells B5:B10, C5:C10, E5:E10, H5:H10.
4. To get Figure 9–4, set Cell E14 to 1 (promotion on) and Cell E15 to 1 (January promotion),
H14 to 1.0, H25 to 0.2 and enter appropriate values in Cells B5:B10, C5:C10, E5:E10, H5:H10.
5. To get Figure 9–5, set Cell E14 to 1 (promotion on) and Cell E15 to 1 (April promotion), H14
to 1.0, H25 to 0.2 and enter appropriate values in Cells B5:B10, C5:C10, E5:E10, H5:H10.
0
500
4,000
1 2 3 4 5 6
Period
Aggregate Plan
Figure 9-2
Optimal Aggregate Plan When Discounting Price in January to $39
HtLtWtOtItStCtPt
Period # Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand
0 0 0 80 0 1,000 0 0
1 0 15 65 0600 0 0 2,600 3,000 650
2 0 0 65 0800 0 0 2,600 2,400 650
3 0 0 65 0840 0 0 2,600 2,560 650
4 0 0 65 0 0 300 60 2,600 3,800 650
5 0 0 65 0100 0 0 2,600 2,200 650
6 0 0 65 0500 0 0 2,600 2,200 650
Total Cost = 422,080$
Base Price 40$
Total Revenue =
643,400$ Promote? (0/1) 1 Consumption 0.10
Profit = 221,320$ Month (1/4) 1 Forward buy 0.20
Max.
Overtime
Available
Chapter 9
2. To get Figure 9–2, set Cell E14 to 1 (promotion on) and Cell E15 to 1 (January promotion),
H14 to 0.1, H15 to 0.2 and enter appropriate values in Cells B5:B10, C5:C10, E5:E10, H5:H10.
Page 4
Figure 9-3
Optimal Aggregate Plan When Discounting Price in April to $39
HtLtWtOtItStCtPt
Period # Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand
0 0 0 80 0 1,000 0 0
1 0 14 66 0 2,040 0 0 2,640 1,600 660
2 0 0 66 0 1,680 0 0 2,640 3,000 660
3 0 0 66 0 1,120 0 0 2,640 3,200 660
4 0 0 66 0 0 1,260 40 2,640 5,060 660
5 0 0 66 0 0 380 0 2,640 1,760 660
6 0 0 66 0500 0 0 2,640 1,760 660
Total Cost = 438,920$
Base Price 40$
Total Revenue =
650,140$ Promote? (0/1) 1 Consumption 0.10
Profit = 211,220$ Month (1/4) 4 Forward buy 0.20
Max.
Overtime
Available
Chapter 9
3. To get Figure 9–3, set Cell E14 to 1 (promotion on) and Cell E15 to 4 (April promotion), H14
to 0.1, H15 to 0.2 and enter appropriate values in Cells B5:B10, C5:C10, E5:E10, H5:H10.
Page 5
Figure 9-4
Optimal Aggregate Plan When Discounting Price inJanuary to $39 with Large Increase in Demand
HtLtWtOtItStCtPt
Period # Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand
0 0 0 80 0 1,000 0 0
1 0 0 80 0 0 140 100 3,200 4,440 800
2 0 11 69 0220 0 0 2,760 2,400 690
3 0 0 69 0420 0 0 2,760 2,560 690
4 0 0 69 0 0 620 0 2,760 3,800 690
5 0 0 69 0 0 60 0 2,760 2,200 690
6 0 0 69 0500 0 0 2,760 2,200 690
Total Cost = 456,880$
Base Price 40$
Total Revenue =
699,560$ Promote? (0/1) 1 Consumption 1.00
Profit = 242,680$ Month (1/4) 1 Forward buy 0.20
Max.
Overtime
Available
Chapter 9
4. To get Figure 9–4, set Cell E14 to 1 (promotion on) and Cell E15 to 1 (January promotion),
H14 to 1.0, H25 to 0.2 and enter appropriate values in Cells B5:B10, C5:C10, E5:E10, H5:H10.
Figure 9-5
Optimal Aggregate Plan When Discounting Price in April to $39 with Large Increase in Demand
HtLtWtOtItStCtPt
Period # Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand
0 0 0 80 0 1,000 0 0
1 0 0 80 0 2,600 0 0 3,200 1,600 800
2 0 0 80 0 2,800 0 0 3,200 3,000 800
3 0 0 80 0 2,800 0 0 3,200 3,200 800
4 0 0 80 0 0 2,380 100 3,200 8,480 800
5 0 0 80 0 0 940 0 3,200 1,760 800
6 0 0 80 0500 0 0 3,200 1,760 800
Total Cost = 536,200$
Base Price 40$
Total Revenue =
783,520$ Promote? (0/1) 1 Consumption 1.00
Profit = 247,320$ Month (1/4) 4 Forward buy 0.20
Max.
Overtime
Available
Chapter 9
5. To get Figure 9–5, set Cell E14 to 1 (promotion on) and Cell E15 to 1 (April promotion), H14
to 1.0, H25 to 0.2 and enter appropriate values in Cells B5:B10, C5:C10, E5:E10, H5:H10.
Sheet2
Aggregate Plan
Period # Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand
0 0 0 80 1,000 0 0
1 0 18 62 01,880 0 0 2,480 1,600
2 0 0 62 01,360 0 0 2,480 3,000
3 0 0 62 620 795 0 0 2,635 3,200
4 0 0 62 620 0370 02,635 3,800
5 0 0 62 620 65 0 0 2,635 2,200
6 0 0 62 620 500 0 0 2,635 2,200
Aggregate Plan Costs
Period Hiring Lay off Regular Time Overtime Inventory Stockout Subcontract
1 0 9000 39680 03760 0 0
2 0 0 39680 02720 0 0
3 0 0 39680 01590 0 0
4 0 0 39680 0 0 1850 0
5 0 0 39680 0130 0 0
6 0 0 39680 01000 0 0
Total Cost = 258130
Sheet3
Aggregate Plan Decision Variables
Constraints
Period # Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand Workforce Production Inventory Over time
0 0 0 80 0 1,000 0 0
1 0 29 51 0 1,450 0 0 2,050 1,600 0 0 0 513
2 0 0 51 0500 0 0 2,050 3,000 0 0 0 513
316 068 0 0 0 0 2,700 3,200 0 0 0 675
4 0 0 68 0 0 500 600 2,700 3,800 0 0 0 675
5 0 0 68 0 0 0 0 2,700 2,200 0 0 0 675
6 0 0 68 0500 0 0 2,700 2,200 0 0 0 675
Aggregate Plan Costs
Period Hiring Lay off
Regular Time
Overtime Inventory Stockout Subcontract Material
1 0 14,375 32,800 0 2,900 0 0 20,500
2 0 0 32,800 0 1,000 0 0 20,500
3 0 0 43,200 0 0 0 0 27,000
4 0 0 43,200 0 0 2,500 18,000 27,000
5 0 0 43,200 0 0 0 0 27,000
6 0 0 43,200 0 1,000 0 0 27,000
Total Cost = 427,175$
Total Revenue = 496,000$