Assigning MBA Students to Teams[1] The MBA program at State University has approximately 260 incoming students each Fall semester. These students are divided into cohorts of approximately 65 students...

Assigning MBA Students to Teams[1]

The MBA program at State University has approximately 260 incoming students each Fall semester. These students are divided into cohorts of approximately 65 students each, and the students in each cohort sit through exactly the same set of Fall courses together. Much of the work in these courses is done in teams. To ensure that the teams are comparable, the MBA Office tries to divide the students in each cohort into 14 teams so that each team has the following qualities:

• It should have four or five members.


• It should have at least one member with a CPA.


• It should have at least one member with quantitative expertise.


• It should have at least one female.


• It should have at least one minority student.


• It should have at least one international student.

The file
mbateams.xls
indicates the characteristics of the students in a particular cohort of this year's incoming class. Your job is to use the Evolutionary Solver to see if you can create teams that have all of the desired properties. It is not clear whether this will be possible — for example, there might not be enough minority students to go around — so you should create "penalties" for failing to meet the various goals, where the penalties can be different for different goals.

This is what I have so far. I am not sure how to let solver know that each group needs at least one student of each attribute. Thank you!

Extracted text: AutoSave OFF mbateams Home Insert Draw Page Layout Formulas Data Review View Developer Tree Plan Tell me Share O Comments A Insert v Σ MSFT | A^ A° ab 22 Wrap Text v Arial 10 General $ 47.24 A 0.26% Delete v A $ . Sort & Filter .00 Conditional Format Find & Analyze Data Paste U v Merge & Center v .00 Cell Launch Formatting as Table Styles Format v Select G76 fx A B C D E F G H J K M Q R S U W Y AA АВ AC AD AE AF AC 1 Student СРА? Quant? Female? Minority? Foreign? Assignment of groups Use Evolutionary solver Key to 0-1 variables Objective- minimize total number of penalit 1 = Yes 13 groups 1 1 1 OF OF 3 0 = No 4 1 1 2 4 1 1 6 1 7 6 1 2 8 1 1 2 8 1 1 1 10 9 11 10 1 2 12 11 1 2 13 12 1 14 13 1 1 2 15 14 1 16 15 17 16 1 1 OF OF 18 17 1 19 18 20 19 1 21 20 1 1 22 21 1 1 23 22 1 1 1 3 24 23 25 24 OF 26 25 27 26 1 1 2 28 27 1 1 2 29 28 1 1 1 3 30 29 1 1 2 31 30 1 1 32 31 1 OF 33 32 Total 34 33 OF 35 34 1 36 35 37 36 1 1 1 3 38 37 39 38 40 39 1 41 40 1 2 42 41 43 42 44 43 1 1 45 44 1 1 46 45 1 1 1 4 47 46 48 47 49 48 1 1 50 49 51 50 1 52 51 53 52 1 54 53 1 2 55 54 1 1 56 55 1 57 56 1 1 58 57 1 59 58 1 1 2 60 59 1 1 2 61 60 1 1 OF 62 61 1 63 62 1 1 1 3 64 63 1 1 65 64 1 66 65 67 14 16 19 15 17 68 Constraints Decision variables= integer Every student needs to be assigned DV= between 1 and 14 69 70 71 72 73 Information on Groups СРА? Quant? Female? Minority? Foreign? Used? Amount over maximum 74 Data + Ready 70% + N > P.