Question

Problem 8-14 (Algo) 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 are 1,010, 1,540, and 1,180, 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 per unit, storage cost is $6 per unit per month, standard pay rate is $6 per hour, overtime rate is $9 per hour, cost of stock-out is $9 per unit per month, marginal cost of subcontracting is $10 per unit, hiring and training cost is $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 the cells blank, whenever zero (0) is required. Input all values as positive values. Round your answers to the nearest whole number.) February March January 1,010 Forecast 1.540 Safety stock Beginning inventory Net production required Workers required Hired Laid of Actual production Ending inventory

Answer 1

Production schedule based on varying workforce strategy is as follows:

A B C January 22 D February 19 March 21 Working days 1,180 590 775 995 42 119 60 56 14 0 59 1008 603 4 Forecast 1,010 1,540 5 Safety Stock 505 770 6 Beginning inventory 530 506 7 Net production required 985 1804 8 Workers required 9 Hired 63 10 Laid off 11 Actual production 986 1809 12 Ending inventory 530 506 775 13 14 January February 15 Labor 6 59,136 108,528 Inventory 3,036 4,650 17 Hiring 210 2,940 13,230 18 Layoff 19 Total cost for the month 65,112 126,408 20 21 Total Cost of the plan (excluding manufacturing cost) March 60,480 3,618 0 18,290 82,388 310 273,908

EXCEL FORMULAS:

		January	February	March
Working days		22	19	21
Forecast		1010	1540	1180
Safety Stock		=C4/2	=D4/2	=E4/2
Beginning inventory		=B12	=C12	=D12
Net production required		=C4+C5-C6	=D4+D5-D6	=E4+E5-E6
Workers required	42	=ROUNDUP(C7/0.1/C2/8,0)	=ROUNDUP(D7/0.1/D2/8,0)	=ROUNDUP(E7/0.1/E2/8,0)
Hired		=MAX(0,C8-B8)	=MAX(0,D8-C8)	=MAX(0,E8-D8)
Laid off		=MAX(0,B8-C8)	=MAX(0,C8-D8)	=MAX(0,D8-E8)
Actual production		=ROUND(C2*C8*8*0.1,0)	=ROUND(D2*D8*8*0.1,0)	=ROUND(E2*E8*8*0.1,0)
Ending inventory	530	=-C4+C6+C11	=-D4+D6+D11	=-E4+E6+E11
		January	February	March
Labor	6	=C2*C8*8*$B15	=D2*D8*8*$B15	=E2*E8*8*$B15
Inventory	6	=C12*$B16	=D12*$B16	=E12*$B16
Hiring	210	=C9*$B17	=D9*$B17	=E9*$B17
Layoff	310	=C10*$B18	=D10*$B18	=E10*$B18
Total cost for the month		=SUM(C15:C18)	=SUM(D15:D18)	=SUM(E15:E18)
Total Cost of the plan (excluding manufacturing cost)				=SUM(C19:E19)