Systems Design · Education · Operations Management

TERA School Operations Management System

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.

Client
The Edward Royal Academy (TERA)
Location
Sabatia, Vihiga County, Kenya
Deliverable
12-File Google Sheets OMS
Year
2025 / 2026

The Challenge

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.

No unified dashboard

Attendance, fees, staff, and inventory lived in separate, unconnected places with no single summary view.

Manual fee tracking

Fee collection was tracked manually, making it difficult to identify unpaid students or calculate collection rates quickly.

Academic data scattered

CAT and exam scores were recorded individually per teacher with no aggregated class or school-level performance view.

No inventory alerts

Stock levels for stationery, equipment, and supplies were not tracked systematically, leading to shortages discovered too late.

Four-Layer Architecture

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.

L1
Input Layer

9 Class Sheets (PP1 through Grade 7)

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.

L2
Module Layer

Finance Sheet + Operations Sheet

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.

L3
Feed Layer

Summary Tabs (Feed to Master)

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.

L4
Dashboard

Principal's Master Operations 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.

What the System Covers

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

Results Delivered

12
Linked Google Sheets files working as a single integrated system
245
Students tracked across 9 classes from PP1 to Grade 7
0
New software required — built entirely on tools the school already had
KES 3.3M+
Term fee collections tracked and monitored with auto-calculated arrears
32
Inventory items tracked with automatic low-stock and out-of-stock alerts
1
Dashboard giving the principal a complete school picture in real time

Technology Stack

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.

Google Sheets IMPORTRANGE VLOOKUP COUNTIF / SUMIF Data Validation Conditional Formatting Named Ranges Dashboard Design CBC Curriculum Alignment M-Pesa Payment Integration

Key Lessons

Interested in a similar system for your school or organisation?

I design and build operational data systems for schools, NGOs, and SMEs across East Africa.

Get In Touch View More Projects