As admissions director for an exclusive executive MBA program which takes place on Necker Island in the Caribbean, Richard Branson must decide which applicants should receive admission offers. This is a difficult decision-making problem, as an applicant may or may not accept an admission offer. Currently, Richard is considering 30 applicants, each of which has a different probability of accepting an admission offer. Based on their academic qualifications and experience, Richard has rated each of these applicants using a value score from 1 to 10 (higher value scores represent better applicants). The data contains 30 applicants.

Based on the capacity of their facilities, Richard would like a class of 12 students. Fewer students than 12 results in under-utilized resources (empty classroom seats), but more than 12 students results in increased marginal costs. Specifically, each attending student beyond 12 incurs a cost of 20 value points. Note that an applicant will be an attending student only if he or she is admitted and he or she accepts the admission offer. Construct a spreadsheet model that computes the net value of offering admission to the top 20 students as ranked by value score. Compute net value as the sum of the value of attending students minus the costs of students beyond the capacity of 12 seats. What is the average net value obtained when offering admission to the top 20 students? (Hint: you can used the BINOM.INV function to estimate whether the candidate will accept the offer or not.)

Admissions Parameters Marginal Overage Cost 20 Student ID 1 2 3 4 5 6 7 8 9 10 Acceptance Probability 0.72 0.76 0.18 0.19 0.88 0.41 0.6 0.2 0.56 0.95 0.75 0.64 0.32 0.3 0.55 0.18 0.61 0.52 11 12 13 Value 9.47 9.31 9 8.94 8.41 8.29 8.04 7.39 7.12 6.69 6.34 6.01 5.99 5.94 5.68 5.44 4.16 4.04 3.83 3.5 3.41 3.34 3.07 3.06 3.01 2.99 2.92 1.27 0.65 0.08 14 15 0.36 16 17 18 19 20 21 22 23 24 25 26 27 28 29 0.45 0.5 0.74 0.28 0.43 0.07 0.87 0.82 0.14 0.3 0.47 30 Model Class Capacity 12