Our Work: Loan Portfolio Analysis
An executive dashboard to understand loan portfolio trends
Executives at STI needed a better way to make strategic business decisions regarding their auto loan portfolio. Their silo’d data systems were getting in the way of understanding how all of the data fit into the big picture.
he end result of our work together is a system that helps their business leaders see what’s happening across their disparate systems with ease -- something that just wasn’t possible before. It also gives the entire executive team a clearer picture of what’s out of alignment in the business which allows the team to make needed adjustments in a timely manner.
About the client
STI specializes in fulfilling the loan business for auto dealers around the country. Their products are offered through dealers (indirect) or from the bank (direct). One of their goals is to maximize the return provided by balancing their loan portfolio yield based on the loan vintage (when it was originated) and yield (the weighted-average interest rate).
The company came to MB because they were struggling to bring data from many systems together and see how their return was being impacted by the vintage, or age, of the loans in the portfolio. This data was available across multiple systems in the company. They had tried to pull the data together before but it was challenging to find a common element in each data system. And to maximize the yield on the portfolio – they had to find a way to get it done.
Our team quickly determined that the best way to solve the problem was to create a robust data mart that would hold the organization data. We fast-tracked the development of a set of powerful ETL routines that would extract the data form each separate system, transform that data to a normalized view and then loaded the data into a cloud based data warehouse.
First, loan data was extracted from their in-house AS/400 database and staged into new MS SQL Server staging tables. We developed a component to clean missing and invalid data to ensure data integrity. Finally, we established a normalized data mart dimensional model. From the data mart, it was an easy step to establish a robust set of dashboards for the executive team so they could analyze their portfolio by location. This gave them quick access into many crucial new insights.