Please be neat and show all work. A graduating senior has becn accepted hy three
ID: 2817560 • Letter: P
Question
Please be neat and show all work.
A graduating senior has becn accepted hy three universities for an M.S. in engineering Two criteria have been identified. The first is the program and university's academic ranking. The second is the cost. A third criteria of location was initially considered, but re only made urrently enrolled in the first university, which is rated r academic rank and a 10 for cost. The second is a larger out-of-state public university, which is rated as an 8 for academic rank and a 6 for cost. The third is a prestigious private school, which is rated as a 10 academically and a 3 for its higher cost then the student recognized that it is only for about a year, and applications we to acceptable schools. The student is c as a 5 fo Use Excel to (a) Calculate the total score for each school if the two objectives have the same weight? Use (b) If academic rank has a weight of 75%, what is the total score for each school? (6 points)Explanation / Answer
In order to proceed with this solution, lets tabulate the data provided for the 3 universities for both the scenarios with different weightages for the two criteria (academic rank and cost). We shall use the same terminology for naming the universities as used in the question (First University, Second University and Third University).
The Total score for any university will be calculated as follows:
Total Score = Weight for Academic Rank * Academic Rank + Weight for Cost Rank * Cost Rank
Using the above formula, the data and results for all the universities in both the scenarios are tabulated below:
Answer a.
Scenario 1 – Equal Objective Weightage
Sr. No.
Universities
Academic Rank
Cost Rank
Total Score (RA*WA+RC*WC)
Rank (RA)
Weight (WA)
Rank (RC)
Weight (WC)
1
First University
5
50%
10
50%
7.50
2
Second University
8
50%
6
50%
7.00
3
Third University
10
50%
3
50%
6.50
Answer b.
Scenario 2 – Higher Objective Weightage for Academic Rank
Sr. No.
Universities
Academic Rank
Cost Rank
Total Score (RA*WA+RC*WC)
Rank (RA)
Weight (WA)
Rank (RC)
Weight (WC)
1
First University
5
75%
10
25%
6.25
2
Second University
8
75%
6
25%
7.50
3
Third University
10
75%
3
25%
8.25
It can be seen that while in the first scenario, the total score is just an average of the academic and cost ranks, in case of the second scenario the higher weightage for the academic rank boosts the total score of universities with a relatively better academic rank. As a result, while the First University has the highest total score in Scenario 1, the Third University has the highest total score in Scenario 2.
Scenario 1 – Equal Objective Weightage
Sr. No.
Universities
Academic Rank
Cost Rank
Total Score (RA*WA+RC*WC)
Rank (RA)
Weight (WA)
Rank (RC)
Weight (WC)
1
First University
5
50%
10
50%
7.50
2
Second University
8
50%
6
50%
7.00
3
Third University
10
50%
3
50%
6.50