Q4. Company Nebula manufactures and sells the oxygen cylinder with kit (April, 2021 Covid situation in India) to different hospitals in the city. Company operates only two departments: Manufacturing and Sales department. Currently, there are 20 workers employed in the company and these workers are skilled enough that they can be rotated across both the departments. The company manager is attempting to schedule work for 20 workers. Each of these workers will work for four consecutive days; hence can be assigned to one of the following schedules, Schedule Days Worked 1 Monday-Thursday 2 Tuesday-Friday 3 Wednesday-Saturday 4 Thursday-Sunday 5 Friday-Monday 6 Saturday-Tuesday 7 Sunday-Wednesdays Each worker will be assigned for the week to either the manufacturing department or sales department. Each worker's satisfaction with his/her assignment has been provided in sheet Q4.xlsx. For instance, if worker 5 is assigned to manufacturing, he/she gives a perfect 10 rating to work schedule 3. Both the departments are operational on all days of week. The manufacturing department needs at least 6 workers each day for its proper functionality while the sales department needs at least 5 workers each day. Schedule the workers to maximize their satisfaction and meet the needs of both the departments. In case, the minimum worker need of any department does not meet, it has a penalty of 50 units associated with the shortage of one worker. Also, you will need to know which schedules satisfy the need of minimum number of workers required for different days of week. For instance, workers who start work on Monday, Friday, Saturday and Sunday (i.e. under schedule 1, 5, 6, and 7) will be available to work on Monday. Similarly, the workers who have started work on Saturday, Sunday, Monday and Tuesday (i.e. assigned under schedule 6, 7, 1 & 2) will be available to work Tuesday (As indicated the Table named Number_of_employees_on_duty_on_each_day_of_week). (Mention each possible computation steps with proper formula (in terms of better understanding) in your answer sheet, marking based on what you mentioned logically in your answer sheet) [15] on in as R L N P M D E G 1 1 2 3 4 5 1 3 7 6 Schedule 5 5 9 9 6 6 2 8 2 9 5 7 9 2 4 6 3 0 6 0 6 7 4 4 2 7 1 10 6 1 7 O 10 7 3 9 0 2 8 9 3 1 2 30 8 10 6 9 1 0 9 6 3 8 4 Sales Worker 1 2 3 4 5 6 7 8 8 9 10 11 8 4 6 9 5 9 4 S 5 4 3 10 8 6 2 1 10 5 2 3 5 3 3 2 6 8 0 1 6 1 5 5 4 Schedule . 1 10 2 7 6 1 5 3 10 10 B 4 9 7 10 2 3 8 5 7 9 1 10 8 2 4 1 7 9 1 5 5 7 3 5 4 10 10 1 2 10 6 8 6 5 5 5 10 6 2 2 1 3 6 6 7 1 8 7 4 2 1 3 12 13 14 15 16 17 18 19 20 21 22 23 2 3 9 7 2 8 3 0 8 5 7 2 2 6 4 4 7 7 0 1 4 3 10 2 9 2 2 1 1 3 3 6 5 4 10 4 8 10 3 4 4 1 2 3 4 6 6 9 8 2 5 6 3 3 7 5 9 5 9 10 13 10 15 16 17 18 19 20 7 3 5 2 9 2 2 4 6 5 7 9 7 6 4 3 0 3 5 S 3 3 2 6 6 20 7 9 10 1 10 25 18 EX D E F G H K M N R Q No. of workers 6 5 Mig need Sales need Mfg workers 1 2 Worker Mfg/Slaes Schedule Satisfaction 1 1 2 3 Sales workers Shortage 4 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Number of employees on duty on each day of week Day Workers with schedule Monday 1 S 6 7 Tuesday 2 6 7 1 Wednesda 3 7 1 2 Thursday 4 1 2 3 Friday S 2 3 4 Saturday 6 3 4 5 Sunday 7 4 5 6 3 5 4 5 6 7 6 7 8 9 42 43 44 45 10 11 12 13 14 15 16 17 - 18 19 20 46 47 48 49 50 51 52 53 Total satisfaction Penalty total Q3 Q4. Company Nebula manufactures and sells the oxygen cylinder with kit (April, 2021 Covid situation in India) to different hospitals in the city. Company operates only two departments: Manufacturing and Sales department. Currently, there are 20 workers employed in the company and these workers are skilled enough that they can be rotated across both the departments. The company manager is attempting to schedule work for 20 workers. Each of these workers will work for four consecutive days; hence can be assigned to one of the following schedules, Schedule Days Worked 1 Monday-Thursday 2 Tuesday-Friday 3 Wednesday-Saturday 4 Thursday-Sunday 5 Friday-Monday 6 Saturday-Tuesday 7 Sunday-Wednesdays Each worker will be assigned for the week to either the manufacturing department or sales department. Each worker's satisfaction with his/her assignment has been provided in sheet Q4.xlsx. For instance, if worker 5 is assigned to manufacturing, he/she gives a perfect 10 rating to work schedule 3. Both the departments are operational on all days of week. The manufacturing department needs at least 6 workers each day for its proper functionality while the sales department needs at least 5 workers each day. Schedule the workers to maximize their satisfaction and meet the needs of both the departments. In case, the minimum worker need of any department does not meet, it has a penalty of 50 units associated with the shortage of one worker. Also, you will need to know which schedules satisfy the need of minimum number of workers required for different days of week. For instance, workers who start work on Monday, Friday, Saturday and Sunday (i.e. under schedule 1, 5, 6, and 7) will be available to work on Monday. Similarly, the workers who have started work on Saturday, Sunday, Monday and Tuesday (i.e. assigned under schedule 6, 7, 1 & 2) will be available to work Tuesday (As indicated the Table named Number_of_employees_on_duty_on_each_day_of_week). (Mention each possible computation steps with proper formula (in terms of better understanding) in your answer sheet, marking based on what you mentioned logically in your answer sheet) [15] on in as R L N P M D E G 1 1 2 3 4 5 1 3 7 6 Schedule 5 5 9 9 6 6 2 8 2 9 5 7 9 2 4 6 3 0 6 0 6 7 4 4 2 7 1 10 6 1 7 O 10 7 3 9 0 2 8 9 3 1 2 30 8 10 6 9 1 0 9 6 3 8 4 Sales Worker 1 2 3 4 5 6 7 8 8 9 10 11 8 4 6 9 5 9 4 S 5 4 3 10 8 6 2 1 10 5 2 3 5 3 3 2 6 8 0 1 6 1 5 5 4 Schedule . 1 10 2 7 6 1 5 3 10 10 B 4 9 7 10 2 3 8 5 7 9 1 10 8 2 4 1 7 9 1 5 5 7 3 5 4 10 10 1 2 10 6 8 6 5 5 5 10 6 2 2 1 3 6 6 7 1 8 7 4 2 1 3 12 13 14 15 16 17 18 19 20 21 22 23 2 3 9 7 2 8 3 0 8 5 7 2 2 6 4 4 7 7 0 1 4 3 10 2 9 2 2 1 1 3 3 6 5 4 10 4 8 10 3 4 4 1 2 3 4 6 6 9 8 2 5 6 3 3 7 5 9 5 9 10 13 10 15 16 17 18 19 20 7 3 5 2 9 2 2 4 6 5 7 9 7 6 4 3 0 3 5 S 3 3 2 6 6 20 7 9 10 1 10 25 18 EX D E F G H K M N R Q No. of workers 6 5 Mig need Sales need Mfg workers 1 2 Worker Mfg/Slaes Schedule Satisfaction 1 1 2 3 Sales workers Shortage 4 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Number of employees on duty on each day of week Day Workers with schedule Monday 1 S 6 7 Tuesday 2 6 7 1 Wednesda 3 7 1 2 Thursday 4 1 2 3 Friday S 2 3 4 Saturday 6 3 4 5 Sunday 7 4 5 6 3 5 4 5 6 7 6 7 8 9 42 43 44 45 10 11 12 13 14 15 16 17 - 18 19 20 46 47 48 49 50 51 52 53 Total satisfaction Penalty total Q3