aggregate planning linear programming - use excel solver

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

Planning

Aggregate Plan Decision Variables												Constraints
	Ht	Lt	Wt	Ot	It	St	Ct	Pt
Period	# Hired	# Laid off	# Workforce	Overtime	Inventory	Stockout	Subcontract	Production	Demand	Price		Workforce	Capacity	Inventory	Over time
0	0	0	80	0	1,000	0	0
1	0	16	64	0	1,960	0	0	2,560	1,600	40		0	0	-0	640
2	0	0	64	0	1,520	0	0	2,560	3,000	40		-0	0	-0	640
3	0	0	64	0	880	0	0	2,560	3,200	40		0	0	0	640
4	0	0	64	0	0	220	140	2,560	3,800	40		0	0	0	640
5	0	0	64	0	140	0	0	2,560	2,200	40		0	0	-0	640
6	0	0	64	0	500	0	0	2,560	2,200	40		0	0	0	640
				0	857	31	20
Aggregate Plan Costs
Period	Hiring	Lay off	Regular time	Overtime	Inventory	Stockout	Subcontract	Material
1	0	8,000	40,960	0	3,920	0	0	25,600
2	0	0	40,960	0	3,040	0	0	25,600
3	0	0	40,960	0	1,760	0	0	25,600
4	0	0	40,960	0	0	1,100	4,200	25,600
5	0	0	40,960	0	280	0	0	25,600
6	0	0	40,960	0	1,000	0	0	25,600
Total Cost =		$ 422,660
					Base Price		$ 40
Total Revenue =		$ 640,000	Promote? (0/1)	0	Consumption		0.10
Profit =		$ 217,340	Month (1/4)	4	Forward buy		0.20

Chapter 8 Set Cell E24 to 0 (there is no promotion) 1. To get Table 8-4, run Solver as is. 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 run Solver. 3. To get Table 8-7, return Cells B6-B11 to as they are in Table 8-2 and change Cells B19 and B20 in worksheet Tables 8-2, 8-3 to 50 each.

Chapter 9 1. To get Figure 9-1, set Cell E24 to 0 and run Solver. 2. To get Figure 9-2, set Cell E24 to 1 (promotion on) and Cell E25 to 1 (January promotion), H24 to 0.1, H25 to 0.2 and run Solver. 3. To get Figure 9-3, set Cell E24 to 1 (promotion on) and Cell E25 to 4 (April promotion), H24 to 0.1, H25 to 0.2 and run Solver. 4. To get Figure 9-4, set Cell E24 to 1 (promotion on) and Cell E25 to 1 (January promotion), H24 to 1.0, H25 to 0.2 and run Solver. 5. To get Figure 9-5, set Cell E24 to 1 (promotion on) and Cell E25 to 1 (April promotion), H24 to 1.0, H25 to 0.2 and run Solver.

Plan Chart

Aggregate Plan

Inventory 1959.9999999999995 1519.9999999999991 879.99999999999841 0 140.0000000000006 500 Production 2559.9999999999986 2559.9999999999986 2560 2560 2559.9999999999991 2559.9999999999995 Demand 1600 3000 3200 3800 2200 2200 Stockout 0 0 0 219.99999999999849 0 0 Subcontracting 0 0 0 140.00000000000355 0 0

Period