Question

Question # 3. New York Tech maintains a powerful mainframe computer for research use by its faculty, students, and research assistants. During all working hours, an operator must be available to operate and maintain the computer, as well as to perform some programming services. Bernie Hartman, the director of the computer facility, oversees the operation. It is now the beginning of the fall semester, and Bernie is confronted with the problem of assigning different working hours to his operators. Because all the operators are currently enrolled in the university, they are available to work only a limited number of hours each day. There are six operators (four undergraduate students and two graduate students). They all have different wage rates because of differences in their experience with computers and their programming ability. The following table shows their wage rates, along with the maximum number of hours that each can work each day. [25 points) Maximum Hours of Availability Operators Wage Rate Mon Tue Wed Thurs. Fri. K.C. $10.00/hour D.H. $10.10/hour H.B. $9.90/hour $9.80/hour $10.80/hour N. K. $11.30/hour 6 2 + 3.3k/s 6 6 0 4 5 3 0 0 6 8 5 0 0 25.9K/S 6 0 4 5 3 0 0 6 0 0 8 6 0 4 5 0 S.C. KS. Each operator is guaranteed a certain minimum number of hours per week that will maintain adequate knowledge of the operation. This level is set arbitrarily at 8 hours per week for the undergraduate students (K. C., D. H., H. B., and S. C.) and 7 hours per week for the graduate students (K. S. and N. K.). The computer facility is to be open for operation from 8 am to 10 pm (i.e., 14 hours of work needed daily) Monday through Friday with exactly one operator on duty during these hours. On Saturdays and Sundays, the computer is to be operated by other staff. Because of a tight budget, Bernie has to minimize cost. He wishes to determine the number of hours he should assign to each operator on each day. Formulate and solve a spreadsheet model for this problem.

Please describe the process of excel

Answer 1

Sol:

Decision Variables:

Let, Xij = number of hours operator I is assigned to work on day j

Where,

i = 1, 2,…, 6 for the operators K.C., D.H., H.B., S.C., K.S., N.K. respectively

j = 1, 2,..,5 for the days Mon. to Fri. respectively

Subject To:

The objective of the problem is to minimize total cost of labor:

Min. Z = $25(X11 + X13 + X15) + $26(X22 + X24) + $24(X31 + X32 + X33+X35) +

$23(X41 + X42 + X43 + X45) + $28(X51 + X53 + X54) + 30(X64 + X65)

Subject to:

Number of hours per day availability of operators:

Operator	Mon	Tue	Wed	Thus	Fri
K.C.	X11 <= 6		X13 <=6		X <= 6
D.H.	-	X22 <= 6	-	X24 <= 6
H.B.	X31 <= 4	X32 <= 8	X33 <= 4		X35 <= 4
S.C.	X41 <=5	X42 <= 5	X43 <= 5		X45 <= 5
K.S.	X51 <= 3		X53 <= 3	X54 <= 8
N.K.				X64 <= 6	X66 <= 2

Minimum Number of hours guaranteed for each operator:

Operator
K.C.	X11 + X13 + X15 >= 8
D.H.	X22 + X24 >= 8
H.B.	X31 + X32 + X33+X35 >= 8
S.C.	X41 + X42 + X43 + X45 >= 8
K.S.	X51 + X53 + X54 >= 7
N.K.	X64 + X65 >= 7

Number of hours the lab is open:

Day
Mon	X11 + X31 + X41 + X51 = 14
Tues	X22 + X32 + X42 = 14
Wed	X13 + X33 + X43+X53 = 14
Thus	X24 + X44 + X54 = 14
Fri	X15 + X35 + X45 + X65 = 14

Non-negativity Constraint: all Xij >= 0

H I J Solver Parameters MIN. HOURS C D E F G NUMBER OF WORKING HOURS PER DAY (Xij) Mon Tue Wed Thus Fri 2 0 3 0 4 0 2 0 6 0 4 7 4. 0 4 CONSTRAINT 9]>= 8 Set Objective: $C$14 8 8>= 19]>= To: Max Min O Value Of: 0 8 5 5 5 0 5 20 >= 8 A B 1 2 Operator Cost 3 K.C. $25 4 D.H. $26 5 H.B. $24 6 S.C. $23 7 K.S. $28 8 N.K. $30 9 WORKING HOURS 10 PER DAY 11 CONSTRAINT 12 13 Cost per day 14 Cost for week 3 0 2 2 0 71>= 7 By Changing Variable Cells: SC$3:$G$8 0 0 0 1 7]>= 7 6 14 14 14 14 14 = = = = 14 14 14 14 14 Subject to the Constraints: SC$3:$C$8 <= $C$18:$C$23 SC$9:$G$9 = $C$11:$G$11 $D$3:$D$8 <= $D$18:$D$23 SE$3:SE$8 < = $E$18:$E$23 $F$3:$F$8 <= $F$18:$F$23 SG$3:$G$8 <= $G$18:$G$23 SH$3:SH$8 >= $J$3:$J$8 $345 $335 $392 $341 $342 $1,755 Objective Function 15 16 17 Operator 18 K.C. 19 D.H. AVAILABILITY OF OPERATORS CONSTRAINT Mon Tue Wed Thus Fri 6 0 6 0 6 0 6 0 6 0 4 8 4 0 4 5 5 5 0 5 3 0 3 8 0 Make Unconstrained Variables Non-Negative 20 H.B. 21 S.C. Select a Solving Method: Simplex LP 22 K.S. 23 N.K. 0 0 0 6 2 24

Н I J MIN. HOURS CONSTRAINT =SUM(C3:G3) >= 8 =SUM(C4:G4) >= 8 =SUM(C5:45) >= 8 =SUM(C6:G6) >= 8 =SUM(C7:67) >= 17 =SUMC8:68) >= 7 = = = А B C D E F G 1 NUMBER OF WORKING HOURS PER DAY (Xij) 2 Operato Cost Mon Tue Wed Thus Fri 3 K.C. 25 2 10 3 0 4 4 D.H. 26 0 2 0 6 0 5 H.B. 24 4 7 4 0 4 6 S.C. 23 5 5 5 0 5 7 K.S. 28 3 0 2 2 0 8 N.K. 30 0 0 0 6 1 9 WORKING HOURS =SUM(C3:08) =SUM(D3 =SUM(E3:58) =SUM(F3: =SUM(G3:58) 10 PER DAY = 11 CONSTRAINT 14 14 14 14 14 12 13 Cost per day I=SUMPRODUCT(C3:C8,$B$3:$B$8) =SUMPRC=SUMPRODISUMPRESUMPRODUCT(G3:58,$B$3:$B$8) 14 Cost for week =SUM(C13:13) 15 16 AVAILABILITY OF OPERATORS CONSTRAINT 17 Operato Mon Tue Wed Thus Fri 18 K.C. 6 0 6 0 6 19 D.H. 0 6 10 6 0 20 H.B. 4 8 4 0 4 21 S.C. 5 5 5 0 5 Objective Function 22 K.S. 3 0 3 0 8 6 23 N.K. 0 0 0 2 24

Optimal Solution:

	NUMBER OF WORKING HOURS PER DAY (Xij)	Hours per week
Operator	Mon	Tue	Wed	Thus	Fri
K.C.	2	0	3	0	4	9
D.H.	0	2	0	6	0	8
H.B.	4	7	4	0	4	19
S.C.	5	5	5	0	5	20
K.S.	3	0	2	2	0	7
N.K.	0	0	0	6	1	7

Total Weekly Cost = $1,755

If you Satisfy with Answer, Please give me "Thumb Up". It is very useful for me.
Thank you for your support.