could you please help me modelling a problem in excel to form a balanced group in open solver.i need help in Excel please help me I need urgently please help me.

Case Study: Creating Balanced Syndicate Groups K particular during their group work, students often fail harnessing cultural diversity for their learning in such groups. The research in this area has shown that a proper support and a proper environment needs to be established in order for students to reap all the potential benefits of cultural diversity. While at xyz, students are often allocated into syndicate groups to work together on various projects, which are subsequently assessed as a group work. Ideally, an allocation of students into groups should support and encourage harnessing cultural diversity. To achieve this the groups formed should maximise the diversity of members within groups and minimise the average differences between groups. Groups formed on this basis are called "balanced groups". Creating balanced groups for a large class can be a very tedious and time-consuming administrative task if that process is carried out manually using a trial and error approach. For this reason, it would be good to construct a system that would produce balanced groups at a "touch of a button". Your task for this case study will be to prepare the model that will be able to perform automatic allocation of students into groups while satisfying as much as possible these ideal targets: Groups should be of the same size if possible. The ideal size is 5 members per group. 2. Groups should be balanced with respect to gender (G). 3. Groups should be balanced with respect to quantitative background (Q). Groups should be balanced with respect to ethnic background but in a very specific way. According to the most up-to-date research on harnessing cultural diversity: 1 Quantitative Background (0) WN 4 5 7 9 AAA9999 & NASA & Cultural Student ID(S) Nationality (N) Gender G Background (C) 101 Saudi Arabian Middle East Female 102 Saudi Arabian Middle East Male 103 Romanian Eastern Europe Female 104 British British Female 105 British British Male 106 Chinese East Asia Male 107 British British Male 108 Chinese East Asia Female British British British British Chinese East Asia Female Chinese East Asia Male Spanish Western Europe Female Chinese East Asia Male Saudi Arabian Middle East Vale German Western Europe British Female Lithuanian Eastern Europe Russian Eastern Europe Male Chinese East Asia Female Chinese East Asia Na e Mongolian East Asia Chinese East Asia Female British Female Indian South Asia Vietnamise East Asia Hranian Middle East Turkish Middle East Female Indian South Asia Female Fast N = = ==== Case Study - Balanced Groups sheet1. Do bonus no no no Syndicate Groups Groups should be of the same size if possible. The ideal size is 5 members per group. Groups should be balanced with respect to gender (G). Groups should be balanced with respect to quantitative background (0). Groups should be balanced with respect to ethnic background but in a very specific way. According to the most up-to-date research on harnessing cultural diversity. Ideally, each international (non-British) student should be paired in the same group with another international student of the same nationality (N). If the above is not possible to achieve, then the next best thing is to pair international students based on the same cultural background (). Pairing based on the nationality or cultural background is not important to achieve only for home (British) students. The data provided to you contains the records for 45 students with the relevant characteristics for each one of them given through the five columns: Student ID (S), Nationality (N), Cultural Background (C). Gender (G) and Quantitative Background (Q.). To solve the problem, feel free to use any of the techniques (or any mix of the techniques we learned in Decision Models Also, feel free to transform any of the raw data provided to you before you use them in your model, if you think that any such transformation will have more positive than negative effects to the overall quality of your solution. You will need to produce: a model, the final solution obtained from the model, a brief justification of any assumptions you made in the process, and a brief explanation as for what you have managed to achieve with your solution. Your model should be flexible enough to be feasible and to produce a reasonable solution for any other set of 45 students (that may have very different characteristics from the ones given), but it does not have to flexible to work well for any other number of students. 118 Lithuanian Russian Chinese Chinese Mongolian Chinese British Male Male Female Male Male Female Female Male 5.5 128 Vietnamise lanlan Torkish Indian Vietnamise Vietnamise Female Female Female Eastern Europe Eastern Europe East Asia East Asia East Asia East Asia British South Asia East Asia Middle East Middle East South Asia East Asia East Asia East Asia East Asia South Asia East Asia British Eastern Europe East Asia Eastern Europe British East Asia East Asia East Asia East Asia Eastern Europe Chinese Female Male Female Female Chinese British Bulgarian Japanese Russian British Chinese Female Male Male Female = Female Chinese Female Female Female Case Study: Creating Balanced Syndicate Groups K particular during their group work, students often fail harnessing cultural diversity for their learning in such groups. The research in this area has shown that a proper support and a proper environment needs to be established in order for students to reap all the potential benefits of cultural diversity. While at xyz, students are often allocated into syndicate groups to work together on various projects, which are subsequently assessed as a group work. Ideally, an allocation of students into groups should support and encourage harnessing cultural diversity. To achieve this the groups formed should maximise the diversity of members within groups and minimise the average differences between groups. Groups formed on this basis are called "balanced groups". Creating balanced groups for a large class can be a very tedious and time-consuming administrative task if that process is carried out manually using a trial and error approach. For this reason, it would be good to construct a system that would produce balanced groups at a "touch of a button". Your task for this case study will be to prepare the model that will be able to perform automatic allocation of students into groups while satisfying as much as possible these ideal targets: Groups should be of the same size if possible. The ideal size is 5 members per group. 2. Groups should be balanced with respect to gender (G). 3. Groups should be balanced with respect to quantitative background (Q). Groups should be balanced with respect to ethnic background but in a very specific way. According to the most up-to-date research on harnessing cultural diversity: 1 Quantitative Background (0) WN 4 5 7 9 AAA9999 & NASA & Cultural Student ID(S) Nationality (N) Gender G Background (C) 101 Saudi Arabian Middle East Female 102 Saudi Arabian Middle East Male 103 Romanian Eastern Europe Female 104 British British Female 105 British British Male 106 Chinese East Asia Male 107 British British Male 108 Chinese East Asia Female British British British British Chinese East Asia Female Chinese East Asia Male Spanish Western Europe Female Chinese East Asia Male Saudi Arabian Middle East Vale German Western Europe British Female Lithuanian Eastern Europe Russian Eastern Europe Male Chinese East Asia Female Chinese East Asia Na e Mongolian East Asia Chinese East Asia Female British Female Indian South Asia Vietnamise East Asia Hranian Middle East Turkish Middle East Female Indian South Asia Female Fast N = = ==== Case Study - Balanced Groups sheet1. Do bonus no no no Syndicate Groups Groups should be of the same size if possible. The ideal size is 5 members per group. Groups should be balanced with respect to gender (G). Groups should be balanced with respect to quantitative background (0). Groups should be balanced with respect to ethnic background but in a very specific way. According to the most up-to-date research on harnessing cultural diversity. Ideally, each international (non-British) student should be paired in the same group with another international student of the same nationality (N). If the above is not possible to achieve, then the next best thing is to pair international students based on the same cultural background (). Pairing based on the nationality or cultural background is not important to achieve only for home (British) students. The data provided to you contains the records for 45 students with the relevant characteristics for each one of them given through the five columns: Student ID (S), Nationality (N), Cultural Background (C). Gender (G) and Quantitative Background (Q.). To solve the problem, feel free to use any of the techniques (or any mix of the techniques we learned in Decision Models Also, feel free to transform any of the raw data provided to you before you use them in your model, if you think that any such transformation will have more positive than negative effects to the overall quality of your solution. You will need to produce: a model, the final solution obtained from the model, a brief justification of any assumptions you made in the process, and a brief explanation as for what you have managed to achieve with your solution. Your model should be flexible enough to be feasible and to produce a reasonable solution for any other set of 45 students (that may have very different characteristics from the ones given), but it does not have to flexible to work well for any other number of students. 118 Lithuanian Russian Chinese Chinese Mongolian Chinese British Male Male Female Male Male Female Female Male 5.5 128 Vietnamise lanlan Torkish Indian Vietnamise Vietnamise Female Female Female Eastern Europe Eastern Europe East Asia East Asia East Asia East Asia British South Asia East Asia Middle East Middle East South Asia East Asia East Asia East Asia East Asia South Asia East Asia British Eastern Europe East Asia Eastern Europe British East Asia East Asia East Asia East Asia Eastern Europe Chinese Female Male Female Female Chinese British Bulgarian Japanese Russian British Chinese Female Male Male Female = Female Chinese Female Female Female