Question

Develop a production plan and calculate the annual cost for a firm whose demand forecast is fall, 11,000; winter, 7,700; spring, 6,700; summer, 13,000. Inventory at the beginning of fall is 550 units. At the beginning of fall you currently have 30 workers, but you plan to hire temporary workers at the beginning of summer and lay them off at the end of summer. In addition, you have negotiated with the union an option to use the regular workforce on overtime during winter or spring only if overtime is necessary to prevent stockouts at the end of those quarters. Overtime is not available during the fall. Relevant costs are hiring, $90 for each temp; layoff, $180 for each worker laid off; inventory holding, $5 per unit-quarter; backorder, $10 per unit; straight time, $5 per hour; overtime, $8 per hour. Assume that the productivity is 0.5 unit per worker hour, with eight hours per day and 60 days per season. In each quarter, produce to the full output of your regular workforce, even if that results in excess production. In Winter and Spring, use overtime only if needed to meet the production required in that quarter. Do not use overtime to build excess inventory in prior seasons expressly for the purpose of reducing the number of temp workers in Summer. (Leave no cells blank - be certain to enter "0" wherever required. Negative values should be indicated by a minus sign. Round up "Number of temp workers, Workers hired and Workers laid off" to the next whole number and all other answers to the nearest whole number.)

	Fall Winter Spring Summer Forecast 11,000 7,700 6,700 13,000 Beginning inventory Production required Production hours required Production hours available Overtime hours Temp workers Temp worker hours available Total hours available Actual production Ending inventory Workers hired Workers laid off

	Fall Winter Spring Summer Straight time Overtime Inventory Backorder Hiring Layoff Total Annual cost

Answer 1

A B C D E F 1 N 3 4 5 6 7 00 9 10 11 12 13 Fall Winter Spring Summer Forecast 11000 7700 6700 13000 Beginning inventory 550 =C13 =D13 =E13 Production required =C3-C4 =D3-04 =E3-E4 =F3-F4 Production hours required =C5/0.5 =D5/0.5 =E5/0.5 =F5/0.5 Production hours available =8*60*30 =8*60*30 =8*60*30 =8*60*30 Overtime hours 0 =MAX(0,06-D7) =MAX(0,E6-E7) 0 Temp workers 0 0 0 =ROUNDUP((F6-F7)/(8*60),0) Temp worker hours available =C9*8*60 =D9*8*60 =E9*8*60 =F9*8*60 Total hours available =C7+C8+C10 =D7+D8+D10 =E7+E8+E10 =F7+F8+F10 Actual production =C11*0.5 =D11*0.5 =E11*0.5 =F11*0.5 Ending inventory =C4+C12-C3 =D4+D12-D3 =E4+E12-E3 =F4+F12-F3 Workers hired 0 0 0 =F9 Workers laid off 0 0 0 =F14 Qtr-1 Qtr-2 Qtr-3 Qtr-4 Straight time =(C7+C10)*5 =(D7+D10)*5 =(E7+E10)*5 =(F7+F10)*5 Overtime =C8*8 =D8*8 =E8*8 =F8*8 Inventory =MAX(0,013)*5 =MAX(0,013)*5 =MAX(0,E13) *5 =MAX(0,F13)*5 Backorder =MAX(0,-C13)*10 =MAX(0,-D13)*10 =MAX(0,-E13)*10 =MAX(0,-F13)*10 Hiring =C14*90 D14*90 =E14*90 =F14*90 Layoff C15*180 =D15*180 =E15 180 F15*180 Total =SUM(C17:C22) =SUM(D17:022) =SUM(E17:E22) =SUM(F17:F22) Annual cost =SUM(C23:F23) 14 15 16 17 18 19 20 21 22 23 24

	Fall	Winter	Spring	Summer
Forecast	11,000	7,700	6,700	13,000
Beginning inventory	550	-3,250	0	500
Production required	10,450	10,950	6,700	12,500
Production hours required	20,900	21,900	13,400	25,000
Production hours available	14,400	14,400	14,400	14,400
Overtime hours	0	7,500	0	0
Temp workers	0	0	0	23
Temp worker hours available	0	0	0	11,040
Total hours available	14,400	21,900	14,400	25,440
Actual production	7,200	10,950	7,200	12,720
Ending inventory	-3,250	0	500	220
Workers hired	0	0	0	23
Workers laid off	0	0	0	23
	Qtr-1	Qtr-2	Qtr-3	Qtr-4
Straight time	$72,000	$72,000	$72,000	$127,200
Overtime	$0	$60,000	$0	$0
Inventory	$0	$0	$2,500	$1,100
Backorder	$32,500	$0	$0	$0
Hiring	$0	$0	$0	$2,070
Layoff	$0	$0	$0	$4,140
Total	$104,500	$132,000	$74,500	$134,510
Annual cost				$445,510