Excel Homework for Logistics

Chapter 8: Aggregate Planning in a Supply Chain

1. Right click and save theRed Tomato Demoto your hard drive.

Using the original data in the file, what happened to inventory and stockouts over the six periods of the planning horizon?

2. Right click and save theRed Tomato Demo for Example 1to your hard drive.

What happened to stockouts as demand fluctuation increased? What happened to profits? What did you learn from this exercise?

3. Right click and save theRed Tomato Demo for Example 2to your hard drive.

What happened to the use of subcontractors as holding costs increased? What happened to profits? What did you learn from this exercise?

Note:Use the ?Solver? capability under the ?Tools? menu for the above three exercises.

Aggregate Planning (Chapter 8-9) Demand Forecast Month January February March April May June Demand Forecast 1,000 3,000 3,800 4,800 2,000 1,400 Costs Item Materials cost/unit Inventory holding cost/unit/month Marginal cost of stockout/unit/month Hiring and training cost/worker Layoff cost/worker Labor hours required/unit Regular time cost/hour Over time cost/hour Marginal subcontracting cost/unit Cost $ $ $ $ $ $ $ $ 10 2 5 300 500 4 4 6 30 Aggregate Plan Decision Variables Ht Period Lt # Hired 0 1 2 3 4 5 6 Wt # Laid off 0 0 0 0 0 0 0 Constraints 0 0 0 0 0 0 0 Ot It St # Workforce Overtime Inventory Stockout 80 0 1,000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 143 Ct Pt Subcontract 0 0 0 0 0 0 0 0 Production 0 0 0 0 0 0 0 0 Demand 0 0 0 0 0 0 Aggregate Plan Costs Period Hiring 1 2 3 4 5 6 Total Cost = Total Revenue = Profit = Promote? (0/1) Month (1/4) Base Price Consumption Forward buy Lay off 0 0 0 0 0 0 Regular time 0 0 0 0 0 0 $ $ 640,000 $ 640,000 $ 0 1 40 1.00 0.20 Overtime 0 0 0 0 0 0 0 0 0 0 0 0 Inventory Stockout 0 0 0 0 0 0 Subcontract 0 0 0 0 0 0 Material 0 0 0 0 0 0 0 0 0 0 0 0 1000 3000 3800 4800 2000 1400 Price 40 40 40 40 40 40 Workforce Capacity Inventory Over time -80 0 0 0 0 0 0 0 0 0 0 0 0 -3000 -3800 -4800 -2000 -1400 0 0 0 0 0 0 Aggregat e Plan 6,000 5,000 4,000 Inventory Production Demand Stockout Subcontracting 3,000 2,000 1,000 0 1 2 3 4 Period 5 6 Aggregate Planning for Red Tomato Demand Forecast Month January February March April May June Demand Forecast 1,600 3,000 3,200 3,800 2,200 2,200 16,000 Costs Item Materials cost/unit Inventory holding cost/unit/month Marginal cost of stockout/unit/month Hiring and training cost/worker Layoff cost/worker Labor hours required/unit Regular time cost/hour Over time cost/hour Marginal subcontracting cost/unit Cost $ $ $ $ $ $ $ $ 10 6 5 300 500 4 4 6 30 Aggregate Plan Decision Variables Ht Period Lt # Hired 0 1 2 3 4 5 6 Wt # Laid off 0 0 0 0 0 0 0 Constraints 0 0 0 0 0 0 0 Ot It St # Workforce Overtime Inventory Stockout 80 0 1,000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 143 Ct Pt Subcontract 0 0 0 0 0 0 0 0 Production 0 0 0 0 0 0 0 0 Demand 0 0 0 0 0 0 Aggregate Plan Costs Period Hiring 1 2 3 4 5 6 Total Cost = Total Revenue = Profit = Promote? (0/1) Month (1/4) Base Price Consumption Forward buy Lay off 0 0 0 0 0 0 Regular time 0 0 0 0 0 0 $ $ 640,000 $ 640,000 $ 0 1 40 1.00 0.20 Overtime 0 0 0 0 0 0 0 0 0 0 0 0 Inventory Stockout 0 0 0 0 0 0 Subcontract 0 0 0 0 0 0 Material 0 0 0 0 0 0 0 0 0 0 0 0 1600 3000 3200 3800 2200 2200 Price 40 40 40 40 40 40 Workforce Capacity Inventory Over time -80 0 0 0 0 0 0 0 0 0 0 0 -600 -3000 -3200 -3800 -2200 -2200 0 0 0 0 0 0 Aggregat e Plan 4,000 3,500 3,000 2,500 Inventory Production Demand Stockout Subcontracting 2,000 1,500 1,000 500 0 1 2 3 4 Period 5 6 Aggregate Planning Dat for Red Tomato Demand Forecast Month January February March April May June Demand Forecast 1,600 3,000 3,200 3,800 2,200 2,200 Costs Item Materials cost/unit Inventory holding cost/unit/month Marginal cost of stockout/unit/month Hiring and training cost/worker Layoff cost/worker Labor hours required/unit Regular time cost/hour Over time cost/hour Marginal subcontracting cost/unit Cost $ $ $ $ $ $ $ $ 10 2 5 300 500 4 4 6 30 Aggregate Plan Decision Variables Ht Period Lt # Hired 0 1 2 3 4 5 6 Wt # Laid off 0 0 0 0 0 0 0 Constraints 0 0 0 0 0 0 0 Ot It St # Workforce Overtime Inventory Stockout 80 0 1,000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 143 Ct Pt Subcontract 0 0 0 0 0 0 0 0 Production 0 0 0 0 0 0 0 0 Demand 0 0 0 0 0 0 Aggregate Plan Costs Period Hiring 1 2 3 4 5 6 Total Cost = Total Revenue = Profit = Promote? (0/1) Month (1/4) Base Price Consumption Forward buy Lay off 0 0 0 0 0 0 Regular time 0 0 0 0 0 0 $ $ 640,000 $ 640,000 $ 0 1 40 1.00 0.20 Overtime 0 0 0 0 0 0 0 0 0 0 0 0 Inventory Stockout 0 0 0 0 0 0 Subcontract 0 0 0 0 0 0 Material 0 0 0 0 0 0 0 0 0 0 0 0 1600 3000 3200 3800 2200 2200 Price 40 40 40 40 40 40 Workforce Capacity Inventory Over time -80 0 0 0 0 0 0 0 0 0 0 0 -600 -3000 -3200 -3800 -2200 -2200 0 0 0 0 0 0 Aggregat e Plan 4,000 3,500 3,000 2,500 Inventory Production Demand Stockout Subcontracting 2,000 1,500 1,000 500 0 1 2 3 4 Period 5 6