Assignment # 2 The Acer Corporation has estimated the demand for its products for the upcoming year as: Month January February March April May June Working Days 22 19 21 22 21 21 Demand (in units) 8,000 12,000 18,000 20,000 28,000 25,000 Additional Problem Information: • • • • • • • • • • • • There are 100 employees on the payroll. Any change in employment must be accounted for in layoff or hiring costs. Productivity is 12 units per day per employee. Regular time salaries average $80 per day. Capacity can be increased up to an additional 25% through overtime. Units produced on overtime cost an additional $2 per unit. Units in inventory are charged at $2 per unit per month. Inventory shortages are charged at $10 per unit per month. Hiring and training an employee costs $300. Laying off an employee costs $200. Additional capacity is available through subcontracting at a cost of $8 per unit. The initial inventory level is 5,000 units. There should be at least 3,000 units in inventory at the end of June. Questions: 1. Formulate a minimum cost LP model to solve the above aggregate planning problem by clearly defining all the decision variables and writing out the objective function and constraints. 2. Develop a decision model in EXCEL and solve the problem. Explain the problem solution. Aggregate Planning What does aggregate planning involve? Aggregate planning and forecasting Single product vs. multi-product planning 1 Fundamental Tradeoffs in Aggregate Planning Capacity (regular time, over time, subcontract) Inventory Backlog / lost sales Basic Strategies Chase strategy Level strategy 2 Aggregate Planning Methods Simple spreadsheet models – Limited set of options – Sub-optimal solutions Optimization models – Linear and integer programming 3 Aggregate Planning at Red Tomato Tools Month January February March April May June Demand Forecast 1,600 3,000 3,200 3,800 2,200 2,200 4 Data Item Materials Inventory holding cost Marginal cost of a stockout Hiring and training costs Layoff cost Labor hours required Regular time cost Over time cost Cost of subcontracting Cost $10/unit $2/unit/month $5/unit/month $300/worker $500/worker 4/unit $4/hour $6/hour $30/unit 5 Aggregate Planning (Define Decision Variables) Wt = Workforce size for month t, t = 1, ..., 6 Ht = Number of employees hired at the beginning of month t, t = 1, ..., 6 Lt = Number of employees laid off at the beginning of month t, t = 1, ..., 6 Pt = Production in month t, t = 1, ..., 6 It = Inventory at the end of month t, t = 1, ..., 6 St = Number of units stocked out at the end of month t, t = 1, ..., 6 Ct = Number of units subcontracted for month t, t = 1, ..., 6 Ot = Number of overtime hours worked in month t, t = 1, ..., 6 6 Aggregate Planning (Define Objective Function)) 6 6 t =1 t =1 Min 640 W t +  300 H t 6 6 6 t =1 t =1 t =1 +  500 Lt +  6 Ot +  2 I t 6 6 6 t =1 t =1 t =1 +  5 S t + 10 Pt +  30 C t 7 Aggregate Planning (Define Constraints Linking Variables) Workforce size for each month is based on hiring and layoffs W t = W t −1 + H t − Lt, or W t − W t −1 − H t + Lt = 0 for t = 1,...,6, where W 0 = 80. 8 Aggregate Planning (Constraints) Production for each month cannot exceed capacity Pt  40 W t + Ot 4 , 40 W t + Ot 4 − Pt  0, for t = 1,...,6. 9 Aggregate Planning (Constraints) Inventory balance for each month I t −1 + Pt + C t = Dt + S t −1 + I t − S t , I t −1 + Pt + C t − Dt − S t −1 − I t + S t = 0, for t = 1,...,6,where I 0 = 1,000 , S 0 = 0,and I 6  500 . 10 Aggregate Planning (Constraints) Over time for each month Ot  10 W t, 10 W t − Ot  0, for t = 1,...,6. 11 Promotion Decisions Product margins Demand increase from discounting – Market growth – Stealing market share – Forward buying Discount of $1 increases period demand by 10% and moves 20% of next two months demand forward 12 Off-Peak (January) Discount from $40 to $39 Month January February March April May June Demand Forecast 3,000 2,400 2,560 3,800 2,200 2,200 Cost = $421,915, Revenue = $643,400, Profit = $221,485 13 Peak (April) Discount from $40 to $39 Month January February March April May June Demand Forecast 1,600 3,000 3,200 5,060 1,760 1,760 Cost = $438,857, Revenue = $650,140, Profit = $211,283 14 Optimal Promotion Decisions for Red Tomato Based on the given information it is optimal to offer a promotion in January (off-peak) than in April (peak) 15 Coordinated Decisions Pricing and Aggregate Planning must be done jointly Factors affecting discount timing – Product Margin: Impact of higher margin ($40 instead of $31) – Consumption: Changing fraction of increase in demand (100% increase in consumption instead of 10% increase) – Forward buy 16 Performance Under Different Scenarios Regular Price Promotion Promotion % Price Period Increase in Demand % Forward Buy Profit $40 $40 N/A NA NA $217,725 $40 $39 January 10% 20% $221,485 $40 $39 April 10% 20% $211,283 $40 $39 January 100% 20% $242,398 $40 $39 April 100% 20% $247,320 $31 $31 N/A NA NA $73,725 $31 $30 January 100% 20% $84,410 $31 $30 April 100% 20% $69,120 17 Factors Affecting Promotion Timing Factor Favored Timing High Forward Buying Low Demand Period High Growth of Market High Demand Period High Margin High Demand Period Low Margin Low Demand Period 18
Purchase answer to see full attachment