The Student & Parent Loan Programs department in Student Treasury rebuilt their Partners in Education with Parents (PEP) application to replace their decommissioned application, and they approached the Data and Analytics (D&A) team for a solution to provide strong operational reporting capabilities for this data from a single source.
“When I came in we had the reporting part, all these Access databases, it was all over the place,” Geoff Winkler, Supervisor in WashU Loan Reporting and Collections, said. “But now, it’s kind of like we have a central location, a central ODS (Operational Data Store) warehouse, where everything is and we can access it quickly. We’re not pulling from this, pulling from this, pulling from this. We have something that brings it all together.”
PEP loans are offered exclusively through the PEP application and are a type of student loan unique to WashU, made with WashU funds, and not associated with federal loans.
What exactly is a PEP loan?
Very often, parents try to fund education in partnership with their students, and WashU helps this effort by providing the option of a PEP loan. If parents sign on to this loan option, any tuition or housing cost increases over the course of the loan are absorbed by WashU, providing the parents with some savings at the end of their student’s time at WashU.
In addition to PEP loans WashU has other types of traditional student loans, and they are administered by a third party called University Accounting Service (UAS). The Student & Parent Loan Programs department manages all the loans, and currently the non-PEP loans are managed by the Student & Parent Loan Programs department using a mix of Microsoft Access databases and Excel reporting.
In the future, all loans will be administered through the PEP application – at which point, it will be called the Institutional Loan Management System (ILMS). With that in mind, and since the solution contains the data for all loans, WashU has chosen to use ILMS as the primary name for the solution.
Another requirement from the Student & Parent Loan Programs department to the D&A team was for a solution to manage their monthly UAS interactions and produce reporting on all WashU loans, with the understanding that their current use of Microsoft Access and Excel was not a scalable and sustainable option.
According to Kelli Lively, Director of Student and Parent Loan Programs, the use of Microsoft Access and Excel was made out of necessity as an interim solution when their old system needed to be upgraded.
The solution the D&A team implemented helped create a more succinct and scalable process, dramatically reducing the time spent producing reports and eliminating manual efforts with automation. The work led to transitioning some reports into Cognos with specific data from the greater Student Information System (SIS) dataset, providing the Student & Parent Loan Programs department with everything they need to see in less time.
“Creating efficiency, that’s what’s important,” Lively said. “When we finish our accounting and reporting work each month, we can then shift our attention to other responsibilities like working with our internal partners to develop new programs, educating undergraduate and graduate students prior to taking a loan, ensuring preparedness to begin repayment, and assisting when ‘life happens’ at any time during repayment.”
The solution the D&A team designed consists of three main parts, together addressing all the needs of the Student & Parent Loan Programs department:
- A PEP-centric datamart (ILMS datamart) that supports extensive reporting on PEP application data.
- A database that functions as an ODS and contains all the UAS, SIS, and Workday Finance interactions, additional translated data elements, and data values needed, with automated processes loading all monthly data.
- A detailed and business-focused Cognos package on top of both data areas (along with tie-ins to UAS, SIS, and Workday Finance data), allowing for easy interaction and report generation.
The D&A team also delivered complex and carefully formed Cognos reports to allow for fast adoption of the product, and for the Student & Parent Loan Programs department to hit the ground running with this solution.
“In 2019, as Chancellor Martin was leading us into our long-term strategic planning process, he emphasized the importance of data-driven decision making,” Lively said. “For quite a while it has felt like, even though we’re in Financial Services, we have struggled to put our hands on the right data and to connect data across the various systems we work in each day. This database and reporting tool provides our team with the ability to slice and dice data for ourselves in order to make business-informed and data-driven decisions.”
The potential savings in time and effort for the Student & Parent Loan Programs department are tremendous.
For example, monthly reporting manipulating fields and cells in Excel spreadsheets that has consumed the first weeks of each month is now completed within a day or two. Instead of doing copy and paste work to create their own analysis, this solution takes that effort and generates critical reports in much less time.
The D&A team is helping to boost the Student & Parent Loan Programs department’s productivity by scaling their business for the future.