The client, an investment firm, wanted the TresVista Team to add the functionality to toggle between Key Performance Indicators (KPIs) from different data vendors (Canalyst, Capital IQ, and Visible Alpha) to the financial model used to perform company valuations. Initially, the client sourced the data through a single data vendor (Canalyst) which limited the client’s findings and conclusions as the forecast estimation methodology varied among different vendors. Hence, the client requested a toggle functionality to leverage multiple data-sources while evaluating an investment in a public company.
To create a functionality that toggles between KPIs reported by different data vendors in a financial model template.
The model should have a seamless rollover i.e., the financial model should stay consistent when the data-sources are updated after a quarterly filing
The TresVista Team followed the following process:
The primary hurdle faced by the TresVista Team was during the process of the model rollover. One of the data vendors provided a toolkit application to update the model with quarterly filings data. However, this toolkit removed pre-existing connections to other data vendors within the model. This threatened one of the project’s primary objectives which was to facilitate a seamless model rollover between different data vendors. To resolve this issue, the TresVista Team created an Excel workbook event, a code that gets triggered whenever an Excel workbook is opened. The code first checks if the file came from the toolkit and then reinserts the data connections, if required.
Another hurdle faced was the inconsistent reporting of data across vendors. For certain tickers, the date headers were not aligned amongst the three data vendors. For example, Canalyst’s FY 2021 revenue matched Capital IQ’s FY 2020 revenue. To overcome this, the team incorporated an offset formula in the financial model itself. The formula calculated the offset in the number of quarters and added this to the date-headers
The TresVista Team provided the client with additional features like model roll-back, which allowed the client to retain work on the current model and roll-back to a previous version of the financial model template, thus ensuring no loss of data
The TresVista Team also added additional prompts for the users in case there was a lag in the data-source refresh. This can occur when quarterly results are announced, and one of the data-vendor updates their data, but others do not. The team identified a way to determine this problem and prompt the users about the data lag.
The system developed by the TresVista Team automated the process of template insertion, dataset fetch, and added additional features within the models. The TresVista Team automated the processing at the backend by pre-processing the models and saving them to cloud storage so that the users can access the processed model directly. Manually done, this process would take ~10 mins per ticker. There were ~3500 models that needed processing which translates to ~583 hours of manual processing time saved for the users.