Question

Problem 8-7 Develop a production plan and calculate the annual cost for a firm whose demand forecast is fall, 10,700; winter, 8,300; spring, 6,900; summer, 12,700. Inventory at the beginning of fall is 535 units. At the beginning of fall you currently have 35 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, $110 for each temp; layoff, $220 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 6 900 Summer 12.700 Forecast 10.700 8.300 Fall 10,700 Winter Spring 6,900 Summer Forecast 8,300 -1765 12,700 535 0 1500 Beginning inventory 10165 6900 7765 12165 Production required 20330 15530 13800 24330 Production hours required 16800 16800 16800 16800 Production hours available1 0 10 Overtime hours 0 0 Temp workers C Temp worker hours available 0 16800 16800 Total hours available 16800 16800 10165 7765 Actual production 6365 12165 -1765 Ending inventory 0 1500 2500 Workers hired 0 0 Workers laid off Fall Winter Spring Summer Straight time $ Overtime erv cot Fall Winter Spring Summer Straight time per Overtime Inventory Backorder Hiring Layoff Total $ m o Annual cost vic cal

Answer 1

A C H K L 1 Fall Winter Spring 6,900 Summer S Solver Parameters X 2 Forecast 10,700 8,300 12,700 3 Beginning inventory Production required Production hours required Production hours available Overtime hours 535 -1,765 C 1,500 SF$25 Set Objective: 4 10,165 10,065 6,900 11,200 O Max O Min O Value Of: 5 20,330 20.130 13,800 22,400 To: 0 16,800 16,800 16,800 16,800 By Changing Variable Cells: 3,330 0 SDS7:SES7,SFS13 Temp workers 9 0 0 12 Subject to the Constraints: Temp worker hours avl. 10 Total hours available 11 Actual production 12 Ending inventory 13 Workers hired 14 Workers laid off 15 16 Costs 0 0 5,760 SD$12:SFS12> 0 Add 16,800 20,130 16,800 22,560 SFS13 integer 8,400 10,065 8,400 11,280 Change 0 80 -1,765 1,500 12 Delete 0 12 Reset All 17 Straight time 18 Overtime 19 Inventory 20 Backorder 21 Hiring 22 Layoff 23 Total 24 25 26 27 $ 5 $ 8 $ 5 84,000 84,000 84,000 112,800 Make Unconstrained Variables Non-Negative 26,640 0 0 0 7,500 400 IPOPT Nonlinear Select a Solving Method: Options $10 $110 $220 0 17,650 0 0 0 1,320 Solving Method 0 0 0 2,640 Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Sele 101,650 110,640 91,500 117,160 Simplex engine for linear Solver Problems the Annual Cost = 420,950 Solve Close

FORMULAS:

		Fall	Winter	Spring	Summer
Forecast		10700	8300	6900	12700
Beginning inventory		535	=C12	=D12	=E12
Production required		=C2-C3	=D2-D3	=E2-E3	=F2-F3
Production hours required		=C4/0.5	=D4/0.5	=E4/0.5	=F4/0.5
Production hours available		=35*60*8	=35*60*8	=35*60*8	=35*60*8
Overtime hours			3330	3.01167188609594E-20
Temp workers		=B8+C13-B14	=C8+D13-C14	=D8+E13-D14	=E8+F13-E14
Temp worker hours avl.		=C8*60*8	=D8*60*8	=E8*60*8	=F8*60*8
Total hours available		=C6+C7+C9	=D6+D7+D9	=E6+E7+E9	=F6+F7+F9
Actual production		=C10*0.5	=D10*0.5	=E10*0.5	=F10*0.5
Ending inventory		=C3+C11-C2	=D3+D11-D2	=E3+E11-E2	=F3+F11-F2
Workers hired				0	12
Workers laid off			0		=F13
Costs
Straight time	5	=(C6+C9)*$B17	=(D6+D9)*$B17	=(E6+E9)*$B17	=(F6+F9)*$B17
Overtime	8	=$B18*C7	=$B18*D7	=$B18*E7	=$B18*F7
Inventory	5	=$B19*MAX(0,C12)	=$B19*MAX(0,D12)	=$B19*MAX(0,E12)	=$B19*MAX(0,F12)
Backorder	10	=$B20*MAX(0,-C12)	=$B20*MAX(0,-D12)	=$B20*MAX(0,-E12)	=$B20*MAX(0,-F12)
Hiring	110	=$B21*C13	=$B21*D13	=$B21*E13	=$B21*F13
Layoff	220	=$B22*C14	=$B22*D14	=$B22*E14	=$B22*F14
Total		=SUM(C17:C22)	=SUM(D17:D22)	=SUM(E17:E22)	=SUM(F17:F22)
				Annual Cost =	=SUM(C23:F23)