File name: Red Wagon AggPlan.xls
Red Wagons.com Sales and Operations planning model
Input data
Initial inventory of wagons 0
Initial number of workers 125
Regular hours/worker/month 100
Maximum overtime hours/worker/month 20
Hiring cost/worker $300
Firing cost/worker $1,000
Regular wages/worker/month $2,000
Overtime wage rate/hour $25
Labor hours/wagon 4
Raw material cost/wagon $40
Holding cost/wagon in inventory/month $20
Worker plan Month 1 Month 2 Month 3 Month 4
Workers from previous month 125
Workers hired
Workers fired
Workers available after hiring and firing
Regular-time hours available
Overtime labor hours used 0 0 0 0
= >= >= >=
Demand 1000 2500 5000 3500
Ending inventory
Summary of costs Month 1 Month 2 Month 3 Month 4 Totals
Hiring cost
Firing cost
Regular-time wages
Overtime wages
Raw material cost
Holding cost
Totals
SureStep Aggregate Planning Problem
Red Wagons.com Sales and Operations planning model Level Plan
Input data
Initial inventory of wagons 0
Initial number of workers 125
Regular hours/worker/month 100
Maximum overtime hours/worker/month 20
Hiring cost/worker $300
Firing cost/worker $1,000
Regular wages/worker/month $2,000
Overtime wage rate/hour $25
Labor hours/wagon 4
Raw material cost/wagon $40
Holding cost/wagon in inventory/month $20
Worker plan Month 1 Month 2 Month 3 Month 4
Workers from previous month 125 125 125 125
Workers hired 0 0 0 0
Workers fired 0 0 0 0
Workers available after hiring and firing 125 125 125 125
Regular-time hours available 12500 12500 12500 12500
Overtime labor hours used 0 0 0 0
= >= >= >=
Demand 1000 2500 5000 3500
Ending inventory 2000 2500 500 0
Summary of costs Month 1 Month 2 Month 3 Month 4 Totals
Hiring cost $0 $0 $0 $0 $0
Firing cost $0 $0 $0 $0 $0
Regular-time wages $250,000 $250,000 $250,000 $250,000 $1,000,000
Overtime wages $0 $0 $0 $0 $0
Raw material cost $120,000 $120,000 $120,000 $120,000 $480,000
Holding cost $40,000 $50,000 $10,000 $0 $100,000
Totals $410,000 $420,000 $380,000 $370,000 $1,580,000
Red Wagons.com Sales and Operations planning model Chase Plan
Input data
Initial inventory of wagons 0
Initial number of workers 125
Regular hours/worker/month 100
Maximum overtime hours/worker/month 20
Hiring cost/worker $300
Firing cost/worker $1,000
Regular wages/worker/month $2,000
Overtime wage rate/hour $25
Labor hours/wagon 4
Raw material cost/wagon $40
Holding cost/wagon in inventory/month $20
Worker plan Month 1 Month 2 Month 3 Month 4
Workers from previous month 125 40 100 200
Workers hired 0 60 100 0
Workers fired 85 0 0 60
Workers available after hiring and firing 40 100 200 140
Regular-time hours available 4000 10000 20000 14000
Overtime labor hours used 0 0 0 0
= >= >= >=
Demand 1000 2500 5000 3500
Ending inventory 0 0 0 0
Summary of costs Month 1 Month 2 Month 3 Month 4 Totals
Hiring cost $0 $18,000 $30,000 $0 $48,000
Firing cost $85,000 $0 $0 $60,000 $145,000
Regular-time wages $80,000 $200,000 $400,000 $280,000 $960,000
Overtime wages $0 $0 $0 $0 $0
Raw material cost $40,000 $100,000 $200,000 $140,000 $480,000
Holding cost $0 $0 $0 $0 $0
Totals $205,000 $318,000 $630,000 $480,000 $1,633,000
Red Wagons.com Sales and Operations planning model
Input data
Initial inventory of wagons 0
Initial number of workers 125
Regular hours/worker/month 100
Maximum overtime hours/worker/month 20
Hiring cost/worker $300
Firing cost/worker $1,000
Regular wages/worker/month $2,000
Overtime wage rate/hour $25
Labor hours/wagon 4
Raw material cost/wagon $40
Holding cost/wagon in inventory/month $20
Worker plan Month 1 Month 2 Month 3 Month 4
Workers from previous month 125 108 124 124
Workers hired 0 16 0 0
Workers fired 17 0 0 0
Workers available after hiring and firing 108 124 124 124
Regular-time hours available 10800 12400 12400 12400
Overtime labor hours used 0 0 0 0
= >= >= >=
Demand 1000 2500 5000 3500
Ending inventory 1700 2300 400 0
Summary of costs Month 1 Month 2 Month 3 Month 4 Totals
Hiring cost $0 $4,800 $0 $0 $4,800
Firing cost $17,000 $0 $0 $0 $17,000
Regular-time wages $216,000 $248,000 $248,000 $248,000 $960,000
Overtime wages $0 $0 $0 $0 $0
Raw material cost $108,000 $124,000 $124,000 $124,000 $480,000
Holding cost $34,000 $46,000 $8,000 $0 $88,000
Totals $375,000 $422,800 $380,000 $372,000 $1,549,800
Class Discussion Questions for March 22
Q1) What is the optimal Sales and Operations Plan for Red Wagon.com?
Q2) How does your answer to question 1 change if your warehouse capacity is
limited to a max of 1000 units?
Q3) Assuming a warehouse capacity of 1000 units, how much would you be willing to
spend for an extra 1unit of warehouse capacity from an outside vendor?