A D G M Formula Bar D 0 Points Earred Comments 2 6 7 22 Points Available the data below is a slightly modified version of what we used in 1988 1 Convert the range of apicant data ta an excel table and sort by date moved from oldest to newest 1 In "Admit Earty' calumn, put "YES" If the person is to be admitted based on carty admesan crter (SAT>1450 and GPA above 3.8) and appication date. "NO" atherate 1 In "Reject Early column, put "YES" if the person is to be rejected based on carty rejection criteria (SAT 300 or GPA 2) and application date ("NO" if after 1/1), 'NO' otherwise 2 Calculate the cumulative score; for International, score-SAT+GPA Multipier; for US, score-1.1"/SAT-GPA Multipleritat ustment for US: use vlookup to add adjustment points based on family income 1 1 In "Final Decision" columni, wrile "Aumi'y the score is above or equal to threshold and the appication was received by final deadline: put "Not Admirollerwise 4 Count the number of US and International Students admitted and their SAT state in the Summary Data table 2 Find the number of early and regular admits in different SAT bracket from 0 to 1200, from 1200 to 1300. etc Hint: Use "s="cell reference in criteria 2 Apply conditional tamatting: fil the ID column cels with grecen if the person is to admitted Below, heat the data as population rather than sample 5 Calculate the measures describing the final score distrbution 1 Calculate the correlation coefficient of fainiy income and SAT and farrily income and final Store 1 Calculate the requared for linear relationships between family income and SAT and famiy income and final Score 1 Intrepret the meaning of the requared values for the two relationships above Admission Criteria Standard Early Admit Early Reject SAT 1450 BOD GPA 3.80 2.00 Input Area Threshold Score 3600 Multiplier 600 Early Ap. Deadline 1/1/21 Final Deadline 2/1/21 Summary Data Deseription Early Admit; Reg. Admit * Average SAT Highest SAT Lowest SAT US International SAT brokets 0 1200 1300 1400 1500 Income Adjustment Income D 25000 50000 7500 Adjustment 150 100 50 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 3G 37 38 39 40 41 42 43 44 45 46 47 48 49 Admit Early Reject Early Score Final Decision SAT 1403 874 1163 1225 1545 1397 B55 725 1341 950 1529 1245 785 1645 1555 1510 1305 1280 1491 1486 ID Date Recolved Family Income Residence State 2020036 11/15/20 82733 US ME 2020040 11/15/20 31985 US NY 2020330 11/30/20 53245 US NY 2020335 11/30/20 64353 Us INH 2020345 11/30/20 92046 LIS NH 2020350 11/20/20 78611 lus IMA 2020356 11/30/20 43005 us NH 2020395 11/30/20 38429 Us MA 3090 2020485 12/1/20 78123 US MA 2020515 12/1/20 51373 US INY 2020520 12/1/20 82929 US CT 2020600 12/14/20 56520 LS NY 2020610 12/14/20 43094 us ME ME 2020690 12/15/20 81851 Us ME 2020700 12/15/20 81768 Us MA 2020700 12/15/20 88000 US INH 2020730 12/15/20 60446 Us IME 2020730 12/15/20 61555 Us IMA 2020825 12/15/20 75511 us INH 2020025 12/15/20 71005 US NH Instructions TSLA_forecast Optimization Ammortization GPA 3.73 3.26 3.53 3.58 4.ca 3.32 3.29 2.42 3.61 2.60 3.63 3.50 2.22 3.79 4.00 4.00 3.50 3.50 3.45 3.60 Measure Score Mean Variance Standard Dev Interquartie range lower bound (quartile 1) Interquartie nga upper bound (quartie 3) 95th percentile 99th parcentile Sest value Third highest Percent Rank of Score-1000 Valus Measure Come latlan (score, Famic) Correlation (SAT.Famine) R-squared (Score, Farinel Recuared (SAT,Farrinc) Coronapivot Coronadata Applicants Ready WU A D G M Formula Bar D 0 Points Earred Comments 2 6 7 22 Points Available the data below is a slightly modified version of what we used in 1988 1 Convert the range of apicant data ta an excel table and sort by date moved from oldest to newest 1 In "Admit Earty' calumn, put "YES" If the person is to be admitted based on carty admesan crter (SAT>1450 and GPA above 3.8) and appication date. "NO" atherate 1 In "Reject Early column, put "YES" if the person is to be rejected based on carty rejection criteria (SAT 300 or GPA 2) and application date ("NO" if after 1/1), 'NO' otherwise 2 Calculate the cumulative score; for International, score-SAT+GPA Multipier; for US, score-1.1"/SAT-GPA Multipleritat ustment for US: use vlookup to add adjustment points based on family income 1 1 In "Final Decision" columni, wrile "Aumi'y the score is above or equal to threshold and the appication was received by final deadline: put "Not Admirollerwise 4 Count the number of US and International Students admitted and their SAT state in the Summary Data table 2 Find the number of early and regular admits in different SAT bracket from 0 to 1200, from 1200 to 1300. etc Hint: Use "s="cell reference in criteria 2 Apply conditional tamatting: fil the ID column cels with grecen if the person is to admitted Below, heat the data as population rather than sample 5 Calculate the measures describing the final score distrbution 1 Calculate the correlation coefficient of fainiy income and SAT and farrily income and final Store 1 Calculate the requared for linear relationships between family income and SAT and famiy income and final Score 1 Intrepret the meaning of the requared values for the two relationships above Admission Criteria Standard Early Admit Early Reject SAT 1450 BOD GPA 3.80 2.00 Input Area Threshold Score 3600 Multiplier 600 Early Ap. Deadline 1/1/21 Final Deadline 2/1/21 Summary Data Deseription Early Admit; Reg. Admit * Average SAT Highest SAT Lowest SAT US International SAT brokets 0 1200 1300 1400 1500 Income Adjustment Income D 25000 50000 7500 Adjustment 150 100 50 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 3G 37 38 39 40 41 42 43 44 45 46 47 48 49 Admit Early Reject Early Score Final Decision SAT 1403 874 1163 1225 1545 1397 B55 725 1341 950 1529 1245 785 1645 1555 1510 1305 1280 1491 1486 ID Date Recolved Family Income Residence State 2020036 11/15/20 82733 US ME 2020040 11/15/20 31985 US NY 2020330 11/30/20 53245 US NY 2020335 11/30/20 64353 Us INH 2020345 11/30/20 92046 LIS NH 2020350 11/20/20 78611 lus IMA 2020356 11/30/20 43005 us NH 2020395 11/30/20 38429 Us MA 3090 2020485 12/1/20 78123 US MA 2020515 12/1/20 51373 US INY 2020520 12/1/20 82929 US CT 2020600 12/14/20 56520 LS NY 2020610 12/14/20 43094 us ME ME 2020690 12/15/20 81851 Us ME 2020700 12/15/20 81768 Us MA 2020700 12/15/20 88000 US INH 2020730 12/15/20 60446 Us IME 2020730 12/15/20 61555 Us IMA 2020825 12/15/20 75511 us INH 2020025 12/15/20 71005 US NH Instructions TSLA_forecast Optimization Ammortization GPA 3.73 3.26 3.53 3.58 4.ca 3.32 3.29 2.42 3.61 2.60 3.63 3.50 2.22 3.79 4.00 4.00 3.50 3.50 3.45 3.60 Measure Score Mean Variance Standard Dev Interquartie range lower bound (quartile 1) Interquartie nga upper bound (quartie 3) 95th percentile 99th parcentile Sest value Third highest Percent Rank of Score-1000 Valus Measure Come latlan (score, Famic) Correlation (SAT.Famine) R-squared (Score, Farinel Recuared (SAT,Farrinc) Coronapivot Coronadata Applicants Ready WU