Part XI: Capstone Projects

🏫 School Management System

📋 Learning Objectives

• Design a multi-sheet relational database structure in Excel
• Use VLOOKUP, INDEX-MATCH to link data across sheets
• Build dynamic dashboards with COUNTIFS, SUMIFS
• Create a VBA UserForm for data entry with validation
• Apply conditional formatting for visual alerts

📊 Dataset Description & Sheet Structure

The workbook consists of 5 interlinked sheets plus a Dashboard sheet:

Sheet 1: Students (Master Data)

Sheet 2: Classes (Structure)

Sheet 3: Fees

Sheet 4: Attendance

Sheet 5: Results

🔧 Design — Sheet Layout Plan

Create a workbook named GFS_School_Management.xlsx with the following sheet tabs (colour-coded):

📐 Key Formulas Used

📊 Pivot Tables

1. Class-wise Student Count: Rows = Class, Columns = Section, Values = Count of StudentID
2. Fee Collection Summary: Rows = Fee Type, Columns = Status, Values = Sum of Amount
3. Monthly Attendance Summary: Rows = StudentID+Name, Columns = Month, Values = Count of "P" status
4. Subject-wise Average Marks: Rows = Class, Values = Average of each subject column

📈 Charts

• Bar Chart: Class-wise student strength (clustered by section)
• Pie Chart: Fee collection status distribution (Paid vs Pending vs Late)
• Line Chart: Monthly attendance trend per class
• Column Chart: Subject-wise average marks comparison across classes

🖥️ Dashboard Layout

The Dashboard sheet contains:

• Row 1-3: School header with logo placeholder, date, academic year
• Row 5-8: 4 KPI cards — Total Students (=COUNTA(Students!A:A)-1), Total Fee Collected, Overall Attendance %, Overall Pass %
• Row 10-25: Left — Class strength bar chart; Right — Fee status pie chart
• Row 27-42: Left — Attendance trend line chart; Right — Subject average column chart

🤖 VBA Automation — Student Data Entry Form

VBA
' === UserForm: frmStudentEntry ===
' Controls: txtStudentID, txtName, cmbClass, cmbSection,
'           txtDOB, cmbGender, txtFatherName, txtPhone, txtAddress
'           btnSave, btnClear, btnClose

Private Sub UserForm_Initialize()
    ' Auto-generate next StudentID
    Dim lastRow As Long
    lastRow = Sheets("Students").Cells(Rows.Count, 1).End(xlUp).Row
    If lastRow = 1 Then
        txtStudentID.Value = "GFS001"
    Else
        Dim lastID As String
        lastID = Sheets("Students").Cells(lastRow, 1).Value
        Dim nextNum As Long
        nextNum = CLng(Mid(lastID, 4)) + 1
        txtStudentID.Value = "GFS" & Format(nextNum, "000")
    End If
    txtStudentID.Enabled = False

    ' Populate Class dropdown
    Dim cls As Variant
    For Each cls In Array(1,2,3,4,5,6,7,8,9,10,11,12)
        cmbClass.AddItem cls
    Next
    ' Populate Section dropdown
    cmbSection.AddItem "A"
    cmbSection.AddItem "B"
    cmbSection.AddItem "C"
    ' Populate Gender
    cmbGender.AddItem "M"
    cmbGender.AddItem "F"
End Sub

Private Sub btnSave_Click()
    ' Validation
    If txtName.Value = "" Then
        MsgBox "Student Name is required!", vbExclamation
        txtName.SetFocus: Exit Sub
    End If
    If cmbClass.Value = "" Then
        MsgBox "Please select a Class!", vbExclamation
        Exit Sub
    End If
    If Not IsDate(txtDOB.Value) Then
        MsgBox "Enter valid Date of Birth (DD-MMM-YYYY)!", vbExclamation
        txtDOB.SetFocus: Exit Sub
    End If
    If Len(txtPhone.Value) <> 10 Or Not IsNumeric(txtPhone.Value) Then
        MsgBox "Enter valid 10-digit phone number!", vbExclamation
        txtPhone.SetFocus: Exit Sub
    End If

    ' Save to Students sheet
    Dim ws As Worksheet
    Set ws = Sheets("Students")
    Dim nr As Long
    nr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(nr, 1).Value = txtStudentID.Value
    ws.Cells(nr, 2).Value = txtName.Value
    ws.Cells(nr, 3).Value = CLng(cmbClass.Value)
    ws.Cells(nr, 4).Value = cmbSection.Value
    ws.Cells(nr, 5).Value = CDate(txtDOB.Value)
    ws.Cells(nr, 6).Value = cmbGender.Value
    ws.Cells(nr, 7).Value = txtFatherName.Value
    ws.Cells(nr, 8).Value = txtPhone.Value
    ws.Cells(nr, 9).Value = txtAddress.Value
    ws.Cells(nr, 10).Value = Date  ' Admission Date = Today

    MsgBox "Student " & txtName.Value & " registered successfully!" & vbCrLf & _
           "ID: " & txtStudentID.Value, vbInformation
    btnClear_Click  ' Reset form
End Sub

Private Sub btnClear_Click()
    txtName.Value = ""
    cmbClass.Value = ""
    cmbSection.Value = ""
    txtDOB.Value = ""
    cmbGender.Value = ""
    txtFatherName.Value = ""
    txtPhone.Value = ""
    txtAddress.Value = ""
    ' Regenerate next ID
    UserForm_Initialize
