Using Business Intelligence Tools to Conduct Better Margin Analytics: A Hypothetical Case Study

Introduction

Financial products offered by the financial services industry are becoming increasingly complex. Their complexity along with the changing interest rate markets, competitive landscapes and informed customer, requires analytics that decompose margin performance. The margin component

Traditional trend or monthly profitability reports offer little insight to the explanation behind results and even less into the forecast of future results. Many organizations have migrated or are in the process of migrating to robust warehouse based platforms that allow them to measure profitability and risk at an instrument (individual loan or deposit) level. This is the first step in developing advanced analytics required to better understand margin and develop action plans to increase margin or prevent margin erosion.

The business intelligence (BI) tools available today offer the ability to analyze data on a multi-dimensional basis with an on-line capability that allows the user to view the information and data in combination of formats including tabular reporting, graphical, and scorecard views.

Architecture and Data Considerations

The information delivered to organizations is only as good as the underlying data and architecture of the platform. Regulations such as Basel II are requiring institutions to reconcile core financial and risk data. Oftentimes, organizations have data that exists but is unused or unknown to finance and performance measurement groups. A first step is to determine the type of data and information that is desired. The next step is to gather an inventory of the data available and finally produce a basic gap analysis to document additional data requirements and determine feasibility and availability. If some of the data is not readily available, it can be sourced or calculated with minimal effort. Most BI tools can directly access existing reporting data marts or utilize their own tables and schemas to enable analytics. The next step in the overall process is to architect the proper table and schema structure considering items such as security and performance.

If data exists at the individual instrument level, then a bottoms up approach will produce more accurate information. As the availability of data becomes broader, there are benefits to conducting the analysis which may produce better results. The key in using data above the instrument dimension is to determine stratification points that do not vary far from the mean. For example, at a product or account dimension if data from other sources such as asset/liability platforms is stratified from instrument data, then calculated averages for the aggregated data will provide a good basis for analysis.

Hypothetical Case Overview

For the purposes of this hypothethical case let us assume that you have taken the position of managing a line of auto loan products for the Northern Region of XYZ Bank. The annual plan for next year is well underway and you need to determine if your targets are feasible and within range. You have been given the following targets for your product group.

* Maintain a spread between 2.25%-2.40% or greater to cover allocated costs, taxes and produce a Risk-Adjusted Return on Capital (RAROC) number in the 15-18% range

* Grow balances by at least 10%-15% that may relieve some basis points on margin because of efficiencies gained by excess capacity in operations.

You have also learned that with recent credit concerns Grade C loans (credit score range 550-625), have been priced out of market or not approved at all. Your first inclination is to look at the current and past performance of the product group for the Northern Region. You review some trend reports and the following historical graphs.

The information in the graphs easily illustrates that spread has fallen in the rising rate environments due to the funding costs rising faster than yield levels. This information however is not sufficient to determine future spreads or develop action plans to affect future spreads. Factors beyond just the product dimension are required. At this point you can begin to compose a series of questions that might provide more clarity into the performance of the product you have inherited. The questions asked are:

* How have credit scores/loan grades affected spreads?

* How has the mix of loans relative to credit scores/loan grades changed over time?

* What is the relative contribution of yields, funding costs and spreads based on period of origination?

* What are recent trends (pricing, funding costs, terms, etc.)?

* Is there any data that analyzes what the results over the next 4-6 quarters might be?

Sometimes more information is not always better information, but in this case these questions raised valuable insights that provided additional value-driven analysis. As a manager, you will need the information in a timely, effective and user friendly manner.

Case Analysis

You determine that the data you require is available in a performance measurement warehouse but does not in an existing reporting data mart or reporting cube. Let us assume for this hypothetical case that a reporting data mart is created in the matter of days and you are utilizing a BI tool that allows the end user to "point to the right place" to access the data. Yes, believe it or not the technology does exist. After configuring the tool you begin your analysis. The first item you wish to analyze is the growth in balances and composition by credit grade because this will provide a basis for determining how the changes have affected the total spread. This is based on the premise that there is an inverse relationship between the credit grade and coupon-the lower the score/ grade, the higher the coupon should be. Graphs three and four provides analysis of the balance composition and spreads by credit score/grade. Graph three illustrates that the spreads have been correlated to credit grade and each grade has individually seen spread compression.

In reviewing graph four, you can validate that changes in credit standards have caused a decrease in grade C loans. Furthermore, drilling into the data and calculating the changes between the start of the analysis and current quarter, you can further determine that Grade B loans have seen the most growth while grade A loans have seen a slight decrease.

Now that you have a better understanding of the past performance, you can begin the analysis of more current results and what might be in store in the next four to six quarters. The summary of the 2007 third quarter results are as follows:

Maintaining the current spread of 2.38% would enable you to meet the spread targets established for the 2008 plan. This raises other questions:

* All other items remaining constant how will run-off of higher spread loans affect future spreads?

* What is the current mix of spread made up of relative to period of origination? (This is asked because it is apparent that spreads were higher in past periods and you want to determine how those balances currently and in the near future affect spreads.)

