SESSION: A LOOK AT UCC’s FINANCIAL AID SOLUTION

Presenters: Jacob Geluk, Data Management Coordinator UCC Registrar’s OfficeDennis Mayberry, Registrar

Electronic Confirmation of Enrollment – The Challenge

Students are waiting in lineProcessing is labour intensiveProcessing is slowWe do not have an automatic audit trail of who we approved

ECE Design Considerations:

It must handle 90% of the workload without human interventionIt must be accurateIt must be simple to maintainThere must be a manual override for the inevitable exceptionsThere must be a good audit trail

ECE Platform Choice: MS Access

We used the tool I was most familiar withThe staff were familiar with MS AccessAccess is good for creating reportsLack of training in Colleague case tool – If I were to do it again I would use Envision.

ECE Diagram

ECE: Information Flow (IN)

1. Ministry ECE requests2. Student Enrollment information3. Student Program Information4. Financial Aid Institutional

Appendix information

(1) Ministry ECE Requests

Lists of students who have approved loansThe ministry needs to verify that they are enrolled as full time studentsAvailable at their FTP site as a text file, CON_008.TXT, which is updated daily

(2) Student Enrollment

Student enrollment is stored in colleagueCan be extracted from colleague using Cedarville download into a text fileSince we only need snapshots of enrollment text file extracts are a simple solution

(3) Student Program Information

As with enrollment, student program information is stored in colleagueText files are simple to useSnapshot information actually more desirable than live information here as it will remain consistent while staff verify how the automated portion of the system has flagged students

(4) Finaid Institutional Appendix

This information is maintained in the access databaseDetails of what is full time for a given programThis is the biggest weakness of using MS access - having to maintain information outside of colleague

Outputs:

1. ECE replies to ministry2. Reports for Finaid3. Activity logs

(1) Replies to Ministry

Ministry looks for a file CONR_008.TXT on their FTP site nightlyFile must meet ministry specifications and have a correct header and footer

Reports for Financial Aid

Printed reports of the details of how the applications were processedECE decision information is also archive in an access table

(3) Activity Logs

ECE information is archived in a history table in access before the CONR_008.TXT file is createdBefore a new CONR_008.TXT export file is created the previous one is archivedAll errors are logged in an error logImport details are logged in an import table

Processing ECE

Preparation of appendixImporting dataMerging dataPerforming manual overridesExporting the confirmation file

Main Menu Screen

Two Ways to Calculate Full Time

By current semester:Winter – 9 creditsSummer – 12 creditsFall – 9 credits

By terms attended E.G.Term 1 – 12 creditsTerm 2 – 14 credits

By Current Semester

By Terms Attended

Importing Data

All data is imported as text filesTables are cleared before every importMinistry file is checked for completeness

1. Header exists2. Footer exists3. Record count (ministry file has blank lines

divide by two and subtract one)

Merging Data

Student numbers are sanitized – spaces and dashes are strippedProgram information is applied to studentsFull time requirements are calculated for each student based on program infoCredits are totalled for each student based on a query against imported enrollment data

Full Time Status Is Determined

For each student their full time status requirement is compared to their eligible registered creditsStudents are flagged as to whether they meet their requirement for full time status

Students Are Flagged For Manual Intervention If:

They have more than one academic programTheir birth date or name does not match between the colleague and ministry extractsThey were flagged as a disabled studentTheir program was flagged for manual interventionThey do not meet the requirements for full time enrollment (only a staff member can say no)

ECE Manual Intervention

Before an extract for upload to the ministry can be produced all students flagged for manual processing must be checked as either:

1. Approved2. Not approved3. Deferred

Manual Processing Options

If the “send doc” flag is checked then the loan application will be sent to UCC rather than to the studentIf an amount is entered in the “pay to school” field then a cheque for that amount will be made out to UCC directly

Manual Processing Screen

Export File To Ministry

The previous export file (CONR_008.Txt) is archivedA new export file (CONR_008.Txt) is createdThe CONR_008.Txt file will automatically be transferred to the ministry FTP server that evening

Results:

No line-ups at the financial aid officeStaff are freed up to do other workGood audit trailVery few calls for maintenance assistance

Questions ?