Special Requirements
- Each job should be done by at least 2 workers
- No worker should have more than 4 jobs
- No worker should stay idle
- Workers 4, 5, and 12 cannot do jobs 8 and 13.
- Job 14 should be done by exactly 4 workers
- No fractional assignments are allowed, all assignments should be 0 or 1
You are given a data set (in Data Sheet) containing unit costs for 14 jobs and 14 workers. The costs are random between [20, 100]. Your main objective is minimizing the total cost while satisfying all the requirements. The data and the requirements are specific to your project group, so please work only on your group's data.
INSTRUCTIONS
1. Create a new sheet named "Solution" (5 pts)
2. Create necessary places for your decision variables, objective function, and constraints in the "Solution" sheet (5 pts)
3. Calculate the objective function using the correct Excel formula in the "Solution" sheet (5 pts)
4. Calculate the total number of assignments to each worker and each job in the "Solution" sheet (5 pts)
5. First solve the problem as a classical assignment problem, similar to what we have done in class (each job is assigned to only 1 worker and record the optimal cost) (20 pts)
6. For each special requirement, create the necessary formula in the "Solution" sheet (10 pts)
7. Open Solver and add the modified constraints for the special requirements (15 pts)
8. Solve your modified problem and find the optimal solution, compare your objective function with the previous one (25 pts)
9. Create a separate sheet named "Assignments". In this sheet, you should have 20 rows showing the jobs, and next to each row, you should display the list of workers who are assigned to these jobs from your optimal solution (5 pts)
General tidiness, a nice-looking worksheet, clear comments, good English (5 pts)
Workers
JOBS
1 81 87 45 88 90 25 51 42 69 36 32 52 48 48
2 62 85 74 92 89 46 24 68 79 74 29 99 25 72
3 64 71 95 93 44 23 57 73 48 59 50 87 57 91
4 37 75 25 52 51 97 97 77 89 24 31 80 68 92
5 20 74 68 38 79 35 56 92 90 22 42 65 85 39
6 36 84 66 54 51 82 48 86 36 54 98 58 80 23
7 94 24 72 70 65 77 36 22 69 91 37 100 27 87
8 39 21 57 32 64 57 96 37 62 86 28 31 58 97
9 63 98 62 36 83 95 42 98 27 62 94 29 74 59
10 100 99 32 40 91 44 61 22 69 23 33 23 95 43
11 51 27 80 44 75 30 95 41 20 25 76 34 36 38
12 83 59 35 36 90 45 89 90 42 42 85 30 52 55
13 35 48 95 27 100 75 49 27 82 65 62 81 83 87
14 25 64 75 26 98 60 63 31 80 63 99 90 46 66