How we helped an Asset Management firm in building a flexible and comprehensive financial model

The Context

The client, an Asset Management firm, wanted the TresVista Team to build a custom financial model template that could generate KPI-based forecast models of all US publicly listed companies based on a ticker input and leverage financial KPIs from 2 data vendors – Capital IQ (CapIQ) and Visible Alpha (VA) into the unified financial model. The client also wanted the team to provide an option to end-users to change certain assumptions. They wanted TresVista Team to build a formula that could capture any changes that were not a part of the original template to understand how it would impact the consensus numbers.

The Objective

To assist in exploring non-macro approaches for the dynamic model template and build excel functionalities to ensure a smoother flow of the financial model.

The Approach

The TresVista Team followed the following process:

• Flatlining estimates:
  • Built a dynamic INDEX MATCH functionality that captured null values from the different databases and flatlined estimates once there was a break in consensus
• Capturing user inputs:
  • Created a ‘Default formulas’ section to use as a base for comparison of the financial model assumptions
  • Used SUBSTITUTE functionality to capture all kinds of changes made to the input formulas and extracted the same

The Challenges We Overcame

The major hurdles faced by the TresVista Team were:

• Inconsistency across Vendors: The team noticed that CapIQ and VA treat null values differently. Further, differentiating between estimates which were “0” and those which were null was challenging
• Override changes: The user could make changes in the estimate formulas in a lot of ways

The team overcame these hurdles by changing the CapIQ default settings to pull “-” for a null value to ensure consistency with VA. Additionally, the team listed the types of changes the user could input and incorporated all cases to build a dynamic formula.

Final Product (Sanitized)

The Value Add – Catalyzing the Client’s Impact

The TresVista Team assisted the client in building the custom financial model while adding various modules and making it dynamic. The model produced KPI-based forecasted financial statements based on the database estimates (Capital IQ and Visible Alpha) simply by changing the ticker name, thereby saving a lot of time and effort on the analysts’ end by avoiding duplication of work. The capturing changes made by the user aided the client by storing them as time-series data which could be used for further analysis.