Precision Transfer, Inc. makes more than 300 types of precision gears. The operations to make the different types are similar, but the time and materials
Precision Transfer, Inc. makes more than 300 types of precision gears. The operations to make the different types are similar, but the time and materials required are different. Because all the gears made by Precision are similar, the aggregation unit for planning purposes is a gear. Forecasts for the aggregate demand of gears are given below. Month Forecast Demand January 2700, February 3000, March 4000, April 4500, May 3200, June 2900, Total 20,300 From historical data, it was determined that a worker can make 3.8 gears/day. Production costs do not change over the planning horizon and include worker wages and benefits. A unit produced but not sold in a month is counted as inventory for the entire month (end-of-month) inventory. The following summarizes the important cost and workforce information: Production Costs $30/unit Inventory holding costs $5/unit/month Backordering cost $15/unit/month Worker hiring costs $4500/worker Worker layoff costs $6000/worker Worker wages and benefits $8/hour Working day 8 hours/day Beginning workforce level 35 workers.
1. When backorder is not allowed, add an appropriate constraint in the spreadsheet and find the optimal solution. Please include a snapshot of the constraint you add, list the optimal solution including the decision variables and corresponding minimized total cost.
2. Assuming that backorder is not allowed, add an appropriate constraint that forces the factory to maintain a minimum buffer inventory of 100 gears each month. Please include a snapshot of the constraint you add, list the optimal solution including the decision variables and corresponding minimized total cost.
3. Assuming that backorder is not allowed, add an appropriate constraint that forces the factory to maintain a minimum buffer inventory of 100 gears each month. If you are using level strategy, what needs to be changed in the given spreadsheet? Please attach a snapshot of the new spreadsheet with the changed number(s) bolded.
AGGREGATE PLANNING EXAMPLE (9/24/2020) Precision Transfer, Inc. makes more than 300 types of precision gears. The operations to make the different types are similar, but the time and materials required are different. Because all the gears made by Precision are similar, the aggregation unit for planning purposes is a gear. Forecasts for the aggregate demand of gears are given below. Forecast Month Demand January 2700 February 3000 March 4000 April 4500 May 3200 June 2900 Total 20,300 From historical data, it was determined that a worker can make 3.8 gears/day. Production costs do not change over the planning horizon and include worker wages and benefits. A unit produced but not sold in a month is counted as inventory for the entire month (end-of-month) inventory. The following summarizes the important cost and workforce in e information: Production Costs Inventory holding costs Backordering cost Worker hiring costs Worker layoff costs Worker wages and benefits Working day Beginning workforce level $30/unit $3/unit month SIS/unit/month $4500 worker $6000 worker S8 hour 8 hoursday 35 workers With this information, develop an aggregate production plan using chase, level, and Linear Programming Profit Maximization Model Chase strategy Chase Strategy (include ending inventory in planning) K- gears day Days Forecast less OH Iny Units/Wkr per Month Wkrs Required Units Produced per Month Hires Lay Offs Wkrs (rounded) 35 34 40 Jan 0 6 Feb Forecasted Demand D 2700 3000 4000 4500 3200 2900 20300 1 0 0 46 2700 29868 3946.8 4426.4 31576 2880.8 21 20 23 21 22 22 Mar Apr May Jun Total 79.8 76 87.4 79.8 83.6 83.6 Ending Inventory 0 132 532 73.6 42.4 192 452 246 33.83 39.30 45.16 55.47 37.77 34.46 2713.2 30-40 4020.4 4468.8 3176.8 2926 203452 56 38 35 0 6 10 0 0 22 22 Cost of Strategy Units 203452 22 Production Costs Hiring Costs Lavoff Costs Inv Holding Costs Backorder Costs Cost (per unit) 30.00 4,500.00 6,000.00 5.00 15.00 Subtotal S610,356.00 $99.000.00 S132,000.00 S1,234.00 $0.00 5842.590.00 2468 unit cost subtotal Cost (per day) days hourly worker costs 5354 $342.656.00 $342.656.00 hourly cost subtotal Total Cost S1,185,246.00 S1.185, 246.00 Level Strategy (No backorder allowed) Constant Workforce (with no backorders) K- rears day Forecas Cummulati ted ve Days Cum Units Wer Deman per Forecast Month Days Cum Workers Needed round W Select Ut Prodas Ending Invent Backor Lay On Jer Moth Holdi ng 35 2700 2700 21 21 79.8 798 14 44 35112 9 0 Januar Y Februa DY 8112 0 3000 5700 20 41 76 155 37 46 3344 8112 1155 2 1000 O 0 11552 0 9700 64 243.2 0 March April May 4000 4500 3200 23 21 874 798 40 44 43 44 44 44 38-45.6 35112 0 0 0 O 1000 12 4904 0 0 17400 107 836 4066 0 lol lol June 2900 20300 22 129 83.6 42 12 490.4 1268 3 4738 4 44 . 3678.4 21568 1268.3 0 Total 20000 4902 47384 0 max workers in month 44 Cost of Strategy Production Costs Hiring Costs Layoff Costs Inv Holding Costs Backorder Costs Cost per unit) Units 30,00 21568.8 4.500.00 9 6.000,00 0 5.00 4738.4 15.00 0 Subtotal $64706400 S-40, 50000 $0.00 $23.692.00 $0.00 unit co subtotal S711.256.00 Cost per day) hourly worker 8.00 45408 $363 26400 hourly cost subtotal Total Cost $363,264.00 $1.074.520.00 Linear program (Profit Max) Given Parameters Profit Maximization Strategy Solver Variables LP Variables K gears/day Ending Inventory 3.8 Forecaste d Days Units kr per Month PE Productio t Hire LE Lay Ons W! Worker Holdin 1- Backord er Demand per Month 35 38 42 42 4 Jan 2700 Feb 3000 Mar 4000 4500 May 3200 Jun 2900 Total 20300 Cost of Strategy 21 20 23 21 22 22 798 76 87.4 798 83.6 83.6 0 0 0 0 30324 3192 3670.8 33516 35112 3594.8 20352.8 Apr ole 332.4 524.4 195.2 0 0 528 1104.8 0 0 0 9532 642 0 42 43 0 1 8 0 0 1595.2 Profit Maximization Strate Costs 30.00 Units 203528 Subtotal S610.584,00 4,500.00 8 $36,000.00 Production Costs Hiring Costs Layoff Costs Iny Holding Costs Backorder Costs 6,000.00 5.00 15.00 0 11048 1595.2 SO 00 $5.524.00 $23.928,00 unit cost subtotal 5676,036,00 Cost (per day (hr) 64.00 days 5356 hourly worker costs $342,784,00 hourly cost subtotal $342.784.00 $1,018.820. Total Cost What if no backorder is allowed? What constraints are needed? AGGREGATE PLANNING EXAMPLE (9/24/2020) Precision Transfer, Inc. makes more than 300 types of precision gears. The operations to make the different types are similar, but the time and materials required are different. Because all the gears made by Precision are similar, the aggregation unit for planning purposes is a gear. Forecasts for the aggregate demand of gears are given below. Forecast Month Demand January 2700 February 3000 March 4000 April 4500 May 3200 June 2900 Total 20,300 From historical data, it was determined that a worker can make 3.8 gears/day. Production costs do not change over the planning horizon and include worker wages and benefits. A unit produced but not sold in a month is counted as inventory for the entire month (end-of-month) inventory. The following summarizes the important cost and workforce in e information: Production Costs Inventory holding costs Backordering cost Worker hiring costs Worker layoff costs Worker wages and benefits Working day Beginning workforce level $30/unit $3/unit month SIS/unit/month $4500 worker $6000 worker S8 hour 8 hoursday 35 workers With this information, develop an aggregate production plan using chase, level, and Linear Programming Profit Maximization Model Chase strategy Chase Strategy (include ending inventory in planning) K- gears day Days Forecast less OH Iny Units/Wkr per Month Wkrs Required Units Produced per Month Hires Lay Offs Wkrs (rounded) 35 34 40 Jan 0 6 Feb Forecasted Demand D 2700 3000 4000 4500 3200 2900 20300 1 0 0 46 2700 29868 3946.8 4426.4 31576 2880.8 21 20 23 21 22 22 Mar Apr May Jun Total 79.8 76 87.4 79.8 83.6 83.6 Ending Inventory 0 132 532 73.6 42.4 192 452 246 33.83 39.30 45.16 55.47 37.77 34.46 2713.2 30-40 4020.4 4468.8 3176.8 2926 203452 56 38 35 0 6 10 0 0 22 22 Cost of Strategy Units 203452 22 Production Costs Hiring Costs Lavoff Costs Inv Holding Costs Backorder Costs Cost (per unit) 30.00 4,500.00 6,000.00 5.00 15.00 Subtotal S610,356.00 $99.000.00 S132,000.00 S1,234.00 $0.00 5842.590.00 2468 unit cost subtotal Cost (per day) days hourly worker costs 5354 $342.656.00 $342.656.00 hourly cost subtotal Total Cost S1,185,246.00 S1.185, 246.00 Level Strategy (No backorder allowed) Constant Workforce (with no backorders) K- rears day Forecas Cummulati ted ve Days Cum Units Wer Deman per Forecast Month Days Cum Workers Needed round W Select Ut Prodas Ending Invent Backor Lay On Jer Moth Holdi ng 35 2700 2700 21 21 79.8 798 14 44 35112 9 0 Januar Y Februa DY 8112 0 3000 5700 20 41 76 155 37 46 3344 8112 1155 2 1000 O 0 11552 0 9700 64 243.2 0 March April May 4000 4500 3200 23 21 874 798 40 44 43 44 44 44 38-45.6 35112 0 0 0 O 1000 12 4904 0 0 17400 107 836 4066 0 lol lol June 2900 20300 22 129 83.6 42 12 490.4 1268 3 4738 4 44 . 3678.4 21568 1268.3 0 Total 20000 4902 47384 0 max workers in month 44 Cost of Strategy Production Costs Hiring Costs Layoff Costs Inv Holding Costs Backorder Costs Cost per unit) Units 30,00 21568.8 4.500.00 9 6.000,00 0 5.00 4738.4 15.00 0 Subtotal $64706400 S-40, 50000 $0.00 $23.692.00 $0.00 unit co subtotal S711.256.00 Cost per day) hourly worker 8.00 45408 $363 26400 hourly cost subtotal Total Cost $363,264.00 $1.074.520.00 Linear program (Profit Max) Given Parameters Profit Maximization Strategy Solver Variables LP Variables K gears/day Ending Inventory 3.8 Forecaste d Days Units kr per Month PE Productio t Hire LE Lay Ons W! Worker Holdin 1- Backord er Demand per Month 35 38 42 42 4 Jan 2700 Feb 3000 Mar 4000 4500 May 3200 Jun 2900 Total 20300 Cost of Strategy 21 20 23 21 22 22 798 76 87.4 798 83.6 83.6 0 0 0 0 30324 3192 3670.8 33516 35112 3594.8 20352.8 Apr ole 332.4 524.4 195.2 0 0 528 1104.8 0 0 0 9532 642 0 42 43 0 1 8 0 0 1595.2 Profit Maximization Strate Costs 30.00 Units 203528 Subtotal S610.584,00 4,500.00 8 $36,000.00 Production Costs Hiring Costs Layoff Costs Iny Holding Costs Backorder Costs 6,000.00 5.00 15.00 0 11048 1595.2 SO 00 $5.524.00 $23.928,00 unit cost subtotal 5676,036,00 Cost (per day (hr) 64.00 days 5356 hourly worker costs $342,784,00 hourly cost subtotal $342.784.00 $1,018.820. Total Cost What if no backorder is allowed? What constraints are needed
