Excel is one of the world’s most widely used tools for data analysis and modeling. In many industries, Excel is used to distribute complex business rules to individual workers. While the in-memory processing of relatively small data sets is rapid in Excel, this model for distributed analysis leads to various challenges when business rules are complex and frequently updated. We explore how Spotfire provides a superior solution for both end users and modelers.
Use case: counterparty risk in banking
A bank typically lends to a large number of other banks and businesses. The credit risk incurred by the bank’s exposure to these counterparties is managed by teams of analysts who periodically update the risk profile of their assigned counterparties. The update can comprise both changed opinion and changed business rules.
Many institutions encapsulate their business rules in Excel workbooks, and associated cell formulas, named ranges, and VBA subroutines and functions. In Excel, the individual bank analyst chooses a counterparty, specifies opinion on the counterparty, and receives a summary of its calculated risk profile in the workbook.
This approach presents several challenges.
- When business rules change, new versions of the Excel workbook must be propagated to all analysts. Analysts must be sure to use the latest version.
- It is easy to make inadvertent changes to an Excel workbook. This can have unintended consequences.
- Business rules and presentation are mixed in Excel workbooks, leading to confusion and difficulty in maintenance.
- Business rules containing calculations are expressed through a complex combination of cell formulas, named ranges, and VBA subroutines and functions. This is notoriously difficult to maintain.
- It is not simple to create graphical summaries that reflect changing data, including drill-down or drill-across views, mash-up views, map charts, and other visualizations.
Spotfire: Analyst View of Counterparty Risk
In Spotfire, inputs and outputs can be designed in the Spotfire Analyst desktop application, and exposed to end consumers through the web. Importantly, Spotfire users typically combine tabular and graphical summaries of the counterparty position against the portfolio.
For example, when an analyst specifies the new risk profile of a chosen counterparty, the baseline position and the newly posited scenario are represented in tabular format.
As the counterparty scenario is specified, a graphical summary updates to show the position against the backdrop of the entire loan portfolio, as seen below. Categorical responses are shown with bar charts, in which coloring of bars indicates the separation of baseline and scenario. Numeric responses are similarly understood with bullet charts.
At the same time, the risk rating and associated scores are refreshed and displayed in a combination of tabular and graphical formats. In the chart at bottom, the crosshairs indicate both the risk rating bucket (horizontal axis) and the position in the distribution of selected score within that bucket (vertical axis).
The rules for calculating the risk rating and scores are coded in the R language, using a single script. The script stored in the Spotfire Server Library allows easy reuse and a single point of update. The script is then embedded in a Spotfire analysis through a point-and-click wizard that does not expose any code to the analysis author. Finally, the script is executed by TIBCO’s high-performance R engine, TERR. The end user experiences a snappy, seamless integration in Spotfire on the desktop or through the web.
The Spotfire counterparty analysis is accessed from the Spotfire Server Library, where user and group permissions are managed centrally. When the counterparty analysis changes, all users will get the updated analysis on opening. This dramatically reduces the risk and effort in disseminating new versions to users.
Spotfire: Modeler View of Counterparty Risk
The same approach can benefit the modeling workflow. Below, the different parameters of the counterparty risk rating model are exposed through editable controls in Spotfire. The modeler has increased the weight given to the Funding Stability question from 1 to 3.
As a result, the bar chart showing the distribution of risk ratings across a snapshot of the loan portfolio immediately updates. It is clear at a glance that while the baseline model yielded a bimodal distribution of ratings (orange), the new scenario has a far more unimodal distribution, with mode further to the high side at rating class 7. Higher ratings mean higher risk, so the scenario is a more pessimistic model.
A related visualization in Spotfire shows the migration of risk ratings between the baseline and scenario models. The position of observations below the diagonal indicates migration to more risky ratings classes. Jittering and transparency are added to reveal the density of observations in the transition blocks. In Spotfire users typically select counterparties in the clusters to investigate other characteristics that might be associated with the sensitivity to Funding Stability.
We have explored a guided analysis of counterparty risk in Spotfire, supported by business rules in a single R script. The author of the analysis does not have to see the R script. Both the analysis and the script are stored in the Spotfire Server Library, greatly reducing the challenges involved in distributing revisions of the analysis. Desktop and web users will get the latest version from the Spotfire Server Library. Web users can give scenario inputs, filter, and drill down, but cannot change the business rules.