Sheet1
Aggregate Planning
Demand Forecast
Month Demand Forecast
January 100,000
February 110,000
March 130,000
April 180,000
May 250,000
June 300,000
Costs
Item Cost
Materials cost/unit 12.00$
Inventory holding cost/unit/month 2.00$
Marginal cost of stockout/unit/month 10.00$
Hiring and training cost/worker 3,000.00$
Layoff cost/worker 5,000.00$
Labor hours required/unit 0.25
Regular time cost/hour 15.00$
Over time cost/hour 22.50$
Marginal subcontracting cost/unit 18.00$
Page 1
Pricing
Aggregate Plan Decision Variables
Constraints
HtLtWtOtItStCtPt
Period # Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand Price Workforce Capacity Inventory Overtime
0 0 0 300 0 50,000 0 0 0
1 0 8 292 0 0 0 0 50,000 100,000 50 0136880 011680
2 0 0 292 0 0 0 0 110,000 110,000 50 076880 011680
3 0 0 292 0 56,240 0 0 186,240 130,000 50 0640 011680
4 0.00 0 292 0 63,120 0 0 186,880 180,000 50 0 0 0 11680
5 0 0 292 0 0 0 0 186,880 250,000 50 0 0 0 11680
6 0 0 292 11,680 0 0 66,400 233,600 300,000 50 0 0 0 0
1669 24194 09486
Aggregate Plan Costs
Period Hiring Lay off Regular Time Overtime Inventory Stockout Subcontract Material
1 0 40,000 700,800 0 0 0 0 600,000
2 0 0 700,800 0 0 0 0 1,320,000
3 0 0 700,800 0 112,480 0 0 2,234,880
4 0 0 700,800 0 126,240 0 0 2,242,560
5 0 0 700,800 0 0 0 0 2,242,560
6 0 0 700,800 262,800 0 0 1,195,200 2,803,200
Total Cost = 17,384,720$
Total Revenue = 53,500,000$
Profit = 36,115,280$
Promote? (0/1) 0
Month (3/5) 5
Base Price 50$ Discount= 5.00$
Consumption 0.50
Forward buy 0.30
0
50,000
150,000
200,000
250,000
300,000
350,000
1 2 3 4 5 6
Period
Aggregate Plan
Inventory
Production
Demand
Stockout
Comparison at $5 discount
Ht Lt Wt Ot It St Ct Pt
# Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand Total Cost
Total Revenues
Total Profit
Base Case 31 19 2,050 25,000 50,000 0 50,000 970,000 1,070,000 17,490,000$ 53,500,000$ 36,010,000$
Sandra 0 0 2,100 24,000 149,000 0 45,000 1,040,000 1,135,000 18,546,000$ 55,130,000$ 36,584,000$
Bill 0 19 1,988 18,750 50,000 0 225,000 905,000 1,180,000 19,745,625$ 57,425,000$ 37,679,375$
Comparison at $10 discount
Ht Lt Wt Ot It St Ct Pt
# Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand Total Cost
Total Revenues
Total Profit
Base Case 31 19 2,050 25,000 50,000 0 50,000 970,000 1,070,000 17,490,000$ 53,500,000$ 36,010,000$
Sandra 0 0 2,100 24,000 149,000 0 45,000 1,040,000 1,135,000 18,546,000$ 53,510,000$ 34,964,000$
Bill 0 19 1,988 18,750 50,000 0 225,000 905,000 1,180,000 19,745,625$ 55,100,000$ 35,354,375$
Comparison at $5 discount and $22 marginal subcontracting Costs
Ht Lt Wt Ot It St Ct Pt
# Hired # Laid off # Workforce Overtime Inventory Stockout Subcontract Production Demand Total Cost
Total Revenues
Total Profit
Base Case 94 19 2,175 17,500 50,000 0 0 1,020,000 1,070,000 17,508,750$ 53,500,000$ 35,991,250$
Sandra 30 0 2,165 25,294 168,111 0 0 1,085,000 1,135,000 18,626,486$ 55,130,000$ 36,503,514$
Bill 94 0 2,383 15,778 246,444 0 0 1,130,000 1,180,000 20,306,416$ 57,425,000$ 37,118,584$
Sheet4
Sucontractor Price % Growth in Consumption
16 24
17 24.5
18 25.5
19 28
20 30.5
21 30.5
22 31.5
Subcontractor price does
No Limit on Subcontractor Capacity
Discount Offered % Growth in Consumption
525
633
741
849
957
10 65
Subcontractor capacity limited to 50,000
Discount Offered % Growth in Consumption
529
637
744
852
961
10 74
50
60
70
80
25
57
65
0
10
20
50
60
70
80
45678910 11
% Growth in Consumption
Discount offered
Implement Bill’s Plan
37
44
52
61
74
40
50
60
70
80
Implement Bill’s Plan
Page 5
Sheet4
Discount Offered