The Context
The client, a Real Estate Private Equity firm, wanted to track the performance of each of their funds under different scenarios through a single dynamic model and find out the total fund commitment & investment figures along with the annual distribution forecast. This was a part of the client’s annual business plan.
The Objective
To prepare a flexible and universal fund model to track the current and future performance of each deal in the fund. Focus on the portfolio analysis and waterfall structure for each type of fund.
The Approach
The TresVista Team broke down the task and built the model section by section:
- An assumption section for the entire fund was created, which included the program size and costs, management fee assumptions, fund life extensions, exchange rate assumptions, etc.
- Built the historical, reforecast, and underwritten data dump tabs to store the cashflows for each deal which flows into the respective deal’s tab
- Created a control panel that includes the data for each deal such as deal name, date of investment, local currency, total commitment and investment size, and waterfall assumptions for each deal
- Worked on the fund summary tab, which consolidates the gross cashflows of all deals at an overall fund level and calculated the net cash flow of the fund after applying certain costs and expenses
- Post the fund returns summary tab, a detailed waterfall structure was created to calculate the total profit distribution to the key stakeholders as per the waterfall assumptions
Finally, the TresVista Team took care of other aspects such as the annual distribution forecast and created a portfolio analysis summary that displays each deal’s performance and the entire fund at both gross & net levels
The Challenges We Overcame
The challenges faced by the TresVista Team were:
- Running various combinations of scenarios related to the addition of new funds, portfolio companies, fund life extensions, etc.
- Preparing an exhaustive control panel section to incorporate different assumptions and scenarios
Ensuring the model is user friendly in terms of easy dumping of inputs
Final Product (Sanitized)
The Value Add – Catalyzing the Client’s Impact
The TresVista Team provided the client with a user-friendly and automated model that allowed the flexibility to run various funds under different combinations of scenarios. This helped the client streamline the entire process of evaluating their key funds, which is a part of their annual business plan process.
