A fully linked, 12-file Google Sheets system with a four-layer architecture — automating attendance tracking, fee collection, academic performance monitoring, staff management, and inventory control for The Edward Royal Academy in Vihiga County, Kenya.
The Edward Royal Academy is a growing CBC-aligned primary school serving students from PP1 through Grade 7. Like many Kenyan schools operating at this scale, the school was managing its operations through a combination of physical registers, manual records, and disconnected spreadsheets. There was no unified view of attendance, fees, or performance.
The principal needed a system that could give a real-time picture of the school at a glance — without expensive software, without IT support, and without requiring teachers to learn complex new tools.
Attendance, fees, staff, and inventory lived in separate, unconnected places with no single summary view.
Fee collection was tracked manually, making it difficult to identify unpaid students or calculate collection rates quickly.
CAT and exam scores were recorded individually per teacher with no aggregated class or school-level performance view.
Stock levels for stationery, equipment, and supplies were not tracked systematically, leading to shortages discovered too late.
The system was designed as a four-layer hierarchy where data flows upward automatically using IMPORTRANGE formulas. Teachers and staff only ever update their own designated sheet. The principal sees everything aggregated in real time on the Master Dashboard without touching any underlying data.
Each class teacher updates daily attendance (P/A/L/E), student register, and academic scores (CAT1 + CAT2 + Exam) for their class only. Auto-calculates totals and grades.
The Finance Sheet manages fee structure configuration, M-Pesa payment tracking, and arrears flagging. The Operations Sheet manages the staff register, weekly CBC timetable, inventory tracking, and leave records.
Each module sheet contains a dedicated summary tab that consolidates key KPIs into a clean, structured range ready for IMPORTRANGE pickup by the Master Dashboard.
A single live dashboard pulling from all 11 underlying files. Displays attendance by class, fee collection rates, staff status, inventory alerts, academic snapshots, and prioritised action alerts — updated automatically every time any source sheet changes.
| Module | Key Features | Auto-Calculated |
|---|---|---|
| Attendance | Daily register per class (P/A/L/E), 48 school days tracked per term, parent notification flag | Present count, absent count, attendance rate per class and school-wide |
| Fee Collection | Fee structure by class, M-Pesa code and receipt tracking, installment recording, arrears flagging | Balance, collection rate, paid/partial/unpaid status per student |
| Academic Performance | 8 subjects tracked (CAT1 + CAT2 + Exam = 100), grade boundaries A to E | Total marks, average %, overall grade, students below 50% flagged |
| Staff Management | Full staff register with roles, departments, employment type, TSC number, emergency contacts | Teaching vs support staff counts, leave tracking, staff on leave today |
| Inventory | 32 tracked items across stationery, equipment, ICT, sports, furniture, cleaning categories | Stock status (OK / Low / Out of Stock), reorder alerts, inventory value in KES |
| Timetable | Weekly CBC-aligned timetable for all 9 classes, all periods Monday through Friday | Period coverage tracking, free periods flagged |
| Master Dashboard | Live KPI panels, class-by-class breakdown, action alerts with priority levels and target dates | Everything — pulls automatically from all 11 source sheets via IMPORTRANGE |
The entire system was built using tools already available to the school at zero additional cost. The design principle was maximum impact with minimum complexity.
I design and build operational data systems for schools, NGOs, and SMEs across East Africa.
Get In Touch View More Projects