End Sub

📝 Step-by-Step Implementation Guide

1. Create Workbook: Open Excel → Save As GFS_School_Management.xlsm (Macro-Enabled)
2. Create Sheets: Add 6 sheets — Dashboard, Students, Classes, Fees, Attendance, Results. Colour-code each tab.
3. Set Up Students Sheet: Enter headers in Row 1. Apply Data Validation — Class (List: 1-12), Section (List: A,B,C), Gender (List: M,F). Format DOB column as Date.
4. Set Up Classes Sheet: Enter class structure. Use =COUNTIFS(Students!C:C, A2, Students!D:D, B2) for Current Strength column.
5. Set Up Fees Sheet: Add headers. Use =VLOOKUP(A2,Students!A:B,2,FALSE) in column B for auto-name lookup. Add Data Validation for Status (Paid/Pending/Late) and Payment Mode (Cash/Online/Cheque).
6. Set Up Attendance Sheet: Add headers. Use Data Validation for Status (P/A/L). Apply Conditional Formatting: P=Green, A=Red, L=Yellow.
7. Set Up Results Sheet: Add subject columns. Calculate Total with =SUM(C2:G2), Percentage with =H2/500*100, Grade using IFS formula, Rank using =RANK(H2, H$2:H$100).
8. Build Pivot Tables: Insert → PivotTable for each summary. Place on Dashboard sheet.
9. Create Charts: Insert charts from pivot data. Format with school colors.
10. Build Dashboard: Arrange KPI cards and charts. Add school header. Freeze panes at Row 4.
11. Create VBA Form: Alt+F11 → Insert → UserForm. Add controls and paste code above.
12. Add Macro Button: On Students sheet, Insert → Button → Assign Macro frmStudentEntry.Show.

✅ Final Deliverables Checklist

• Students master sheet with 50+ sample records and data validation
• Classes sheet with auto-calculated current strength
• Fees sheet with VLOOKUP-linked student names and payment tracking
• Attendance sheet with conditional formatting (P/A/L color codes)
• Results sheet with auto-calculated Total, Percentage, Grade, Rank
• Dashboard with 4 KPI cards and 4 charts
• Working VBA Student Entry Form with validation
• At least 2 Pivot Tables with slicers
• Print-ready report card format on a separate sheet

📋 Assessment Rubric

📝 Student Result Management System

📋 Learning Objectives

• Implement the CBSE 9-point grading scale using IFS
• Use RANK, PERCENTILE, and LARGE for rank generation
• Master INDEX-MATCH for flexible data retrieval
• Create a print-ready report card template
• Automate PDF report card generation with VBA

📊 Dataset Description

CBSE 9-Point Grading Scale

📐 Key Formulas

📊 Charts

• Bar Chart: Subject-wise class average marks — shows which subjects need improvement
• Pie Chart: Grade distribution — proportion of A1, A2, B1, B2, etc.
• Column Chart: Section-wise comparison (X-A vs X-B average) for each subject
• Histogram: Total marks frequency distribution (bins: 0-100, 101-200, ... 501-600)

🤖 VBA — Auto-Generate Report Cards & Export PDF

VBA
Sub GenerateAllReportCards()
    Dim wsMarks As Worksheet, wsReport As Worksheet
    Set wsMarks = Sheets("MarksEntry")
    Set wsReport = Sheets("ReportCard")

    Dim lastRow As Long
    lastRow = wsMarks.Cells(Rows.Count, 1).End(xlUp).Row
    Dim savePath As String
    savePath = ThisWorkbook.Path & "\ReportCards\"

    ' Create folder if not exists
    If Dir(savePath, vbDirectory) = "" Then MkDir savePath

    Dim i As Long
    For i = 2 To lastRow
        ' Populate Report Card
        wsReport.Range("B3").Value = wsMarks.Cells(i, 1).Value  ' RollNo
        wsReport.Range("B4").Value = wsMarks.Cells(i, 2).Value  ' Name
        wsReport.Range("B5").Value = wsMarks.Cells(i, 3).Value  ' Class

        ' Marks (fetched via INDEX-MATCH formulas already in sheet)
        wsReport.Calculate

        ' Export as PDF
        Dim fileName As String
        fileName = savePath & wsMarks.Cells(i, 1).Value & "_" & _
                   Replace(wsMarks.Cells(i, 2).Value, " ", "_") & ".pdf"

        wsReport.ExportAsFixedFormat Type:=xlTypePDF, _
            fileName:=fileName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False

        Application.StatusBar = "Generated: " & i - 1 & " of " & lastRow - 1
    Next i

    Application.StatusBar = False
    MsgBox lastRow - 1 & " Report Cards exported to:" & vbCrLf & savePath, vbInformation
End Sub

✅ Final Deliverables

• MarksEntry sheet with 50+ student records, all formulas for grades, totals, ranks
• GradeScale reference sheet with CBSE 9-point mapping
• ReportCard template sheet with INDEX-MATCH formulas, school header, and print layout
• Analysis sheet with subject-wise averages, pass/fail counts, topper lists
• 4 charts: subject average bar, grade pie, section comparison, histogram
• VBA macro to auto-generate and export PDF report cards

📋 Assessment Rubric