Simplifying products can actually be quite complex. This week, our Senior Product Manager, Margaret Preuss walks us through the process of creating the deceptively simple core of our credit and risk viewer, the dynamic net availability number. This number is the aggregation of thousands of lines of data that tells our credit team and each borrower what his or her available loan capital is at any given time. Seems easy, right? Below, she explains the steps involved from start to finish.
Starting at the End
The journey to any useful data visualization — or product in general — starts with asking questions. Our tech team asks themselves “Who is my customer?” “What is the goal of this product and how will it add value to customers?” and “How do we build it?”. In many ways, the beginning of the process actually focuses on the end product – having a working understanding of the final goal creates the structure for the team to move forward.
When we asked ourselves these questions five years ago, we knew that the end goal was to give our borrowers a dynamic net availability number by calculating their aggregated collateral availability, less their current loan balance and block. We wanted to summarize large quantities of previously unsorted data into a simple visualization that would help borrowers better understand their loan.
To reach the end goal — producing a borrower’s net availability — our team begins by collecting raw data from borrowers in the form of collateral reports. We lend against a wide range of assets, including accounts receivable, inventory, machinery and equipment, intellectual property, purchase orders and real estate. Unsurprisingly, these reports can come in a wide range of formats: QuickBooks files, Microsoft Excel spreadsheets, Sage files and more, which can make it difficult to sort through the data and derive meaningful insights.
Collecting collateral reports is just the beginning; adding the data to our database and standardizing it is critical to producing net availability calculations. Our system organizes collateral reports provided by the borrower into a standard TCJ format, making each report consistent across collateral types in all loans. This means that, for example, a Q3 2016 inventory report from Borrower A will be put in the same format as a Q1 2019 inventory report from Borrower B, and so on. Standardizing data makes it easier for us to pull it into configurable calculations and display meaningful information to our users.
Taking a Snapshot
Once the data for each collateral type is put into a standard format, the focus shifts to condensing data into digestible pieces that summarize the gross amount of each available asset type. Creating these snapshots of information allows us to succinctly display changes in gross asset amounts over the past week and month, as well as show trends over greater amounts of time.
Calculating the Collateral Availability
After summarizing the data into gross amount snapshots, we can easily calculate collateral availability by using the configuration set up by users for ineligible calculations and advance rates. By removing ineligible line items from the gross amount and multiplying the net by a given collateral advance rate, we can succinctly display the exact availability amount for each collateral type in a given loan.
Calculating Net Availability
The final step is to combine all of collateral availability amounts across assets used as collateral for a given loan to see the total gross availability. From here removing the block and current loan balance produces the net availability to determine if borrowers or in an over-advance or have excess room to borrow more. This single number tracked over time gives a snapshot of the health of the borrower’s loan and if we can lend more.
The dynamic net availability number untangles and summarizes large quantities of previously unsorted data into a simple visualization for borrowers. Users of our credit and risk viewer retain the ability to expand the calculation details and can always navigate to the original submitted collateral reports to gain a deeper understanding, but they are by default spared from wading through lines of information.
Margaret is our Senior Product Manager who comes to TCJ with a background in Investment Banking. She is currently managing a cross-border team to build our newest financials feature set.