Question

Bob Carlton's golf camp estimates the following workforce requirements for its services over the next two years: Quarter 1 2 3 4 5 6 7 8 Demand (hrs) 4,400 6,500 3,200 5,200 4,500 6,300 3,800 4,900 Each certified instructor puts in 480 hours per quarter regular time and can work an additional 120 hours overtime. Regular-time wages and benefits cost Carlton $7,200 per employee per quarter for regular time worked up to 480 hours, with an overtime cost of $20 per hour. Unused regular time for certified instructors is paid at $15 per hour. There is no cost for unused overtime capacity. The cost of hiring, training, and certifying a new employee is $10,000. Layoff costs are $4,000 per employee. Currently 8 employees work in this capacity. a. Find a workforce plan using the level strategy that allows for no delay in service. It should rely only on overtime and the minimum amount of undertime necessary. What is the total cost of the plan? $ 708,400. (Enter your response as an integer.) b. Use a chase strategy that varies the workforce level without using overtime or undertime. What is the total cost of this plan? $ 838,000. (Enter your response as an integer.) c. Consider the following proposed plan for a different demand scenario. In this case, each certified instructor puts in 480 hours per quarter regular time. All other cost and capacity values are the same as above. Quarter Workforce Overtime (hours) Hires Layoffs 1,120 00 voor AWN- Demand (hours) 4,200 6,400 3,000 4,800 4,400 6,240 3,600 4,800 480 80 960 - 11 2 2 480 3,120 Total 5 4 Total cost for this plan would be . (Enter your response as an integer.)

Answer 1

a)

Level Strategy

E F G H I J K L M S Solver Parameters 1 2 3 4 5 6 7 8 Total Cost/ category Category cost 20 44,800 А B C D 1 Level strategy Quarter 3 Demand (hours) 4,400 6,500 4. Regular time 4,400 5,280 5 Overtime O 1,220 Service Delay 0 0 7 Undertime 8800 8 Overtime capacity remaining 1,320 100 9 # of employees 8 11 11 10 Hired 0 11 Fired 0 0 12 13 14 Set Objective: $M$14 To: Max Min value of: O By Changing Variable Cells: SC54:5J55,5C510:50511 Subject to the Constraints: SCS6:5J56 = 0 SCS7:$J$8 >= 0 $C$10:5C511 = integer 6 Add 3,2005,200 3,200 5,200 0 0 0 0 2,080 80 1,320 1,320 11 11 0 0 0 0 4,500 6,300 3,800 4,500 5,280 3,800 0 1,020 0 0 0 0 780 O 1,480 1,320 300 1,320 11 11 11 0 0 0 0 0 0 4,900 38,800 4,900 36,560 0 2,240 0 0 380 5,680 1,320 8,320 11 88 0 0 0 Change Delete 7,200 10,000 4,000 633,600 30,000 0 | Reset All Make Unconstrained variables Non-Negative Total Cost 708,400 Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems. 19 20 21 Solve Close

EXCEL FORMULAS:

Cell	Formula	Copy to
C6	=C3-SUM(C4:C5)	C6:J6
C7	=480*C9-C4	C7:J7
C8	=120*C9-C5	C8:J8
C9	=B9+C10-C11	C9:J9
K3	=SUM(C3:J3)	K3:K11
M5	=K5*L5	M5:M11
M14	=SUM(M3:M11)

---------------------------------------------------------

b)

Chase strategy

---------------------------------------------------------

c)

Monthly demand given in the beginning of the question is different than the demand in the table in part (c)

Cost of plan is shown for both demands

Based on demand in beginning of the question:

B C D E F G H I J K L M N 1 Mixed strategy 2 Quarter Total Cost/ category Category cost 20 79,200 3 Demand (hours) 4 Regular time 5 Overtime 6 Service Delay 7 Undertime 8 Overtime capacity remaining 9 # of employees 10 Hired 11 Fired 12 13 4,400 4,320 80 0 0 1,000 9 6,500 5,280 1,220 0 0 100 11 3,2005,200 3,200 4,320 0 880 0 0 1,120 0 1,080 200 9 9 0 2 0 4,500 4,320 180 0 0 900 9 0 0 6,300 5,280 1,020 0 0 300 11 3,800 4,900 3,800 4,320 0 580 0 0 5200 1,080 500 9 9 0 0 2 O 38,800 34,840 3,960 0 1,640 5,160 76 2 2 0 7,200 10,000 4,000 547,200 50,000 16,000 0 4 Total Cost 692,400

Based on demand in part (c):

B C D E F G H I J K L M N 1 Mixed strategy 2 4 Quarter 5 6 Category 7 8 Total Cost/ category | cost 20 | 62,400 3 Demand (hours) 4 Regular time 5 Overtime 6 Service Delay 7 Undertime 8 Overtime capacity remaining 9 # of employees 10 Hired 11 Fired 12 13 4,200 6,400 4,200 5,280 0 1,120 0 0 1200 1,080 9 11 0 3,000 4,800 3,000 4,320 0 480 0 0 1,320 0 1,080 600 9 9 0 O 4,400 6,240 4,320 5,280 80 960 0 0 0 0 1,000 9 11 0 0 0 3,600 4,800 37,440 3,600 4,320 34,320 0 480 3,120 0 0 7200 2,160 1,080 600 6,000 9 9 76 0 0 0 4 200 360 2 2 7,200 10,000 4,000 547,200 50,000 16,000 0 2 2 Total Cost 675,600