Question

Develop a production schedule to produce the exact production requirements by varying the workforce size for the following problem The monthly forecasts for Product X for January February, and March 1010, 1510 and 1180 respectively. Safety stock policy recommends that half of the forecast for that month be defined as safety stock. There are 22 working days in January, 19 in February, and 21 in March. Beginning inventory is 530 units Manufacturing cost is $180 por unit, storage cost is 36 per unit per month standard pay rate is $6 per hour overtime rate is $9 por hour cost of stockout is 39 per unit per month, hiring and training costis $210 per worker, layoff cost is $310 per worker, and worker productivity is 0.1 unit per hour Assume that you start off with 42 workers and that they work 8 hours per day. Leave no cells blank be certain to enter"0" wherever required. Input all values as positive values. Round Workers Required up to next higher whole number. Round all other variables to nearest whole number) Safety stock Beginning inventory Net production required Workers required Hired Laid of Actual production Ending inventory Inventory Hiring Layon Total

Answer 1

March 21 1180 590 February 19 1540 770 506 1804 119 63 775 January 22 1010 505 530 530 985 42 56 14 O 986 506 995 60 1 2 Working Days 3 Forecast 4 Safety Stock 5 Beginning Inventory 6 Net Production Required 7 Workers Required 8 Hired 9 Laid off 10 Actual Production 11 Ending Inventory 12 13 Labour 14 Inventory Hiring Layoff Total 18 Total Cost 0 0 59 1008 603 1809 775 $108,540.0 $4,650 $13,230 15 $6 $59, 160.0 $6 $3,036 $210 $2,940 $310 $0 $65,136.0 $273,944.0 $60,480.0 $3,618 $0 $18.290 $82,388.0 $0 $126,420.0

121 =B5 1 2 Working Days Forecast 4 Safety Stock Beginning Inventory 6 Net Production Required 7 Workers Required 8 Hired 9 Laid off 10 Actual Production Ending Inventory January February March 22 19 1010 1540 1180 =C3/2 =D3/2 =E3/2 =C11 =D11 =C3+C4-C5 =D3+D4-D5 =E3+E4-E5 =ROUNDUP(C6/0.1/22/8,0) =ROUNDUP(D6/0.1/19/8,0) =ROUNDUP(E6/0.1/21/8,0) =MAX(0, C7-B7) =MAX(0,D7-C7) =MAX(0, E7-07) =MAX(O,B7-C7) =MAX(0, C7-D7) =MAX(0,D7-E7) =ROUND(C7*C2*0.1*8,0) =ROUND(D7*D2*0.1*8,0) =ROUND(E7*E2*0.1*8,0) =C10+C5-C3 =D10+D5-D3 =E10+E5-E3 42 13 Labour 14 Inventory Hiring Layoff 17 Total Total Cost 210 310 (C10/0.1)*B13 C11*B14 =C8*B15 =C9*B16 =SUM(C13:C16) =C17+D17+E17 =(D 10/0.1)*B13 =D11*B14 =D8*B15 =D9*B16 =SUM(D13:D16) =(E 10/0.1)*B13 =E11*B14 =E8*B15 =E9*B16 =SUM(E13:E16) 16