STUDENT PERFORMANCE TRACKING
SYSTEM
Project Documentation — Phases 1 to 4
Cambridge · Edexcel · IB · National Curriculum Support
Prepared for: Terry Akukulong
Target Market: Secondary schools across Africa
Document Date: 14 July 2026
Table of Contents
TOC \h \o "1-2"
Project Overview
This document records the design and build decisions made for the Student Performance Tracking
System, a commercial-grade product intended to serve Cambridge, Pearson Edexcel, IB, and national-
curriculum secondary schools across Africa. The project began as an advanced Microsoft Excel
workbook, with a clear migration path to a web and mobile application in later phases.
Development is being run in ten phases. This document covers Phases 1 through 4, which established
requirements, the underlying data model, the working Excel MVP, and the role-based dashboards
(Teacher, Admin, and a printable Report Card).
Roadmap Reference
● Phase 1 — Requirements Analysis
● Phase 2 — Database Design
● Phase 3 — Excel Workbook Structure
● Phase 4 — Dashboard Design
● Phase 5 — Formulas and Automation (next)
● Phase 6 — Reporting
● Phase 7 — VBA Programming
● Phase 8 — Migration to a Web Application
● Phase 9 — Mobile App Development
● Phase 10 — Deployment and Commercialization
Phase 1: Requirements Analysis
Before any construction began, the curricula the product must support were compared, since grading
structure differs meaningfully between them.
Curriculum Comparison
System Grading Key Quirk
Cambridge (IGCSE/A- Component-weighted (coursework
A*–U or 9–1
Level) % + exam %)
Similar to Cambridge, different
Pearson Edexcel 9–1 or A*–E
boundaries
IB (MYP/DP) 1–7 scale Criterion-based, not simple averages
National (varies by Wide variation — Cameroon,
Often /20 or /100
country) Nigeria, Ghana, Kenya all differ
Key Design Decision: Configurable Grading Scheme
Grading logic could not be hardcoded into formulas. Instead, grade boundaries and component
weightings were made into data tables (GradingScheme and GradeBoundaries), configurable per subject
and per school. This single decision is what allows the same workbook to serve every curriculum above
without redesign.
Approved Assessment Components (per subject, configurable)
Component Default Weight
Exam 30%
Individual Student Work 15%
Laboratory Practicals (science subjects) 15%
Book Report 10%
Presentation 10%
Classwork 8%
Worksheet 5%
Homework 4%
Class Participation 3%
Note: not every subject uses all nine components. Each subject's active components and weights are
defined independently in the GradingScheme sheet, and must sum to exactly 100%. A live check flags
any subject that does not.
Non-Functional Requirements
● Must handle 500–2,000 students without performance loss (Power Query / Power Pivot
readiness).
● Must be safely editable by non-technical teachers (locked formulas, dropdown-only entry).
● Must include error-checking so a broken reference or bad weight is caught immediately.
● Must produce a report card professional enough to hand to a parent.
Go-to-Market Decision
The product will be built and piloted for a single school first, but every table includes a SchoolID-ready
structure so it can migrate cleanly into a true multi-tenant web platform in Phase 8 without redesigning
the data model.
Phase 2: Database Design
The workbook is structured the same way a relational database would be — every sheet is a table,
linked by IDs, with one row per fact rather than wide columns per test. This is what makes the eventual
migration to Phase 8 a straight data export rather than a redesign.
Core Entities
Entity Key Fields Purpose
StudentID, Name, Class, Stream, House,
Students Core student record
Parent info
Subjects SubjectID, Name, Curriculum Master subject list
Teachers /
TeacherID, Class, Stream, SubjectID, Term Staffing and timetable links
TeacherAssignments
GradingScheme SubjectID, Component, Weight% Configurable per-subject weighting
GradeBoundaries MinScore, MaxScore, Grade Editable grade bands per curriculum
StudentID, SubjectID, Term, Component,
Scores One row per raw mark entered
Score, MaxScore
StudentID, SubjectID, Term, Calculated, one row per
Grades
FinalPercentage, Grade student/subject/term
Attendance StudentID, Date, Status Daily attendance log
StudentID, Date, Type, Description,
Behaviour Reward/demerit log
RecordedBy
Why 'One Row Per Score'
Rather than one column per test, every mark is its own row (StudentID + SubjectID + Term +
Component). This allows Power Query and Power Pivot to aggregate cleanly regardless of how many
assessments a teacher enters, and mirrors exactly how a SQL database will store the same data later.
Phase 3: Excel Workbook Structure
The Excel MVP was built with sample data for 12 students across two classes (Grade 8 and Grade 9,
streams A/B) and 12 subjects, to prove the mechanics before scaling to real school data.
Sheet Inventory
Sheet Type Editable? Purpose
README Reference No Instructions and colour legend
Lists Reference No Dropdown source lists
Subjects Master data Yes All subjects offered
Component weights per subject,
GradingScheme Master data Yes
with live 100% check
GradeBoundarie
Master data Yes Grade letter bands
s
Students Master data Yes Student bio and parent info
Raw marks — the only place
Scores Transaction Yes
teachers type scores
Auto-computed final % and grade
Grades Calculated No
per student/subject/term
Daily log plus auto attendance %
Attendance Transaction Yes
summary
Behaviour Transaction Yes Reward / demerit log
Class averages, pass rate, top
Dashboard Calculated No
performers, at-risk list, charts
Design and Usability Features
● Dropdown-validated entry for every categorical field (gender, class, stream, house, term,
component, status).
● Navy-and-gold brand styling, colour-coded sheet tabs, zebra-striped rows, frozen header rows,
hidden gridlines.
● Conditional formatting: red/yellow/green heatmaps on percentages, red flags on at-risk
students and demerits, green on rewards and strong grades.
● A live weight-check table on GradingScheme that flags any subject whose components do not
sum to 100%.
● KPI cards and two charts (bar chart of class averages, pie chart of pass/fail) on the Dashboard.
Verification
The workbook was recalculated using a full formula engine after every change; the current build carries
1,793 formulas with zero calculation errors, and every KPI value was independently checked against the
underlying sample data.
Phase 4: Dashboard Design
Phase 4 added role-based views on top of the Phase 3 data model, plus the master data needed to
support them: a Teachers sheet and a TeacherAssignments sheet linking staff to classes, subjects, and
terms.
Teacher Dashboard
● Teacher selector (dropdown) — shows the selected teacher's name and email, and every
class/subject/term they are assigned to.
● Class Roster & Marks Overview — select a Class, Stream, and Subject to see a live, colour-
heatmapped list of final percentage, grade, and attendance % for every matching student.
Administrator Dashboard
● KPI cards: Total Students, Total Teachers, Total Subjects, Total Classes.
● Management overview tables: teacher list, class sizes, subjects offered.
● Data Integrity Checks panel — automatically flags grading-weight errors and orphaned
Student/Subject IDs in the Scores sheet (all currently reporting OK).
● Backup & Import/Export guidance (one-click automation arrives in Phase 7 with VBA).
Report Card (Printable)
● Student and Term selectors auto-populate name, class, stream, house, and date of birth.
● Subject-by-subject performance table with colour-scaled percentages and letter grades.
● Overall average, overall grade, class rank (e.g. '7 of 12'), and attendance %.
● Auto-generated teacher remark based on performance band, plus an editable free-text remarks
field.
● Signature lines for Class Teacher and Principal; print area and page-fit already configured for
clean one-page printing.
Verification
All three dashboards were checked against known sample data: the Admin KPIs correctly report 12
students, 4 teachers, 12 subjects, and 2 classes; the Teacher Dashboard correctly filters rosters by
class/stream/subject; and the Report Card correctly computed a 66.7% overall average, Grade C, and
rank 7 of 12 for the sample student used to test it.
Status and Next Steps
Phases 1–4 are complete and tested. The workbook is a functioning MVP suitable for piloting with real
student data at a single school, while remaining structurally ready for multi-school and web/mobile
migration.
Immediate Next Phase
Phase 5 (Formulas and Automation) will tighten remaining manual formulas, add data-entry safeguards
such as duplicate-ID detection and score-exceeds-maximum flags, and lay the groundwork that Phase 7's
VBA automation will build on.