21.
The Excel file Freshman College Data shows data for 4 years at a
large urban university. Use PivotTables to examine differences in
student high school performance and first-year retention among different
colleges at this university. What conclusions do you reach?
Based on the results obtained for the differences in high school
performance of students and the rates between different schools at the
university:
Architecture has the highest average first-year retention rate of 0.89,
representing 89%.
According to the survey, Music and Engineering have an average of
0.82, representing 82%. This shows that students in these programs are
more likely to continue into their second year than students in other
programs.
Nursing has an average first-year retention rate of 0.76, representing
76%. Business has an average first-year retention rate of 0.74,
representing 74%. Education has an average first-year retention rate of
0.70, representing 70%. Liberal Arts and of Technology have an average
first-year retention rate of 0.69, representing 69%. Health Sciences has
an average first-year retention rate of 0.68, representing 68%.
Overall, the difference between the retention rates of Freshman across
these schools is negligible.
The North Central and South Central Branch Campuses have the lowest
average first-year retention rates of 0.63, representing 63%. This
suggests that the two Campuses are likely to have higher rates of non-
second-year students than the other schools. These two Campuses must
work to address potential challenges in retaining students who study at
these locations.
Engineering has the highest average ACT score of 26.40, indicating that
students in this program have a strong academic foundation in terms of
standardized testing. The North Central Branch Campus has the lowest
average ACT score of 19.07, indicating that students with an unstable
academic background are 1.38 times more likely to have a lower
standardized test score in Engineering.
SAT scores vary between different programs, with Architecture having
the highest average SAT score of 1,199.94 and the North Central Branch
having the lowest average SAT score of 917.26. The average
Engineering SAT score is 1.31 times higher than the North Central
Branch.
The average GPA of students in the Engineering is 3.55. The average
GPA of students in Music is 3.41. The average GPA of students in the
Nursing is 3.34. The average GPA of students in the School of Business
is 3.24. The average GPA of students in Health Sciences is 3.21. The
average GPA of students in the School of Liberal Arts is 3.15. The
average GPA of students in Education is 3.10. The average GPA of
students at Technology is 2.78. The average GPA of students at the
South Central Branch is 2.71. In general, the GPA of students at the
schools is not too different.
The average GPA of students at the North Central Branch is 2.61.
Meanwhile, the GPA of students at the Architecture Branch is 3.61.
Therefore, the GPA of students at the Architecture Branch is 1.38 times
higher than that of the North Central Branch.
=> Conclusion:
The differences in student academic performance and first-year student
retention rates among junior colleges and universities are not significant.
This means that the quality of training and student retention of these
schools are quite similar.
Architecture has the highest retention rate of first-year students and
student academic performance. This shows that the school has a good
learning environment, attracts students and creates conditions for
students to achieve high academic results.
The South Central Branch School has the lowest retention rate and
student academic performance compared to other universities. This
shows that the school needs to improve its learning environment to
retain first-year students and improve the quality of training.
22. The Excel file Cell Phone Survey reports opinions of a sample of
consumers regarding the signal strength, value for the dollar, and
customer service for their cell phone carriers. Use PivotTables to find
the following:
a. the average signal strength by type of carrier
Based on the cell phone data table with the average signal strength
provided for different cell phone carriers, it can be concluded that
Verizon has the highest average signal strength of 3.80, followed by
AT&T at 3.5, T-Mobile at 3.00, Sprint at 2.8. The average signal
strength of Other networks is low at 2.7.
From this information, it can be inferred that Verizon generally provides
the strongest signal strength among the carriers mentioned, while Sprint
and Other have relatively lower average signal strength. Customers
looking for strong signal strength may prefer Verizon, while those who
prioritize other factors may still find AT&T and T-Mobile to be viable
options.
b. average value for the dollar by type of carrier and usage level
The data analysis shows that Sprint is rated the highest in terms of value
for money, with an average score of $4.67. This shows that customers
are satisfied with the quality of service that Sprint provides.
In contrast, Other received the lowest average score of $2.83, showing
that customers are not really satisfied with the price compared to the
quality of service that this provider offers.
AT&T and Verizon have average scores of $3.50 and $2.67,
respectively, placing them in the middle.
Overall, T-Mobile and Sprint are rated high in terms of value for money.
However, it is important to note that this score is only a partial view and
can vary depending on other factors such as coverage, network speed,
and accompanying services.
In terms of usage, AT&T is the only carrier that uses the High level,
with a score of $4.00. Meanwhile, Other and T-Mobile had the highest
Low usage, both at $4.00. Verizon had a relatively stable Low usage of
$3.00, while AT&T had the lowest Low usage of $2.50
Based on this data, customers tend to rate Sprint and T-Mobile highly
for value. However, customers should consider other factors such as
coverage, network speed, and the additional services each carrier offers
when making their final decision.
Sprint's Very high usage is highest at $5.00. AT&T and Other's lowest
usage is $3.00. Verizon's moderate usage is $4.00.
In summary, the average dollar value for Spint carrier type and usage
level is the highest, indicating that customers tend to use this network
because the transmission speed is quite good compared to the cost. The
remaining carrier types have a fair dollar value relative to the
transmission level. The average dollar value for Other network is the
lowest, but many customers use this network at various levels.
c. variance of perception of customer service by carrier and gender
Based on the data table, customer service differences by service provider
and gender
Sprint has the highest overall customer service satisfaction rating with a
avarage score 3.8. Verizon had the lowest overall customer service
satisfaction rating with a avarage score is 3.0
When comparing satisfaction levels by gender, for women, Sprint and T-
mobile had highest score at 4.0, while for men, Verizon had the lowest
score at 2.4
On average across all genders and telecommunications companies, men
tend to rate customer service higher than women.
Verizon had higher ratings from men 3.6 than women 2.4, indicating
gender differences in perceptions of customer service quality.
Conclude:
Sprint has the highest customer service satisfaction, both male and
female.
Verizon has the lowest customer service satisfaction, but there is a clear
difference between male and female
Both male and female customer service satisfaction by AT&T and Sprint
are almost equal
23. Call centers have high turnover rates because of the stressful
environment. The national average is approximately 50%. The director
of human resources for a large bank has compiled data about 70 former
employees at one of the bank’s call centers (see the Excel file Call
Center Data). Use PivotTables to find these statistics:
a. the average length of service for males and females in the sample
the average length of service for males is 2.01
the average length of service for female is 1.76
b. the average length of service for individuals with and without a
college degree
the average length of service for individuals with college degree is 1.52
the average length of service for individuals without a college degree is
2.06
c. the average length of service for males and females with and without
prior call center experience
the average length of service for males and females without prior call
center experience is 1.99
the average length of service for males and females with prior call center
experience is 1.75
