| 176 Total Unique Students | 331 Total Students Record | 80 Total Number of Customers | | --- | --- | --- |
This report covers a project where I converted Crown Hope's manual logbook records into proper Excel databases. The business runs computer and engineering training alongside sales of laptops and accessories like chargers and mouse, but had no digital system in place before this. Everything, from student fees to customer payments, was written by hand in one logbook with nothing separating the two. Since the logbook didn't clearly mark which entries were students and which were customers, I worked out the difference by looking at the amount paid, since student fees and customer purchases fell into noticeably different ranges. From there I built separate relational databases: one for students, one for customers, one for inventory, and a summary sheet pulling everything together. Alongside the logbook, students had also filled out registration forms containing their personal details, so I developed a macro to automate the entry of this information into the student database, reducing reliance on paper records and cutting down on manual retyping. The data covers October 2016 through December 2025. Once organised, the student database showed 331 payment entries, but only 176 of those were actually unique students, since many paid in installments and appeared more than once under the same name. On the customer side, there were 80 unique customers recorded for laptop and accessory purchases. The client now has a proper record system instead of a paper logbook, making it far easier going forward to track who has paid, who's still owing on an installment plan, what's been sold, and what's left in stock.
The business relied entirely on a manual paper logbook to record all payments, with no system in place to distinguish between student and customer transactions or to track partial payments made in installments. As a result, it was difficult to determine which students had fully paid, which still owed a balance, and how much had actually been collected over time. Names appearing multiple times in the logbook due to installment payments made it nearly impossible to identify actual unique students versus repeat entries, leading to unreliable records and no clear way to monitor outstanding payments or verify total enrollment numbers.
This project focuses on transforming manual logbook records into a structured Database Management System (DBMS) for a small computer sales and training business, to ensure data safety, accuracy, and long-term usability for analysis and decision-making. The primary goal was to convert messy, unstructured manual records into well-organized relational databases using Microsoft Excel. A macro was developed to automate the entry of student information from filled registration forms directly into the database, reducing reliance on paper records and improving efficiency and reliability.
| Issue Found | Decision Made |
|---|---|
| No clear separation between student and customer entries in the logbook | Categorised entries by amount paid, since student fees and customer payments fell into distinct ranges |
| Same student name appearing multiple times | Attributed to installment payments; used this to distinguish unique students (176) from total payment entries (331) |
| Student personal details existed only on paper forms | Developed a macro to transfer form data into the student database instead of manual retyping |
A five-stage pipeline using Excel was adopted to ensures reproducibility and separation of raw from analytical data.
| Stage | Key Decision |
|---|---|
| Data Collection | Gathered raw data from two sources, the manual logbook (October 2016 to December 2025) and paper registration forms filled by students |
| Data Classification | Since the logbook mixed student and customer entries with no labels, records were classified based on amount paid, as student and customer payments fell into distinct ranges |
| Database Design | Built relational databases in Excel, linked through shared identifiers, to keep raw data separate from analytical output. Six databases created: Student Data, Student Payment, Customer Data, Customer Payment, Laptop & Accessory Inventory, and Summary. |
| Automation | Developed a macro to transfer student information from registration forms directly into the student database, reducing manual retyping and transcription errors |
| Validation and Analysis | Used SUMIFS to total previous installment payments per student and determine actual amount paid versus outstanding balance owed. Applied IF formulas to flag payment status (e.g., paid in full vs owing). Used XLOOKUP to link student names and customer names to the Summary database, making it easy to track each individual's records across the system |