The next analysis you access is the 2007 third quarter results broken into the relative contributions of balances and spread based on year of origination. Graph 5 below provides that analysis. It essentially illustrates that the 2.38% third quarter spread is made up of a small amount of balances remaining from 2003 at a spread of 4.98%; 2004 balances make up about 5% of the balances and almost 10% of the spread; 2005 balances make up about 20% of the balances and 25% of the spread. In 2006 and 2007, the balance relative contribution is higher than the spread relative contribution. Those originations in 2006 and 2007 are producing spreads below the current 2.38%. This could suggest that if current spreads remain constant and projected principal reductions for those originations before 2006 continue at the same pace, further spread compression should be expected. In order to confirm this, you would need to depict onto another graph analysis of projected run-off and exit spreads versus current spread levels. Graph 6 confirms the notion of future compression as it analyzes run-off and exit spreads across the next five quarters through to the 2008 year end.

Approximately 52% of the outstanding balances are scheduled to runoff over the next 5 quarters at an average spread above that current spreads and target of 35-40 basis points.

The focus of your analysis should be on the future and the 2008 plan, but let us review the findings from your analysis thus far.

* Spreads in for all loan grades have compressed and falling over the last 4 years

* Balance compositions have also changed with the highest yielding group (Grade C) having dropped by nine-percent.

* Overall total product balances have increased.

* Spreads based on interest rate environment have not been linear and have been wider in lower rate environments and narrower in current higher environments.

* Credit spreads have also not been linear especially for lower credit scores which have been priced outside the market.

* In the most recent quarter with a drop in interest rates spreads have seen a widening trend.

* Projected run-off balances are at spreads higher than current levels.

* A little over 50% of the current outstanding balances are expected to run-off in the next five quarters.

Recaps of the goals for 2008 are to maintain or increase balances at spread levels in the 2.25% to 2.40% range. In order to maintain balances at current levels approximately $450MM in new loans need to book in the next five quarters. $500MM plus would have to book in order to meet desired growth to use some of the excess operational capacity. The next and final part of the analysis before you begin formulating strategy and action plans is to look at the results of the planning and forecasting platform for your product group. The current scenarios in the plan model leave funding rates and spreads relatively flat with expected new originations to equal expected run-off for each month going forward. There are no expected prepayment adjustments and pricing spreads are linear. After determining that the planning module has rate scenario capabilities that have been linked into the BI tool data model, you produce a graph outlining some hypothetical "what-if' scenarios. Graph seven illustrates spread outcomes with immediate and sustained rate changes.

The results of graph seven may be contradictory to the notion that increases in market rates produce narrower spreads while decreases in market rates produce widening spreads. Also, the model parameters are very static and linear which often do not reflect economic reality. Thus, you and an analyst recalibrate the planning model to first reflect changing pricing spreads and prepayment assumptions linked to changing market rates. The parameters are based on a combination of past history and assumptions from the Treasury department. After the model is recalibrated, another run is produced. Graph eight illustrates the spread outcomes with changed non-linear model assumptions.

Yet another story is told by graph 8 as it reflects how the spreads are not linear and that the spreads for the product will increase as market rates fall. It also illustrates, that with a falling rate environment as expected by your Economics Department, spread under the current assumption set will increase. The risk is that if spreads remain constant or if rates begin to rise, spread compression would be the expected result. Based on past experience and having an assumption set that is correlated to the asset/liability management assumptions, you are now readily to develop your strategy and action plans.

The strategy is simple and focuses on balance growth as well as maintaining or increasing margin. The action plan is as follows:

* After discussing with the credit risk group, you determine that a window for grade C loans in the higher credit score range will be accepted. This would allow for small to modest growth in a sector that historically has provided higher spreads. Due to the decreasing balances over the past four years, the relative contribution to total product spread has also decreased. The strategy calls for a 5-10% growth target.

* To increase spread, you instigate a new pricing model that will tier pricing based on relationships to other products and automatic payment though deposit or ACH. Those that do not meet the criteria will be given a higher rate by about 25 bps, which is still within range to competitive channels as your current pricing was at the low end of the spectrum.

* To increase balances, you will look to expand your indirect lending channels by looking for more dealerships in the area to participate. You currently are only dealing with 18% of the new auto dealerships in the region. Even a five-percent increase can have a great impact on balances.

A key point to consider here is that the action plans were a result of analyzing information and determining that past and projected results are based on factors that often are not easily changed but are driven by the market. Another logical step would be to build an action plan and strategy assumptions in the planning and forecasting tool. Make sure you test the outcomes before seeking acceptance and approval of the action plans. Not only should you configure the models with the strategy, but also to build in "worse case" assumptions.

Conclusion

Business intelligence tools do not drive themselves and are only valid if the underlying data is complete and accurate. The analysis illustrated here are typically conducted in one or multiple "offline" exercises that are often time consuming and by the time the analysis is complete, often are too late to turn information into strategy. This hypothetical case depicts how the proper architected tools and technology, along with the ability to analyze information on a non-traditional multidimensional basis, helps make decisions easier through quantified supporting analysis.

In this example, it is also obvious that the development of the action plans would require analysis into non-financial or competitive data because sometimes the financial story does not produce the answer, but provides a heuristic approach to developing actions that drive strategy. Another way to view new discoveries is creating new doors to information that may lead to the analysis of others, until multiple, possible actions and outcomes are analyzed leading to a more informed decision.