Portfolio-Wide Scenario and Risk Analysis

Problem

An asset or portfolio manager, and/or risk officer, might be responsible for monitoring the valuations of multiple transactions, each transaction being modeled in a bespoke manner in a legacy spreadsheet. Over the period of months or years, dozens to hundreds (or more!) of these still-active spreadsheets can accumulate, with various stakeholders needing to understand various pricing, valuation and risk exposure metrics. Beyond the physical logistics, just maintaining the institutional knowledge of how these spreadsheets work can be challenging at best.

Solution

A ClearFactr-powered solution for this situation works like this:

1. The models are uploaded to ClearFactr via the Importer.
2. Ideally, key inputs and outputs for each of the models are standardized via named ranges. This allows for a generic, elegant solution involving some custom code to interact with all of the models in a standardized way via ClearFactr’s API.
3. At a desired frequency (daily, monthly, quarterly?), the custom code identifies each of the models in the portfolio, and gathers the required input data from external sources to put to the models.
4. Via the API, the models can be updated with these new valuation-affecting inputs, either of two ways:
   1. They can be permanently updated, generating new versions for other people and/or processes downstream to utilize.
   2. They can be called as calculators with the inputs, producing any number of on-the-fly scenario results with one or more output values.
5. In either case, the custom software accumulates the computed outputs from each model and produces the compiled results.

Key Results and Benefits

1. All of the model-understanding-enhancing features of ClearFactr can be utilized in a simple and consistent manner across the entire portfolio. Institutional knowledge is securely enhanced and maintained.
2. Unlike the manually-managed spreadsheet files, things are completely systemized within ClearFactr. All interacts and change operations to the models are permissioned and audited.
3. The ability to easily do the above actually incentivizes the creation of better and simpler models. Conditional, scenario-based calculations that were formerly embedded in the models can now be eliminated, making them smaller, easier to understand, and less likely to break. Operational risk is vastly reduced.