Learner Management Reporting Tool

Problem:
 

The client required a streamlined system for managing and reporting on learner data, which was previously compiled using Excel spreadsheets. This manual process resulted in several inefficiencies, including:
 

• Incorrect ID number capturing, leading to data inaccuracies.

​

• No duplicate detection, causing reporting errors.

​

• Manual merging of over 12,000 records across multiple sheets, which was time-consuming.

​

• Lack of data triangulation, as the learner sheets changed throughout the year without a unified update process.

​

• Data capturers not following quality assurance protocols, leading to inaccurate or lost physical records.

​

• Manual entry of date of birth, age, and gender, which slowed down the data processing and affected time-sensitive targets.

​

• Delayed detection of learners dropping out due to late submission of termination letters.

​

• Loss of physical records with no reconciliation process in place.
   

Solution:

We optimized the client's existing Excel-based system, addressing their preference for familiar tools while introducing several critical enhancements:

​

• Centralized workbook: We consolidated multiple sheets into one workbook, shared via OneDrive, allowing for real-time collaboration and improved version control.

​

• ID number validation system: An integrated validator was added to automatically detect incorrect ID numbers during data entry, reducing capture errors to zero.

​

• Quality assurance checklist: Built directly into the workbook, this provided data capturers with clear guidelines, minimizing errors and improving data integrity.

​

• Automated data calculations: Gender, age, and date of birth were now automatically calculated from valid ID numbers, streamlining the process.

​

• Power Query: Used to merge all learner data into one combined listing, allowing for automatic updating and reporting with the click of a button.

​

• Duplicate detection and dropout tracking: The new system cross-referenced learner data with a dropout database, helping identify and report dropouts before they were officially submitted.

​

• Batch system for physical submissions: This introduced accurate tracking of physical records and connected them to digital submissions.

​

• SharePoint integration: Physical evidence and documentation were uploaded to SharePoint, linked directly to learner records in the workbook, allowing easy access and document retrieval with one click.

​

• Names, Surnames and ID Number Verification was done through the Department of Home Affairs database through a separate intervention.

​

Tools Used:

​

• Microsoft Excel

• SharePoint

• PowerApps

• Office 365

• Power Query

• Department of Home Affairs database verification

​

Results:

​

• Accurate reporting: Significantly improved data accuracy and quality, with zero errors in ID number capture.

​

• Faster data capture and validation: Automated processes sped up data entry and ensured more reliable quality assurance.

​

• Real-time auditor responses: Reports and documents are now instantly accessible via the app, allowing for quick and efficient responses to auditor requests.

​

• Centralized data: Collaboration improved, and manual tasks like merging sheets or verifying duplicates became instantaneous, enhancing overall efficiency.

​