📊 Student Performance Analytics: Retaining School Reputation Through Data-Driven Intervention 

 1. Executive Summary

This project aims to address the downward trend in students' average scores from Semester 1 to Semester 2, which poses a risk of lowering university acceptance rates and damaging the school's reputation. Using Excel, raw data containing socio-economic factors and student habits was cleaned and transformed to calculate the Score Progress metric. The analysis reveals an overall average score decline of -3.5 points, heavily dominated by students in the 17-19 age group. As a solution, I recommend a focused academic intervention program for vulnerable groups, education on screen time limits at home, and optimizing the role of student guardians—specifically father figures—to boost the school's overall average performance.

2. Business Problem

• Background: The school detected indications of declining student scores in Semester 2 compared to the previous semester.

• Business Impact: If left unaddressed, this decline is feared to lower the percentage of graduates accepted into universities. This potentially damages the school's reputation in the eyes of prospective students' parents and negatively impacts new student enrollment in the following year.

• Primary Analytical Questions:

  • What external or internal factors (such as family relationships, study time, or internet usage duration) correlate most strongly with students' final scores?

  • Which group of students requires special intervention (at-risk students) to prevent them from failing the final exams?

 3. Tech Stack & Data Preparation

• Tools: Microsoft Excel (Advanced Formulas & Interactive Dashboard).

• Excel Features & Methodology:

  • Data Cleaning & Standardization: Utilizing the IFS function and Replace Function to transform categorical raw data into standardized groups (e.g., Age Group, Family Size Group).

  • Feature Engineering: Creating a Score Progress column (Formula: Avg Semester 2 - Avg Semester 1) to measure individual academic performance trends.

  • Data Aggregation: Leveraging pivot tables to group average scores based on behavioral and demographic variables.

  • Data Visualization: Building an Interactive Dashboard using KPI Cards, Bar Charts, and Slicers (Sex, Age Group, Family Relationship, The Guardian) for granular analysis.

 4. Key Insights

• Main Performance Alert: There was a decline in the academic performance of all students from an average score of 65 in Semester 1 to 62 in Semester 2, resulting in an average score progress of -3.5.

• Impact of Demographic Factors & Study Time:

  • Students with longer study times (>6 hours) consistently achieved the highest final scores (68-69), across both male and female students.

  • The most drastic score drop occurred in the 17-19 age group, with a decline of -4 points.

• Impact of Environmental Factors (Family & Guardian):

  • Students who have harmonious family relationships in the "Very Good" and "Excellent" categories and are raised by a father performed better compared to other groups. For instance, the average score reached 90 for the Excellence category and 67 for the Very Good category.

• Impact of Internet Usage:

  • Less internet usage indicates better results. Both male and female students with internet usage time of less than 2 hours achieved better score results compared to those who used it longer.

5. Business Recommendations

Based on the insights above, the following strategic recommendations are proposed to the Principal and the Counseling Team:

1. Intervention Program for Vulnerable Groups (At-Risk Students): The school must prioritize specialized counseling and academic assistance for the 17-19 age group experiencing declining scores. Assisting this specific age group in a focused manner will yield a more significant and direct impact on raising the overall school performance average.

2. Screen Time Control: Conduct education for students' parents regarding limiting non-study internet usage at home to less than 2 hours a day, given that this shorter duration directly correlates with better score achievements for both male and female students.

3. Optimizing the Role of Student Guardians (Father's Role): The school needs to encourage active parental involvement at home through partnership programs. In particular, this program needs to focus on the significance of the presence and guidance of the Father. As the data indicates, when a Father becomes the guardian, the academic scores of students tend to rise significantly.