Question

Plan production for a four-month period: February through May. For February and March, you should produce to exact demand forecast. For April and May, you should use overtime and inventory with a stable workforce; stable means that the number of workers needed for March will be held constant through May. However, government constraints put a maximum of 5,000 hours of overtime labor per month in April and May (zero overtime in February and March). If demand exceeds supply, then backorders occur. There are 100 workers on January 31. You are given the following demand forecast: February, 83,328; March, 67,200; April, 100,080; May, 40,080 Productivity is four units per worker hour, eight hours per day, 21 days per month Assume zero inventory on February 1. Costs are hiring, $51 per new worker, layoff $71 per worker laid off; inventory holding, $10 per unit-month; straight-time labor, $10 per hour; overtime, $15 per hour; backorder, $20 per unit. Develop a production plan and calculate the total cost of this plan. Note: Assume any layoffs occur at beginning of next month. (Leave no cells blank be certain to enter "o" wherever required. Negative values should be indicated by a minus sign Round your answers to the nearest whole number.)

Answer 1

Solution is generated using Excel Solver as follows:

F G February March April Ma 2 Forecast 3 Beginning invento 4 Production required 5 Production hours required 6 Regular workforce 7 Regular production 8 Overtime hours 9 Overtime production 10 Total production 11Ending invento 12 Ending backorders 13 Workers hired 14 Workers laid off 83,328 Days per month Hours per day 21 67,200100,080 40,080 83,328 67,200100,080 52,960 Productivity 20,83216,800 25,02013,40(units per worker-hour) 100 100 67,200 5,000 20,000 100 100 124 83,328 Excel Formulas 67,200 67,200 0C3-B11 C4 C2-C3+B12 83,328,87,20067,200 C5-C4/SI$4 14,240| C6-BG+C13-C14 12,880 24 24 С 10-С74C9 C18 C8 SB18 February March Ma ri 17 Straight time ($/hr 18 Overtime ($/hr) 19 Invento 20 Backorder 21 Hirin 22 Layoff 23 Total 24 Balance 25 26 Total Cost 10 208,320 168,000 168,000 168,000C19-C11*SB19 75,000 C20-c12*$820 15 10 20 51 0142,400 C21 -C13 *SB21 01 257,600 0 C22 -C14 SB22 0 C23-SUM(C17:C22) 01 C24-811-B12-C2+C10-C11+C12 1,224 1,704 209,544 169,704500,60010,40Copy the above formulas upto May month. (column F) F26 SUM(C23:F23) 1,190,248

Solver parameters:

SSolver Parameters SFS26| Set Objective: O Max ⓔMin ○ Value Of: 0 0: By Changing Variable Cells: SCS13:SDS14,SES8:SFS8,SCS11:SFS12 Subject to the Constraints: SFS11 >= 0 SFS12 0 SES8:5FS8 <= 5000 SCS24:SFS24 0 CS13:SDS14-integer Add Change Delete Reset All Make Unconstrained Variables Non-Negative Simplex LP Select a Solving Method: Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. Solve Close

After creating the excel model using the given formulas and data, and entering the solver parameters, Click Solve button to generate optimal solution:

EXCEL FORMULAS:

Excel Formulas:

C3 =B11

C4 =C2-C3+B12

C5 =C4/$I$4

C6 =B6+C13-C14

C7 =C6*$I$2*$I$3*$I$4

C9 =C8*$I$4

C10 =C7+C9

C17 =$B17*C6*$I$2*$I$3

C18 =C8*$B18

C19 =C11*$B19

C20 =C12*$B20

C21 =C13*$B21

C22 =C14*$B22

C23 =SUM(C17:C22)

C24 =B11-B12-C2+C10-C11+C12

Copy the above formulas upto May month. (column F)

F26 =SUM(C23:F23)

Total cost = $ 1,190,248